Here at the MySQL Conference and Expo, Laura Thomson gave a great talk about Scalability and Performance Best Practices.
She had some interesting points about scalability. She basically said that no matter what language you write your web application, whether it’s compiled (C/C++) or interpreted (PHP, Java), you are subject to scalability issues. Another potential problem is optimizing before you know what exactly to optimize. This can lead to a loss of time that could have been spent on more important things.
Laura’s talk covers three types of best practices: general, scalability, and performance.
General Best Practices
First tip is to profile early and profile often. The earlier you can detect poor performance, the easier it is to fix. There are a handful of tools (APD, Xdebug, Zend) that can help with profiling. Use system profiling tools such as strace, dtrace, and ltrace to gather more information.
There are two types of effective profiling: debugging and habitual. Debugging profiling is about spotting deviations from the norm and habitual profiling is making the norm better. Profiling is an art and requires lots of practice to know where to look.
It is essential that the IT admins and the developers cooperate. This allows crisis’ to be handled properly, especially in production environments were outages are time critical. Team members should report alert the developers of any abnormal behavior changes after a new code release is pushed. Before pushing new code, schedule a launch window and procedures for having developers fix problems and possibly falling back to a previous version. Avoid pushing releases on Fridays, otherwise key team members may be unavailable or over worked on the weekend.
It is recommended to test your application with production data. Test data may not take into account certain scenarios where bugs can be introduced. It is advised to have a staging environment which uses production data and also undergoes simulated load testing.
In order for you to track your application’s performance, you should record your applications performance over a period of time, then analyze the data to find potential issues. There are several means of tracking performance including access logs, system metrics, application profiling, and query profiling.
When a problem occurs, don’t make assumptions. The problem may be caused by something other than what you think it might be.
Scalability Best Practices
When the web application begins to suffer performance issues, start to decouple and isolate components to track down the source. If you need to tweak code, spend only enough time to refactor as needed. Reduce load on servers by moving static content on to dedicated servers.
By default, PHP stores session data on the hard drive. This can cause performance issues and can benefit from storing the session data in a database or better yet in a distributed cache such as memcached.
The most important thing you can do to improve performance is to cache as much data as possible. There are many levels of caching. You can cache data sets or precomputed fragments. For things like images, you can set up dedicated services for caching and serving static content. The usual suspects are recommended for caching (APC, memcached, Squid).
PHP out of the box does not cache compiled pages. That means that ever request, each page has to be parsed and executed. Extensions such as APC and Zend can cache the compiled pages for an immediate speed improvement.
MySQL’s query cache works, but isn’t necessarily implemented the best way. If you query a table, the results are cached on the MySQL server. If a row is inserted, updated, or deleted, the entire cache is flushed. This supposedly has been fixed in MySQL 5.1, but a setting has to be set to not flush the cache.
To scale, your data can be federated across multiple MySQL servers. There can be complications with regards to data reliability and table joins can suffer from major speed hits.
A more reliable way to scale is to use replication. Replication does suffer from “slave lag” issues. The reason the lag can be high is because the master server uses multiple threads to store the data locally. The slave server has to process the replicated items in a single threaded mode to ensure the order of which the transactions is preserved. You can display the status of MySQL’s I/O and SQL thread by executing a
SHOW PROCESSLIST statement.
The more database writes, the greater the lag. Depending on your application, you may only want to use replication for failover or backups.
Sometimes you may benefit from designing your application to avoid situations where data is hard to scale and can’t easily be cached.
Performance Best Practices
When tuning your applications performance, change one thing at a time. If you change more than one thing, how do you know which change caused the improvement and how do you know if you didn’t introduce new bugs changing the other things. Use MySQL’s EXPLAIN statement to profile your queries and enable the slow query logging. Use MyTop or InnoTop to help profile your queries.
It is crucial that your database is properly indexed. If a table has poorly designed indexes or perhaps too many indexes. Use the smallest data type possible and try to design your tables to be fixed width. That means, use char instead of varchar, set the length of your fields to logical lengths (ie use 128 chars instead of 107 chars). De-normalize when necessary. Remove static data out of the database or store it in a MEMORY table. Use the appropriate storage engine for each table.
For your queries, minimize the number of queries and cache them outside the database when possible.
She claims that deeply recursive code is expensive in PHP. Make sure you are not doing unnecessary looping. If you find that you are, chances are you are doing something wrong and that there is a better idiom for performing the task.
Don’t try to work around or re-write perceived inefficiencies in PHP. Use regular expressions to do intense string manipulation. instead of writing complex serialization code, use PHP’s extensions to do the heavy lifting. Before spending time to write some boilerplate function, check to see if there are any extensions that exist that could help save you time.
Laura gave this talk on behalf of George Schlossnagle. George’s original presentation can be found at http://omniti.com/~george/talks. This was an excellent session and proved to be beneficial.