MySQL 导致严重的 I/O 延迟

MySQL 导致严重的 I/O 延迟

我有一台 mysql 服务器(版本 5.1),在 plesk box(onyx)上运行 Roundcube Webmail 客户端。白天我们有几百个用户在使用。我的虚拟机管理程序 Virtuozzo 整天都在与我们合作,发现容器本身正在产生大量的 I/O。

I/O 太高以至于导致整个服务器崩溃,并且文件系统损坏。

**

免责声明:下面的显示内容都是在 mysql 停止/启动后更新的!

**

(在 Centos 6.9 上)

眼镜:

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.cnf 配置如下:(抱歉,这很乱,我们很多人整天都在努力保持服务器正常运行。如果您愿意,可以组织它..)

[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

更多信息:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| innodb_log_file_size | 5242880 |
+----------------------+---------+
1 row in set (0.00 sec)

更多信息:

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)

和我的全局变量在这里

我不知道为什么,但我从这个容器中获得了虚拟机管理程序的主要 I/O。以下是来自 newrelic 的一些信息:

在此处输入图片描述

在此处输入图片描述

在此处输入图片描述

在此处输入图片描述

在此处输入图片描述

在此处输入图片描述

现在我明白了,MYSQLTuner 需要让 mysql 服务在出现此问题时运行,但我们对 my.cnf 文件进行了更改并重新启动,然后运行它。以下是在新服务停止/启动后立即运行的一些附加信息,因此请记住这可能没有帮助:

在此处输入图片描述

编辑:

运行服务器几个小时后,以下是 mysqltuner 输出: 在此处输入图片描述 在此处输入图片描述 在此处输入图片描述

再次向各位问好 - 我并不是数据库专家所以请对我宽容一点 :)

所以我的问题是,我该如何解决这个极其严重的减速问题。我不知道从哪里开始。如果您需要我提供更多信息,请告诉我。谢谢大家!

相关内容