HUGE IO Spike?? Server Crashed / 504 Gateway timeout

Hi Guys,

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??

~~[https://docs.google.com/file/d/0B6KEPqyasW5kOHlGWENZb0xnM3M/edit?usp=sharing" target="_blank">](https://docs.google.com/file/d/0B6KEPqy … sp=sharing">https://docs.google.com/file/d/0B6KEPqyasW5kOHlGWENZb0xnM3M/edit?usp=sharing](

~~[https://docs.google.com/file/d/0B6KEPqyasW5kQk5WR1NEQ2FSczg/edit?usp=sharing" target="_blank">](https://docs.google.com/file/d/0B6KEPqy … sp=sharing">https://docs.google.com/file/d/0B6KEPqyasW5kQk5WR1NEQ2FSczg/edit?usp=sharing](

free output under my linux user that owns my blogs

[email protected] ~: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

[email protected] ~:free        
total       used       free     shared    buffers     cached
Mem:        508100     488816      19284          0        796     258720
-/+ buffers/cache:     229300     278800
Swap:       262140      19000     243140

33 Replies

What do your logs say? Access/error logs? Also check things like mail logs / auth.log and syslog/messages to see if there's anything special. Same for PHP-FPM logs if you use it. It should tell you which pool is causing it if there's too much traffic for FPM to handle.

````
[email protected] ~: 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 ]


[email protected] ~: 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


[email protected] ~: 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


[email protected] ~: 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


[email protected] ~: 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"


[email protected] ~: 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)

[email protected] ~: tail -f /var/log/nginx/mail.*
tail: cannot open `/var/log/nginx/mail.*' for reading: No such file or directory
tail: no files remaining

````

iotop:

 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]

I think maybe this is the problem, found in my php5-fpm.log…

[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..

Sounds like you've got some rather inefficient MySQL usage going on. Try SHOW PROCESSLIST to ask the MySQL server what's going on right now.

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.

so my guess is the io is cause mostly by swapping, you only have 19M of free swap.

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.

Well before you'll responded I goofed.. I went in and changed my pm.max_children to 50 and now I can't get into my server by Console, Remote Access, or FTP…

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?

reboot. you'll want to be quick about logging in after it come back up though

Better get /etc/init.d/php-fpm (or php5-fpm) stop on your copy/paste :)

PS. htop might be handy for showing memory and such through. I find it a lot clearer than normal top and free.

Thanks for the help guys! I did a reboot and logged in by ftp the second it rebooted, deleted the autoblog plugin and it cleared the IO problems and now my sites are back and running!

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"

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.

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

@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 |
+-----+---------+-----------+------+---------+------+-------+-------------------                                     ----+

how do you run mysqltuner.pl?

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?

````
[email protected] ~: 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?

``` [email protected] ~: 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]

[email protected] ~: chmod +x mysqltuner.pl
[email protected] ~: ./mysqltuner.pl
./mysqltuner.pl: line 4: syntax error near unexpected token newline' ./mysqltuner.pl: line 4:'
[email protected] ~:

````

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:

[email protected] ~: 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]

[email protected] ~: chmod +x mysqltuner.pl
[email protected] ~: ./mysqltuner.pl
./mysqltuner.pl: line 4: syntax error near unexpected token `newline'
./mysqltuner.pl: line 4: `'
[email protected] ~:

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.

``` [email protected] ~: 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]


[email protected] ~: 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

````

mv index.html.3 mysqltuner.pl

head mysqltuner.pl

(check first line, if ok, then)

chmod +x mysqltuner.pl

./mysqltuner.pl

okay thanks, as you can see I'm very much still learning linux.. this is what it outputs

[email protected] ~: ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>>>  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)</[email protected]> 

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

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

My.cnf: This is the config file of mysql. So it will probably be here: /etc/mysql/my.cnf

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 ; - after that you can run SELECT * FROM wpoptions WHERE optionname = 'active_plugins';

your my.cnf file is probably

/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.

THANK YOU NUVINI AND CHESTY! THANK YOU!

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??

i don't know, go through

/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

okay this plugin I use called dsidxpress is leaving some entries in my wp_options when I read mysql-slow.log … I sent a query to the developer's support team and they have not gotten back to me.

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?

BUMP - at since my last post and upgrade to a Linode 768 I have not experienced any more IO spikes but my CPU has been running too high numerous times, however it comes back to normal and corrects itself after a huge spike, can anyone help me diagnose & cure this one? Chesty? Nuvini?

I've actually had this problem for a while but haven't done anything about it since it wasn't crashing my server it wasn't a top priority

30 day graph: https://docs.google.com/file/d/0B6KEPqy … sp=sharing">https://docs.google.com/file/d/0B6KEPqyasW5kUHZXdjMxczVSb2s/edit?usp=sharing

24 hour graph: https://docs.google.com/file/d/0B6KEPqy … sp=sharing">https://docs.google.com/file/d/0B6KEPqyasW5kcnlaMTNVeGstaHc/edit?usp=sharing

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct