Intermittent blocking mysql query

Question

I have a big problem on our server which host about ten magento website. I make all obvious optimizations like APC for Opcode, APC for cache backend, innodb settings tuning, magento compilation, etc.

The websites load correctly but sometimes, 5-6 times a day, there are MySQL queries “COMMIT” that block the MySQL server and put a very long time to execute (> 500s). Then with all the new incoming connections the server is full loaded with an error “Too many connections”.

Here a screenshot of Phpmyadmin when the problem come: http://imagesia.com/process_2l0n

My my.cnf (without the first default part)

# # * Fine Tuning # key_buffer = 384M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 100 table_cache = 2048 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_type = 1 query_cache_limit = 1M query_cache_size = 64M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 log_error = /var/log/mysql/error.log # Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 5 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 64M innodb_flush_method=O_DIRECT # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem # limite à partir de laquelle les tables temporaires sont créées sur le DD tmp_table_size=128M max_heap_table_size=128M 

Any ideas ?

Thank you for your help.

Answer 1

I made many MySQL optimizations with the help of the great utility MySQLtuner and now the problem is gone.