TOCICI

VPS Performance Tuning

Does your VPS seem sluggish? What follows are recommended best-practices for tuning and improving the performance of services running within your VPS.

MySQL

MySQLTuner

Taking common MySQL database server information, such as “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”, mysqltuner.pl will help you optimize your specific MySQL database/server installation's configuration.

Database Defragmentation

Fragmentation means that the physical ordering of index pages on disk are not close to the index ordering of the records on pages, or that there are many unused pages in the 64-page blocks that were allocated to an index. One symptom of fragmentation is that a database table consumes more disk space than it “should”. How much that is exactly, is difficult to determine. All InnoDB data and indexes are stored in B-trees, and their fill factor may vary from 50% to 100%. Another symptom of fragmentation is that a table scan takes more time than it “should”.

To defragment your MySQL database, run the command:

SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";") FROM information_schema.tables WHERE DATA_FREE > 0 INTO OUTFILE '/tmp/optimize.sql'; SOURCE /tmp/optimize.sql;

Memory Management

How large is your MySQL database? First, run a database defragmentation, and then do your assessment. To ensure the best possible database server performance, always ensure that your VPS's dedicated/committed RAM equals or exceeds your database's total size. If this seems impractical (e.g. you have a 100GB database), then you should consider our solid state VPSes (available: November 2011).

DNS Lookups

Do you have skip_name_resolve set in your /etc/my.cnf? If not, consider it. DNS works fine, until it doesn’t. Don’t let it catch you off guard.

Do you really need to restrict MySQL users based on hostnames? Worse, do you really want your MySQL server open to DoS attacks? Do you want a slow or non-responsive DNS server to cause your MySQL instance to cause application connection delays, slowness, or interruptions? If you don’t, you should probably disable this feature of MySQL’s authentication system.

To disable two DNS lookups per authentication attempt, you just need to set skip_name_resolve in your my.cnf file and restart MySQL. But before you do that, run the following command:

mysql> SELECT user, host FROM mysql.user
    -> WHERE host <> 'localhost' AND host RLIKE '[a-z]';
+------+--------+
| user | host   |
+------+--------+
| foo  | my.com |
+------+--------+

Any users you see here need to be converted to use IP addresses, IP address wildcards, or ‘localhost’ or they won’t be able to log in after you disable DNS resolution.

Apache

DNS Lookups

Have you disabled Apache's DNS lookup behavior?