MySQL 性能和变量调整

Question

我的 MySql 服务器有问题，导致它一直卡住。在 phpMyAdmin 中，当发生这种情况时，我可以在“显示进程列表”中看到很多用户“未经身份验证的用户”的进程，命令“连接”和状态“从网络读取”。

数据库使用 InnoDB，但我也使用一个运行 MyISAM 的表来执行全文搜索。服务器内存约4GB，使用中不到500MB。

我一直在使用 MySql 中的慢查询日志来查找不使用索引的查询。我还认为我需要对服务器变量进行一些更改/调整。

我真的可以在这里使用一些帮助，因此我在这里发布了 SHOW GLOBAL STATUS 和 SHOW GLOBAL VARIABLES，也许你可以给我一些我应该如何设置变量的想法？

当前显示全局状态:

Variable_name   Value
Aborted_clients     730
Aborted_connects    35
Binlog_cache_disk_use   0
Binlog_cache_use    0
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   0
Bytes_received  31558148370
Bytes_sent  556410688609
Com_admin_commands  4565
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     3
Com_alter_tablespace    0
Com_analyze     0
Com_begin   168
Com_binlog  0
Com_call_procedure  0
Com_change_db   26791502
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  584152
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  5871056
Com_insert_select   2833
Com_install_plugin  0
Com_kill    160
Com_load    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_resignal    0
Com_revoke  0
Com_revoke_all  0
Com_rollback    0
Com_rollback_to_savepoint   0
Com_savepoint   0
Com_select  21739455
Com_set_option  53600251
Com_signal  0
Com_show_authors    0
Com_show_binlog_events  0
Com_show_binlogs    217
Com_show_charsets   3
Com_show_collations     26786645
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   6412
Com_show_create_trigger     84
Com_show_databases  213
Variable_name   Value
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     4389
Com_show_function_status    14
Com_show_grants     2
Com_show_keys   25
Com_show_master_status  5
Com_show_open_tables    0
Com_show_plugins    560
Com_show_privileges     0
Com_show_procedure_status   14
Com_show_processlist    189
Com_show_profile    0
Com_show_profiles   0
Com_show_relaylog_events    0
Com_show_slave_hosts    0
Com_show_slave_status   5
Com_show_status     156
Com_show_storage_engines    16
Com_show_table_status   4154
Com_show_tables     280
Com_show_triggers   4144
Com_show_variables  26786703
Com_show_warnings   2363
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   168
Com_update  2572452
Com_update_multi    35171
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     26787752
Created_tmp_disk_tables     119645
Created_tmp_files   7069
Created_tmp_tables  58427880
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  30698922
Handler_delete  177424
Handler_discover    0
Handler_prepare     0
Handler_read_first  1239039
Handler_read_key    2226723080
Handler_read_last   9200
Handler_read_next   4186985860
Handler_read_prev   6811735740
Handler_read_rnd    8942473
Handler_read_rnd_next   16431972655
Handler_rollback    495
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  9720709
Handler_write   15014651212
Innodb_buffer_pool_pages_data   8173
Innodb_buffer_pool_pages_dirty  138
Innodb_buffer_pool_pages_flushed    34780758
Innodb_buffer_pool_pages_free   0
Innodb_buffer_pool_pages_misc   18
Innodb_buffer_pool_pages_total  8191
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   26163447
Innodb_buffer_pool_read_ahead_evicted   669245354
Innodb_buffer_pool_read_requests    19793139859
Innodb_buffer_pool_reads    1156881183
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   81288931
Innodb_data_fsyncs  8482576
Innodb_data_pending_fsyncs  0
Innodb_data_pending_reads   0
Innodb_data_pending_writes  0
Innodb_data_read    19409628139520
Innodb_data_reads   1184668281
Innodb_data_writes  25747879
Innodb_data_written     576917547008
Innodb_dblwr_pages_written  17390379
Innodb_dblwr_writes     255445
Innodb_have_atomic_builtins     ON
Innodb_log_waits    0
Innodb_log_write_requests   6930648
Innodb_log_writes   7850155
Innodb_os_log_fsyncs    7976564
Variable_name   Value
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   7007020032
Innodb_page_size    16384
Innodb_pages_created    78647
Innodb_pages_read   1184669628
Innodb_pages_written    17390379
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    1116
Innodb_row_lock_time_avg    39
Innodb_row_lock_time_max    963
Innodb_row_lock_waits   28
Innodb_rows_deleted     177424
Innodb_rows_inserted    5863854
Innodb_rows_read    14138431339
Innodb_rows_updated     4886889
Innodb_truncated_status_writes  0
Key_blocks_not_flushed  0
Key_blocks_unused   13
Key_blocks_used     13
Key_read_requests   3458723878
Key_reads   49092342
Key_write_requests  462162
Key_writes  436874
Last_query_cost     0.000000
Max_used_connections    137
Not_flushed_delayed_rows    0
Open_files  0
Open_streams    0
Open_table_definitions  113
Open_tables     4
Opened_files    6485943
Opened_table_definitions    116
Opened_tables   31771706
Performance_schema_cond_classes_lost    0
Performance_schema_cond_instances_lost  0
Performance_schema_file_classes_lost    0
Performance_schema_file_handles_lost    0
Performance_schema_file_instances_lost  0
Performance_schema_locker_lost  0
Performance_schema_mutex_classes_lost   0
Performance_schema_mutex_instances_lost     0
Performance_schema_rwlock_classes_lost  0
Performance_schema_rwlock_instances_lost    0
Performance_schema_table_handles_lost   0
Performance_schema_table_instances_lost     0
Performance_schema_thread_classes_lost  0
Performance_schema_thread_instances_lost    0
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     191630861
Questions   191562751
Rpl_status  AUTH_MASTER
Select_full_join    7193
Select_full_range_join  0
Select_range    957755
Select_range_check  0
Select_scan     57577013
Slave_heartbeat_period  0.000
Slave_open_temp_tables  0
Slave_received_heartbeats   0
Slave_retried_transactions  0
Slave_running   OFF
Slow_launch_threads     0
Slow_queries    445
Sort_merge_passes   3531
Sort_range  5655320
Sort_rows   43857009
Sort_scan   757681
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   51310065
Variable_name   Value
Table_locks_waited  14101
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  0
Threads_connected   4
Threads_created     26787751
Threads_running     1
Uptime  1208447
Uptime_since_flush_status   1208447

