我使用的是mysql 5.5，使用myisam db。目前，mysql的cpu使用率有时会达到200%。而且mysql中有很多“等待表锁定”的查询。此时php-fpm的cpu使用率会越来越高。然后服务器就会变得非常非常慢。我必须重新启动php-fpm，然后服务器才会恢复正常
我做了很多测试，看来这个问题是由mysql引起的。下面是我的服务器配置，有人能给我一些调整mysql的建议吗？
目前我的数据库大约为5.5G，存储在SSD磁盘上。我的服务器有32G内存。
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
You have 4886 out of 66789208 that take longer than 2.000000 sec. to complete
[: 0.00000000000000000000: bad number
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 512
Current threads_cached = 505
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 768
Current threads_connected = 11
Historic max_used_connections = 513
The number of used connections is 66% of the configured maximum.
Your max_connections variable seems to be fine.
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 8.13 G
Configured Max Per-thread Buffers : 11.43 G
Configured Max Global Buffers : 512 M
Configured Max Memory Limit : 11.93 G
Physical Memory : 32.75 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 1.55 G
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 1341
Key buffer free ratio = 0 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.
QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 8 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 3.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
OPEN FILES LIMIT
Current open_files_limit = 10240 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 4096 tables
Current table_definition_cache = 400 tables
You have a total of 347 tables
You have 1991 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M
Of 18373 temp tables, 4% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 308 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 154
You may benefit from selective use of InnoDB.
| Com_admin_commands | 0 |
| 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_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| 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 | 0 |
| 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 | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| 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 | 1 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 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 | 0 |
| 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 | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 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_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| 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 | 0 |
| 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 | 6015328 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 351847 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 428684 |
| Key_read_requests | 1195291869 |
| Key_reads | 890527 |
| Key_write_requests | 5132794 |
| Key_writes | 4715128 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 513 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1867 |
| Open_streams | 0 |
| Open_table_definitions | 347 |
| Open_tables | 1991 |
| Opened_files | 393481 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| 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 | 66891279 |
| Questions | 2 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| 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 | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| 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 | 52625968 |
| Table_locks_waited | 341275 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 506 |
| Threads_connected | 6 |
| Threads_created | 513 |
| Threads_running | 4 |
| Uptime | 89383 |
| Uptime_since_flush_status | 89383 |
+------------------------------------------+-------------+
265 rows in set (0.00 sec)
[mysqld]
server-id = 1
port = 3306
socket = /tmp/mysql.sock
default-storage-engine=MyISAM
ignore-builtin-innodb
skip-external-locking
skip-networking
skip-name-resolve
event_scheduler = 1
sql-mode = NO_UNSIGNED_SUBTRACTION
max_allowed_packet = 2M
max_connections = 768
max_connect_errors = 1844674407370954751
slow_launch_time=2
general-log=0
slow_query_log=1
slow-query-log-file=/web/log/slowquery.log
long_query_time=2
wait_timeout = 10
connect_timeout = 10
interactive_timeout = 10
tmp_table_size=32M
max_heap_table_size=32M
key_buffer_size = 512M
join_buffer_size = 3M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 2M
myisam_sort_buffer_size = 64M
table_open_cache = 4096
query_cache_type = 0
query_cache_limit = 2M
query_cache_size = 0
#query_cache_size = 32M
#query_cache_size = 256M
query_cache_min_res_unit = 4K
query_alloc_block_size = 1024
query_prealloc_size = 8192
thread_concurrency = 48
thread_cache_size = 512
open-files-limit=10240
low-priority-updates=1
concurrent_insert=ALWAYS
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 64M
write_buffer = 64M
[mysqlhotcopy]
interactive-timeout
答案1
等待表锁意味着您在一个或多个表上有写入争用。MyISAM 仅具有每个表的锁定，因此比 InnoDB 等更频繁地遇到此锁定问题。您可能能够优化读取查询以留出更多时间进行写入，但最好启用 InnoDB 并将表转换为 InnoDB 格式。
答案2
当然，切换到 innodb 会解决争用问题 - 您不会有那么多的表等待 - 但它不会帮助您的系统运行得更快。目前看来您的查询缓存已被禁用 - 您不使用它的原因是什么？它并不总是有助于提高性能，但它确实可以。
您应该在这个系统上有足够的备用内存 - 增加排序缓冲区会有所帮助（它们也会对您的 SSD 产生比对它有益的更多的写入）。
您的打开文件限制很高-为什么？
您的数据库目前正在非常努力地工作 - 我猜想磁盘 I/O 很少。但整个数据库都放在内存中 - 因此您的问题主要是由于查询速度慢 - 您需要开始分析您的慢查询日志并添加更好的索引。
在处理这些错误查询时，为了暂时保持您的网站正常运行，您可能需要限制 mysql 可以使用的 CPU 数量。