WordPress MySQL & PHP Performance

Question

have a website (www.americanbankingnews.com) that gets 40,000-50,000 page views today. It's currently sitting on a dedicated quad-core Xeon server with 8GB of ram. The site is powered by WordPress and MySQL (sitting on the same server) and I'm currently using W3 Total Cache for page and MySQL query Caching.

Unfortunately, that doesn't seem to be enough. During peak traffic times, my servers are getting a few HTTP 500 errors and pages that aren't cached load slowly.

I'm currently not using Xcache or any other PHP cache/acceleration tools.

Are there additional steps that I should take to try to optimize MySQL and PHP performance? Or should I fork over for an additional server. Specifically, I'd be interested in additional suggestions to improve MySQL performance and whether or not tools like xcache might help in this situation

Answer 1

Hi Matthew Paulson,

I see your using W3 Total Cache but your database and object cache is set to disk. Caching objects and your database to disk can actually have a negative performance effect especially if your getting that much traffic.

You can read more about the effects on caching database and objects to disk in an article I wrote on how to set up W3 Total Cache The plugin author agreed with the instructions in my settings.

To really see the benefit of database and object caching you need to be using a PHP opcode cache like APC. You can follow the copy and paste instructions in the plugin FAQ to compile and set up APC. If your on Ubuntu or Debian you can simply run the command: apt-get apc-php5 to install.

Like others have also mentioned you will get a huge performance boost and enable your site to scale much larger by setting up a reverse proxy with Nginx.

I give detailed instructions on how to configure and set it up in my WordPress Performance Stack. article.

You should also read some of the other questions and answers on here. A lot of good performance and scaling advice has been given.

Good luck on your quest. Managing your own server can be very stressful sometimes.

Edit

Just to show the performance you can gain by installing Nginx as a reverse proxy I'm posting an Apache Benchmark test I just ran on my server:

x-wing ~: ab -n 1000 -c 80 http://wp-performance.com/
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking wp-performance.com (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests


Server Software:        nginx/0.8.54
Server Hostname:        wp-performance.com
Server Port:            80

Document Path:          /
Document Length:        3132 bytes

Concurrency Level:      80
Time taken for tests:   0.066 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      3605000 bytes
HTML transferred:       3132000 bytes
Requests per second:    15164.15 [#/sec] (mean)
Time per request:       5.276 [ms] (mean)
Time per request:       0.066 [ms] (mean, across all concurrent requests)
Transfer rate:          53385.52 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    2   0.6      1       3
Processing:     1    4   0.8      4       5
Waiting:        1    3   0.8      3       5
Total:          3    5   0.6      5       7

Percentage of the requests served within a certain time (ms)
  50%      5
  66%      5
  75%      6
  80%      6
  90%      6
  95%      6
  98%      6
  99%      6
 100%      7 (longest request)

Theoretically it's able to handle over 15,000 requests per second. (Same Network)

Answer 2

Lots of similar questions and answers: https://wordpress.stackexchange.com/search?q=mysql+optimize

It comes down to using tools - like mysqltuner - to investigate the bottlenecks, checking logs for errors and memory usage, php opcode-caching, clearing post/page revisons to get the DB down to size, etc.

Answer 3

Hi @Matthew Paulson:

You may be asking the wrong question.

With your traffic you may want to look at at front-end cache using nginx. Here are Q&As for nginx here on the site, lots of relevant articles in a Google search, and plugin that can interface WordPress to nginx at wordpress.org and lastly an article on installing and configuring:

• https://wordpress.stackexchange.com/search?q=nginx
• http://www.google.com/search?q=nginx+wordpress
• http://wordpress.org/extend/plugins/search.php?q=nginx
• http://elasticdog.com/2008/02/howto-install-wordpress-on-nginx/

If that doesn't help or if you don't want to do it for some other reason please let us know what plugins you are using. Most of the time with WordPress performance problems are not the obvious but instead they are result of some poorly written plugins.

Answer 4

Do you have insight in what exactly is becoming a bottleneck under high load? It could be different type of resource (CPU load, sustaining network conenctions, running out of memory, etc).

General things:

• opcode cache (keeping compiled PHP code in memory) is a must;
• you seem to have memory to burn, so it's worth trying memory-based object cache (W3TC supports it, look into that). It will make a lot of stuff more persistent and move load away from MySQL;
• if web server software is bottleneck (you don't mention what you run, Apache?) you might want to look into alternative web server (like nginx) or reverse proxy (again - you have memory to burn, I heard good things about Varnish).

Answer 5

You weren't kidding, just tried going to your site and got a 500 internal error. Perhaps you can lower the number of plugins you are using and make sure all images are optimized, etc. to make page sizes smaller so they consume less bandwidth to load hence less errors.

You may also want to look into HIP HOP for PHP . I never implemented it but it was released open source by the facbook people who after creating and using it saw their server load go down by about 30%. Basically it takes regular php files, converts them to C++ binaries and serves those out.

Answer 6

The hardware you have should easily cope with the stated traffic (as a benchmark, a site I run peaks at ~40k daily page views on a 2GB Slicehost VPS) - so that suggests something grossly wrong.

So, as other people have said, the first thing you need to do is understand where the problem(s) is/are.

1. What information does top give you when you're under load? Are you using swap memory, is your load spiking, when you sort by memory and cpu% what are the top processes?

2. Can you install something like munin to give you an insight into your server?

3. Assuming you're running Apache, how is it configured - the important things to know are:

   • Timeout
   • KeepAlive
   • MaxKeepAliveRequests
   • KeepAliveTimeout
   • (assuming you're running Apache in prefork mode) - all the config lines in the prefork section of your apache conf file.

4. Running this command - ps -ylC httpd --sort:rss will give you a indication of how much memory Apache processes are using.

5. Install mtop and mysqltuner - from what I remember both are available via apt-get. You should also turn on MySQL slow logging - you normally just need to uncomment a line in your my.cnf file.

   • mtop gives you a real time view of what SQL queries are running (slowly). mysqltuner will give you some sense of what changes you need to make to your MySQL configuration

6. As suggested by others, what happens if you turn off plugins during a busy period? Does the site speed up? What plugins do you have installed?

7. Do you have any idea of how much traffic you're getting during your busy periods - eg 4000 page views in 10 minutes.

Armed with information from these sources, you'll have a much better idea of what's going wrong.

Answer 7

i dont know what programs you installed but maybe its APC - Zend problem:

http://www.ivankristianto.com/web-development/server/alternative-php-cache-apc-not-compatible-with-zend-optimizer/1726/

This problem happen in my VPS after i install Alternative PHP cache (APC). And also i already have Zend optimizer installed on the same VPS. After i installed APC, my WordPress blog show strange behavior. Sometimes i got Error 500 Internal Server Error, and sometimes i got PHP fatal error.