HomeMySQL Page 2 - Take Some Load off MySQL with MemCached
A Solution - Caching - MySQL
While the execution speed of your codebase can be a factor in the overall scalability of your application, more often than not, your database will become a bottleneck first. Modern web development environments can generally serve many page loads per second, and each of these pages will often make many requests to the database for fresh information. These pages may also be rendered by an easily expandable pool of web servers. While databases, including MySQL, are adequately designed to handle a significant number of queries, eventually, the load from all these requests can become too much to handle.
The bottleneck in many modern web sites is the database. This may seem like a daunting problem because database hardware can be expensive and rearchitecting the application with a different database structure can require a large time commitment. Luckily, most of the data presented on the internet is ideal for caching.
Let us consider our example set of queries from before: user credentials, session information, system notifications, new messages, latest headlines, configuration information, and content areas. Of those, latest headlines, configuration information, system notifications, and content areas are not likely to change very often or vary per user. Digging down further, we’ll consider news headlines specifically and build up a solution to dramatically reduce the database queries to this table.
For a typical website, news headlines will be generated with a query like this:
WHERE category_id=21 AND status=1
ORDER BY publish_date DESC
Each time this query is executed, MySQL will (assuming the query cache is not enabled, which is another issue) first parse the query, decide how to locate the data, read the table index, filter out rows by status or category_id, order the remaing rows, and return the first five. With proper indexes, this query may take a fraction of a second, but enough queries will eventually bog down the database. And 99% of the time, that query will return the same data it returned the last time.
So here, we’ve found a query that runs on nearly every page load, is the same for every user to the site, and returns a relatively small result. Five news headlines, perhaps the links to the full article, the URL for a photo, and a published date can likely fit in under one kilobyte of memory.
Now that we’ve identified a candidate for caching, we simply need a place to cache it. PHP and the Linux/Apache environment provide numerous ways to do this, but we’ll focus on one solution here, MemCached.