Magento – enterprise_refresh_index Sleeping MySQL Connection

indexermagento-enterpriseMySQLreindex

There are times that the indexer that is ran by cron seems to get stuck at the MySQL as Sleeping connections can be seen. Indexer runs for several hours and this actually not running at all. I've searched for this but I couldn't find any related. Anyone who can shed a light? Possibly server configuration or Magento bug?

Best Answer

Are you using persistent connections? How many sleep connections do you have? What is the max amount of connections your DB Server is configured to allow?

To check how many sleep connections you have just run:

show full processlist;

To see the max_connections run:

show variables like 'max_connections';

I think that the sleeping connections are not the problem, mysqld will timeout sleep connections based on 2 values:

interactive_timeout wait_timetout

Both are 28800 seconds (8 hours) by default.

You can set these options in my.cnf (location of this file is different in different OS and DBs, percona, mysql, etc)

Also see this answer from Database Administrators: https://dba.stackexchange.com/a/1559 and if you would like to know more on how to debug the origin of the sleeping connections check this excellent article: https://www.percona.com/blog/2007/02/08/debugging-sleeping-connections-with-mysql/

"If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your app works just fine, you can leave the default. This is up to you."

Try to run the indexer from the console to see if it outputs some error:

php shell/indexer info # this will output the list of indexes then
php shell/indexer --reindex {index_name}
Related Topic