Tips for optimizing Apache, PHP and MySQL (again)
Recently, I moved a website to a Linode 1024, and although I'm happy with the performance of the server, I think I can improve the speed a bit more. Before someone warns me about the same, yes, I read other threads about Apache/PHP/MySQL optimization, I searched in Google, etc.
I run Ubuntu 10.04 and Apache with PHP as a module, and MySQL, all upgraded to the latests versions available for the distribution. I just want to compile general tips about optimization for these servers. In my case, my CPU usage is high, it ranges from 0.5 to 1.0. Memory consumption is about 30% in the entire system, so I have a huge margin here. Note that I'm not an expert in servers administration, but I don't consider myself a noob
In MySQL, I run the Slow Queries Log, with longquerytime as low as 0.15 for it to log something. I've targeted some queries that are executed with these times. Yes, they're low times, but these queries are executed very often, so they imply high load. In fact, disabling them reduces MySQL's CPU usage from 20-30% to 0-3%. Although they use properly indexed tables, I will simplify them.
I also run MySQL Tuner script. It made some recommendations, I applied them, but didn't notice any improvements in the server. Now, considering the characteristics of my Linode 1024, what values do you think I should modify at my.cnf to reduce CPU usage even more? I don't want to show my present settings, so they don't influence what you may say.
Regarding Apache, I have disabled AllowOverride directive and moved the content of all .htaccess files to the sites definition files, so that .htacess is not read every time a page is loaded, and all the settings loaded with Apache. This reduced server load about 10-15%. Again, what values do you think I should modify at apache2.conf to reduce CPU usage even more?
And finally, about PHP… Well, I think this is more related to the script than PHP's settings, but any tip will be welcome.
As I said, my priority is to reduce CPU. I don't mind to increase memory or disk consumption.
Thanks in advance!
9 Replies
As a general rule a load of 1 per core is as high as you want to go, so in the case of a linode 4.
For mysql it depends what you're serving, innodb the bigger the buffer pool the better (this stores stuff in ram for easy access), if you're using myisam make sure your key buffer is large enough, it's used for indexing.
Also make sure you have a decent query cache. Mysql tuner can all point you in the right direction for all of those.
Try running EXPLAIN SELECT…. on your select queries in your slow log. Also the minimum time for the mysql slow log is 1 second, setting it below that won't help.
Try removing any text/blob columns on any queries that require temporary tables (see
Apache, make sure you have modgzip or moddeflate enabled, these won't reduce cpu but will decrease page load times. Also enable mod_expires and set a decent expire time for static resources this will prevent unnecessary requests to your server and decrease load time.
PHP, there's tons of things you can do there. Reduce disk IO operations a good way is to uses memory based sessions, try something like
Reduce unnecessary for/while loops, install php apc.
In a Linode environment cpu is rarely a problem wait's for zunzun to pop up. Most people find bottle necks with disk IO or not enough ram first.
@obs:
Your cpu usage isn't high, I assume you mean load (i.e what top outputs) when you say 0.5 to 1. That's not true cpu usage, it includes things like io wait, i.e. a process is doing nothing while it waits for the disk to retrieve data.
As a general rule a load of 1 per core is as high as you want to go, so in the case of a linode 4.
Yes, yes, but… why use 1 core if I can use, let's say, 0.3?
I don't want this thread to serve just me, so I will make a summary with the tips shown until now:
MySQL
Correct use of indexes
Use Slow Queries Log
Run EXPLAIN SELECT…. on SELECT queries in slow queries log
Run MySQL Tuner
Increase the buffer pool for tables based on InnoDB
Increase query cache and key buffer in MyISAM tables
Remove text/blob columns on queries that require temporary tables. Otherwise, increase the value of tmptablesize and maxheaptable_size settings to avoid creating on-disk temporary tables and leave them in the memory
Apache
Avoid using AllowOverride setting for .htaccess files and move their contents to proper
in sites definition files Enable modgzip and moddeflate
Use mod_expires to set expire time for static entities
PHP
Learn how to write efficient code
:D Reduce disk IO operations
Install a cache like APC. In this case, disable apc.stat setting if you don't change your script very often, but be sure to restart your server or empty the cache when changes are done
OK. I will update this list so that it's useful for everybody, if new ideas are exposed. What can be modified at apache2.conf? Any more ideas for PHP?
C'mon people, let's squeeze our [virtual] machines!
@obs:
Why use 1 when you can use 0.3, why not? It doesn't make things slower it just means you're using your server closer to it's capacity, you might as well use the resources you can. Idle resources are wasted resources. (Which is why the linux kernel caches things in ram).
That's right. But if you have an A scenario with a server configuration for N visits that uses 1, and a B scenario that uses 0.3, if you have 4N you will have to upgrade your server in case A, but just a server load of 1.2 in case B. In other words, B can support 13N in one server, but A needs four servers for the same work.
This is an example, I don't pretend to be rigorous
I know, I know, two posts for the same thing, but I wrote a duplicated post and now I can't delete it, so I fill it with more ideas
@JshWright:
An object level cache like APC is great, but you can also get a huge benefit from using something like memcached to store query results/etc…
You can use APC for caching users data too:) But, of course, sometimes it's better to use memcache, sometimes - APC.
@usr01:
@obs:Why use 1 when you can use 0.3, why not? It doesn't make things slower it just means you're using your server closer to it's capacity, you might as well use the resources you can. Idle resources are wasted resources. (Which is why the linux kernel caches things in ram).
That's right. But if you have an A scenario with a server configuration for N visits that uses 1, and a B scenario that uses 0.3, if you have 4N you will have to upgrade your server in case A, but just a server load of 1.2 in case B. In other words, B can support 13N in one server, but A needs four servers for the same work.
This is an example, I don't pretend to be rigorous
:wink:
Except that a Linode has four cores, so if you're using 20% CPU time right now, you're at 5% of your overall capacity.
