I have a mysql server (version 5.1) running Roundcube Webmail client. We have a few hundred users on it during the day. My hypervisor, Virtuozzo, they have been working with us on this all day and found that the container itself is generating an enormous amount of I/O.
The I/O got so high that it crashed the entire server, with corruption
to the file system.
**
DISCLAIMER: The SHOW anything below is fresh after a mysql stop/start!
**
(On Centos 6.9)
Specs:
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.
My my.cnf config is as follows: (and sorry, it is messy, a lot of us have been trying to keep the server up all day. You may organize it if you wish to do so..)
[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
#innodb_log_buffer_size=16M
query_cache_size=0
query_cache_type=0
innodb_additional_mem_pool_size=32M
#innodb_log_file_size=128M
#innodb_log_buffer_size=16M
innodb_file_io_threads=8
innodb_stats_on_metadata=0
#innodb_additional_mem_pool_size=1024M
innodb_thread_concurrency=24
# performance enhancements by python script
# innodb_file_per_table=1
# query_cache_size=0
# query_cache_type=0
# thread_cache_size=4
# Recommended. Also changed thread concurrency to 24 from 12
innodb_flush_log_at_trx_commit=0
#query_cache_size=128M
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
More 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)
More 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)
And my GLOBAL VARIABLES are here
I am not sure why, but I am getting MAJOR I/O to my hypervisor from this container. Here is some info from newrelic:
Now I understand that MYSQLTuner needs to have the mysql service be running during this issue times but we made changes to the my.cnf file and restarted, and then ran it. Here is some additional information running right after a fresh service stop/start so keep in mind this may not be helpful:
So my question is how can I resolve this extremely bad slowdown I have. I am at a loss for where to begin.. If you need additional info from me let me know. Thank you all!
Best Answer
SHOW GLOBAL STATUS-- Please rerun after being up for 24 hours, not a mere 405 seconds.I see nothing in
my.cnfto explain the problem.Since you have the slowlog on, let's analyze it. See this for how to analyze: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog This will probably help identify "temp tables written to disk", which is a factor in I/O. Typical cures involve composite indexes or reformulating queries.
You should plan on upgrading the version of MySQL; 5.1 is now 'antique'. (No, upgrading won't fix your problem, but it may help.)
"Free pages" sounds like your dataset is much smaller than the buffer_pool_size?
"Thread cache is disabled"??? I don't know how to do that, except perhaps via
thread_cache_size, but that is 20.The MySQLTuner output seems to have been run when the buffer_pool was a different size??
"The I/O got so high that it crashed the entire server, with corruption to the file system." -- No. Something else causes corruption. Did you "pull the plug"?