Skip navigation links

MySQL Forums :: Performance :: Tuning headaches and questions


Advanced Search

Tuning headaches and questions
Posted by: Chad Gladue ()
Date: June 17, 2012 06:47AM

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

Options: ReplyQuote


Subject Views Written By Posted
Tuning headaches and questions 991 Chad Gladue 06/17/2012 06:47AM
Re: Tuning headaches and questions 529 Rick James 06/18/2012 07:59AM
Re: Tuning headaches and questions 585 Chad Gladue 06/18/2012 08:45AM
Re: Tuning headaches and questions 466 Rick James 06/19/2012 08:27AM
Re: Tuning headaches and questions 619 Chad Gladue 06/19/2012 08:38AM
Re: Tuning headaches and questions 470 Rick James 06/20/2012 09:11AM
Re: Tuning headaches and questions 412 Aftab Khan 06/22/2012 01:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.