Percona server performance degradation

We have Percona server 5.5.29-30.0 up and running with the following configuration

innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 310G
innodb_buffer_pool_instances = 64
innodb_thread_concurrency = 48
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 128M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_sync_spin_loops = 80
innodb_buffer_pool_restore_at_startup = 300
innodb_adaptive_hash_index_partitions = 20
innodb_flush_method = O_DIRECT
innodb_flush_neighbor_pages = cont
innodb_log_block_size = 4096

and every 5-7 days all of the queries are getting slower, restart of client application doesn’t help, but after server restart everything looks fine.
Below you can see graph from zabbix with query response time, at 05:00 the server was restarted.

Has anyone ideas ? May be MySQL can work good with such huge buffer pool?

Do you have more graphs? Preferably on:

  • disk i/o
  • flushed pages
  • buffer pool size and content (esp. dirty pages)
  • binary logging

buffer pool

Checkpoint age

Commands

Innodb pages

Innodb rows

Disk data (2 images because of buffer pool restore on startup and zabbix terrible scaling)


Disk IO


Innodb pendings

Threads

I don’t see anything odd here. You say queries are getting slower. How much slower? Is this visible in a 10-day graph?

I still miss direct disk stats. What is the swapiness? Have you checked vmstat swapping statistics?

Could you check buffer pool mutex contention when the server is slow? See http://www.percona.com/doc/percona-server/5.5/scalability/innodb_split_buf_pool_mutex.html

There may be other troubleshooting hints in http://www.percona.com/resources/technical-presentations/expert-troubleshooting-resolving-mysql-problems-quickly-percona

On the first graph the number of queries with response time 0.01 (yellow line) after restart gets much lower. But the load on server is completely the same.
swapiness is 0.
Server is not very slow, its getting slower and this is enough for us to see the problems in our software with response time.

I have same problem in 5.1.54 (FreeBSD 9.0 amd64). After restart percona server works fast, but after some hours (about 24) it works very slow. There is some statistics:

query_response_time_stats when performance are degraded (resets each 2 min):

time count total
0.000001 0 0.000000
0.000010 165 0.000646
0.000100 9172 0.464741
0.001000 7510 2.384370
0.010000 2555 9.394653
0.100000 1447 67.788210
1.000000 2634 1040.339679
10.000000 210 336.660582
100.000000 0 0.000000
1000.000000 0 0.000000
10000.000000 0 0.000000
100000.000000 0 0.000000
1000000.00000 0 0.000000

After restart:

time count total
0.000001 1 0.000000
0.000010 354 0.001318
0.000100 22695 1.113743
0.001000 12765 3.382774
0.010000 4640 15.994595
0.100000 1821 97.915290
1.000000 901 178.562421
10.000000 1 1.671335
100.000000 0 0.000000
1000.000000 0 0.000000
10000.000000 0 0.000000
100000.000000 0 0.000000
1000000.00000 0 0.000000

