LAMP stack tuning

@brgsstm:

I am under the impression that any usage of swap is undesirable?
No, there's nothing wrong with using swap, especially if you have a lot of RAM left. The kernel sometimes uses swap even when there's plenty of RAM left, because Linus Torvalds is a genius who knows how to optimize everything.

What is undesirable is thrashing swap. Thrashing happens when you run out of physical RAM. You can usually tell when your server is thrashing, because the red line in your I/O graph will go through the roof.

The guide you linked to doesn't seem to have been written with PHP in mind. ServerLimit/MaxClients 64 could make you thrash if you ever get decent traffic. Cut it to less than 20.

Also, if you have a significant amount of data in your MySQL databases, you might want to increase keybuffer and sortbuffer. The numbers in the guide are suitable for tiny servers with double-digit MB of RAM. On a Linode 512, 16M/1M is perfectly fine, and might even help reduce disk I/O.

I don't think /etc/my.cnf is the proper location for the MySQL configuration file. Isn't it /etc/mysql/my.cnf ? If thread_stack is not already in that file, don't bother adding it. As I said, the MySQL part of the guide seems to have been written for servers much smaller than a Linode 512.

Finally, KeepAlive 25 is really high. Your pages will probably finish loading in 1.5 seconds, after which the connection will sit idle, doing nothing. Apache works perfectly fine with no KeepAlive or very low KeepAlive (2-5 seconds).

Thanks for the reply! Very helpful.

There was no mysql folder in /etc. I found this strange, maybe a disty difference? I am using CentOS 5.6? The my.cnf is straight under /etc and any manual changes are being seen in Webmin too so I guess this is the right file.

Ok, after making the changes you mentioned in Webmin to keybuffer and sortbuffer I entered my.cnf and the thread_stack variable had been removed so I guess Webmin didn't recognise it and omitted it.

I just found this more recent advice from Linode http://library.linode.com/troubleshooti … y-settings">http://library.linode.com/troubleshooting/memory-networking#sph_apache-2-low-memory-settings

Seems to suggest a low keybuffer and sortbuffer too?

Here is the contents on my my.cnf

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql

old_passwords=1
set-variable = net_buffer_length=2K
set-variable = max_allowed_packet=1M
set-variable = sort_buffer=1M
set-variable = key_buffer=16M
set-variable = table_cache=4
skip-locking

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

And the contents of the afore mentioned part of httpd.conf

##
## Server-Pool Size Regulation (MPM specific)
## 

# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# ServerLimit: maximum value for MaxClients for the lifetime of the server
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
#StartServers       8
#MinSpareServers    5
#MaxSpareServers   20
#ServerLimit      256
#MaxClients       256
#MaxRequestsPerChild  4000
 <ifmodule prefork.c="">StartServers       1
MinSpareServers    1
MaxSpareServers   5
ServerLimit      20
MaxClients       20
MaxRequestsPerChild  3000</ifmodule> 

# worker MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
 <ifmodule worker.c="">StartServers         2
MaxClients         150
MinSpareThreads     25
MaxSpareThreads     75 
ThreadsPerChild     25
MaxRequestsPerChild  0</ifmodule> 

How do these look? I made the changes in prefork mpm as the output of httpd -V | grep 'MPM' indicated prefork.

Thanks,

Tom

Looks about right. The other link you posted is also for "low memory settings" – these suggested values haven't changed since the time when VPS's only had 64MB of RAM. They work fine on small servers, but having low buffers means you're trading RAM for disk I/O. Since you have plenty of RAM and you're trying to reduce disk I/O here, it's probably better to give MySQL generous buffer sizes.

Also, both of the tutorials you mentioned assume no PHP, which drastically changes the memory usage characteristics of your server. Today's heavy PHP apps require much lower MaxClients than what the tutorials suggest, and they're generally much more taxing on MySQL than older apps used to be. So you should take both tutorials with a spoonful of salt.

This might be a Webmin thing, but "oldpasswords=1" can be a security risk because it forces MySQL to use a weaker hashing algorithm. No modern PHP app that I know of relies on oldpassword. But Webmin or some other package might use it, so you should be careful about removing it.

You can omit "skip-locking" because it's an obsolete name for a setting that is now turned on by default.

The "set-variable" thing also looks weird, but again, this might be a Webmin thing. At least this won't do any harm.

(Rant: Seriously, why is Webmin defaulting to these weird, outdated settings? Or maybe it's a CentOS thing. I haven't touched CentOS in a while.)

Thankyou so much for your help.

I have taken all the advice onboard and implemented the changes. I'm still showing a little swap in use but now I know it is not neccesarily a bad thing :)

I set old_passwords=0 and everything seems ok for now but I will keep an eye on it.

I try not to use Webmin where possible as I have read it makes a mess of config files. Sometimes it's just too easy to use though, I must be less lazy :)

Thanks again,

Tom

Sorry, me again :)

I have just ran mysqltuner.pl and the output is below.

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: 
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 104M (Tables: 286)
[--] Data in InnoDB tables: 5M (Tables: 76)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 47

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6h 57m 50s (3K q [0.130 qps], 128 conn, TX: 5M, RX: 931K)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 42.0M global + 1.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 210.8M (42% of installed RAM)
[OK] Slow queries: 0% (0/3K)
[OK] Highest usage of available connections: 3% (3/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/10.7M
[!!] Key buffer hit rate: 86.5% (8K cached / 1K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 249 sorts)
[!!] Temporary tables created on disk: 34% (88 on disk / 255 total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (4 open / 1K opened)
[OK] Open file limit used: 0% (2/1K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)
[OK] InnoDB data size / buffer pool: 5.5M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-bdb to MySQL configuration to disable BDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 4)</major@mhtx.net> 

Would it be advantageous to make these changes?

Thanks,

Tom

All recommendations look reasonable. You won't be able to do anything about the "SELECT DISTINCT queries without LIMIT clauses" because you're using third-party web apps, but the rest of them would be easy to implement.

Test again in a few days and see if mysqltuner has any more recommendations. Also watch memory usage for a while. Enabling the various buffers and caches can cause your memory usage to increase.

I'd also recommend dumping MyISAM altogether and converting all your tables to InnoDB (the newer, faster, better storage engine) – but that's a different topic, and it involves tuning a whole new set of parameters. If your sites are mostly for development, MyISAM is fine.

Thanks, I have made a few changes and will monitor over the next couple of days. I'm beginning to realise that I will be checking this often as a single tune is not enough if traffic increases.

I will let everything settle down and then look at converting to InnoDB. Strangely, when I first ran mysqltuner it advised disabling InnoDB as it wasnt in use. Now it seems I have 5Mb of InnoDB?

Anyhow, thanks for the help!

Cheers,

Tom

Most things are moving towards InnoDB, slowly but surely. It's the default in MySQL 5.5+, and there are few downsides. WordPress is 100% solid on InnoDB, as is Drupal, so you can ALTER TABLE to your heart's content if you're using either of those.