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