Tengo un servidor MySQL (versión 5.1) que ejecuta el cliente de correo web Roundcube en una caja Plesk (onyx). Tenemos unos cientos de usuarios durante el día. Mi hipervisor, Virtuozzo, ha estado trabajando con nosotros en esto todo el día y descubrió que el contenedor en sí está generando una enorme cantidad de E/S.
La E/S aumentó tanto que colapsó todo el servidor, dañando el sistema de archivos.
**
DESCARGO DE RESPONSABILIDAD: ¡MOSTRAR todo lo que aparece a continuación es nuevo después de una parada/inicio de MySQL!
**
(En Centos 6.9)
Especificaciones:
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.
Mi configuración de my.cnf es la siguiente: (y lo siento, es complicado, muchos de nosotros hemos estado intentando mantener el servidor activo todo el día. Puedes organizarlo si así lo deseas...)
[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
Más información:
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| innodb_log_file_size | 5242880 |
+----------------------+---------+
1 row in set (0.00 sec)
Más información:
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)
Y miVARIABLES GLOBALESestán aquí
No estoy seguro de por qué, pero estoy recibiendo E/S PRINCIPALES en mi hipervisor desde este contenedor. Aquí hay información de newrelic:
Ahora entiendo que MYSQLTuner necesita que el servicio mysql esté ejecutándose durante este momento del problema, pero hicimos cambios en el archivo my.cnf, lo reiniciamos y luego lo ejecutamos. Aquí hay información adicional que se ejecuta inmediatamente después de una nueva parada/inicio del servicio, así que tenga en cuenta que esto puede no ser útil:
EDITAR:
Después de ejecutar el servidor durante unas horas, aquí está el resultado de mysqltuner:
Nuevamente a todos: no soy un profesional de bases de datos, así que sean amables conmigo :)
Entonces mi pregunta es ¿cómo puedo solucionar esta desaceleración tan grave que tengo? No sé por dónde empezar. Si necesita información adicional, hágamelo saber. ¡Gracias a todos!