Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Every so often our box is HAMMERED to a crawl %Wait goes to 40 or 50% and connections grows to 300 or so. i think its due to a query running, but i wanted to see if any of the settigns on the mysql config file were obviously causing more damage then good... here is my config file:

We are running on a server with MySQL v5.5.19 on Linux 64-bit with 16G of ram and 6 CPU Cores, we are almost 95% read and 5% write so optimize for read performance is priority, thank you so much for looking !

[mysqld]
port                            = 3306
user                            = mysql
socket                          = /var/lib/mysql/mysql.sock
pid-file                        = /mysql/mysqld.pid
log-error                       = /var/log/mysql/mysqld.err
basedir                         = /usr/
datadir                         = /mysql
skip-bdb

back_log                        = 50
max_connections                 = 500
max_connect_errors              = 9999999
table_cache                     = 150000
max_allowed_packet              = 16M
binlog_cache_size               = 1M
max_heap_table_size             = 32M
join_buffer_size                = 500K
thread_cache_size               = 100
thread_concurrency              = 12
sort_buffer_size                = 2M
read_buffer_size                = 2M
read_rnd_buffer_size            = 2M
query_cache_size                = 512M
query_cache_limit               = 8M
query_cache_min_res_unit        = 2K
thread_stack                    = 192K
#transaction_isolation           = READ-COMMITED
tmp_table_size                  = 32M
#log_slow_queries
long_query_time                 = 2
#log_long_format
tmpdir                          = /dev/shm
memlock

# *** INNODB Specific options ***

innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size         = 2G
#innodb_data_file_path           = ibdata1:643M:autoextend
innodb_flush_method             = O_DIRECT
innodb_file_io_threads          = 6
innodb_thread_concurrency       = 12
innodb_flush_log_at_trx_commit  = 0
innodb_log_buffer_size          = 20M
#innodb_log_file_size            = 800M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table           = 1

# *** MyIsam Specific Options ***
myisam_sort_buffer_size         = 64M


[client]
socket                          = /var/lib/mysql/mysql.sock

and here is our "Show Status" from the database:

    Variable_name   Value
Aborted_clients 80
Aborted_connects        44
Binlog_cache_disk_use   0
Binlog_cache_use        0
Binlog_stmt_cache_disk_use      0
Binlog_stmt_cache_use   0
Bytes_received  140
Bytes_sent      188
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     589414
Created_tmp_disk_tables 0
Created_tmp_files       4857
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
Innodb_buffer_pool_pages_data   72958
Innodb_buffer_pool_pages_dirty  42
Innodb_buffer_pool_pages_flushed        6008262
Innodb_buffer_pool_pages_free   56946
Innodb_buffer_pool_pages_misc   1168
Innodb_buffer_pool_pages_total  131072
Innodb_buffer_pool_read_ahead_rnd       0
Innodb_buffer_pool_read_ahead   7734
Innodb_buffer_pool_read_ahead_evicted   0
Innodb_buffer_pool_read_requests        30450838150
Innodb_buffer_pool_reads        52324
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests       15217281
Innodb_data_fsyncs      595342
Innodb_data_pending_fsyncs      0
Innodb_data_pending_reads       0
Innodb_data_pending_writes      0
Innodb_data_read        987058176
Innodb_data_reads       62345
Innodb_data_writes      3401311
Innodb_data_written     105511973376
Innodb_dblwr_pages_written      3004131
Innodb_dblwr_writes     83802
Innodb_have_atomic_builtins     ON
Innodb_log_waits        0
Innodb_log_write_requests       15868390
Innodb_log_writes       263129
Innodb_os_log_fsyncs    293796
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   7056227840
Innodb_page_size        16384
Innodb_pages_created    13192
Innodb_pages_read       60111
Innodb_pages_written    3004131
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    1763399
Innodb_row_lock_time_avg        33
Innodb_row_lock_time_max        18220
Innodb_row_lock_waits   52024
Innodb_rows_deleted     115172
Innodb_rows_inserted    157979
Innodb_rows_read        10140856389
Innodb_rows_updated     1181312
Innodb_truncated_status_writes  0
Key_blocks_not_flushed  0
Key_blocks_unused       1
Key_blocks_used 6698
Key_read_requests       909967460
Key_reads       131260
Key_write_requests      6034682
Key_writes      456468
Last_query_cost 0.000000
Max_used_connections    289
Not_flushed_delayed_rows        0
Open_files      23537
Open_streams    0
Open_table_definitions  11716
Open_tables     25689
Opened_files    8995586
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      40836
Qcache_free_memory      63388816
Qcache_hits     26422772
Qcache_inserts  30862857
Qcache_lowmem_prunes    259834
Qcache_not_cached       1104542
Qcache_queries_in_cache 97427
Qcache_total_blocks     277837
Queries 65505499
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   43157142
Table_locks_waited      3791
Tc_log_max_pages_used   0
Tc_log_page_size        0
Tc_log_page_waits       0
Threads_cached  97
Threads_connected       5
Threads_created 536
Threads_running 1
Uptime  318939
Uptime_since_flush_status       318939
share|improve this question
add comment

3 Answers 3

High CPU --> queries/indexes/schema needs fixing. Period. Tuning won't help. Period.

Also, the SHOW STATUS is of little use, since it applies to the SESSION. Do SHOW GLOBAL STATUS instead.

table_cache = 150000

That's excessive (as I said on forums.mysql.com)

share|improve this answer
add comment

MONyog proactively monitors all MySQL servers using a set of predefined expert advisors, to identify and alert DBAs of problems, security vulnerabilities and tuning opportunities so they can be acted upon well in advance of a problem or outage occurring.

Also, MONyog finds problem SQL by these methods

  1. Querying MySQL Proxy
  2. Analysing General Query Log
  3. Analysing Slow Query Log
  4. Issuing SHOW PROCESSLIST at regular intervals

and shows in human readable form, it's very hard to manually monitor slow and general log files.

Apart from this MONyog has several other important features which can be helpful in tuning your server performance - http://www.webyog.com/en/monyog_feature_list.php

share|improve this answer
add comment

Perhaps you are missing indexes on some pertinent fields? You can use the slow query log to help determine which queries are running slowly, and which fields should have indexes added.

share|improve this answer
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.