# [solved] How to improve LAMP performance?

## Kosa

Hi i still have small webserver  :Smile:  But a week ago, one of our websites launched some contest and, well, people are stupid and they're browsing like mad there (they don't understand the rules).

So it's killing the server for a bit - minute average load above 20 is usual. This one site only is now serving about 500.000 pages per day (PHP only, total hits including static content are about 2.000.000 per day). Other pages add a litle more, but this is the true killer app.

HW is P4 2,66 GHz, 1GB RAM, RAID1 disks (normal SATA), some interesting configs from MySQL:

```
key_buffer                      = 128M

max_allowed_packet              = 1M

table_cache                     = 1024

sort_buffer_size                = 512K

net_buffer_length               = 8K

read_buffer_size                = 256K

tmp_table_size                  = 128M

read_rnd_buffer_size            = 512K

myisam_sort_buffer_size         = 8M

language                        = /usr/share/mysql/english

max_connections                 = 300

```

And defaults from Apache with mod_prefork:

```
StartServers         5

MinSpareServers      5

MaxSpareServers     10

MaxClients         150

MaxRequestsPerChild  0

```

PHP5 is built very lightweight, with needed extensions only. But also without any optimizer or cache.

Is there any way how to improve the performace without great cost? Thanks for any tip.Last edited by Kosa on Tue Oct 10, 2006 8:25 am; edited 1 time in total

----------

## bjlockie

