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)