Sunday, June 14, 2015

Caching MySQL with Memcache

Since we've already gone over the idea behind Memcache in a past article, this is going to be focused more on Memcache implementation. Let's begin.

Prerequisites: You'll want to be familiar with setting up VirtualBox with Vagrant and need to spin up a MySQL instance. This guide was written for Mac OS X users.

Preparing Our MySQL Database

Make sure to execute these steps before you go any further. Once you have MySQL running, we'll need to create and populate our database:

# ssh into the vm
cd vagrant-ansible-mysql-python
vagrant ssh

# create a user with privileges to access the db
mysql -uroot

# create and populate db
CREATE DATABASE test;

USE test;
CREATE TABLE `test`.`names` (`id` INT NOT NULL AUTO_INCREMENT, `firstname` VARCHAR(45) NULL, `lastname` VARCHAR(45) NULL, PRIMARY KEY (`id`));

Now that we have MySQL running, we're going to pull down a Python application that will perform reads and writes, which we will monitor and use as our baseline, prior to implementing Memcache.

Our Python Application

Installing Dependencies

# prepare the virtualenv
virtualenv venv
source venv/bin/activate

# install dependencies
pip install flask

Running the App

Run python nameserver.py against this file, and it will become accessible at http://localhost:5000. We'll be sending requests to this app from the command line, next. You should already have a mysql shell open in a terminal window, as well as the python application running in the foreground. Open up one more terminal window and run the following commands:

# request names
curl -i -X GET http://localhost:5000/names

# insert a new name
curl -i -H "Content-Type: application/json" -X POST -d '{"firstname": "FirstName", "lastname":"LastName"}' http://localhost:5000/add

# request names again to see what you just added
curl -i -X GET http://localhost:5000/names

Manually Setting Up Memcache

Vagrant VM

We're going to set up Memcache, manually, so we can learn a bit about how it works, hands on. Spinning the Vagrant VM is done the same way as always. Run vagrant up against the following file, but make sure to change the ip to something different since the MySQL VM is already occupying that. You can use 192.168.33.11, instead.

# ssh into the new vm
vagrant ssh

# install memcached
sudo apt-get update
sudo apt-get install memcached

# make sure it's running
ps aux | grep memcached
memcache  2246  0.0  0.2 325396  1192 ?        Sl   00:14   0:00 /usr/bin/memcached -m 64 -p 11211 -u memcache -l 127.0.0.1
vagrant   2260  0.0  0.1  10464   916 pts/0    S+   00:14   0:00 grep --color=auto memcached

# query the service for stats
echo "stats settings" | nc localhost 11211

And there you go. You now have Memcache running at 192.168.33.11:11211. Now, let's perform some more reads and writes to MySQL.

# request names
curl -i -X GET http://localhost:5000/names

# insert a new name
curl -i -H "Content-Type: application/json" -X POST -d '{"firstname": "FirstName", "lastname":"LastName"}' http://localhost:5000/add

# request names again to see what you just added
curl -i -X GET http://localhost:5000/names

No comments:

Post a Comment