5

I'm having a very weird issue, I have an Apache server running with mod_wsgi. The website runs fine but every once in a while I get the 

IOError: failed to write data

error on all the pages of the website. I then gets solved with 

sudo service mysqld restart

Since the website can't be down for long, I have not time to debug this problem and I just run the command every time this happens. I only see the error in the logs that's why I can't really debug it, and it has no clear replication steps, it just occurs randomly.

Any help would be appreciated and let me know if you need me to post any configuration files.

Edit: The exact error displayed by django is:

(2002, "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)")

I saved the error message and it is hosted here. (passwords edited out)

Edit: Here is an extract from the mysql server error log.

160610 10:51:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160610 10:51:53 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 7658 ...
160617 14:35:47 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 32054 ...
160617 14:35:47 [Note] Plugin 'FEDERATED' is disabled.
160617 14:35:47 InnoDB: The InnoDB memory heap is disabled
160617 14:35:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160617 14:35:47 InnoDB: Compressed tables use zlib 1.2.8
160617 14:35:47 InnoDB: Using Linux native AIO
160617 14:35:47 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160617 14:35:47 InnoDB: Completed initialization of buffer pool
160617 14:35:47 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160617 14:35:47 [ERROR] Plugin 'InnoDB' init function returned error.
160617 14:35:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160617 14:35:47 [ERROR] Unknown/unsupported storage engine: InnoDB
160617 14:35:47 [ERROR] Aborting

I saved the full mysqld.log here.

e4c5
  • 52,766
  • 11
  • 101
  • 134
Bit68
  • 1,446
  • 10
  • 25
  • 1
    It sounds as though you need to make time to debug the problem. You should look at the whole error stack trace to see what extra details it has – Sayse Jun 10 '16 at 11:18
  • I'll look at it and post it next time it appears, do you have any general tips about connecting mysql to django? – Bit68 Jun 10 '16 at 14:25
  • Ok, it happened again and I saved the error log. Can you please help? – Bit68 Jun 19 '16 at 02:03
  • 2
    You are more likely to find the source of the error in the mysql error log – e4c5 Jun 19 '16 at 05:18
  • 1
    try looking at or posting the mysql error log (should be under /var/log/mysqld.log ) – Nir Levy Jun 19 '16 at 12:04
  • Posted it in the edit. – Bit68 Jun 19 '16 at 13:46
  • This problem has nothing to do with django. It's a mysql problem. Please check [this page](http://dev.mysql.com/doc/refman/5.5/en/error-creating-innodb.html). – scriptmonster Jun 19 '16 at 15:31
  • How much RAM do you have? Is this a VM? Are other things running on the same server (or VM)? What is `swappiness` set to in the OS? – Rick James Jun 20 '16 at 06:24
  • I'm using an AWS EC2 small instance with 2GB of RAM with static files being served from another server. Average traffic is 6,000 Page views/day. How do I check the swapiness? – Bit68 Jun 20 '16 at 14:15

1 Answers1

3

Why it crashes

This seems to be the culprit

160617 14:35:47 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12

This condition is caused by the server running out of physical memory. That's what errno 12 stands for in the kernel errors.

This topic has been discussed on both Stack Overflow and dba.stackexchange. If you want to simulate low memory situations or try to manually reproduce the error try some of these tools: How to fill 90% of the free memory?

Quick Solution

If you are able to upgrade memory, you can try that. If not, you can try creating a large swap file. It's possible that you don't have a swap at all. Some AWS EC2 instances don't have one by default. You can find out by typing top in the shell. If you don't see swap near the top, that means you don't have one.

A swap file would make the queries a lot slower but at least it's better than the site getting offline.

You might be tempted to try to modify the systemd files to make mysql auto start. Update: @PeterBrittain points out that mysql is auto restarting anyway as shown by the logs. Sometimes though databases can take a bit of time to restart and if the data get's corrupted it will refuse to restart.

Why is memory being exhausted?

If you don't have any other server running on it, 2GB will be more than enough to host a site that serves 6000 pages per day. It could be that you have some rather heavy queries that put an unnecessary load on the db. There are some remedial actions that can be taken

  1. Use django-debug-toolbar to identify pages that execute many queries and see if select_related or prefetch_related can be used to reduce the number.
  2. use mysql slow query log to find the queries that take a long time to execute and optimize them.
  3. Use caching to save the results of complex queries.
Community
  • 1
  • 1
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Can you propose a method to test your hypothesis? ie, can you tell me how to deprive my server of physical memory in order to reproduce the error? – Bit68 Jun 20 '16 at 01:53
  • I used the selected tool (stress) and used up to 90% of the free memory (using the mentioned command), however the error did not occur, I kept refreshing many pages (that use a lot of queries). Do you recommend some other tool/command? – Bit68 Jun 20 '16 at 02:15
  • Ok, when the stress takes up too much memory, it gets terminated when mysqld & httpd request more memory. Any recommendations? – Bit68 Jun 20 '16 at 02:27
  • And if we make the assumption that memory usage is the issue, is there a certain way to make mysqld restart itself when it fails? – Bit68 Jun 20 '16 at 02:32
  • Swapping is terrible for MySQL performance; let's see if we can solve it some other way. (OK, swapping is better than crashing, but...) – Rick James Jun 20 '16 at 06:26
  • I'll give the systemd script a shot and let you know if it solves the issue. – Bit68 Jun 20 '16 at 14:15
  • Added more details and some more suggestions – e4c5 Jun 20 '16 at 23:30
  • 1
    @bit68 Note that mysql 5.5 already has a restart wrapper that has clearly been invoke in the logs. I therefore think using systemd will make little difference. – Peter Brittain Jun 20 '16 at 23:44
  • Thanks @PeterBrittain will update my answer with this. – e4c5 Jun 20 '16 at 23:45
  • 2
    @e4c5 Nice write up. When I've hit similar issues on an overloaded server, I've also had to tune Apache so that it didn't try to start too many workers, which then chew up more memory while some slow requests were completing. – Peter Brittain Jun 20 '16 at 23:47
  • Thank you all, I have accepted this answer and will update this post if this ever occurs again. – Bit68 Jun 21 '16 at 00:14
  • 1
    Glad to have been of help – e4c5 Jun 21 '16 at 00:26