mysql becomes unresponsive at same time every day

Question

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

what do you mean under `My server goes down`? linux becomes unresponsive or mysql is stuck or mysql is stuck for this blog only? etc. please clarify. — GioMac, Sep 04 '13 at 01:54
linux is working fine.The problem is mysql database enters in to sleep mode.Hence the frontend or backend is not accessible until we restart mysql. — Satyendra Mishra, Sep 04 '13 at 07:10
You can scan your mysql-slow-query log and find which imports are slow. — fsoppelsa, Sep 04 '13 at 09:06
@fsoppelsa How do i get mysql slow query log. Kindly guide I have no idea about slow query logs. — Satyendra Mishra, Sep 04 '13 at 09:13
Note that your database isn't going to "sleep"...mysql keeps open connections for a defined period then closes them. The "sleep" you see is an idle connection. If you have lots of them, then it's your application not closing the connections properly. — Nathan C, Sep 04 '13 at 11:05
@NathanC If the connections are idle what should i do so that all connections are closed and my web site dosent go down. It happens every day at same time. — Satyendra Mishra, Sep 04 '13 at 12:41

score 1 · Answer 1 · answered Sep 04 '13 at 09:18

1

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.

answered Sep 04 '13 at 09:18

fsoppelsa

457
1
6
12

I activated slow query log and the response i have got i updated it in question. But still i cannot figure out why mysql becomes unresponsive at that time – Satyendra Mishra Sep 05 '13 at 09:40
Are there any cronjobs scheduled onto the system or inside Magento? – fsoppelsa Sep 05 '13 at 11:29

score 0 · Answer 2 · answered Apr 16 '15 at 21:09

Magento is kind of a resource hog.

Anyways, from what I recall, there are plugins for Magento that optimize not only the site (PHP), but also background processes such as MySQL.

Im saying this because if it happens every day at the exact same time, then its Magento launching a query, its not a MySQL issue.

Personally, I would either

A:

1: Install Magento from scratch on a VM

2: Make a new database on that MySQL server

3: See if it happens

or B:

1: Clone that Magento installation to a VM

2: Make a new MySQL server in a VM

3: See if it happens

I have a feeling it will be B as MySQL itself does not do any kind of optimization of this sort from stock. While this might not solve your problem, it will isolate it if it is a MySQL problem (not likely) or a Magento problem (most likely)

If its neither of these, Apache COULD be the culprit but it is rare as it happens at the exact same time.

mysql becomes unresponsive at same time every day

2 Answers2