0

i have cloud vps server, cpu 512 mb.

i have website. if i hold f5 in browser after 10-20 sec mysql server no responding and it gives me error

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

i also installed drupal, open home page and hold f5, it also crashes mysql.

after that mysql server need to restart service mysqld restart

i think problem is in mysql server configuration.

any advice?

server centos 6

log file after f5 hold

    160502 10:42:45 mysqld_safe Number of processes running now: 0
160502 10:42:45 mysqld_safe mysqld restarted
160502 10:42:45 [Note] /usr/libexec/mysqld (mysqld 5.5.48) starting as process 12843 ...
160502 10:42:45 [Note] Plugin 'FEDERATED' is disabled.
160502 10:42:45 InnoDB: The InnoDB memory heap is disabled
160502 10:42:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160502 10:42:45 InnoDB: Compressed tables use zlib 1.2.3
160502 10:42:45 InnoDB: Using Linux native AIO
160502 10:42:45 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160502 10:42:45 InnoDB: Completed initialization of buffer pool
160502 10:42:45 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160502 10:42:45 [ERROR] Plugin 'InnoDB' init function returned error.
160502 10:42:45 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160502 10:42:45 [ERROR] Unknown/unsupported storage engine: InnoDB
160502 10:42:45 [ERROR] Aborting

160502 10:42:45 [Note] /usr/libexec/mysqld: Shutdown complete

160502 10:42:45 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
0

The important part of that log is

InnoDB: mmap(137363456 bytes) failed; errno 12 - ENOMEM 12 /* Out of memory */

If you check system log you will see OOM killer doing its work too.

It means your browser sends multiple requests when you hold F5, they probably are executing slowly so they stack, the web server eats more and more memory for each new request and after some time there is just not enough so some process has to be killed - that victim is the mysqld server process which holds some big buffers (key buffer and/or innodb buffer pool).

Then it cannot start anymore until some memory is freed (for example by restarting the system or the web server service).

Check this guide to set your MySQL server to use less memory or separate web and db servers.

  • i created swapfile, so after mysql crash it restarts. digitalocean.com/community/tutorials/… with this tutorial. – Gia Nebieridze May 3 '16 at 0:20
  • MySQL is not supposed to crash, you might lose some data when it does. Swap should prevent the crash but will affect the performance heavily. – jkavalik May 3 '16 at 4:22
0

512MB is tiny by today's standards. In particular, 128MB for innodb_buffer_pool_size is too much. Lower that to 40M. If it still crashes the same way, then show us your my.cnf so we can cut back some other settings.

  • i created swapfile, so after mysql crash it restarts. digitalocean.com/community/tutorials/… with this tutorial. – Gia Nebieridze May 3 '16 at 0:20
  • When swapping does occur, MySQL runs slooooowly. This is because it assumes its in-RAM tables are really in-RAM, and freely jumps around accessing them. – Rick James May 3 '16 at 1:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.