Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
run 'show full processlist' and include that info please –  Hrvoje Špoljar Oct 8 '12 at 10:16
    
the screenshot above is the result of show full processlist in phpmyadmin –  Sidhannowe Oct 9 '12 at 7:19
    
I don't see any queries there... try 'swho full processlist' and then look at your screenshot and see if it looks the same.... –  Hrvoje Špoljar Oct 9 '12 at 10:24
    
This is the problem: show full processlist and even the mysql-slow.log show me "COMMIT" queries, this is the way innodb and Magento works. I don't know how to track the queries before this commit –  Sidhannowe Oct 10 '12 at 9:18
    
I was hoping there would be more than "COMMIT" queries in that output; for example some query that is blocking those "COMMIT"s . Try enabling slow query log; on the other hand if you have any system trending enabled; check trends to see if anything else hogs system at that time that would explain why MySQL hangs/slows down at the time. –  Hrvoje Špoljar Oct 10 '12 at 9:24

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.