MySQL 5.0, InnoDB Table, Slow inserts when heavy traffic.

Question

I have INNODB table that stores user navigation details once user logs in. I have simple INSERT statement for this purpose. But sometimes this INSERT will take 15-24 secs when there is heavy traffic otherwise for single user it comes in micro seconds. Server has 2GB RAM.
Below is MySQL configuration details:

max_connections=500

# You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 800M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size 
innodb_log_file_size = 200M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
table_cache = 90
query_cache_size = 256M
query_cache_limit = 256M 
thread_cache_size = 16
sort_buffer_size = 64M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT 
innodb_buffer_pool_instances=8 

Thanks.

Answer 1

As a first measure have you considered updating? 5.0 is old. It's end of product lifecycle was reached. There has not been any changes to it since two years. There were made serious improvements to different aspects of the whole DBMS in versions 5.1 and 5.5. You should seriously consider upgrading.

You might want to try the tuning primer as another direction in what options you can change.

You can also check with SHOW FULL PROCESSLIST in what state single threads of MySQL are hanging. Maybe you spot something relevant.