0

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:

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

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:

enter image description here

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!

  • What does the cron job do ever hour at about :10 ? – Rick James Mar 29 '18 at 12:30
  • @gordonsnappleweed Please post the entire mysqltuner report (either here or on pastebin.com). There is additional valuable information available that answers many questions. – Wilson Hauck Mar 30 '18 at 15:37
  • @gordonsnappleweed Still need entire TEXT of mysqltuner report. Also, A) complete error.log B) ulimit -a C) iostat -x (when your test is running for extended IOPS information. Is there any good reason for running 5.1.73 version of MySQL? 5.6 and 5.7 have been successful in production for many months and have been significantly improved.. – Wilson Hauck Apr 4 '18 at 13:20
1

SHOW GLOBAL STATUS -- Please rerun after being up for 24 hours, not a mere 405 seconds.

I see nothing in my.cnf to 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"?

  • @gordonsnappleweed Any chance you will Edit your Question and POST SHOW GLOBAL STATUS; after 24 hours of UPTIME? If not, we really can not assist. – Wilson Hauck Apr 7 '18 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.