Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm using mysql 5.5 with myisam db. Currently, sometimes the cpu usage of mysql will reach 200% CPU resource. And there are lots of queries 'waiting for table locking' in mysql. And at this time, the cpu usage of php-fpm will getting higher and higher. Then the server will be very very slow. I have to restart php-fpm, then server will go back to normal

I made lots of test, and it seems that this problem is caused by mysql. Below is my servers configuration, any one can give me any suggestion to tunning mysql?

Currently my DB is about 5.5G and stored on a SSD disk.My server has 32G memory.

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
share|improve this question
    
Can you send over the queries that cause the table lock ? Also during a lock can you send the status of the server ? –  silviud May 7 '13 at 1:55
add comment

2 Answers 2

Waiting for table lock means that you have write contention on one or more tables. MyISAM only has per-table locks and thus runs into this locking issues much mre frequently than e.g. InnoDB. You may be able to optimize your reading queries to leave more time for writing, but it's better to enable InnoDB and convert your tables to InnoDB format.

share|improve this answer
1  
Waiting for table locks does not consume large amounts of CPU –  symcbean May 6 '13 at 17:10
    
Thanks for your help. Currently I can't change it to innodb. –  Meteor May 7 '13 at 0:04
add comment

Certainly switching to innodb will solve the contention problem - you won't have as many table waits - but it won't help your system to go any faster. Currently it looks like your query cache is disabled - is there a reason you don't use it? It doesn't always help with performance but it can do.

You should have plenty of spare memory on this system - increasing the sort buffers will help (they're also generating a lot more writes to your SSD than is good for it).

Your open files limit is very high - why?

Your DB is currently working very hard - I'm guessing there's very little disk I/O going on. But the entire DB fits in memory - therefore your problems are mostly due to slow queries - you need to start analysing your slow query log and adding better indexes.

As a temporary reprieve to keep your site running when it's handling these bad queries you might want to restrict the number of CPUs mysql can use.

share|improve this answer
    
In the past I enabled query cache, but the hit rate is less than 1/3. So I disabled it. And for the slow query, it seems that 4886 out of 66789208 are slow queries. I think it's not very high. –  Meteor May 7 '13 at 0:06
    
1/3 is not bad for a hit ratio - the reason for NOT using the cache depends on it's effect on overall performance - there are some contention issues regarding running a large cache. It doesn'y matter if its 4886 queries which are slow or just 1 - that's where your bottleneck is. –  symcbean May 7 '13 at 10:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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