当前显示全局变量:

Variable_name   Value
auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges     ON
back_log    50
basedir     /usr
big_tables  OFF
binlog_cache_size   32768
binlog_direct_non_transactional_updates     OFF
binlog_format   STATEMENT
binlog_stmt_cache_size  32768
bulk_insert_buffer_size     8388608
character_set_client    latin1
character_set_connection    latin1
character_set_database  latin1
character_set_filesystem    binary
character_set_results   latin1
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    latin1_swedish_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
completion_type     NO_CHAIN
concurrent_insert   AUTO
connect_timeout     10
datadir     /var/lib/mysql/
date_format     %Y-%m-%d
datetime_format     %Y-%m-%d %H:%i:%s
default_storage_engine  InnoDB
default_week_format     0
delay_key_write     ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size  1000
div_precision_increment     4
engine_condition_pushdown   ON
event_scheduler     OFF
expire_logs_days    0
flush   OFF
flush_time  0
foreign_key_checks  ON
ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len     84
ft_min_word_len     3
ft_query_expansion_limit    20
ft_stopword_file    (built-in)
general_log     OFF
general_log_file    /var/lib/mysql/db.log
group_concat_max_len    1024
have_compress   YES
have_crypt  YES
have_csv    YES
have_dynamic_loading    YES
have_geometry   YES
have_innodb     YES
have_ndbcluster     NO
have_openssl    DISABLED
have_partitioning   YES
have_profiling  YES
have_query_cache    YES
have_rtree_keys     YES
have_ssl    DISABLED
have_symlink    YES
hostname    silldb5
ignore_builtin_innodb   OFF
init_connect    
init_file   
init_slave  
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size     8388608
innodb_autoextend_increment     8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size     134217728
innodb_change_buffering     all
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir    
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method     
innodb_force_load_corrupted     OFF
innodb_force_recovery   0
innodb_io_capacity  200
innodb_large_prefix     OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
Variable_name   Value
innodb_max_dirty_pages_pct  75
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files   300
innodb_purge_batch_size     20
innodb_purge_threads    0
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold     56
innodb_read_io_threads  4
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_spin_wait_delay  6
innodb_stats_method     nulls_equal
innodb_stats_on_metadata    ON
innodb_stats_sample_pages   8
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   0
innodb_thread_sleep_delay   10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  1.1.8
innodb_write_io_threads     4
interactive_timeout     28800
join_buffer_size    131072
keep_files_on_create    OFF
key_buffer_size     16384
key_cache_age_threshold     300
key_cache_block_size    1024
key_cache_division_limit    100
large_files_support     ON
large_page_size     0
large_pages     OFF
lc_messages     en_US
lc_messages_dir     /usr/share/mysql/
lc_time_names   en_US
license     GPL
local_infile    ON
lock_wait_timeout   31536000
locked_in_memory    OFF
log     OFF
log_bin     OFF
log_bin_trust_function_creators     OFF
log_error   /var/lib/mysql/db.err
log_output  FILE
log_queries_not_using_indexes   OFF
log_slave_updates   OFF
log_slow_queries    OFF
log_warnings    1
long_query_time     10.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
max_allowed_packet  1048576
max_binlog_cache_size   18446744073709547520
max_binlog_size     1073741824
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  10
max_connections     2000
max_delayed_threads     20
max_error_count     64
max_heap_table_size     16777216
max_insert_delayed_threads  20
max_join_size   18446744073709551615
max_length_for_sort_data    1024
max_long_data_size  1048576
max_prepared_stmt_count     16382
max_relay_log_size  0
max_seeks_for_key   18446744073709551615
max_sort_length     1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    18446744073709551615
metadata_locks_cache_size   1024
min_examined_row_limit  0
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   9223372036853727232
myisam_mmap_size    18446744073709551615
myisam_recover_options  OFF
myisam_repair_threads   1
myisam_sort_buffer_size     8388608
myisam_stats_method     nulls_unequal
myisam_use_mmap     OFF
net_buffer_length   2048
net_read_timeout    30
net_retry_count     10
net_write_timeout   60
new     OFF
old     OFF
old_alter_table     OFF
old_passwords   OFF
open_files_limit    10000
optimizer_prune_level   1
Variable_name   Value
optimizer_search_depth  62
optimizer_switch    index_merge=on,index_merge_union=on,index_merge_so...
performance_schema  OFF
performance_schema_events_waits_history_long_size   10000
performance_schema_events_waits_history_size    10
performance_schema_max_cond_classes     80
performance_schema_max_cond_instances   1000
performance_schema_max_file_classes     50
performance_schema_max_file_handles     32768
performance_schema_max_file_instances   10000
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  1000000
performance_schema_max_rwlock_classes   30
performance_schema_max_rwlock_instances     1000000
performance_schema_max_table_handles    100000
performance_schema_max_table_instances  50000
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances     1000
pid_file    /var/run/mysqld/mysqld.pid
plugin_dir  /usr/lib64/mysql/plugin
port    3306
preload_buffer_size     32768
profiling   OFF
profiling_history_size  15
protocol_version    10
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    8 388 608
query_cache_type    ON
query_cache_wlock_invalidate    OFF
query_prealloc_size     8192
range_alloc_block_size  4096
read_buffer_size    262144
read_only   OFF
read_rnd_buffer_size    262144
relay_log   
relay_log_index     
relay_log_info_file     relay-log.info
relay_log_purge     ON
relay_log_recovery  OFF
relay_log_space_limit   0
report_host     
report_password     
report_port     3306
report_user     
rpl_recovery_rank   0
secure_auth     OFF
secure_file_priv    
server_id   1
skip_external_locking   ON
skip_name_resolve   ON
skip_networking     OFF
skip_show_database  OFF
slave_compressed_protocol   OFF
slave_exec_mode     STRICT
slave_load_tmpdir   /tmp
slave_net_timeout   3600
slave_skip_errors   OFF
slave_transaction_retries   10
slave_type_conversions  
slow_launch_time    2
slow_query_log  OFF
slow_query_log_file     /var/lib/mysql/slow.log
socket  /var/lib/mysql/mysql.sock
sort_buffer_size    65536
sql_auto_is_null    OFF
sql_big_selects     ON
sql_big_tables  OFF
sql_buffer_result   OFF
sql_log_bin     ON
sql_log_off     OFF
sql_low_priority_updates    OFF
sql_max_join_size   18446744073709551615
sql_mode    
sql_notes   ON
sql_quote_show_create   ON
sql_safe_updates    OFF
sql_select_limit    18446744073709551615
sql_slave_skip_counter  0
sql_warnings    OFF
ssl_ca  
ssl_capath  
ssl_cert    
ssl_cipher  
ssl_key     
storage_engine  InnoDB
sync_binlog     0
sync_frm    ON
sync_master_info    0
sync_relay_log  0
sync_relay_log_info     0
system_time_zone    CET
table_definition_cache  400
table_open_cache    4
thread_cache_size   100
thread_concurrency  8
thread_handling     one-thread-per-connection
thread_stack    524288
time_format     %H:%i:%s
Variable_name   Value
time_zone   SYSTEM
timed_mutexes   OFF
tmp_table_size  16777216
tmpdir  /tmp
transaction_alloc_block_size    8192
transaction_prealloc_size   4096
tx_isolation    REPEATABLE-READ
unique_checks   ON
updatable_views_with_limit  YES
version     5.5.20
version_comment     MySQL Community Server (GPL) by Remi
version_compile_machine     x86_64
version_compile_os  Linux
wait_timeout    28800