Status when performance are degraded




	Variable_name Value


	Aborted_clients 56


	Aborted_connects 154


	Binlog_cache_disk_use 6130


	Binlog_cache_use 2110517


	Bytes_received 115


	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_backup_table 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_load_master_data 0


	Com_load_master_table 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_restore_table 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_show_authors 0


	Com_show_binlog_events 0


	Com_show_binlogs 0


	Com_show_charsets 0


	Com_show_client_statistics 0


	Com_show_collations 0


	Com_show_column_types 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_index_statistics 0


	Com_show_keys 0


	Com_show_master_status 0


	Com_show_new_master 0


	Com_show_open_tables 0


	Com_show_patches 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_slave_hosts 0


	Com_show_slave_status 0


	Com_show_slave_status_nolock 0


	Com_show_status 1


	Com_show_storage_engines 0


	Com_show_table_statistics 0


	Com_show_table_status 0


	Com_show_tables 0


	Com_show_thread_statistics 0


	Com_show_temporary_tables 0


	Com_show_triggers 0


	Com_show_user_statistics 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 259251


	Created_tmp_disk_tables 0


	Created_tmp_files 8883


	Created_tmp_tables 0


	Delayed_errors 0


	Delayed_insert_threads 2


	Delayed_writes 133198


	Flashcache_enabled OFF


	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_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 852611


	Innodb_buffer_pool_pages_dirty 312


	Innodb_buffer_pool_pages_flushed 6580420


	Innodb_buffer_pool_pages_free 919228


	Innodb_buffer_pool_pages_misc 63169


	Innodb_buffer_pool_pages_total 1835008


	Innodb_buffer_pool_read_ahead 115089


	Innodb_buffer_pool_read_ahead_evicted 0


	Innodb_buffer_pool_read_requests 52557150083


	Innodb_buffer_pool_reads 507097


	Innodb_buffer_pool_wait_free 0


	Innodb_buffer_pool_write_requests 493538656


	Innodb_data_fsyncs 6401331


	Innodb_data_pending_fsyncs 0


	Innodb_data_pending_reads 0


	Innodb_data_pending_writes 0


	Innodb_data_read 10896773120


	Innodb_data_reads 555665


	Innodb_data_writes 9272785


	Innodb_data_written 131561846784


	Innodb_dblwr_pages_written 0


	Innodb_deadlocks 0


	Innodb_dblwr_writes 0


	Innodb_dict_tables 382


	Innodb_have_atomic_builtins ON


	Innodb_log_waits 0


	Innodb_log_write_requests 50662374


	Innodb_log_writes 1933044


	Innodb_os_log_fsyncs 1955611


	Innodb_os_log_pending_fsyncs 0


	Innodb_os_log_pending_writes 0


	Innodb_os_log_written 23735574016


	Innodb_page_size 16384


	Innodb_pages_created 5485198


	Innodb_pages_read 664953


	Innodb_pages_written 6580420


	Innodb_row_lock_current_waits 0


	Innodb_row_lock_time 1024994


	Innodb_row_lock_time_avg 10459


	Innodb_row_lock_time_max 92585


	Innodb_row_lock_waits 98


	Innodb_rows_deleted 8180322


	Innodb_rows_inserted 23039851


	Innodb_rows_read 67296077907


	Innodb_rows_updated 3001715


	Key_blocks_not_flushed 0


	Key_blocks_unused 800000


	Key_blocks_used 57376


	Key_read_requests 147480037


	Key_reads 56872


	Key_write_requests 5282504


	Key_writes 742619


	Last_query_cost 0.000000


	Max_used_connections 39


	Not_flushed_delayed_rows 0


	Open_files 460


	Open_streams 0


	Open_table_definitions 431


	Open_tables 1578


	Opened_files 17114921


	Opened_table_definitions 0


	Opened_tables 0


	Prepared_stmt_count 0


	Qcache_free_blocks 28166


	Qcache_free_memory 95901016


	Qcache_hits 241166


	Qcache_inserts 17846299


	Qcache_lowmem_prunes 6041433


	Qcache_not_cached 5724637


	Qcache_queries_in_cache 81740


	Qcache_total_blocks 196354


	Queries 42349353


	Questions 2


	Rpl_status NULL


	Select_full_join 0


	Select_full_range_join 0


	Select_range 0


	Select_range_check 0


	Select_scan 0


	Slave_open_temp_tables 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


	Table_locks_immediate 65739338


	Table_locks_waited 67201


	Tc_log_max_pages_used 0


	Tc_log_page_size 0


	Tc_log_page_waits 1


	Threads_cached 33


	Threads_connected 8


	Threads_created 39


	Threads_running 5


	Uptime 255146


	Uptime_since_flush_status 255146


	
