Home arrow MySQL arrow 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.

TABLE OF CONTENTS:
  1. Take Some Load off MySQL with MemCached
  2. A Solution - Caching
  3. Enter MemCached
  4. PHP MemCache PECL Extension
By: Chris Moyer
Rating: starstarstarstarstar / 6
July 29, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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:

SELECT *

FROM news

WHERE category_id=21 AND status=1

ORDER BY publish_date DESC

LIMIT 5;

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.



 
 
>>> More MySQL Articles          >>> More By Chris Moyer
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: