Ich habe einen MySQL-Server (Version 5.1), auf dem der Roundcube-Webmail-Client auf einer Plesk-Box (Onyx) läuft. Tagsüber sind mehrere hundert Benutzer darauf. Mein Hypervisor Virtuozzo hat den ganzen Tag mit uns daran gearbeitet und festgestellt, dass der Container selbst eine enorme Menge an I/O generiert.
Die E/A-Daten stiegen so stark an, dass der gesamte Server abstürzte und das Dateisystem beschädigt wurde.
**
HAFTUNGSAUSSCHLUSS: Die folgende Anzeige ist nach einem MySQL-Stopp/-Start aktuell!
**
(Unter Centos 6.9)
Spezifikationen:
AMD Opteron(tm) Processor 6344
Core Name
Abu Dhabi
# of Cores
12-Core
# of Threads
12
Operating Frequency
2.6 GHz
Hyper Transports
6.40 GT/s
L2 Cache
6 x 2MB
L3 Cache
2 x 8MB
Manufacturing Tech
32 nm
30GB RAM (I have provided this to the container), server has 64 total.
Meine my.cnf-Konfiguration sieht wie folgt aus: (und entschuldigen Sie, es ist ein Durcheinander, viele von uns haben den ganzen Tag versucht, den Server am Laufen zu halten. Sie können es organisieren, wenn Sie möchten.)
[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_table_locks=0
#innodb_buffer_pool_size=10240M
#Add
innodb_buffer_pool_size=16G
thread_cache_size=20
tmp_table_size=256M
max_heap_table_size=256M
query_cache_size=0
query_cache_type=0
innodb_additional_mem_pool_size=32M
innodb_file_io_threads=8
innodb_stats_on_metadata=0
innodb_thread_concurrency=24
# Recommended. Also changed thread concurrency to 24 from 12
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2
[mysqld]
max_connections=2000
max_user_connections=0
Mehr Info:
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| innodb_log_file_size | 5242880 |
+----------------------+---------+
1 row in set (0.00 sec)
Mehr Info:
mysql> SHOW GLOBAL STATUS;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 830216 |
| Bytes_sent | 39213368 |
| Com_admin_commands | 5 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 2 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 22 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 7 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| 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 | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 4259 |
| Com_set_option | 1098 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 1 |
| Com_show_column_types | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 3 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 449 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 7 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 3 |
| Com_show_triggers | 0 |
| Com_show_variables | 9 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 26 |
| Com_update_multi | 0 |
| 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 | 1167 |
| Created_tmp_disk_tables | 16 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 39 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 4309 |
| Handler_delete | 2 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 17 |
| Handler_read_key | 6473 |
| Handler_read_next | 26911 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1449 |
| Handler_read_rnd_next | 3241 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 24 |
| Handler_write | 2468 |
| Innodb_buffer_pool_pages_data | 3675 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 239 |
| Innodb_buffer_pool_pages_free | 1044849 |
| Innodb_buffer_pool_pages_misc | 52 |
| Innodb_buffer_pool_pages_total | 1048576 |
| Innodb_buffer_pool_read_ahead_rnd | 5 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 125478 |
| Innodb_buffer_pool_reads | 3403 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 545 |
| Innodb_data_fsyncs | 156 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 62099456 |
| Innodb_data_reads | 3447 |
| Innodb_data_writes | 321 |
| Innodb_data_written | 8141824 |
| Innodb_dblwr_pages_written | 239 |
| Innodb_dblwr_writes | 38 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 575 |
| Innodb_log_writes | 53 |
| Innodb_os_log_fsyncs | 80 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 296448 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 18 |
| Innodb_pages_read | 3657 |
| Innodb_pages_written | 239 |
| 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 | 2 |
| Innodb_rows_inserted | 7 |
| Innodb_rows_read | 32244 |
| Innodb_rows_updated | 24 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 53585 |
| Key_blocks_used | 3 |
| Key_read_requests | 6 |
| Key_reads | 3 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 16 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 3 |
| Open_streams | 0 |
| Open_table_definitions | 241 |
| Open_tables | 64 |
| Opened_files | 365 |
| Opened_table_definitions | 241 |
| Opened_tables | 519 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 7047 |
| Questions | 7047 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 995 |
| Select_range_check | 0 |
| Select_scan | 44 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 31 |
| Sort_merge_passes | 0 |
| Sort_range | 31 |
| Sort_rows | 1449 |
| Sort_scan | 0 |
| 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 | 4331 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 12 |
| Threads_connected | 4 |
| Threads_created | 16 |
| Threads_running | 1 |
| Uptime | 405 |
| Uptime_since_flush_status | 405 |
+-----------------------------------+----------+
291 rows in set (0.01 sec)
Und meinGLOBALE VARIABLENsind hier
Ich bin mir nicht sicher, warum, aber ich erhalte von diesem Container aus RIESIGE E/A-Aufrufe auf meinen Hypervisor. Hier sind einige Informationen von Newrelic:
Jetzt verstehe ich, dass MYSQLTuner den MySQL-Dienst während dieser Problemzeiten laufen lassen muss, aber wir haben Änderungen an der Datei my.cnf vorgenommen, neu gestartet und ihn dann ausgeführt. Hier sind einige zusätzliche Informationen, die direkt nach einem erneuten Stopp/Start des Dienstes ausgeführt werden. Denken Sie also daran, dass dies möglicherweise nicht hilfreich ist:
BEARBEITEN:
Nachdem der Server einige Stunden lang ausgeführt wurde, ist hier die mysqltuner-Ausgabe:
Noch einmal Leute – ich bin kein Datenbankprofi, also seid bitte nachsichtig mit mir :)
Meine Frage ist also, wie ich diese extrem starke Verlangsamung beheben kann. Ich weiß nicht, wo ich anfangen soll. Wenn Sie weitere Informationen von mir benötigen, lassen Sie es mich wissen. Vielen Dank an alle!