Status after restart (10 min ago)



	Variable_name Value


	Aborted_clients 0


	Aborted_connects 0


	Binlog_cache_disk_use 5


	Binlog_cache_use 8139


	Bytes_received 115


	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_backup_table 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_load_master_data 0


	Com_load_master_table 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_restore_table 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_show_authors 0


	Com_show_binlog_events 0


	Com_show_binlogs 0


	Com_show_charsets 0


	Com_show_client_statistics 0


	Com_show_collations 0


	Com_show_column_types 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_index_statistics 0


	Com_show_keys 0


	Com_show_master_status 0


	Com_show_new_master 0


	Com_show_open_tables 0


	Com_show_patches 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_slave_hosts 0


	Com_show_slave_status 0


	Com_show_slave_status_nolock 0


	Com_show_status 1


	Com_show_storage_engines 0


	Com_show_table_statistics 0


	Com_show_table_status 0


	Com_show_tables 0


	Com_show_thread_statistics 0


	Com_show_temporary_tables 0


	Com_show_triggers 0


	Com_show_user_statistics 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 964


	Created_tmp_disk_tables 0


	Created_tmp_files 14


	Created_tmp_tables 0


	Delayed_errors 0


	Delayed_insert_threads 2


	Delayed_writes 704


	Flashcache_enabled OFF


	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_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 50879


	Innodb_buffer_pool_pages_dirty 738


	Innodb_buffer_pool_pages_flushed 10701


	Innodb_buffer_pool_pages_free 1781756


	Innodb_buffer_pool_pages_misc 2373


	Innodb_buffer_pool_pages_total 1835008


	Innodb_buffer_pool_read_ahead 2096


	Innodb_buffer_pool_read_ahead_evicted 0


	Innodb_buffer_pool_read_requests 197925577


	Innodb_buffer_pool_reads 48141


	Innodb_buffer_pool_wait_free 0


	Innodb_buffer_pool_write_requests 1358955


	Innodb_data_fsyncs 30687


	Innodb_data_pending_fsyncs 0


	Innodb_data_pending_reads 0


	Innodb_data_pending_writes 0


	Innodb_data_read 828493824


	Innodb_data_reads 48501


	Innodb_data_writes 34924


	Innodb_data_written 253442048


	Innodb_dblwr_pages_written 0


	Innodb_deadlocks 0


	Innodb_dblwr_writes 0


	Innodb_dict_tables 124


	Innodb_have_atomic_builtins ON


	Innodb_log_waits 0


	Innodb_log_write_requests 161961


	Innodb_log_writes 9379


	Innodb_os_log_fsyncs 9417


	Innodb_os_log_pending_fsyncs 0


	Innodb_os_log_pending_writes 0


	Innodb_os_log_written 78097408


	Innodb_page_size 16384


	Innodb_pages_created 26039


	Innodb_pages_read 50433


	Innodb_pages_written 10701


	Innodb_row_lock_current_waits 0


	Innodb_row_lock_time 0


	Innodb_row_lock_time_avg 0


	Innodb_row_lock_time_max 0


	Innodb_row_lock_waits 0


	Innodb_rows_deleted 2251


	Innodb_rows_inserted 47140


	Innodb_rows_read 258897798


	Innodb_rows_updated 6016


	Key_blocks_not_flushed 1


	Key_blocks_unused 855901


	Key_blocks_used 1467


	Key_read_requests 643025


	Key_reads 1466


	Key_write_requests 19259


	Key_writes 2930


	Last_query_cost 0.000000


	Max_used_connections 12


	Not_flushed_delayed_rows 1


	Open_files 161


	Open_streams 0


	Open_table_definitions 130


	Open_tables 424


	Opened_files 82941


	Opened_table_definitions 0


	Opened_tables 0


	Prepared_stmt_count 0


	Qcache_free_blocks 1


	Qcache_free_memory 178309504


	Qcache_hits 73


	Qcache_inserts 70702


	Qcache_lowmem_prunes 0


	Qcache_not_cached 23411


	Qcache_queries_in_cache 39405


	Qcache_total_blocks 78891


	Queries 166623


	Questions 2


	Rpl_status NULL


	Select_full_join 0


	Select_full_range_join 0


	Select_range 0


	Select_range_check 0


	Select_scan 0


	Slave_open_temp_tables 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


	Table_locks_immediate 281388


	Table_locks_waited 521


	Tc_log_max_pages_used 0


	Tc_log_page_size 0


	Tc_log_page_waits 0


	Threads_cached 4


	Threads_connected 10


	Threads_created 12


	Threads_running 8


	Uptime 376


	Uptime_since_flush_status 376


	

There is no SWAP (48 GB RAM). Disks are not busy. CPU load about 2 (4 cores+hypertrading). What information may helps?