Home arrow MySQL arrow Take Some Load off MySQL with MemCached

Take Some Load off MySQL with MemCached

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 Overworked Database

A web page may generate dozens of queries on each page load – user credentials, session information, system notifications, new messages, latest headlines, configuration information, and content areas may be loaded on every single page and displayed to the user or acted upon by the page logic. As this data is read by the rendering process, other web requests or background processes may be modifying the data in these tables by inserting new headlines, publishing new content areas, and creating sessions for other users. Often, this contention between database reads and writes is where the bottlenecks occur. In order to maintain database integrity, protections are generally in place to prevent the same data from being read as it is written.

In a typical MySQL installation, this problem can become quite apparent because the default table format of MySQL requires that an entire database table be locked while data is being updated in any row. This can create a cascading effect when one process writes to an important table. Perhaps the sessions table is updated whenever a user logs in or logs out.  If so, every page load will then read from the sessions table to determine the authentication status of a given user (based on their cookies). Once you reach a tipping point, the minor pauses caused by the writes to the table can pile up like a traffic jam, slowing the entire site down simply because of writes to a single database table.

In a situation where you notice intermittent slowdowns of your site, you can attempt to troubleshoot the cause from the MySQL command line:

mysql> show processlist;

+----+------+-----------+------+---------+------+--------+--------------

------------------------------+

| Id | User | Host | db | Command | Time | State | Info

|

+----+------+-----------+------+---------+------+--------+--------------

| 1 | root | localhost | test | Query | 3 | Locked | select * from | 2 | root | localhost | test | Query | 6 | Locked | insert into ON

As you can see, you’ll get simple feedback if queries are waiting on a locked table by the State field. The Time field shows how many seconds the query has been running.



 
 
>>> 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: