1

I'm having an issue where my MySQL server (part of a LAMP stack running WordPress) hangs every few hours (giving WordPress a "database connection error").

The error log shows nothing other than launching after a restart, and shutting down ("normal shutdown") when I need to restart it. The only thing of note is that it says it is "purging the queue" during a restart, and then has ~300 lines like this:

151026 19:04:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 31577  user: 'root'

The only other weird behaviour I've noticed is that running mysqladmin status shows "Questions" increasing rapidly, presumably until it hangs. I've looked online and can't find much discussion about what normal range or behaviour "Questions" should exhibit. Eg, this is after 10 minutes uptime:

Uptime: 521  Threads: 3  Questions: 16667  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 100  Queries per second avg: 31.990

I have query_cache_size set to 50M, otherwise my.cnf is default on Ubuntu 14.04. This is the performance analysis from the mysqltuner perl script:

-------- Performance Metrics -------------------------------------------------  
[--] Up for: 15m 52s (29K q [31.475 qps], 1K conn, TX: 73M, RX: 4M)  
[--] Reads / Writes: 88% / 12%  
[--] Binary logging is disabled  
[--] Total buffers: 226.0M global + 2.7M per thread (151 max threads)  
[OK] Maximum reached memory usage: 290.5M (1.81% of installed RAM)  
[OK] Maximum possible memory usage: 631.8M (3.94% of installed RAM)  
[OK] Slow queries: 0% (0/29K)  
[OK] Highest usage of available connections: 15% (24/151)  
[OK] Aborted connections: 0.00%  (0/1515)  
[OK] Query cache efficiency: 71.8% (17K cached / 24K selects)  
[OK] Query cache prunes per day: 0  
[OK] Sorts requiring temporary tables: 0% (10 temp sorts / 1K sorts)  
[!!] Temporary tables created on disk: 59% (1K on disk / 1K total)  
[OK] Thread cache hit rate: 97% (39 created / 1K connections)  
[OK] Table cache hit rate: 93% (103 open / 110 opened)  
[OK] Open file limit used: 4% (51/1K)  
[OK] Table locks acquired immediately: 100% (8K immediate / 8K locks)  

-------- MyISAM Metrics -----------------------------------------------------  
[!!] Key buffer used: 18.2% (3M used / 16M cache)  
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.0M  
[OK] Read Key buffer hit rate: 100.0% (6 cached / 0 reads)  
[OK] Write Key buffer hit rate: 100.0% (8 cached / 0 writes)  

-------- InnoDB Metrics -----------------------------------------------------  
[--] InnoDB is enabled.  
[!!] InnoDB buffer pool / data size: 128.0M/292.9M  
[OK] InnoDB buffer pool instances: 1  
[!!] InnoDB Used buffer: 75.48% (6183 used/ 8192 total)  
[OK] InnoDB Read buffer efficiency: 99.70% (1960793 hits/ 1966769 total)  
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)  
[OK] InnoDB log waits: 0.00% (0 waits / 894 writes)  

The hanging has started happening more frequently since an increase in logged in users (people accessing the database) and changes to my Apache config, which solved Apache crashes - my Apache prefork MPM is set as follows:

StartServers              20
MinSpareServers           10  
MaxSpareServers           20  
ServerLimit               1000  
MaxRequestWorkers         500  
MaxConnectionsPerChild    10000  

The server is a DigitalOcean droplet with 8 CPU cores and 16GB RAM.

  • Can you try to connect at that time using CLI mysql client? It will either give you some proper error message or allow the connection (if there are too many connections, one extra is being kept for root user to connect and fix things afaik). If you get an error message, add it here, otherwise try to run show processlist; and show full processlist; (the second one might be too long) to see what is happening inside - add those results too. – jkavalik Oct 28 '15 at 7:14
  • Hello, perhaps oomkiller? You would see messages in /var/log/messages like this : kernel: Out of memory: Kill process 9163 (mysqld) score 511 or sacrifice child kernel: Killed process 9163, UID 27, (mysqld) total-vm:2457368kB, anon-rss:816780kB, file-rss:4kB – Craig Efrein Oct 28 '15 at 8:58
0
  • Do not have WP connecting as root. Create another 'user'

  • change innodb_buffer_pool_size to 1G.

  • Turn on the slowlog and set long_query_time = 1; you have some naughty queries that need to be investigated

  • What is MaxClients in Apache? Not more than 20, I hope.

  • agree with all, but - why 1Gb for InnoDB on machine with 16G? MySQL still will use this memory but as file level cache == less effective. Also good to test without query cache at all (not for all cases but need to test) – a_vlad Nov 15 '16 at 3:39
  • @a_vlad - With only 292.9M data, 1GB is sufficient to hold all the current data, plus more. Granted, more may be useful later. I was probably silent on the QC because of 71.8% hit rate. (I usually say "turn it off!") – Rick James Nov 15 '16 at 3:46
0

You can also find my answer here

This error might occurs due to multiple installations of MySQL, in the operating system.

sudo ps -A|grep mysql  

Kill the process of mysql

sudo pkill mysql  

Then run the command

sudo ps -A|grep mysqld  

kill process of mysqld

sudo pkill mysqld  

Now you can safely restart MySQL server

sudo service mysql restart    
mysql -u root -p  

Hope this helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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