MySQL 5.5 myisam数据库调整

我正在使用MySQL 5.5与myisam分贝。 目前，有时候mysql的cpu占用率将达到200％的CPU资源。 在mysql中有很多查询“等待表locking”。 而在这个时候，php-fpm的CPU使用率会越来越高。 那么服务器会非常慢。 我必须重新启动php-fpm，然后服务器将恢复正常

我做了很多testing，似乎这个问题是由mysql造成的。 下面是我的服务器configuration，任何一个可以给我任何build议，以调整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 

• 在另一个数据库和另一个表中的数据库表块SELECTS MySQL插入
• 什么是使InnoDB for MySQL最高性能的一般技巧？
• 复制MyISAM表的logging需要很长时间
• 如何使用MySQL上的大部分内存
• 我可以在InnoDB和MyISAM之间来回更改MySQL表吗？