5

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

Improve this question
6
  • 1
    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 4, 2013 at 1:54
  • 1
    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 4, 2013 at 7:10
  • You can scan your mysql-slow-query log and find which imports are slow.
    – fsoppelsa
    Sep 4, 2013 at 9:06
  • @fsoppelsa How do i get mysql slow query log. Kindly guide I have no idea about slow query logs.
    – Satyendra Mishra
    Sep 4, 2013 at 9:13
  • 1
    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 4, 2013 at 11:05

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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 5, 2013 at 9:40
  • Are there any cronjobs scheduled onto the system or inside Magento?
    – fsoppelsa
    Sep 5, 2013 at 11:29
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .