HUGE IO Spike?? Server Crashed / 504 Gateway timeout
So I am a relative linux/vps newbie running a LEMP server, but have had my linode successfully setup and running a few wordpress blogs since January.. until today. I logged into Google Analytics app on my phone earlier and saw that traffic was waay down on all my sites… I thought must be because it is such a nice day outside. But then I check my email just a while ago and got one from linode that says:
Your Linode, linode182850, has exceeded the notification threshold (1000) for disk io rate by averaging 1697.13 for the last 2 hours
When I go and login the dashboard it appears that there has been a huge IO spike since this morning and it appears my sites are down with a 504 Gateway timeout. I tried rebooting my linode but that has not helped. Here are the graphs, any idea what is going on??
free output under my linux user that owns my blogs
cfauver@mail ~:free
total used free shared buffers cached
Mem: 496 490 5 0 1 264
-/+ buffers/cache: 224 271
Swap: 255 18 237
free output under root
root@mail ~:free
total used free shared buffers cached
Mem: 508100 488816 19284 0 796 258720
-/+ buffers/cache: 229300 278800
Swap: 262140 19000 243140
33 Replies
root@mail ~: tail -f /var/log/debug
Mar 10 17:02:58 mail kernel: CPU 0 irqstacks, hard=df408000 soft=df40a000
Mar 10 17:02:58 mail kernel: Xen: using vcpuop timer interface
Mar 10 17:02:58 mail kernel: CPU 1 irqstacks, hard=df482000 soft=df484000
Mar 10 17:02:58 mail kernel: CPU 2 irqstacks, hard=df490000 soft=df492000
Mar 10 17:02:58 mail kernel: CPU 3 irqstacks, hard=df4be000 soft=df4c0000
Mar 10 17:11:01 mail kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:df:b9:2d:c8:4c:75:f5:f0:3f:08:00 SRC=174.138.171.243 DST=74.207.224.68 LEN=44 TOS=0x00 PREC=0x00 TTL=54 ID=0 DF PROTO=TCP SPT=80 DPT=18914 WINDOW=14600 RES=0x00 ACK SYN URGP=0
Mar 10 17:22:09 mail kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:df:b9:2d:c8:4c:75:f5:f0:3f:08:00 SRC=118.123.255.173 DST=74.207.224.68 LEN=40 TOS=0x00 PREC=0x00 TTL=104 ID=256 PROTO=TCP SPT=6000 DPT=1433 WINDOW=16384 RES=0x00 SYN URGP=0
Mar 10 17:54:04 mail kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:df:b9:2d:c8:4c:75:f5:f8:3f:08:00 SRC=174.132.27.126 DST=74.207.224.68 LEN=44 TOS=0x00 PREC=0x00 TTL=55 ID=23801 PROTO=TCP SPT=80 DPT=51470 WINDOW=16384 RES=0x00 ACK SYN URGP=0
Mar 10 17:58:38 mail kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:df:b9:2d:c8:4c:75:f5:f8:3f:08:00 SRC=58.218.199.250 DST=74.207.224.68 LEN=40 TOS=0x00 PREC=0x00 TTL=110 ID=256 DF PROTO=TCP SPT=12200 DPT=8080 WINDOW=8192 RES=0x00 SYN URGP=0
Mar 10 18:19:28 mail kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:df:b9:2d:c8:4c:75:f5:f8:3f:08:00 SRC=69.162.83.102 DST=74.207.224.68 LEN=92 TOS=0x00 PREC=0x00 TTL=54 ID=55388 PROTO=ICMP TYPE=3 CODE=2 [SRC=74.207.224.68 DST=69.162.83.102 LEN=64 TOS=0x00 PREC=0x00 TTL=245 ID=40512 DF PROTO=47 ]
root@mail ~: tail -f /var/log/messages
Mar 10 17:02:58 mail kernel: VFS: Mounted root (ext3 filesystem) readonly on dev ice 202:0.
Mar 10 17:02:58 mail kernel: devtmpfs: mounted
Mar 10 17:02:58 mail kernel: Freeing unused kernel memory: 420k freed
Mar 10 17:02:58 mail kernel: Write protecting the kernel text: 6716k
Mar 10 17:02:58 mail kernel: Write protecting the kernel read-only data: 1548k
Mar 10 17:02:58 mail kernel: NX-protecting the kernel data: 2500k
Mar 10 17:02:58 mail kernel: udevd (1077): /proc/1077/oomadj is deprecated, ple ase use /proc/1077/oomscore_adj instead.
Mar 10 17:02:58 mail kernel: Adding 262140k swap on /dev/xvdb. Priority:-1 exte nts:1 across:262140k SS
Mar 10 17:02:58 mail kernel: EXT3-fs (xvda): using internal journal
Mar 10 17:25:51 mail kernel: hrtimer: interrupt took 7692400 ns
root@mail ~: tail -f /var/log/auth.log
Mar 10 18:11:46 mail sshd[2919]: pamunix(sshd:session): session closed for user cfauver
Mar 10 18:11:55 mail sshd[3135]: Accepted publickey for cfauver from 216.164.38.57 port 56929 ssh2
Mar 10 18:11:55 mail sshd[3135]: pamunix(sshd:session): session opened for user cfauver by (uid=0)
Mar 10 18:12:09 mail sudo: cfauver : TTY=pts/0 ; PWD=/home/cfauver ; USER=root ; COMMAND=/bin/bash
Mar 10 18:14:38 mail sshd[3135]: pamunix(sshd:session): session closed for user cfauver
Mar 10 18:14:53 mail sshd[3229]: Accepted publickey for cfauver from 216.164.38.57 port 56936 ssh2
Mar 10 18:14:53 mail sshd[3229]: pamunix(sshd:session): session opened for user cfauver by (uid=0)
Mar 10 18:15:26 mail sudo: cfauver : TTY=pts/0 ; PWD=/home/cfauver ; USER=root ; COMMAND=/bin/bash
Mar 10 18:17:03 mail CRON[3307]: pamunix(cron:session): session opened for user root by (uid=0)
Mar 10 18:17:04 mail CRON[3307]: pamunix(cron:session): session closed for user root
root@mail ~: tail -f /var/log/dmesg
VFS: Mounted root (ext3 filesystem) readonly on device 202:0.
devtmpfs: mounted
Freeing unused kernel memory: 420k freed
Write protecting the kernel text: 6716k
Write protecting the kernel read-only data: 1548k
NX-protecting the kernel data: 2500k
udev: starting version 151
udevd (1077): /proc/1077/oomadj is deprecated, please use /proc/1077/oomscore_adj instead.
Adding 262140k swap on /dev/xvdb. Priority:-1 extents:1 across:262140k SS
EXT3-fs (xvda): using internal journal
root@mail ~: tail -f /var/log/nginx/error.log
2013/03/10 09:24:17 [error] 10650#0: *9253 open() "/var/www/nginx-default/proxyheader.php" failed (2: No such file or directory), client: 58.218.199.250, server: localhost, request: "GET http://www.verysurf.c om/proxyheader.php HTTP/1.1", host: "www.verysurf.com"
2013/03/10 11:23:51 [error] 10650#0: *15550 open() "/var/www/nginx-default/robots.txt" failed (2: No suc h file or directory), client: 198.20.70.114, server: localhost, request: "GET /robots.txt HTTP/1.1", hos t: "74.207.224.68"
root@mail ~: tail -f /var/log/nginx/access.log
157.55.33.17 - - [10/Mar/2013:16:04:19 -0400] "GET /idx/mls-pw7907422-5205trunniontrlwoodbridgeva_22192 HTTP/1.1" 301 185 "-" "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"
157.55.32.188 - - [10/Mar/2013:16:50:41 -0400] "GET /robots.txt HTTP/1.1" 301 185 "-" "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"
216.164.38.57 - - [10/Mar/2013:17:00:34 -0400] "GET / HTTP/1.1" 301 185 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:19.0) Gecko/20100101 Firefox/19.0"
216.164.38.57 - - [10/Mar/2013:17:01:06 -0400] "GET / HTTP/1.1" 301 185 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:19.0) Gecko/20100101 Firefox/19.0"
157.55.33.17 - - [10/Mar/2013:17:05:30 -0400] "GET /robots.txt HTTP/1.1" 301 185 "-" "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"
157.55.32.146 - - [10/Mar/2013:17:41:29 -0400] "GET /robots.txt HTTP/1.1" 301 185 "-" "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"
69.165.159.215 - - [10/Mar/2013:18:02:14 -0400] "GET / HTTP/1.1" 301 185 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)"
69.165.159.215 - - [10/Mar/2013:18:02:14 -0400] "GET / HTTP/1.1" 301 185 "-" "Mozilla/4.0 (compatible; MSIE 5.0; Windows XP) Opera 6.05 [en]"
69.165.159.215 - - [10/Mar/2013:18:02:29 -0400] "GET / HTTP/1.1" 301 185 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)"
157.55.32.86 - - [10/Mar/2013:18:06:32 -0400] "GET /robots.txt HTTP/1.1" 301 185 "-" "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"
(I run Office365 Exchange server so no mail server setup)
root@mail ~: tail -f /var/log/nginx/mail.*
tail: cannot open `/var/log/nginx/mail.*' for reading: No such file or directory
tail: no files remaining
````
2466 be/4 mysql 1791.20 K/s 0.00 B/s 0.00 % 97.52 % mysqld
2454 be/4 mysql 1688.62 K/s 0.00 B/s 0.00 % 97.50 % mysqld
1 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % init
2 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kthreadd]
3 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/0]
4 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/0:0]
5 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/0:0H]
6 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/u:0]
7 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/u:0H]
8 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/0]
9 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [rcu_bh]
10 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [rcu_sched]
11 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/1]
12 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/1]
13 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/1:0]
14 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/1:0H]
15 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/2]
16 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/2]
17 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/2:0]
18 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/2:0H]
19 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/3]
20 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/3]
21 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/3:0]
22 be/0 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kworker/3:0H]
[10-Mar-2013 19:18:54] NOTICE: fpm is running, pid 2180
[10-Mar-2013 19:18:54] NOTICE: ready to handle connections
[10-Mar-2013 19:18:58] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers)$
[10-Mar-2013 19:18:59] WARNING: [pool www] server reached pm.max_children setting (10), consider raising it
I don't really know how to handle it..
Fire up mysqltuner.pl to see if it has any advice. In particular, turn on the slow_query log to see what's taking so long.
what's happening is (still a guess)
an http request comes in that is handled by php
php, which uses 50MB of memory, opens a DB connection
the DB is soooo slooow that the php process has to sit idly wait for it to return the result, while it's idle it still uses up 50MB of memory.
meanwhile, another http request comes in
eventually you have all these php processes hanging around waiting for the database to finish returning a result that it starts swapping
the swapping causes extra io which slows the database down even more
meanwhile, more request come in.
so, while it might seem logical to increase the number of php processes, this is actually the opposite of what you should do, you need to decrease them so under normal operation it doesn't swap. you have 500MB, lets guess a php process uses 50MB, you can only have a max of 10 php processes running at the same time.
however, you are going to run out of php slots because each one is waiting so long for the database quieres to finish.
so, as hoops said, tune the database. but you'll get the most bang for buck by installing a wordpress caching plugin, then 99% of http requests won't even need to use a php process to handle the query.
I think I figured out that it is probably WP_Autoblog plugin I have running on a few sites which also imports pictures and I could go in and either delete the plugin via FTP or clear the active plugins in my wordpress tables but it appears I'm locked out, how can I get back in?
PS. htop might be handy for showing memory and such through. I find it a lot clearer than normal top and free.
That's great, except now my question is how can I run the auto-blog plugin without this happening again and why was I able to run it on 4 sites for a month and then all of a sudden one day BOOM my IO runs through the roof and my sites won't serve? Do I simply need a bigger linode?
EDIT: Well I thought that was the case.. but actually one site loaded, but now they aren't loading… and one site says "Error establishing database connection"
I would reboot again, login and set pm.max_children to 10 (as a starting place) then restart php
then as hoops suggested, run mysqltuner.pl
then install a wordpress cache plugin.
basically your site is receiving more traffic now, and your database has grown, so php is spending more time waiting for queries as well as more php processes required for the extra traffic.
after you've tuned mysql and installed a wordpress cache, then you can look at whether you need up upgrade your linode.
also, depending on what back end is in use, you might need to run mysqlcheck periodically, make it a cron job
@chesty:
if i was the admin,
I would reboot again, login and set pm.max_children to 10 (as a starting place) then restart php
then as hoops suggested, run mysqltuner.pl
then install a wordpress cache plugin.
basically your site is receiving more traffic now, and your database has grown, so php is spending more time waiting for queries as well as more php processes required for the extra traffic.
after you've tuned mysql and installed a wordpress cache, then you can look at whether you need up upgrade your linode.
also, depending on what back end is in use, you might need to run mysqlcheck periodically, make it a cron job
Is it really traffic though? The graphs were showing like, 300kbit max, that shouldn't cause these issues.
@Nuvini:
Can you manually login to mysql? use mysql -u
-p , then fill in password. You could then also execute show full processlist; to see if it's very busy.
mysql> show full processlist;
+-----+---------+-----------+------+---------+------+-------+------------------- ----+
| Id | User | Host | db | Command | Time | State | Info |
+-----+---------+-----------+------+---------+------+-------+------------------- ----+
| 166 | cfauver | localhost | NULL | Sleep | 67 | | NULL |
| 184 | root | localhost | NULL | Query | 0 | NULL | show full processl ist |
+-----+---------+-----------+------+---------+------+-------+------------------- ----+
I ran:
wget mysqltuner.pl
and it said it saved it as index.html
perl mysqltuner.pl
does not run it for me.. that is what I picked up from another thread
@cfauver:
@Nuvini:Can you manually login to mysql? use mysql -u
-p , then fill in password. You could then also execute show full processlist; to see if it's very busy. mysql> show full processlist; +-----+---------+-----------+------+---------+------+-------+------------------- ----+ | Id | User | Host | db | Command | Time | State | Info | +-----+---------+-----------+------+---------+------+-------+------------------- ----+ | 166 | cfauver | localhost | NULL | Sleep | 67 | | NULL | | 184 | root | localhost | NULL | Query | 0 | NULL | show full processl ist | +-----+---------+-----------+------+---------+------+-------+------------------- ----+
That looks alright, it should be able to connect. Does the error log from your webserver say anything about mysql?
root@mail ~: tail -f /var/log/nginx/error.log
2013/03/10 09:24:17 [error] 10650#0: *9253 open() "/var/www/nginx-default/proxyheader.php" failed (2: No such file or directory), client: 58.218.199.250, server: localhost, request: "GET http://www.verysurf.com/proxyheader.php HTTP/1.1", host: "www.verysurf.com"
2013/03/10 11:23:51 [error] 10650#0: *15550 open() "/var/www/nginx-default/robots.txt" failed (2: No such file or directory), client: 198.20.70.114, server: localhost, request: "GET /robots.txt HTTP/1.1", host: "74.207.224.68"
2013/03/10 20:56:05 [error] 2105#0: *2008 open() "/var/www/nginx-default/aop0igj.php" failed (2: No such file or directory), client: 58.218.199.250, server: localhost, request: "GET http://216.245.211.138/aop0igj.php HTTP/1.1", host: "216.245.211.138"
````
same as before… is that the right log?
```
root@mail ~: wget http://mysqltuner.com/mysqltuner.pl
--2013-03-11 06:46:41-- http://mysqltuner.com/mysqltuner.pl
Resolving mysqltuner.com... 2001:4801:7901:0:abc5:ba2c:0:1, 198.61.150.28
Connecting to mysqltuner.com|2001:4801:7901:0:abc5:ba2c:0:1|:80... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://github.com/rackerhacker/MySQLTuner-perl [following]
--2013-03-11 06:46:41-- https://github.com/rackerhacker/MySQLTuner-perl
Resolving github.com... 207.97.227.239
Connecting to github.com|207.97.227.239|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 31540 (31K) [text/html]
Saving to:mysqltuner.pl'
100%[======================================>] 31,540 --.-K/s in 0.01s
2013-03-11 06:46:41 (2.18 MB/s) - `mysqltuner.pl' saved [31540/31540]
root@mail ~: chmod +x mysqltuner.pl
root@mail ~: ./mysqltuner.pl
./mysqltuner.pl: line 4: syntax error near unexpected token newline'
./mysqltuner.pl: line 4:'
root@mail ~:
````
What am I doing wrong here??
@Nuvini:
Is it really traffic though? The graphs were showing like, 300kbit max, that shouldn't cause these issues.
I didn't see the graphs, but having done so now, it looks more likely a db issue. however, the db issue is highlighting a problem of too high pm.max_children, and that's exacerbating the issue, and is the reason the server become unresponsive.
@cfauver:
What am I doing wrong here??
you're trying to execute html by the looks of it.
try
wget mysqltuner.pl
that's what I've always used. then chmod and the rest.
the first line should be #!/usr/bin/perl -w
@cfauver:
root@mail ~: wget http://mysqltuner.com/mysqltuner.pl --2013-03-11 06:46:41-- http://mysqltuner.com/mysqltuner.pl Resolving mysqltuner.com... 2001:4801:7901:0:abc5:ba2c:0:1, 198.61.150.28 Connecting to mysqltuner.com|2001:4801:7901:0:abc5:ba2c:0:1|:80... connected. HTTP request sent, awaiting response... 302 Moved Temporarily Location: https://github.com/rackerhacker/MySQLTuner-perl [following] --2013-03-11 06:46:41-- https://github.com/rackerhacker/MySQLTuner-perl Resolving github.com... 207.97.227.239 Connecting to github.com|207.97.227.239|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 31540 (31K) [text/html] Saving to: `mysqltuner.pl' 100%[======================================>] 31,540 --.-K/s in 0.01s 2013-03-11 06:46:41 (2.18 MB/s) - `mysqltuner.pl' saved [31540/31540] root@mail ~: chmod +x mysqltuner.pl root@mail ~: ./mysqltuner.pl ./mysqltuner.pl: line 4: syntax error near unexpected token `newline' ./mysqltuner.pl: line 4: `' root@mail ~:What am I doing wrong here??
This redirects to github. So do apt-get install git && git clone git://github.com/rackerhacker/MySQLTuner-perl.git - that'll make a directory and place the files in there.
```
root@mail ~: wget mysqltuner.pl
--2013-03-11 07:06:35-- http://mysqltuner.pl/
Resolving mysqltuner.pl... 2001:4801:7901:0:abc5:ba2c:0:1, 198.61.150.28
Connecting to mysqltuner.pl|2001:4801:7901:0:abc5:ba2c:0:1|:80... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl [following]
--2013-03-11 07:06:35-- https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
Resolving raw.github.com... 199.27.74.133
Connecting to raw.github.com|199.27.74.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 41847 (41K) [text/plain]
Saving to:index.html.3'
100%[======================================>] 41,847 --.-K/s in 0.04s
2013-03-11 07:06:35 (1010 KB/s) - `index.html.3' saved [41847/41847]
root@mail ~: apt-get install git && git clone git://github.com/rackerhacker/MySQLTuner-perl.git
Reading package lists… Done
Building dependency tree
Reading state information… Done
Package git is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
E: Package git has no installation candidate
````
head mysqltuner.pl
(check first line, if ok, then)
chmod +x mysqltuner.pl
./mysqltuner.pl
root@mail ~: ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.67-0ubuntu0.10.04.1
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 53M (Tables: 116)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 14
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 46m 39s (10K q [3.601 qps], 853 conn, TX: 32M, RX: 2M)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 58.0M global + 2.6M per thread (151 max threads)
[!!] Maximum possible memory usage: 454.4M (91% of installed RAM)
[OK] Slow queries: 0% (0/10K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/10.2M
[OK] Key buffer hit rate: 99.3% (75K cached / 546 reads)
[OK] Query cache efficiency: 33.0% (2K cached / 6K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 237 sorts)
[!!] Temporary tables created on disk: 46% (769 on disk / 1K total)
[OK] Thread cache hit rate: 99% (5 created / 853 connections)
[!!] Table cache hit rate: 7% (64 open / 891 opened)
[OK] Open file limit used: 12% (124/1K)
[OK] Table locks acquired immediately: 99% (3K immediate / 3K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 64)</major@mhtx.net>
mysqlcheck -Ac –auto-repair
mysqlcheck -Aa --auto-repair
mysqlcheck -Ao --auto-repair
for the above three commands, you might need to add -u root -p
in my.cnf
max_connections = 20 # this should be higher than the max number of php processes
tmptablesize = 32M
maxheaptable_size = 32M
table_cache = 512
logslowqueries = /var/log/mysql/mysql-slow.log # this is for debian or ubuntu, centos might be different
longquerytime = 1
if the config option isn't specified, then add it under [mysqld]
@chesty:
so first off
mysqlcheck -Ac –auto-repair
mysqlcheck -Aa --auto-repair
mysqlcheck -Ao --auto-repair
for the above three commands, you might need to add -u root -p
Okay I did this, it said it found errors in two of my databases and it says it repaired them. Now I believe WP-Autoblog was not the culprit. All my sites are serving except for 2 sites that I run a plugin called dsIDXPress (for real estate agents). Both of those sites are not serving and it was their 'options' table where all the errors were occurring.
> in my.cnf
max_connections = 20 # this should be higher than the max number of php processes
tmptablesize = 32M
maxheaptable_size = 32M
table_cache = 512
logslowqueries = /var/log/mysql/mysql-slow.log # this is for debian or ubuntu, centos might be different
longquerytime = 1
if the config option isn't specified, then add it under [mysqld]
I do not have a my.cnf file in my /etc folder, so do I just create a new one in nano and that has the values you gave me and then restart mysql? What do you mean "add it under mysqld?
service mysql retart
?
I wanted to try to deactivate this plugin from mysql query, but I think since I have 5 databases I have to tell it which one, I found this in another tutorial:
mysql> SELECT * FROM wp_options WHERE option_name = 'active_plugins';
I receive:
ERROR 1046 (3D000): No database selected
In this file there should be some text and at some point [mysqld] - Place the config just below [mysqld] (make sure there aren't any duplicate lines if you simply copy/paste, the lines may already be there with different values.
After that you must restart mysql, which you can do with the command: service mysql restart (or /etc/init.d/mysql restart)
The query you run: The error kinda says it, no database selected. First run the following: USE
/etc/mysql/my.cnf
it should already exist, but some of the options I specified might not exist in that file, so if they don't, add them under the line that says
[mysqld]
you shouldn't need to modify database tables directly, you should do it though the web page wp-admin for that site.
if the web site is down, then you'll need to look in your web servers errors.log to see what's up.
I am up and running.. weird I actually did change the active plugins to null but they are still active.. but I am all back up..
Now.. the question is why did my IO spike and is it going to happen again or was it as simple as changing some of those settings??
/var/log/mysql.log
/var/log/mysql.err
see if it reported any errors.
keep watching /var/log/mysql/mysql-slow.log
keep running those mysqlcheck commands, if they won't run without a password, you need to create a file in roots home dir called
/root/.my.cnf
with the following lines
[client]
password = yourmysqlrootpassword
then
chmod 600 /root/.my.cnf
then you can add them to cron to run a few times a day
I upgraded my linode to a 768 and everything seems to be running excellent in terms of cpu/memory.. I think that is actually overkill for the amount of traffic I get but since I have 450 MB allowable to mysql it keeps some free memory when mysql does get worked hard? Is this a bad way to look at it?
30 day graph:
24 hour graph: