Hi there!

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 long_query_time 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!

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.

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 http://dev.mysql.com/doc/refman/5.0/en/ ... ables.html)

Apache, make sure you have mod_gzip or mod_deflate 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 www.redis.io it's brilliant for that sort of thing (of course you have to write your own handler and beware of this https://bugs.launchpad.net/ubuntu/+sour ... bug/783747

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.

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

Thank you very much obs



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 tmp_table_size and max_heap_table_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 <Directory> in sites definition files
- Enable mod_gzip and mod_deflate
- Use mod_expires to set expire time for static entities

PHP

- Learn how to write efficient code
- 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!

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

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

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

Or, using the same example, in B scenario you will have more possibilities of improving the services you provide to your users, adding more features to a website, for example. Or even, you can downgrade to a Linode 768 or 512, and use the money for marketing or something.

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

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.

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.

For tweaking my.cnf with mysqltuner I found this link helpful to get started:

http://forums.cpanel.net/f189/mysql-opt ... 74272.html

_________________
PHP Development @ Tomakefast