Mryntu

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?

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

configure your mysql-server by setting a shorter timeout on wait_timeout and interactive_timeout

mysql> show variables like "%timeout%";

+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)

Set with:

set global wait_timeout=3;
set global interactive_timeout=3;

(and also set in your configuration file, for when your server restarts)

But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...