Mysql key_buffer = 1024M
- Auteur de la discussion Recif
- Date de début
T'es en 64 bits ou 32 bits ?
Si t'es en 32, ça pourrait être une piste qu'avec les autres variables tu dépasses tes 4G dispo.
T'as vraiment 1G d'INDEX MyISAM ?
Tes logs disent quoi ? Car bon, normalement c'est eux qui te donnent la solution
T'es en quelle version de MySQL ?
Si t'es en 32, ça pourrait être une piste qu'avec les autres variables tu dépasses tes 4G dispo.
T'as vraiment 1G d'INDEX MyISAM ?
Tes logs disent quoi ? Car bon, normalement c'est eux qui te donnent la solution
T'es en quelle version de MySQL ?
Merci.
Alors... Le premier lien l'utilitaire s'arrête après quelques ligne puis plus
Et le second lien j'ai une erreur à l'execution:
Alors... Le premier lien l'utilitaire s'arrête après quelques ligne puis plus
Code:
ERROR 1317 (70100) at line 1: Query execution was interrupted
ERROR 1317 (70100) at line 1: Query execution was interrupted
ERROR 1317 (70100) at line 1: Query execution was interruptedEt le second lien j'ai une erreur à l'execution:
Code:
Command line calculator 'bc' not found!C'est quoi ton serveur pour que bc soit pas installé ?
apt-get install bc
Pastebin nous ça :
Si t'as pas curl d'installé installe le :
apt-get install curl
apt-get install bc
Pastebin nous ça :
Code:
cd /etc/mysql/ && wget http://day32.com/MySQL/tuning-primer.sh && chmod a+x tuning-primer.sh
./tuning-primer.sh > recif
cat recif | curl -F 'text=<-' http://vpaste.net/apt-get install curl
Bah ça te dit clairement que t'as 11Go de RAM sur ta machine et que t'as configuré MySQL pour en utiliser au max 33Go...
Je t'ai demandé le pastebin complet du résultat de tuning primer Pas juste ce truc
Bref t'as 11Go de RAM et pas 24
Files nous donc le résultat complet de ta conf qu'on puisse te dire ce qui est mal configuré car là 33Go de RAM, même sur des serveurs ayant 192Go de RAM je monte rarement autant.
Je t'ai demandé le pastebin complet du résultat de tuning primer
Pas juste ce truc Bref t'as 11Go de RAM et pas 24
Files nous donc le résultat complet de ta conf qu'on puisse te dire ce qui est mal configuré car là 33Go de RAM, même sur des serveurs ayant 192Go de RAM je monte rarement autant.
Tu n'as pas tout suivi ;-)
Je disais plus haut qu'en fait je suis en train de migrer d'un ancien serveur (12Go RAM) vers un nouveau (24Go RAM). Je fais donc les tests sur l'ancien puisque sur le nouveau il n'y a pas un seul site. Je viens d'ailleurs tout juste d'installer MySQL, donc...
Pour le tuning primer, c'est quoi la ligne :
Pour le "truc" seulement que j'ai mis, c'est tout ce que j'obtiens avec le tuning timer.sh Ensuite il part dans des timeout.
Je disais plus haut qu'en fait je suis en train de migrer d'un ancien serveur (12Go RAM) vers un nouveau (24Go RAM). Je fais donc les tests sur l'ancien puisque sur le nouveau il n'y a pas un seul site. Je viens d'ailleurs tout juste d'installer MySQL, donc...
Pour le tuning primer, c'est quoi la ligne :
Code:
cat recif | curl -F 'text=<-' http://vpaste.net/Pour le "truc" seulement que j'ai mis, c'est tout ce que j'obtiens avec le tuning timer.sh
Ensuite il part dans des timeout.Oula sans rien...
La ligne qui curl -F sert à envoyer sur vpaste.net le ficher et à te donner le retour.
Bon en tout cas là on sait juste que tu as un serveur 12Go de RAM et un autre 24Go, au fur et à mesure des questions, on a sû que t'avais un serveur en 64 bits !
Bref, si tu veux qu'on t'aide, tu peux aussi nous aider en donnant un max d'infos
C'est quoi ta version de MySQL ?
Bizarre pour le timeout par contre
La ligne qui curl -F sert à envoyer sur vpaste.net le ficher et à te donner le retour.
Bon en tout cas là on sait juste que tu as un serveur 12Go de RAM et un autre 24Go, au fur et à mesure des questions, on a sû que t'avais un serveur en 64 bits !
Bref, si tu veux qu'on t'aide, tu peux aussi nous aider en donnant un max d'infos
C'est quoi ta version de MySQL ?
Bizarre pour le timeout par contre
Sauf qu'en informatique ça ne se passe pas comme sur la route...
Il démarre puis il ne se passe rien... Il semble attendre ou tourner sans fin. Le fichier recif n'est pas créé et il n'y a aucun message...
Pour info je suis sur le serveur en prod car sur le nouveau il n'y a aucun intérêt à faire des stats, il n'y a rien dessus
Il démarre puis il ne se passe rien... Il semble attendre ou tourner sans fin. Le fichier recif n'est pas créé et il n'y a aucun message...
Pour info je suis sur le serveur en prod car sur le nouveau il n'y a aucun intérêt à faire des stats, il n'y a rien dessus
Est-ce que le fichier t'affiches au moins le header de tuning primer ?Recif a dit:Sauf qu'en informatique ça ne se passe pas comme sur la route...
Il démarre puis il ne se passe rien... Il semble attendre ou tourner sans fin. Le fichier recif n'est pas créé et il n'y a aucun message...
Pour info je suis sur le serveur en prod car sur le nouveau il n'y a aucun intérêt à faire des stats, il n'y a rien dessus
Code:
angy:/etc/mysql# ./tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.58-1~dotdeb.1-log x86_64
Code:
angy:/etc/mysql# ./tuning-primer.sh --help
usage: ./tuning-primer.sh [ all | banner | file | innodb | memory | misc | prompt ]- Téléchargez le script de nouveau ...
- Essayez une exécution ligne par ligne avec
# bash -v tuning-primer.sh
- lancez le script avec le full path e.g. /root/tuning-primer.sh, le path donné par exemple avec la commande :
# find / -name tuning-primer.sh -print
De toute manière les infos nécessaires sont données par l'analyse de mysqladmin variables ou variables extended-status
Les scripts tuning-primer, mysqltuner.pl se servent de ces variables pour faire leurs calculs.
.
.
OU
plus globalement à une valeur autour 25% de la taille de mémoire physique du serveur. Dans tous les cas, la valeur doit être ajustée en fonction du résulat de la multiplication de (max_connections x thread_memory) .
La sortie du script ressemble beaucoup à celle de Tuning-Primer.sh :
Ce que cela dit : la taille totale des Global Buffers (qui inclus Key_Buffer + 2 ou 3 autres buffers) + (max_connections x thread_memory)
1.50 G + (max_connections x thread_memory) = 33.19 G qui est largement supérieur à la mémoire physique du serveur 10.74 G.
Pour le suivi, copiez-collez-nous une sortie de :
1) my.cnf
2) mysqladmin variables extended-status
.
La sortie de mysqladmin variables extended-status est nécessaire.
.
- Essayez une exécution ligne par ligne avec
# bash -v tuning-primer.sh
- lancez le script avec le full path e.g. /root/tuning-primer.sh, le path donné par exemple avec la commande :
# find / -name tuning-primer.sh -print
De toute manière les infos nécessaires sont données par l'analyse de mysqladmin variables ou variables extended-status
Les scripts tuning-primer, mysqltuner.pl se servent de ces variables pour faire leurs calculs.
.
.
La valeur de key_buffer devrait être légèrement supérieur à la taille de la somme de tous les fichiers .MYI dans le répertoire mysqlRecif a dit:
OU
plus globalement à une valeur autour 25% de la taille de mémoire physique du serveur. Dans tous les cas, la valeur doit être ajustée en fonction du résulat de la multiplication de (max_connections x thread_memory) .
Le premier lien, le script MySQLTuner est en perl. Perl est installée sur votre machine ?Recif a dit:Merci.
Alors... Le premier lien l'utilitaire s'arrête après quelques ligne puis plus
Code:ERROR 1317 (70100) at line 1: Query execution was interrupted ERROR 1317 (70100) at line 1: Query execution was interrupted ERROR 1317 (70100) at line 1: Query execution was interrupted
Et le second lien j'ai une erreur à l'execution:
Code:Command line calculator 'bc' not found!
La sortie du script ressemble beaucoup à celle de Tuning-Primer.sh :
Code:
root@server.phwinfo.com's password:
Last login: Fri Jul 3 05:33:49 2009 from xxxxxxxx.bell.ca
[root@server ~]# perl mysqltuner.pl
>> MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77-community-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 947)
[--] Data in MEMORY tables: 252K (Tables: 6)
[!!] Total fragmented tables: 42
-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 38m 7s (3M q [44.860 qps], 33K conn, TX: 1B, RX: 1B)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 50.0M global + 3.1M per thread (30 max threads)
[OK] Maximum possible memory usage: 141.9M (9% of installed RAM)
[OK] Slow queries: 0% (6/3M)
[OK] Highest usage of available connections: 33% (10/30)
[OK] Key buffer size / total MyISAM indexes: 16.0M/586.2M
[OK] Key buffer hit rate: 98.7% (11M cached / 145K reads)
[OK] Query cache efficiency: 82.9% (1M cached / 1M selects)
[!!] Query cache prunes per day: 65487
[OK] Sorts requiring temporary tables: 7% (5K temp sorts / 70K sorts)
[!!] Joins performed without indexes: 6539
[OK] Temporary tables created on disk: 0% (228 on disk / 35K total)
[OK] Thread cache hit rate: 99% (275 created / 33K connections)
[!!] Table cache hit rate: 2% (1K open / 40K opened)
[OK] Open file limit used: 47% (1K/4K)
[OK] Table locks acquired immediately: 99% (396K immediate / 396K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 8M)
join_buffer_size (> 512.0K, or always use indexes with joins)
table_cache (> 1000)Recif a dit:
Ce que cela dit : la taille totale des Global Buffers (qui inclus Key_Buffer + 2 ou 3 autres buffers) + (max_connections x thread_memory)
1.50 G + (max_connections x thread_memory) = 33.19 G qui est largement supérieur à la mémoire physique du serveur 10.74 G.
Pour le suivi, copiez-collez-nous une sortie de :
1) my.cnf
2) mysqladmin variables extended-status
.
La sortie de mysqladmin variables extended-status est nécessaire.
.
Je mets déjà le my.cnf:
"mysqladmin variables extended-status" je ne sais pas ce que c'est...
Code:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
password = xxxxxxx
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/lib/mysql/tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 0.0.0.0
key_buffer = 512M
max_allowed_packet = 128M
thread_stack = 512K
thread_cache_size = 18
max_connections = 50
table_cache = 9000
open_files_limit = 12000
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M
myisam_sort_buffer_size = 8M
wait_timeout = 120
query_cache_limit = 128M
query_cache_size = 1024M
query_cache_type = 1
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
#log-queries-not-using-indexes
skip-bdb
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
set-variable = max_connections=5000
[mysqldump]
quick
quote-names
max_allowed_packet = 32M
[mysql]
[isamchk]
key_buffer = 32M
!includedir /etc/mysql/conf.d/
!include /etc/mysql/debian.cnf"mysqladmin variables extended-status" je ne sais pas ce que c'est...
Recif a dit:"mysqladmin variables extended-status" je ne sais pas ce que c'est...
1) C'est OK pour my.cnf
2) La commande mysqladmin variables extended-status sort la liste de toutes les options et les valeurs réellement utilisées (tailles des buffers et des piles) sur un serveur en production.
Source: http://dev.mysql.com/doc/refman/5.0/fr/mysqladmin
Un exemple de sortie à l'étape 5 de http://www.phwinfo.com/forum/configuration-serveurs/376271-optimisatio ... ost1732823
Code:
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| keep_files_on_create | OFF |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | YES |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | NO |
| have_ssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | xxxxxxxxxxx |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294963200 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 200 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2146435072 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 1 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/lib/mysql/xxxxxx.xxxxxxxxxxx.pid |
| plugin_dir | |
| port | 0 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 4194304 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | ON |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /home/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| table_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 4 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /home/tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.67-community-log |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 148888886 |
| Bytes_sent | 1670750251 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 23168 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 926 |
| Com_create_table | 508 |
| Com_create_user | 0 |
| Com_dealloc_sql | 2782 |
| Com_delete | 1635 |
| Com_delete_multi | 463 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 314 |
| Com_drop_user | 0 |
| Com_execute_sql | 19506 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_ | 0 |
| Com_insert | 20397 |
| Com_insert_select | 71 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 3724 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 227 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 54888 |
| Com_set_option | 89754 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 2789 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 926 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 48 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 14 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 498 |
| Com_show_triggers | 0 |
| Com_show_variables | 470 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 2782 |
| Com_stmt_execute | 19506 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 3724 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 467 |
| Com_unlock_tables | 0 |
| Com_update | 3839 |
| Com_update_multi | 8 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 4770 |
| Created_tmp_disk_tables | 94 |
| Created_tmp_files | 1044 |
| Created_tmp_tables | 11612 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 3098 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 4630 |
| Handler_read_key | 1466324 |
| Handler_read_next | 2285171 |
| Handler_read_prev | 87446 |
| Handler_read_rnd | 328024 |
| Handler_read_rnd_next | 332608717 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 235864 |
| Handler_write | 283405 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 7245 |
| Key_read_requests | 2757051 |
| Key_reads | 24011 |
| Key_write_requests | 55938 |
| Key_writes | 46067 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 7 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 454 |
| Open_streams | 0 |
| Open_tables | 244 |
| Opened_tables | 4045 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 142 |
| Qcache_free_memory | 301008 |
| Qcache_hits | 187802 |
| Qcache_inserts | 26419 |
| Qcache_lowmem_prunes | 18511 |
| Qcache_not_cached | 22061 |
| Qcache_queries_in_cache | 513 |
| Qcache_total_blocks | 1563 |
| Questions | 400955 |
| Rpl_status | NULL |
| Select_full_join | 8787 |
| Select_full_range_join | 0 |
| Select_range | 3062 |
| Select_range_check | 0 |
| Select_scan | 28822 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 522 |
| Sort_range | 6683 |
| Sort_rows | 1489469 |
| Sort_scan | 13613 |
| Table_locks_immediate | 90706 |
| Table_locks_waited | 1 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 3 |
| Threads_connected | 1 |
| Threads_created | 22 |
| Threads_running | 1 |
| Uptime | 13181 |
| Uptime_since_flush_status | 13181 |
+-----------------------------------+------------+
Uptime: 13181 Threads: 1 Questions: 400956 Slow queries: 0 Opens: 4045 Flush tables: 1 Open tables: 244 Queries per second avg: 30.419
mysqladmin Ver 8.41 Distrib 5.0.67, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.67-community-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 hours 39 min 41 sec
Threads: 1 Questions: 400956 Slow queries: 0 Opens: 4045 Flush tables: 1 Open tables: 244 Queries per second avg: 30.419
+------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+----+---------+------+-------+------------------+
| 4769 | root | localhost | | Query | 0 | | show processlist |
+------+------+-----------+----+---------+------+-------+------------------+
Code:
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | /var/lib/mysql/ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 2093056 |
| key_buffer_size | 536870912 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 5 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 134216704 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 5000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | OFF |
| ndb_cache_check_time | 0 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 25000 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 134217728 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1073741824 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 2093056 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /var/lib/mysql/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 1048568 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| table_cache | 9000 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 18 |
| thread_stack | 524288 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /var/lib/mysql/tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.32-Debian_7etch12-log |
| version_comment | Debian etch distribution |
| version_compile_machine | x86_64 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 120 |
+---------------------------------+-----------------------------+
+-----------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------+--------------+
| Aborted_clients | 6330 |
| Aborted_connects | 314471 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 35717597791 |
| Bytes_sent | 425382545663 |
| Com_admin_commands | 15 |
| Com_alter_db | 0 |
| Com_alter_table | 2 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 12710 |
| Com_change_db | 100794703 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 12711 |
| Com_create_db | 949 |
| Com_create_function | 0 |
| Com_create_index | 238 |
| Com_create_table | 18466 |
| Com_create_user | 0 |
| Com_dealloc_sql | 1205 |
| Com_delete | 8614003 |
| Com_delete_multi | 75 |
| Com_do | 0 |
| Com_drop_db | 846 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 3496 |
| Com_drop_user | 0 |
| Com_execute_sql | 1205 |
| Com_flush | 5 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 4130934 |
| Com_insert_select | 7119 |
| Com_kill | 7 |
| Com_load | 119 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 492 |
| Com_optimize | 559 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 1207 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 603891 |
| Com_replace_select | 114 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 4 |
| Com_savepoint | 0 |
| Com_select | 61691308 |
| Com_set_option | 5073553 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 17 |
| Com_show_charsets | 18 |
| Com_show_collations | 18 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 47 |
| Com_show_databases | 47 |
| Com_show_errors | 0 |
| Com_show_fields | 181772 |
| Com_show_grants | 20 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 2339 |
| Com_show_logs | 0 |
| Com_show_master_status | 29 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 29 |
| Com_show_status | 5606 |
| Com_show_storage_engines | 4 |
| Com_show_tables | 30383 |
| Com_show_triggers | 12 |
| Com_show_variables | 263 |
| Com_show_warnings | 9 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 1205 |
| Com_stmt_execute | 1205 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 1207 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 294 |
| Com_unlock_tables | 492 |
| Com_update | 17058727 |
| Com_update_multi | 242 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 13544800 |
| Created_tmp_disk_tables | 1500370 |
| Created_tmp_files | 173691 |
| Created_tmp_tables | 2200497 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 80717 |
| Handler_delete | 3413304 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 6688943 |
| Handler_read_key | 219347114 |
| Handler_read_next | 74876185107 |
| Handler_read_prev | 26408184394 |
| Handler_read_rnd | 285334156 |
| Handler_read_rnd_next | 78422510882 |
| Handler_rollback | 10 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 10336930 |
| Handler_write | 334050232 |
| Innodb_buffer_pool_pages_data | 495 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 25104 |
| Innodb_buffer_pool_pages_free | 1 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 16 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 57 |
| Innodb_buffer_pool_read_ahead_seq | 8 |
| Innodb_buffer_pool_read_requests | 15200254 |
| Innodb_buffer_pool_reads | 1396 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 114267 |
| Innodb_data_fsyncs | 28823 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 33230848 |
| Innodb_data_reads | 1673 |
| Innodb_data_writes | 45472 |
| Innodb_data_written | 840509952 |
| Innodb_dblwr_pages_written | 25104 |
| Innodb_dblwr_writes | 4887 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 17384 |
| Innodb_log_writes | 15154 |
| Innodb_os_log_fsyncs | 19103 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 15933952 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 130 |
| Innodb_pages_read | 1792 |
| Innodb_pages_written | 25104 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 739 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 45 |
| Innodb_row_lock_waits | 923 |
| Innodb_rows_deleted | 1065 |
| Innodb_rows_inserted | 3299 |
| Innodb_rows_read | 7865735 |
| Innodb_rows_updated | 10255 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 157631 |
| Key_blocks_used | 271074 |
| Key_read_requests | 11544104691 |
| Key_reads | 3160455 |
| Key_write_requests | 24198326 |
| Key_writes | 12461233 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 672 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 17399 |
| Open_streams | 0 |
| Open_tables | 9000 |
| Opened_tables | 1021647 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 108786 |
| Qcache_free_memory | 343782728 |
| Qcache_hits | 256980615 |
| Qcache_inserts | 54009683 |
| Qcache_lowmem_prunes | 7461085 |
| Qcache_not_cached | 6733151 |
| Qcache_queries_in_cache | 238953 |
| Qcache_total_blocks | 623812 |
| Questions | 469601621 |
| Rpl_status | NULL |
| Select_full_join | 16450 |
| Select_full_range_join | 14020 |
| Select_range | 3870690 |
| Select_range_check | 1 |
| Select_scan | 8034661 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 22 |
| Slow_queries | 10940 |
| Sort_merge_passes | 112537 |
| Sort_range | 3818984 |
| Sort_rows | 4110920943 |
| Sort_scan | 2326845 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 90294462 |
| Table_locks_waited | 848823 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 2 |
| Threads_connected | 25 |
| Threads_created | 147784 |
| Threads_running | 2 |
| Uptime | 418489 |
+-----------------------------------+--------------+Pour le nombre de tables : 3260 exactement
Pour max_connections je n'ai pas compris ta remarque...
Explications de :
La sortie de mysqladmin variables extended-status sur votre serveur de production :
Equation d'allocation de la mémoire à MySQL :
Valeurs calculées à partir de la sortie de votre mysqladmin variables extended-status :
configured Max Global Buffers = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size = 8388608 (8M) + 1048576 (1M) + 1048576 (1M) + 536870912 (512M) + 1073741824 (1024M) = 1546M = 1.50G
thread_buffers = 1044480 (1020K) + 2093056 (2044K) + 1048576 (1024K) + 524288 (512K) + 2093056 (2044K) + 32768 (32K)= 6.51M
configured Max Per-thread Buffers = total_thread_buffers = thread_buffers X max_connections = 6.51M x 5000 = 32550M = 31.7G
max_thread_buffers = thread_buffers * Max_used_connections = 6.51M * 672 = 4374.72M = 4.27G
max Memory Ever Allocated = max_memory = global_buffers + max_thread_buffers = 1.50G + 4.27G = 5.77G
Configured Max Memory Limit = configured Max Global Buffers + configured Max Per-thread Buffers = 1.50G + 31.7G = 33.2G
pct_physical_memory = int((total_memory * 100) / physical_memory) = int (( 33.2G * 100) / 10.74G = 309%
pct_physical_memory = 309% > 85% -> danger de crash
MAX CONNECTIONS
Nombre maximal de connexions simultanées (max_connections) = 5000
Nombre de connexions actuellement ouvertes = 25
Nombre maximal de connexions simultanées utilisées (max_used_connections) = 672
Le nombre de connexions utilisées est 13.4% de la valeur maximale max_connections.
Réduire le nombre maximal de connexions simultanées (max_connections) permettrait de réduire la mémoire allouée à MySQL ou encore mieux équilibrer l'équation.
UTILISATION DE LA MÉMOIRE
Mémoire maximale historiquement allouée : 5.76 G
Total_thread_Buffers configurés = 31.7G
Configured Max Global Buffers = 1.50G
Mémoire maximal allouée à MySQL : 33.2G
Mémoire physique : 10.74G
La mémoire allouée à MySQL dépasse les 90% de la mémoire physique
KEY BUFFER (taille des buffers de bloc d'index)
157631 * 1024 / 536870912 * 100
Current MyISAM index space = find /var/lib/mysql/ -name *.MYI | xargs du -sch
Current key_buffer_size = 512M
Key cache miss rate is Key_reads/Key_read_requests soit 3160455/11544104691 soit 1 : 3655
Key buffer free ratio = 0 %
You could increase key_buffer_size
Calculs:
Percentage of the key buffer in use : ((Key_blocks_unused × key_cache_block_size) / key_buffer_size) : 70% utilisé
key_buffer_use_pct = ((1 - (({Key_blocks_unused} * {key_cache_block_size}) / key_buffer_size) * 100) =
((1 - ((157631 * 1024) / 536870912)) * 100 = (1 - 0.30) * 100 = 70. Le buffer est utilisé à 70%.
the percent of key requests that come from the cache (rather than the disk) :
key_from_mem_pct = ((100 - (({Key_reads} / {Key_read_requests}) * 100))) =
((100 - (({3160455} / {11544104691}) * 100))) = 100 - (0.000273 * 100) = 100 - 0.0273 = 99.97%
Key buffer is probably set to the default, should be raised
The key buffer is 99.7% used, and 70% of key requests come from the cache (rather than the disk)
Key Cache hit rate: 3160455 / 11544104691 = 0.000273
(à compléter, suivre http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html).
Recif a dit:
La sortie de mysqladmin variables extended-status sur votre serveur de production :
Code:
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | /var/lib/mysql/ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 2093056 |
| key_buffer_size | 536870912 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 5 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 134216704 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 5000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | OFF |
| ndb_cache_check_time | 0 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 25000 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 134217728 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1073741824 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 2093056 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /var/lib/mysql/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 1048568 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| table_cache | 9000 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 18 |
| thread_stack | 524288 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /var/lib/mysql/tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.32-Debian_7etch12-log |
| version_comment | Debian etch distribution |
| version_compile_machine | x86_64 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 120 |
+---------------------------------+-----------------------------+
+-----------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------+--------------+
| Aborted_clients | 6330 |
| Aborted_connects | 314471 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 35717597791 |
| Bytes_sent | 425382545663 |
| Com_admin_commands | 15 |
| Com_alter_db | 0 |
| Com_alter_table | 2 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 12710 |
| Com_change_db | 100794703 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 12711 |
| Com_create_db | 949 |
| Com_create_function | 0 |
| Com_create_index | 238 |
| Com_create_table | 18466 |
| Com_create_user | 0 |
| Com_dealloc_sql | 1205 |
| Com_delete | 8614003 |
| Com_delete_multi | 75 |
| Com_do | 0 |
| Com_drop_db | 846 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 3496 |
| Com_drop_user | 0 |
| Com_execute_sql | 1205 |
| Com_flush | 5 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 4130934 |
| Com_insert_select | 7119 |
| Com_kill | 7 |
| Com_load | 119 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 492 |
| Com_optimize | 559 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 1207 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 603891 |
| Com_replace_select | 114 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 4 |
| Com_savepoint | 0 |
| Com_select | 61691308 |
| Com_set_option | 5073553 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 17 |
| Com_show_charsets | 18 |
| Com_show_collations | 18 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 47 |
| Com_show_databases | 47 |
| Com_show_errors | 0 |
| Com_show_fields | 181772 |
| Com_show_grants | 20 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 2339 |
| Com_show_logs | 0 |
| Com_show_master_status | 29 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 29 |
| Com_show_status | 5606 |
| Com_show_storage_engines | 4 |
| Com_show_tables | 30383 |
| Com_show_triggers | 12 |
| Com_show_variables | 263 |
| Com_show_warnings | 9 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 1205 |
| Com_stmt_execute | 1205 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 1207 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 294 |
| Com_unlock_tables | 492 |
| Com_update | 17058727 |
| Com_update_multi | 242 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 13544800 |
| Created_tmp_disk_tables | 1500370 |
| Created_tmp_files | 173691 |
| Created_tmp_tables | 2200497 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 80717 |
| Handler_delete | 3413304 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 6688943 |
| Handler_read_key | 219347114 |
| Handler_read_next | 74876185107 |
| Handler_read_prev | 26408184394 |
| Handler_read_rnd | 285334156 |
| Handler_read_rnd_next | 78422510882 |
| Handler_rollback | 10 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 10336930 |
| Handler_write | 334050232 |
| Innodb_buffer_pool_pages_data | 495 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 25104 |
| Innodb_buffer_pool_pages_free | 1 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 16 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 57 |
| Innodb_buffer_pool_read_ahead_seq | 8 |
| Innodb_buffer_pool_read_requests | 15200254 |
| Innodb_buffer_pool_reads | 1396 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 114267 |
| Innodb_data_fsyncs | 28823 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 33230848 |
| Innodb_data_reads | 1673 |
| Innodb_data_writes | 45472 |
| Innodb_data_written | 840509952 |
| Innodb_dblwr_pages_written | 25104 |
| Innodb_dblwr_writes | 4887 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 17384 |
| Innodb_log_writes | 15154 |
| Innodb_os_log_fsyncs | 19103 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 15933952 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 130 |
| Innodb_pages_read | 1792 |
| Innodb_pages_written | 25104 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 739 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 45 |
| Innodb_row_lock_waits | 923 |
| Innodb_rows_deleted | 1065 |
| Innodb_rows_inserted | 3299 |
| Innodb_rows_read | 7865735 |
| Innodb_rows_updated | 10255 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 157631 |
| Key_blocks_used | 271074 |
| Key_read_requests | 11544104691 |
| Key_reads | 3160455 |
| Key_write_requests | 24198326 |
| Key_writes | 12461233 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 672 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 17399 |
| Open_streams | 0 |
| Open_tables | 9000 |
| Opened_tables | 1021647 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 108786 |
| Qcache_free_memory | 343782728 |
| Qcache_hits | 256980615 |
| Qcache_inserts | 54009683 |
| Qcache_lowmem_prunes | 7461085 |
| Qcache_not_cached | 6733151 |
| Qcache_queries_in_cache | 238953 |
| Qcache_total_blocks | 623812 |
| Questions | 469601621 |
| Rpl_status | NULL |
| Select_full_join | 16450 |
| Select_full_range_join | 14020 |
| Select_range | 3870690 |
| Select_range_check | 1 |
| Select_scan | 8034661 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 22 |
| Slow_queries | 10940 |
| Sort_merge_passes | 112537 |
| Sort_range | 3818984 |
| Sort_rows | 4110920943 |
| Sort_scan | 2326845 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 90294462 |
| Table_locks_waited | 848823 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 2 |
| Threads_connected | 25 |
| Threads_created | 147784 |
| Threads_running | 2 |
| Uptime | 418489 |
+-----------------------------------+--------------+Equation d'allocation de la mémoire à MySQL :
Valeurs calculées à partir de la sortie de votre mysqladmin variables extended-status :
configured Max Global Buffers = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size = 8388608 (8M) + 1048576 (1M) + 1048576 (1M) + 536870912 (512M) + 1073741824 (1024M) = 1546M = 1.50G
thread_buffers = 1044480 (1020K) + 2093056 (2044K) + 1048576 (1024K) + 524288 (512K) + 2093056 (2044K) + 32768 (32K)= 6.51M
configured Max Per-thread Buffers = total_thread_buffers = thread_buffers X max_connections = 6.51M x 5000 = 32550M = 31.7G
max_thread_buffers = thread_buffers * Max_used_connections = 6.51M * 672 = 4374.72M = 4.27G
max Memory Ever Allocated = max_memory = global_buffers + max_thread_buffers = 1.50G + 4.27G = 5.77G
Configured Max Memory Limit = configured Max Global Buffers + configured Max Per-thread Buffers = 1.50G + 31.7G = 33.2G
pct_physical_memory = int((total_memory * 100) / physical_memory) = int (( 33.2G * 100) / 10.74G = 309%
pct_physical_memory = 309% > 85% -> danger de crash
MAX CONNECTIONS
Nombre maximal de connexions simultanées (max_connections) = 5000
Nombre de connexions actuellement ouvertes = 25
Nombre maximal de connexions simultanées utilisées (max_used_connections) = 672
Le nombre de connexions utilisées est 13.4% de la valeur maximale max_connections.
Réduire le nombre maximal de connexions simultanées (max_connections) permettrait de réduire la mémoire allouée à MySQL ou encore mieux équilibrer l'équation.
UTILISATION DE LA MÉMOIRE
Mémoire maximale historiquement allouée : 5.76 G
Total_thread_Buffers configurés = 31.7G
Configured Max Global Buffers = 1.50G
Mémoire maximal allouée à MySQL : 33.2G
Mémoire physique : 10.74G
La mémoire allouée à MySQL dépasse les 90% de la mémoire physique
KEY BUFFER (taille des buffers de bloc d'index)
157631 * 1024 / 536870912 * 100
Current MyISAM index space = find /var/lib/mysql/ -name *.MYI | xargs du -sch
Current key_buffer_size = 512M
Key cache miss rate is Key_reads/Key_read_requests soit 3160455/11544104691 soit 1 : 3655
Key buffer free ratio = 0 %
You could increase key_buffer_size
Calculs:
Percentage of the key buffer in use : ((Key_blocks_unused × key_cache_block_size) / key_buffer_size) : 70% utilisé
key_buffer_use_pct = ((1 - (({Key_blocks_unused} * {key_cache_block_size}) / key_buffer_size) * 100) =
((1 - ((157631 * 1024) / 536870912)) * 100 = (1 - 0.30) * 100 = 70. Le buffer est utilisé à 70%.
the percent of key requests that come from the cache (rather than the disk) :
key_from_mem_pct = ((100 - (({Key_reads} / {Key_read_requests}) * 100))) =
((100 - (({3160455} / {11544104691}) * 100))) = 100 - (0.000273 * 100) = 100 - 0.0273 = 99.97%
Key buffer is probably set to the default, should be raised
The key buffer is 99.7% used, and 70% of key requests come from the cache (rather than the disk)
Key Cache hit rate: 3160455 / 11544104691 = 0.000273
(à compléter, suivre http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html).
Pour voir si ca passe, vous pouvez essayer un key_buffer_size à 1024M en réduisant la variable max_connections de 5000 à 750 puisque la valeur historique la plus haute est égale à 672 ou réduire les autres valeurs dans la partie de droite de l'équation donnée plus haut.
Discussions similaires
