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!
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!
Asked by Gordon Snappleweed
(11 rep)
Mar 23, 2018, 12:26 AM
Last activity: Mar 29, 2018, 12:48 PM
Last activity: Mar 29, 2018, 12:48 PM