Try doubling (or more since you've got the RAM).  :Smile: 

Change the httpd.conf so:

StartServers         20

MinSpareServers      20

MaxSpareServers     40

MaxClients         150

MaxRequestsPerChild  0

----------

## skunk

- install eaccelerator

- activate query caching in my.cnf: query_cache_size = 64M

- look at this, this and this

----------

## kashani

I'm going to disagree with Skunk and recommend a query size of 32MB beause you are so short of RAM to begin with. 16MB might be better, but I wouldn't go smaller than that. Here are the relevent bits of my config.

```

#LOGGING =======================================================

# turn on binary logging and daemon logging

# We'll also log slow queries and define a slow query as 4 seconds rather than the default of 10

log-bin

log-error                       = /var/log/mysql/mysqld.err

log-slow-queries                = /var/log/mysql/slow-query.log

long_query_time                 = 4

#QUERY CACHE =====================================================

# 32M seems reasonable and then we lock the object size at 64k to keep giant queries from abusing it.

query_cache_size                = 32M

query_cache_limit               = 64k

```

I'd recommend installing the Mysql template for Cacti and running that against your server as well. That has helped me track down weirdness easily. http://www.faemalia.net/mysqlUtils/

I've had issues with pecl-apc, aka Another PHP Cache, but have found the unstable Eaccelerator build to work well for PHP 5.1. Perfomance will increase significantly on most PHP pages. Additionally I found Apache 2.2 to serve PHP pages about 10% faster than Apache 2.0.

Edit: And one last thing. Bumping up to 2GB of RAM will likely double the traffic you can serve unless something really strange is going on.

kashani

----------

## Kosa

I've installed Zend optimizer yeasterday, i'll take a look at eaccelerator today. Btw. just for being sure, is this normal performance for this HW or am I doing something stupid? Application is written maybe not perfectly as i didn't wrote it but i'm sure that this is not the bootleneck.

I was almost sure that additional 1GB of  RAM is the best solution, so i'll go for it. Thx for your replies.

----------

## kashani

I ran a week or so of test againist Apache and PHP a few months ago. The system was running with 1 GB of RAM and topping out at 30 requests per second in our application. By reducing the PHP binary from 8MB to 5MB by cutting out USE flags, I increased throughput because RAM was the limiting factor. Each time you have to call the PHP parser it takes about that much RAM which is used in the particular Apache process handling the request. More requests equals more processes until your system starts swapping. If you have a PHP cache your should not need the parser sicne you've got compiled opcode already so it can just execute.

There is the free zend optimizer which may be turned on already and then there is the $1k per CPU pro version which I've never used. Either pecl-apc or Eaccelerator can be used in conjuction with the free Zend optimizer... in fact I'm not really sure what the free one does anymore if anything.

I'd definitely try the free zend and Eaccelerator or whatever as a stop gap. Additionally the Mysql query cache is not on by default so adding it even at only 16MB might help. All in all adding more RAM is probably the fastest and cheapest assuming you get paid by the hour.  :Smile: 

kashani

----------

## Kosa

Ok, i will take a closer look in the evening, when all visitors gather there and are clicking like some kind of stress testing tool  :Smile: 

RAM is on the way.

----------

## Janne Pikkarainen

When you have lots of MySQL connections and processes, growing the buffer sizes is not the way to go: that way you make MySQL eat more memory than you ever thought it would need. Instead you need to reduce buffer sizes (or leave them as they are) and concentrate on how to squeeze out more speed from your MySQL tables. Most of  the configurable buffer variables are global and shared by all the MySQL processes, but be sure you grow the ones which one truly are global, and not local buffers.

Have you cross-checked your databases and tables? Is there enough indexes? Are the SELECT queries optimized? What storage engine is in use and is your site mostly read-oriented or does database get lots of inserts, updates and/or deletes? For concurrent read/write access InnoDB would maybe a clever choice, thanks to its row-level locking and multi-versioning. MyISAM needs a table-level lock for write operations, and that can lead to slight delay in SELECTs, which can lead to more MySQL/Apache processes, which leads to resource starvation...  :Smile: 

If your database is heavily write-oriented, then MySQL query cache can actually hurt performance and disabling it can help performance.

Also of course trimming down PHP and Apache can help a lot, as can eaccelerator. But most of the time Apache and PHP are fast enough and making some external resource (like SQL database or some external URL) work faster can help overall performance a lot.

----------

## Kosa

Thanks for another point of view. The biggest problem here is that i can not controll the application itself as i'm only the "server provider". So i can configure apache, mysql, add some accelerators or upgrade HW.

But i will point the developer here, i think he will find your advices helpful  :Smile: 

----------

## kashani

heh heh, welcome to the web hosting industry.  :Smile: 

I've never managed to keep my nose out of the application/web site because most of them are written so poorly. Or people do stupid things like have their front page require twelve separate Mysql queries. I try to look at any application before it gets on the server and then keep any eye on it especially if they are adding new functionaility. 

Here are a couple of additional things you can do

1. Write a script to walk your db and return the number of rows in each table. This catches a lot of problems as many sites like to generate stats they never process or remove. I generally have it alert me when anything goes over 1 million rows depending on the site.

2. mysqldump -p --nodata > schema.sql That command dumps the create statements for your db so you can run through it quickly for strange things. I'd look for tables that don't use int(11) as an id, anything using medtext or larger, and entire databases that don't use at least one ALTER statement on a table. Of course having nine ALTER statements on a table might be an issue as well or at least worth looking into.

medtext means text data from 64k to 17MB which is a very large object in your db.

3. slow-queries These should be looked at every few weeks and cleared out as well. It's going to be a bit hard for you tell what's going on because you have Mysql and Apache on the same box. You're likely to see slow queries if Apache is stealing Mysql's resources.

4. 3-5 Mysql queries per page is a good rule of thumb. More than that should be a special case.

In any case good luck and you should learn quite a bit. 

kashani

----------

## Janne Pikkarainen

 *Kosa wrote:*   

> Thanks for another point of view. The biggest problem here is that i can not controll the application itself as i'm only the "server provider". So i can configure apache, mysql, add some accelerators or upgrade HW.
> 
> But i will point the developer here, i think he will find your advices helpful 

 

 :Smile:  Like Kashani said, welcome to web industry!

Accelerators & MySQL fine tuning ("I'll make sort key buffer size bigger, that'll guarantee my MySQL FLIES!") can only help to some certain point. Throwing in some new hardware helps you some more. But what makes the true difference is some clean, efficient code, well-planned database design and minimizing the amount of needed SQL queries per page load. Co-operation with the developer (team) is the way to fast site and optimized server resource usage.  :Smile: 

And yes, I'm also in the same situation than you... I cannot control everything our users deploy, but sometimes hitting them with a clue stick ("Did you know you have a very bloated statistics system and that makes your site crawl") saves my day - and the server.

----------

## Kosa

Nice tips kashani they will be very helpfull  :Smile:  I am learning all the time  :Smile: 

We plan to move all sites on new servers and separate Apache and MySQL in a near future. Until that, it is still more hobby than real work  :Smile: 

----------

## Kosa

I hit them very hard with "Your site is killing our server"  :Smile:  Hopefully they will move to dedicated (and better) server within a few days which will give me options to play more with all things (very lightweight PHP, better MySQL optimalization for them etc)

----------

## Ast0r

 *skunk wrote:*   

> - install eaccelerator
> 
> - activate query caching in my.cnf: query_cache_size = 64M
> 
> - look at this, this and this

 

For sure. I installed eacclerator a few weeks ago and the performance gains have been phenomenal, especially for very large PHP scripts which use many includes and have large objects. The performance increase from the free Zend Optimizer was crap and it was not very configurable.

You can't rules out really bad PHP code or really dumb queries if your clients are the ones writing them either. You could at least try and find any queries/PHP files that do stupid things and let them know about them so they can fix them.

Optimizing your MySQL queries/tables will do wonders for you too, if they are not currently. We use PostgreSQL, so I can't say if MySQL has a similar feature, but in PostgreSQL the EXPLAIN ANALYZE command is very helpful for finding slow queries.

----------

## Kosa

Btw. problem was finally solved in the most simple way - they had slightly changed the rules, so people stopped their clicking madness  :Smile: 

Anyway thanks for any ideas posted here, they will be very helpfull.

----------

