Web hosting has changed dramatically in the last few years. The majority of accounts are now running PHP scripts and MySQL, whereas in the past most accounts were only serving static HTML files. This presents challenges in security as well as performance tuning.
It is impossible to predict whether a certain configuration will work properly for a given server, unless the hardware, software, accounts, network and traffic are identical to a previously configured server… therefore it is impossible to give a configuration which will cover all applications.
Firewall Unnecessary Traffic from Affecting the Server
Before you begin performance tuning, please consider using a stateful packet inspection (SPI) firewall & login failure daemon (LFD) for your server: ConfigServer Firewall is an excellent free firewall & lfd. This will help prevent brute force probes, port flooding, DDOS, etc. If you have 600 virtual hosts running on a server, it is very likely that the server is experiencing constant malicious exploits, especially if you are also running email servers on the same IP addresses.
Tune Apache the Sane Way (Requires Basic Math Skills)
If you wish to tune your Apache MPM settings, you should consider following this method:
https://telvps.com/clients/knowledgebase/25/HOW-TO-Optimize-Apache-for-Low-Memory-Usage.html
(the formula works well for any level of traffic and memory).
MySQL Tuning Is More Difficult: Trial and Error
To test your MySQL cache variables, you may try mysqltuner: https://github.com/rackerhacker/MySQLTuner-perl
This script will analyze MySQL performance and make suggestions regarding your my.cnf configuration. If you are running MySQL 5 or later, the configuration statements can be in the following form (example from one of my servers):
[mysqld]
safe-show-database
net_buffer_length = 1M
max_allowed_packet = 4M
myisam_sort_buffer_size = 24M
sort_buffer = 8M
key_buffer = 32M
table_cache = 1000
query_cache_size = 128M
thread_cache_size = 8
innodb_buffer_pool_size = 22M
join_buffer_size = 12M
tmp_table_size = 64M
max_heap_table_size = 32M
max_connections = 85
This is for example only! table_cache should be set a little higher than the total number of tables (sum of MYISAM,INNODB,MEMORY etc.), as reported by mysqltuner. If you anticipate adding more virtual hosts, or clients adding more databases or tables, you will need to raise table_cache value.
Follow the recommendations from the Apache tuning, and then follow the recommendations of the mysqltuner script. The apache tuning will give you the number of maxclients, and from this you will know the appropriate beginning value for [mysqld] maxconnections. Begin with maxconnections set slightly higher than maxclients.
Take Time to Get a Realistic Assessment of MySQL Performance
If you have a PHP opcode and database caching strategy (such as APC) implemented on your web server and for your PHP script packages, you will be able to lower maxconnections, based on the information you receive from mysqltuner after 24-48 hours of steady traffic. For example, I have a server with Apache set to 256 maxclients, but the MySQL maxconnections set to 200, because many web requests do not require direct interaction with the MySQL server.
Take a look at “Highest usage of available connections”, and lower maxconnections accordingly. It is safe to lower maxconnections to a number slightly above the reported highest usage. Doing so will allow you to set higher values for the individual cache settings which affect the thread cache size, because the total thread buffer memory size is multiplied by maxconnections. Feedback is available in the reports for “Total buffers” and “Highest usage of available connections”.
After you have the proper settings for maxclients and maxconnections, restart httpd and mysqld. Wait 1 hour, and run mysqltuner to see if there are any recommended changes. Mysqltuner will show you the maximum memory which will be used by mysql. You should adjust config variables to take up no more than 60% of total RAM. Wait 24-48 hours and run mysqltuner again.
Example mysqltuner results:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 748)
[--] Data in InnoDB tables: 20M (Tables: 76)
[--] Data in MEMORY tables: 1M (Tables: 1)
[!!] Total fragmented tables: 55
-------- Performance Metrics -------------------------------------------------
[--] Up for: 15h 7m 18s (1M q [31.195 qps], 105K conn, TX: 7B, RX: 285M)
[--] Reads / Writes: 63% / 37%
[--] Total buffers: 390.0M global + 18.6M per thread (200 max threads)
[OK] Maximum possible memory usage: 4.0G (66% of installed RAM)
[OK] Slow queries: 0% (13/1M)
[OK] Highest usage of available connections: 25% (51/200)
[OK] Key buffer size / total MyISAM indexes: 72.0M/1.1G
[OK] Key buffer hit rate: 99.9% (1B cached / 1M reads)
[OK] Query cache efficiency: 78.2% (967K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 74K sorts)
[OK] Temporary tables created on disk: 20% (7K on disk / 38K total)
[OK] Thread cache hit rate: 99% (294 created / 105K connections)
[OK] Table cache hit rate: 84% (1K open / 1K opened)
[OK] Open file limit used: 71% (1K/2K)
[OK] Table locks acquired immediately: 99% (684K immediate / 686K locks)
[OK] InnoDB data size / buffer pool: 20.2M/28.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
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
I suggest you NOT enable the slow query log unless the Slow Queries result is very high. Slow Queries result % will be somewhat high if MySQL has run for less than 24 hours.
Incoming search terms for the article:
