Cache MySql data using Memcached : A more practical approach to Memcached

Updated 13 April 2017

In my previous blog, i talked about the brief introduction of memcached and how does it help us to make database access faster in most of the web applications. If you have’nt read the blog yet, you can follow the link:- http://cloudkul.com/blog/memcached-a-distributed-memory-object-caching-system

Today, let us focus on some practical aspects of memcached. I will start with the basic memcached installation on Ubuntu 14.04 followed by a practical approach to cache data from MySQL using Memcached and PHP script.

Here we go !!

PREREQUISITES:

Before we get started, you must have a ubuntu machine with the basic LAMP server configured on it.

INSTALL MEMCACHED ON UBUNTU 14.04 :

Run the following commands to install memcached and it’s component :-


Restart Memcached service using the following command :-


CHECK THE INSTALLATION :

In order to verify the installation, create a info.php file in apache’s default document root i.e /var/www/html :-


To call a php function that collects and prints information about our server into web browser, write out the following in this file :-


Hit the URL :-


If you scroll down or search for the “memcached” section header, you should find something that looks like this:

This means that the memcached extension is enabled and being found by the web server.

Also check whether memcached service is running by typing the following command :-


CREATE SAMPLE DATA IN MYSQL :

Login into mysql :-


Create a database, give it a name of your choice and select it for further use.Here, we have named it as ‘memcached_test’.Run the following commands on MySQL prompt :-


Create a user ‘test’ with a password ‘mytesting123’ that has access to the database we created :-


Create a table in database and insert some sample data using the following commands :-


Exit MySQL by simply typing exit command.

CREATE A PHP SCRIPT TO CACHE MYSQL DATA :

Create a php script named ‘database_test.php’ in our document root as :-


Now,we’re going to create a PHP memcached instance and then tell it where the memcached service running on our server is located. Memcached runs on 11211 port by default.Write out the following lines in database_test.php :-


Next, we have to define how PHP can connect to our MySQL database.We need to specify the login credentials for the user we created and then we’ll need to tell it which database to use :-


In order to fetch the data we have inserted into database, we have to create a query and store it in a $query variable.Also,create a $querykey variable to store the key that memcached will use to reference our information.


Next, we’ll create a $result variable that will hold the result from our memcached query.We are now ready to implement the actual testing logic that will determine what will happen when the result is found in memcached. If the results are found, we want to print the data that we pulled out and tell the user that we were able to retrieve it from memcached directly:


If the results are not found, we want to use the query that we crafted to ask MySQL for the data. We will store this into the $result variable we made.After we have the result of the query, we need to add that result to memcached so that the data will be there the next time.

In order to accomplish this, we have to supply the key $querykey variable to reference the data, the data itself as stored in $result variable and the time to cache the data in seconds.


This is our completed script. First,it will attempt to get data from memcached and print it on the screen. If the data could not be found in memcached, it will query from MySQL directly and cache the results for 20 seconds.

TEST THE SCRIPT :

Hit the URL :-


The first time we visit the page, we should see output :-

If we refresh this within 20 seconds,the page should now display the different message :-

After waiting for a while, the cache content will expire and will be removed from memcached again. If we refresh at this point, it will display the first message again since the server must go back to the database to retrieve the data values.

By now, you might have an idea of how memcached works and how you can utilize it to keep your web server from hitting the database repeatedly for the same content.

 

FOR ANY TYPE OF QUERY OR HELP, KINDLY CONTACT US

Category(s) Uncategorized
author
. . .

Leave a Comment

Your email address will not be published. Required fields are marked*


Be the first to comment.

Start a Project




    Message Sent!

    If you have more details or questions, you can reply to the received confirmation email.

    Back to Home