mysql is quick after restart, slow over time

After about an hour of running mysql it becomes quite a hog on the server even when there is little traffic. I will then restart mysql and then it becomes very quick for the first hour or so and then it again starts to bog down. It seems like maybe caching slows mysql down badly?

I've got 16G RAM.

Here is my config

[mysqld_safe]
nice=-5
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=8192
[mysqld]
sort_buffer_size=2M
bulk_insert_buffer_size=8M
key_buffer_size=4200M
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=2
transaction_prealloc_size=4096
range_alloc_block_size=4096
interactive_timeout=60
max_connect_errors=100000
innodb_log_files_in_group=2
query_cache_size=256M
table_open_cache=10000
table_definition_cache=10000
connect_timeout=15
join_buffer_size=1M
query_prealloc_size=262144
wait_timeout=65
tmp_table_size=256M
innodb_read_io_threads=2
innodb_data_file_path="ibdata1:10M:autoextend"
max_length_for_sort_data=1024
read_rnd_buffer_size=3M
innodb_log_buffer_size=8M
innodb_log_file_size=5M
innodb_thread_concurrency=8
max_seeks_for_key=1000
slow_query_log = 1
log_slow_queries ="/var/log/mysql-slow.log"
long_query_time = 1
concurrent_insert=2
myisam_max_sort_file_size=4096M
read_buffer_size=512k
myisam_sort_buffer_size=256M
innodb_io_capacity=200
query_cache_limit=1M
query_alloc_block_size=65536
max_write_lock_count=8
innodb_write_io_threads=2
max_allowed_packet=128M
innodb_buffer_pool_size=32M
 
default-storage-engine=MyISAM
group_concat_max_len=1024
back_log=100
max_connections=750
transaction_alloc_block_size=8192
thread_cache_size=768
tmpdir="/disk5/mysqltmp"
query_cache_type=1
datadir="/disk5"
innodb_open_files=1000
max_heap_table_size=128M
net_buffer_length=16384
[mysqldump]
quick
max_allowed_packet=128M
[mysqlhotcopy]
interactive-timeout
[myisamchk]
sort_buffer_size=1M
key_buffer_size=4500M
write_buffer_size=1M
read_buffer_size=1M
[mysqld]
local-infile=0
 

Code (markup):

 

What's hitting the database? is it custom written code or something off-the-shelf? If it's custom written are you properly closing connections and transactions?

If that's not the problem, then try playing with the optimizer flags, you can find them in the MySQL docs. The optimizer tends to kick in awhile after restart after enough statistics on the database have been gathered. Normally it helps, but sometimes especially for complex queries it can make execution time worse and hog resources.

 

Can you explain your usage and what storage engine you typically use? Also, what sort of hardware and disk setup are you using?

MyISAM is pretty much un-tunable, so assuming you're using Innodb, the biggest factor is innodb_buffer_pool_size which should be around 12Gb on a server like this. Your key_buffer_size is off the chart. Is there a reason you need 5Gb for that value?

Here's what I've got on a 16Gb setup. This DB has about 100Gb of data in it.

 
 
[mysqld]
log-slow-queries="/var/lib/mysql/mysql.slow"
thread_cache_size=50
query_cache_size=0
max_allowed_packet=512M
innodb_log_file_size=256M
max_connections=500
tmp_table_size=32M
port=3306
innodb_additional_mem_pool_size=20M
innodb_lock_wait_timeout=50
default_storage_engine=InnoDB
socket="/var/lib/mysql/mysql.sock"
innodb_flush_log_at_trx_commit=1
long_query_time=1
max_connect_errors=1000000
query_cache_type=0
open_files_limit=65535
innodb_flush_method=O_DIRECT
myisam_recover="FORCE,BACKUP"
table_definition_cache=4096
table_open_cache=10240
skip-symbolic-links
local-infile=0
innodb_buffer_pool_size=12G
key_buffer_size=32M
thread_concurrency=16
innodb_log_buffer_size=8M
skip_show_database
secure_auth=1
sync_binlog=0
skip-external-locking
max_heap_table_size=32M
innodb_file_per_table=1
federated
 
 

Code (markup):

 

Are you properly closing database connections at the end of your queries? Leaving connections open and letting them time out eats memory, which is immediately solved by a reboot, then slowly fills up again. SQL will expand to take up as much memory as allowed, then move to swap file space once memory is full - the problem is if SQL hasn't been configured to limit itself memory-wise, it will expand to the point where it starts robbing other critical system applications of the memory they need so everything starts slowing down. You need to figure out how much memory non-SQL services are taking up on your server and configure SQL to never use more memory than that.

 

You should verify if your server is not hiperpagining(swapping a lot). You have a considerable amount of RAM but is not too much to verify that, plus is very simple(vmstat for example).

 

How's the error log look? I've had performance issues in the past when the error logs start filling up with silent warnings from poorly written software. Some systems are set up to backup or flush the logs on reboot in addition to over a certain period of time, so that could be a possible culprit. (usually though this is more of a PHP issue than a mySQL one)

Also are you actually using innoDB -- the optimizer for that degrades over time, which can also cause performance lag; one of the ways to tell is if it gets worse, worse, worse, then every half hour (or some other interval) it magically gets better, then worse, worse, worse again. The way to get around that is to run a "ANALYZE TABLE tablename" before any really massive ugly queries. The analyze takes a while, but it often speeds up queries on really large tables enough to offer some improvement and 'level out' how long it takes... ends up not as fast as at boot, but not as slow as worst-case. (though my answer for any tables showing this issue is to switch them back to myisam)