Building a Web Application with PHP and MariaDB – Introduction to caching

Exclusive offer: get 50% off this eBook here
Building a Web Application with PHP and MariaDB: A Reference Guide

Building a Web Application with PHP and MariaDB: A Reference Guide — Save 50%

Build fast, secure, and interactive web applications using this comprehensive guide with this book and ebook

$21.99    $11.00
by Sai Srinivas Sriparasa | June 2014 | Open Source Web Development

In this article by Sai Srinivas Sriparasa, author of the book Building a Web Application with PHP and MariaDB: A Reference Guide, we'll learn how caching can be defined as the process of making popular data highly available by temporarily storing the data in memory that allows responding to a request faster by retrieving data from the memory rather than going onto the disk. There are three types of caching, as follows:

  • Caching in the database
  • Caching in the application
  • Content caching

(For more resources related to this topic, see here.)

Let's begin with database caching.

All the data for our application is stored on MariaDB. When a request is made for retrieving the list of available students, we run a query on our course_registry database. Running a single query at a time is simple but as the application gets popular, we will have more concurrent users. As the number of concurrent connections to the database increases, we will have to make sure that our database server is optimized to handle that load. In this section, we will look at the different types of caching that can be performed in the database. Let's start with query caching. Query caching is available by default on MariaDB; to verify if the installation has a query cache, we will use the have_query_cache global variable.

Let's use the SHOW VARIABLES command to verify if the query cache is available on our MariaDB installation, as shown in the following screenshot:

Now that we have a query cache, let's verify if it is active. To do this, we will use the query_cache_type global variable, shown as follows:

From this query, we can verify that the query cache is turned on. Now, let's take a look at the memory that is allocated for the query cache by using the query_cache_size command, shown as follows:

The query cache size is currently set to 64 MB; let's modify our query cache size to 128 MB.

The following screenshot shows the usage of the SET GLOBAL syntax:

We use the SET GLOBAL syntax to set the value for the query_cache_size command, and we verify this by reloading the value of the query_cache_size command. Now that we have the query cache turned on and working, let's look at a few statistics that would give us an idea of how often the queries are being cached.

To retrieve this information, we will query the Qcache variable, as shown in the following screenshot:

From this output, we can verify whether we are retrieving a lot of statistics about the query cache. One thing to verify is the Qcache_not_cached variable that is high for our database. This is due to the use of prepared statements. The prepared statements are not cached by MariaDB. Another important variable to keep an eye on is the Qcache_lowmem_prunes variable that will give us an idea of the number of queries that were deleted due to low memory. This will indicate that the query cache size has to be increased.

From these stats, we understand that for as long as we use the prepared statements, our queries will not be cached on the database server. So, we should use a combination of prepared statements and raw SQL statements, depending on our use cases. Now that we understand a good bit about query caches, let's look at the other caches that MariaDB provides, such as the table open cache, the join cache, and the memory storage cache. The table open cache allows us to define the number of tables that can be left open by the server to allow faster look-ups. This will be very helpful where there is a huge number of requests for a table, and so the table need not be opened for every request. The join buffer cache is commonly used for queries that perform a full join, wherein there are no indexes to be used for finding rows for the next table. Normally, indexes help us avoid these problems. The memory storage cache, previously known as the heap cache, is commonly is used for read-only caches of data from other tables or for temporary work areas.

Let's look at the variables that are with MariaDB, as shown in the following screenshot:

Database caching is a very important step towards making our application scalable. However, it is important to understand when to cache, the correct caching techniques, and the size for each cache. Allocation of memory for caching has to be done very carefully as the application can run out of memory if too much space is allocated. A good method to allocate memory for caching is by running benchmarks to see how the queries perform, and have a list of popular queries that will run often so that we can begin by caching and optimizing the database for those queries. Now that we have a good understanding of database caching, let's proceed to application-level caching.

Summary

In this article we learned how caching in the database is implemented.

Resources for Article:


Further resources on this subject:


Building a Web Application with PHP and MariaDB: A Reference Guide Build fast, secure, and interactive web applications using this comprehensive guide with this book and ebook
Published: June 2014
eBook Price: $21.99
Book Price: $35.99
See more
Select your format and quantity:

About the Author :


Sai Srinivas Sriparasa

Sai Srinivas Sriparasa is a web developer and an open source evangelist living in the Stamford area. Sai was the lead developer for building Dr. Oz’s website, and has led teams for companies such as Sprint Nextel, West Interactive, and Apple. Sai’s repertoire includes JavaScript, PHP, Python, HTML5, responsive web development, ASP.NET, C#, and Silverlight.

Books From Packt


ASP.NET Web API: Build RESTful web applications and services on the .NET framework
ASP.NET Web API: Build RESTful web applications and services on the .NET framework

	 RESTful PHP Web Services
RESTful PHP Web Services

Mastering phpMyAdmin 2.11 for Effective MySQL Management
Mastering phpMyAdmin 2.11 for Effective MySQL Management

 Apache Solr PHP Integration
Apache Solr PHP Integration

MariaDB Cookbook
MariaDB Cookbook

 Getting Started with MariaDB
Getting Started with MariaDB

CakePHP 1.3 Application Development Cookbook
CakePHP 1.3 Application Development Cookbook

 Mastering MariaDB
Mastering MariaDB


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software