Question
Mysql server keeps stopping unexpectedly
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
×These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
×This is happening to me on a WordPress droplet. I’ve done two things, one suggested by another answer here. In the [mysqld]
section I’ve added innodb_buffer_pool_size = 16M
. Note, this is really small, but this is on a small droplet, so it is what it is. Additionally, when mysql eventually crashed I noticed apache having a LOT of processes. It’s likely hogging up all the system memory. Again, this is for a small site, so I tuned down apache by editing /etc/apache2/mods-enabled/mpm_prefork.conf
to this:
<IfModule mpm_prefork_module>
StartServers 3
MinSpareServers 3
MaxSpareServers 5
MaxRequestWorkers 25
MaxConnectionsPerChild 0
</IfModule>
Updating innodb_buffer_pool_size
stabilized the system for a lot longer than before, but apache eventually hogged all the memory. I’m hoping my problem goes away limiting the number of Apache processes that can exist.
Great answer. I have same use case and your solution solved my problem
MaxConnectionsPerChild 0 means that child threads will never restart. This can cause memory leaks. Perhaps a large enough number such as 2048 or 4096.
yes, you are right, thanks for pointing this out!
my site is very a low-traffic, so i want child processes to cycle quickly. i’m updating my config to use MaxConnectionsPerChild 512
. i used chrome’s inspector and my site’s main page makes 45 (!!) requests. with 512 requests per child, each process will render ~10 pages and exit.
for reference, here is a link to the official docs: https://httpd.apache.org/docs/trunk/mod/mpm_common.html#maxconnectionsperchild
This also worked for me. Remember to backup your my.cnf
file before replacing it with the new one, just incase something is missing or different in the pastebin version.
I’ve encountered similar issue, and adding 1GB swap solved my problem. See this:
http://www.webtrafficexchange.com/solved-mysql-crash-fatal-error-cannot-allocate-memory-buffer-pool
After applying the mysql and apache tweaks in my previous post I stressed the system using ab
. Below are my results. Long story short, the system was able to successfully process 5000 requests across 20 concurrent connections. The system remained stable and no mysql issues.
I’m running this on a 1G droplet, might be tight, but this config seems like it would work on a 512MB as well.
Hope this helps!
root@KavanaKava:~# time ab -l -n 5000 -c 20 http://example.com/
This is ApacheBench, Version 2.3 <$Revision: 1528965 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 45.55.159.118 (be patient)
Completed 500 requests
Completed 1000 requests
Completed 1500 requests
Completed 2000 requests
Completed 2500 requests
Completed 3000 requests
Completed 3500 requests
Completed 4000 requests
Completed 4500 requests
Completed 5000 requests
Finished 5000 requests
Server Software: Apache/2.4.7
Server Hostname: example.com
Server Port: 80
Document Path: /
Document Length: Variable
Concurrency Level: 20
Time taken for tests: 508.491 seconds
Complete requests: 5000
Failed requests: 0
Total transferred: 115833214 bytes
HTML transferred: 113373214 bytes
Requests per second: 9.83 [#/sec] (mean)
Time per request: 2033.964 [ms] (mean)
Time per request: 101.698 [ms] (mean, across all concurrent requests)
Transfer rate: 222.46 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.3 0 6
Processing: 957 2033 184.2 1966 3450
Waiting: 544 897 142.7 858 2746
Total: 957 2033 184.2 1966 3450
Percentage of the requests served within a certain time (ms)
50% 1966
66% 2012
75% 2082
80% 2163
90% 2270
95% 2421
98% 2566
99% 2675
100% 3450 (longest request)
real 8m28.506s
user 0m0.319s
sys 0m1.003s
root@KavanaKava:~#
I didn’t see this until now, sorry!
https://gist.github.com/rca/4b3553e0797c98ea0a15fdce95e2033a
I got this when I used the config file provided for 512MB droplet.
140727 12:12:59 [ERROR] Unknown/unsupported storage engine: InnoDB
I fixed it by using these before restarting mysql to the new config provided in the comments.
rm /var/lib/mysql/iblogfile0
rm /var/lib/mysql/iblogfile1
Just my two cents:
We were/are getting this error periodically when we notice that our server is getting hammered with web/sql requests, usually from a european or asian IP. Dropping the IP in IPTABLES seems to help for long periods of time; at least until the next bot or whatever starts again.
Dave
I am on 2GB RAM with apache+php-fpm+nginx as reverse proxy..running latest mariaDB server on centos 6.6..i get this error…
150709 14:06:20 mysqld_safe mysqld from pid file /var/lib/mysql/mysql.pid ended
150709 14:08:06 mysqld_safe Starting mysqld daemon with databases from /va r/lib/mysql/
150709 14:08:06 [Note] /usr/sbin/mysqld (mysqld 10.0.19-MariaDB-log) starting as process 5758 ...
150709 14:08:06 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150709 14:08:06 [Note] InnoDB: The InnoDB memory heap is disabled
150709 14:08:06 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150709 14:08:06 [Note] InnoDB: Memory barrier is not used
150709 14:08:06 [Note] InnoDB: Compressed tables use zlib 1.2.3
150709 14:08:06 [Note] InnoDB: Using Linux native AIO
150709 14:08:06 [Note] InnoDB: Using CPU crc32 instructions
150709 14:08:06 [Note] InnoDB: Initializing buffer pool, size = 624.0M
InnoDB: mmap(669646848 bytes) failed; errno 12
150709 14:08:06 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
150709 14:08:06 [ERROR] Plugin 'InnoDB' init function returned error.
150709 14:08:06 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150709 14:08:06 [ERROR] Failed to initialize plugins.
150709 14:08:06 [ERROR] Aborting
150709 14:08:06 [Note] /usr/sbin/mysqld: Shutdown complete
150709 14:08:06 mysqld_safe mysqld from pid file /var/lib/mysql/mysql.pid ended
Can somebody help and suggest my.cnf configuration file?
my.cnf..
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
performance_schema=ON
# GENERAL #
user = mysql
default-storage-engine = InnoDB
#default-table-type =innodb
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
innodb = FORCE
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 1
query-cache-size = 1000000
max-connections = 100
thread-cache-size = 16
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 256M
# MYISAM ONLYURS#
long_query_time = 1
log-queries-not-using-indexes = 1
#big-tables
#thread_concurrency = 8
myisam_sort_buffer_size = 1G
read_rnd_buffer_size = 2M
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
if your on centos to any rhel distro place your new config here : /etc/my.cnf.d/ also a great tool : https://tools.percona.com
Hi, i am facing this error
“Can’t connect to local MySQL server through socket ’/var/run/mysqld/mysqld.sock’ (111)”
every week(Now every day until i restart server).Now only tester are using my webapp even it shows above error.When i run this commond root@Sharebulk:/var/log/mysql# tail -n 50 error.log
it was showing:
How can i fix.Please Give solution.
Solution: https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-14-04
Looks, like there is not enough memory for InnoDB stuff.
Shortly: add (or replace existing line) to the /etc/mysql/my.cnf :
This will decrease mysql requirements.
And here is the original answer (with explanations):
http://stackoverflow.com/a/12683951/1115187
Another approaches to solve the problem:
http://askubuntu.com/a/457932/400409
You would need to add group
[mysqld]
if not you get theFound option without preceding group in config file /etc/mysql/my.cnf at line 22!