Answer 1

优化 mysql 在网上得到了很好的评论，您会找到大量有关如何执行此操作的信息。从来没有“最好的参数”，最好的参数是那些适合你的需求，盒子硬件，mysql使用......
所以，我不会给出最好的参数，而是如何定义这些参数。做一些测试，你会很快找到自己的参数。

有很多可用的参数，但只有少数参数对调整您的 mysql 框非常重要。

最重要的变量是(对我来说，并不详尽):

-   max_connections
-   wait_timeout
-   thread_cache_size
-   table_cache
-   key_buffer_size
-   query_cache_size
-   tmp_table_size

要获取 mysql 服务器加载后的状态，请运行 mysqladmin processlist extended-status 如上所述。

1 - 两个最重要的变量: Table_cache 和 Key_buffer_size

如果 Opened_tables 很大，那么你的 table_cache 变量可能是
太小。
表缓存 64
Open_tables 64
Opened_tables 544468

这是第一个严重的问题。 "table_cache 是打开的数量
所有线程的表。 MySQL 是多线程的，可能会运行很多
一次对表进行查询，每一次查询都会打开一张表。”
因此，即使我们只有几张 table ，我们也需要更多
open_tables。

Opened_tables 值很高，显示的数量
缓存未命中。获得正确的 table_cache 大小是两个最好的之一
您可以采取哪些措施来提高性能。

