MySQL 5.5.8 Gets Periodic Lag
- by CYREX
Am using MySQL 5.5.8 on an Ubuntu system and every X amount of time it creates a huge lag that lasts a couple of seconds. Then all goes back to normal until the next lag. The time period varies but it looks like it happen periodically. Am using InnoDB. It is like hiccups in the MySQL. What could be creating this sort of periodic problem.
Do not have any cron jobs or process running every time the X period happens.
The X period could be between 30 minutes to 2 hours. So for example it could happen every 30 minutes for the next 12 hours or it could happen every 2 hours for the next 8 hours.
key_buffer_size = 256M
max_allowed_packet = 1M
table_cache = 1024
table_open_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 32M
thread_cache_size = 128
query_cache_size= 128M
log-slow-queries = slow.log
long_query_time = 5
log-queries-not-using-indexes
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
max_connections=512
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 0
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M
There are about 200+ tables divided in 3 databases. The most written too is in InnoDB. The other ones are more read. Several of the tables in the InnoDB have more than 2 million records. The other databases top at about 400 thousand records and do not change so often.
The PC is a Core 2 Duo 8400 with 4GB RAM, 32Bit Ubuntu.
