mysql becomes unresponsive at same time every day
My server goes down every day at same time. I found that the issue was due to mysql.
I tried show processlist; and found that data base was entering in to sleep mode. I saw there was so many inserts in log_url table as shown below
mysql> show processlist;
+-------+---------------+-----------+------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------------+-----------+------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 72962 | sonicsense_db | localhost | sonicsense_db | Query | 1 | Sending data | SELECT `log_url`.* FROM `log_url` LIMIT 2381 OFFSET 2992917 |
| 74069 | sonicsense_db | localhost | sonicsense_db | Query | 4 | Updating | UPDATE `cron_schedule` SET `job_code` = 'captcha_delete_expired_images', `status` = 'error', `messag |
| 74073 | sonicsense_db | localhost | sonicsense_db | Query | 51 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528425', '4646727', '2013-08- |
| 74074 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 52 | | NULL |
| 74077 | sonicsense_db | localhost | sonicsense_db | Query | 41 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528426', '4646728', '2013-08- |
| 74078 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 42 | | NULL |
| 74079 | sonicsense_db | localhost | sonicsense_db | Query | 36 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528427', '4646729', '2013-08- |
| 74080 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 37 | | NULL |
| 74081 | sonicsense_db | localhost | sonicsense_db | Query | 34 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528428', '4646730', '2013-08- |
| 74082 | sonicsense_db | localhost | sonicsense_db | Query | 31 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528429', '4646731', '2013-08- |
| 74084 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 32 | | NULL |
| 74085 | sonicsense_db | localhost | sonicsense_db | Query | 30 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528430', '4646732', '2013-08- |
| 74086 | sonicsense_db | localhost | sonicsense_db | Query | 31 | Updating | UPDATE `catalogsearch_query` SET `query_text` = 'PreSonus', `num_results` = '154', `popularity` = '3 |
| 74087 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 31 | | NULL |
| 74088 | sonicsense_db | localhost | sonicsense_db | Query | 20 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528431', '4646734', '2013-08- |
| 74089 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 20 | | NULL |
| 74090 | sonicsense_db | localhost | sonicsense_db | Query | 12 | update | INSERT IGNORE INTO core_cache_tag (tag, cache_id) VALUES ('MAGE_cache_DEFAULT', 'MAGE_cache_LAYOUT_1 |
| 74091 | sonicsense_db | localhost | sonicsense_db | Query | 10 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528432', '4646736', '2013-08- |
| 74092 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 11 | | NULL |
| 74093 | sonicsense_db | localhost | sonicsense_db | Query | 9 | update | INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('6528433', '4646737', '2013-08- |
| 74094 | sonicsense_db | localhost | sonicsense_wp_db | Sleep | 10 | | NULL |
| 74095 | sonicsense_db | localhost | NULL | Query | 0 | NULL | show processlist |
+-------+---------------+-----------+------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
restart of mysql resolves my problem. How can i detect what is causing so many inserts and why my database is entering in to sleep mode.
I also tried to look in to mysql log and found below error 1) the age of the last checkpoint is 9433987, InnoDB: which exceeds the log group capacity 9433498.
2) Could not use /var/log/mysql/mysql.log for logging (error 2). Turning logging off for the whole
I tried again today and i found below database in sleep mode
63211 sonicsense_db localhost sonicsense_wp_db Sleep 49 NULL
63215 sonicsense_db localhost sonicsense_wp_db Sleep 37 NULL
63217 sonicsense_db localhost sonicsense_wp_db Sleep 37 NULL
63219 sonicsense_db localhost sonicsense_wp_db Sleep 36 NULL
63221 sonicsense_db localhost sonicsense_wp_db Sleep 16 NULL
Does anyone knows how can i fix the site crash problem due to mysql.
I enabled slow query log and i got below details
/usr/libexec/mysqld, Version: 5.1.69-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.1.69-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.1.69-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.1.69-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
Still cannot find any reason why mysql becomes unresponsive every day at same time
Edit /etc/mysql/my.cnf configuration and add:
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
The first row enables the slow query log (remember to rotate it), and the second one is about how many seconds at minimum a query must take to be considered slow.
Once enabled this, you can start investigating which are the queries that sensibly slow down your MySQL instance.