如果 Key_reads 很大，那么你的 key_buffer_size 变量可能是
太小。缓存命中率可以计算为
Key_reads/Key_read_requests。
key_buffer_size 16M
Key_read_requests 2973620399
Key_reads 8490571
(缓存命中率 = 0.0028)

“key_buffer_size 影响索引缓冲区的大小和速度
索引处理，尤其是阅读。” MySQL 手册(和其他来源)说
“Key_reads/Key_read_request 比率通常应 < 0.01。”这是
其他最重要的事情是正确的。这里的值似乎是正确的 (< 0.01)

还要检查 key_write_requests 和 key_writes。
key_writes/key_writes_request 通常应该 < 1(接近 0.5 似乎没问题)

这是一个非常有趣的网络指针: table_cache and key_buffer_size

2 - 其他重要设置是: Wait_timeout、max_connexion、thread_cache

一点解释:
一般来说，你有很多 mysql 进程正在休眠，因为 wait_timeout 没有设置低。所以我确保将 wait_timeout 设置为一个非常低的值: 15 seconds (for me) 。这意味着 MySQL 将关闭任何空闲超过 15 秒的连接。

问题是您还必须增加 max_connexion(我的设置为 300)以确保没有大量空闲客户端保持连接并阻止新客户端连接并完成实际工作。
pbm 是盒子必须以非常高的速度创建新线程(MySQL 是多线程服务器)。这可能会占用大量 CPU 时间。

所以解决方案是使用 Thread_cache (来自 mysql doc):
“我们应该在缓存中保留多少线程以供重用。当客户端断开连接时，如果之前没有超过 thread_cache_size 的线程，则客户端的线程将被放入缓存中。所有新线程首先从缓存中取出，只有当缓存为空时才创建新线程。如果您有很多新连接，可以增加此变量以提高性能。 (通常，如果您有一个好的线程实现，这不会带来显着的性能改进。)通过检查 Connections 和 Threads_created 之间的差异，您可以看到当前线程缓存对您的效率有多高。”

如果 Threads_created 很大，您可能需要增加
thread_cache_size 变量。缓存命中率可以计算为
Threads_created/连接。
线程缓存大小 0
Threads_created 150022
连接 150023

这是应该解决的第二个问题。缓存大小为零是 my-medium.cnf 的默认值，但 my-large.cnf 中的推荐大小为 8。

你可以试试这个公式:table_cache = open table/max_used_connection

3 - 最后，你也可以看看: tmp_table_size 和 Handler_read_rnd/Handler_read_rnd_next

如果 Created_tmp_disk_tables 很大，您可能需要增加
tmp_table_size 变量用于获取基于内存的临时表
基于磁盘的。

tmp_table_size 32M
Created_tmp_disk_tables 3227
Created_tmp_tables 159832
Created_tmp_files 4444

Created_tmp_disk_tables 是“隐式临时表的数量
执行语句时创建的磁盘”和 Created_tmp_tables 是
基于内存。显然，如果您必须转到磁盘而不是
内存。大约 2% 的临时表转到磁盘，这看起来还不错
但增加 tmp_table_size 可能也不会受到伤害。

如果 Handler_read_rnd 很大，那么你可能有很多查询
需要 MySQL 扫描整个表，或者您有不使用的连接
键正确。
Handler_read_rnd 27712353
Handler_read_rnd_next 283536234

这些值很高，我们可能会继续改进
索引和查询。

我希望这将帮助你们中的一些人更多地了解如何优化 MYSQL 以满足您的需求、硬件盒或 mysql 当前使用情况。