Wordpress mysql traffic

[w1z4rd]

I host a wordpress website for a friend on its own dedicated server. Its not weak hardware. Ive gone through 3 different operating systems with this website (centos, ubuntu and now debian).

The site has average traffic but seems to kill whatever hosting it goes onto. The server load goes up to 16 sometimes This makes it unresponsive to anything but icmp.

I run Zenoss to monitor my servers and I noticed that the Mysql traffic was unusually high on the server running the wordpress site.

The following are select graphs showing the wordpress server and comparing it to another server of mine and its loads.

http://goo.gl/JK56L

Is there anyone who knows how to improve the performance of a server hosting a busy wordpress site? Should I perhaps try install Nginx? It has all the usual stuff like cache plugins and CDN dns already optimizing it (more info in the link).

Right now on debian the site seems to be more stable, but every now and again the load goes crazy. The traffic spike around 00:00 is a rsync cron I know about. It does not add much load.

 

[koffiejunkie]

What precisely are you referring to when you say "MySQL traffic"? data throughput or queries per second? Either ways, neither you show are high. I'm doing several times that on really old hardware.

So, is it the same site on both servers? With the same plug-ins enabled and the same theme running? Is it the only site on both servers? Is it the same major version of MySQL on both servers (i.e. 5.0.x or 5.1.x or 5.5.x)? Do they have the same configuration? The default configurations in Debian and CentOS are not the same.

If the problem is MySQL load or traffic, changing Apache for NginX won't help. If the load is Apache bound, it might. If the load is PHP bound, adding some opcode caching (I recommend APC, amongst other reasons because it will become part of PHP in the not too distant future but I'm sure I'll be scolded by PHP devs here) to lower the load PHP causes.

Wordpress on Apache 2.2.x with MySQL 5.0.x and PHP 5.2.x can handle pretty significant load on a 7 year old desktop box with IDE drive and 2GB RAM. I don't expect it to struggle on modern hosting platforms unless there is something wrong with the configuration.

 

[Tinuva]

If this Wordpress site, is the only site on this mysql server, I highly suggest optimizing it with mysqltuner from rackerhacker (sysadmin at rackspace).

http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner

Mysql can be a beast, but if tuned incorrectly, cause major headaches. In my time, mysqltuner has been a great help to lessen problems on mysql servers, however it could also be a bad wordpress plugin or something causing the problems. You might also want to add mod_security to apache and add some rules to block most bots that tries to find mysql injection vulnerabilities which could be spamming the website, and while that traffic seem simple, it can cause huge load problems on the mysql back-end.

I hope the above few tips help you.

 

[w1z4rd]

What precisely are you referring to when you say "MySQL traffic"? data throughput or queries per second? Either ways, neither you show are high. I'm doing several times that on really old hardware.

Well I assumed the mysql traffic graph as shown was how much bandwidth mysql is using? Or do I have it very wrong?

So, is it the same site on both servers? With the same plug-ins enabled and the same theme running? Is it the only site on both servers? Is it the same major version of MySQL on both servers (i.e. 5.0.x or 5.1.x or 5.5.x)? Do they have the same configuration? The default configurations in Debian and CentOS are not the same.

Ive tried it on debian, centos and ubuntu servers. Site was originally kicked off hostgator for using too much resources. My primary server which handles many sites couldnt handle it.

If the problem is MySQL load or traffic, changing Apache for NginX won't help. If the load is Apache bound, it might. If the load is PHP bound, adding some opcode caching (I recommend APC, amongst other reasons because it will become part of PHP in the not too distant future but I'm sure I'll be scolded by PHP devs here) to lower the load PHP causes

.

I dont know enough about mysql to know whats going on here. All I know is the CPU goes to insane levels and eventually the service that dies is mysql.

Wordpress on Apache 2.2.x with MySQL 5.0.x and PHP 5.2.x can handle pretty significant load on a 7 year old desktop box with IDE drive and 2GB RAM. I don't expect it to struggle on modern hosting platforms unless there is something wrong with the configuration.

I also assumed that

If this Wordpress site, is the only site on this mysql server, I highly suggest optimizing it with mysqltuner from rackerhacker (sysadmin at rackspace).

http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner

Mysql can be a beast, but if tuned incorrectly, cause major headaches. In my time, mysqltuner has been a great help to lessen problems on mysql servers, however it could also be a bad wordpress plugin or something causing the problems. You might also want to add mod_security to apache and add some rules to block most bots that tries to find mysql injection vulnerabilities which could be spamming the website, and while that traffic seem simple, it can cause huge load problems on the mysql back-end.

Will definitely look at the tuner. Have mod_security installed + Cloudflare.


Ty for the advice peeps.

 

[koffiejunkie]

Well I assumed the mysql traffic graph as shown was how much bandwidth mysql is using? Or do I have it very wrong?

Well, your graphs show both, that's why I asked which one concerns you. Neither is of particular importance unless you run into network limits, really.

Ive tried it on debian, centos and ubuntu servers.

OS doesn't really matter. You could run it on Windows if you want. There are performance differences between MySQL on different operating systems (not distros, unless one is doing something weird), but nothing that will show up in the level of traffic you show.

Site was originally kicked off hostgator for using too much resources. My primary server which handles many sites couldnt handle it.

It sounds to me like you have a bad plugin or something going. I would assume that Hostgator has a fairly middle-of-the-road semi-sane MySQL configuration, so I don't imagine the problem is there. Red Hat (and I assume CentOS) ships with a very basic MySQL config, with most performance enhancing features disabled. Even in that configuration, it should still handle Wordpress just fine.

I dont know enough about mysql to know whats going on here. All I know is the CPU goes to insane levels and eventually the service that dies is mysql.

Again I would assume a plugin. MySQL doesn't just die. In fact, outside of bad hardware or a really messed up MySQL store, I have never seen MySQL crash. What you're probably running into is MySQL hitting its max_connections limit, i.e. the maximum number of concurrent client connections. The default is usually 100 or 150, which, if each connection was actively running a query, most servers won't be able to handle. What I suspect may be happening is you have something (plugin/theme) opening connections but not closing them. You then have a bunch of stale connections hogging connection slots while new connections are rejected. Check "show processlist" in MySQL when this happens. This should give you an idea of how many connections are open. If you see some, do "show full processlist" to see what they're doing.

Will definitely look at the tuner.

Be aware that mysqltuner.pl is meant for people who understand MySQL configuration and the way each configuration directive is used by MySQL. It will give you trying-to-be-helpful suggestions but it's intelligence has its limits. It can't, for example, make informed decisions about a good risk/performance balance. If you know what you're doing though, it's an excellent tool (and one that I use daily).

 

[Tinuva]

If the problem is indeed a plugin opening connections and not closing them, one of the things I had to enable on our shared hosting mysql servers is a shorter timeout for idle connections, so to forcefully close idle connections server side. While this cause a tiny bit of cpu and resources, it does help a bit, and if clients complain I tell them to fix their code which they usually do after realizing we won't change the setting on our side.