Blog Kurniawan I

Memcached And PHP, Caching Mysql Query Result

2012-01-03T22:35:00.000-08:00

I’ve been messing around with memcached and php-pecl-memcache to cache sql query result. Many web sites & applications such as Facebook, LiveJournal, Flickr, Slashdot, WikiPedia/MediaWiki, SourceForge, Digg and Twitter use memcached to enhance their performance.

Memcached (Memory Cache Daemon) was developed by the team at LiveJournal to improve performance of their social blogging site by minimizing the impact of the bottleneck caused by reading data directly from the database. Memcached is a server that caches Name Value Pairs in memory. The “Name”, or key, is limited to 250 characters, and the “Value” is limited to 1MB in size. Values can consist of data, HTML Fragments, or binary objects; almost any type of data that can be serialized and fits in memcached can be stored.

here is simple example/demonstration how to cache regular sql query

memcached flow

First of all, we need memcached daemon run on system

$ ps ax | grep memcached
 8955 ?        Ssl    0:00 memcached -d -p 11211 -u memcached -m 256 -c 1024 -P /var/run/memcached/memcached.pid -l 127.0.0.1

Setup simple mysql database/tables as shown bellow:

mysql-shell> CREATE DATABASE memcache;

Copy/Paste this tables schema to your mysql shell/console

CREATE TABLE memc(
 personID int NOT NULL AUTO_INCREMENT,
 PRIMARY KEY(personID),
 FirstName varchar(15),
 LastName varchar(15),
 Age int
);
hit enter/return key

Insert some data

mysql-shell> INSERT INTO memc (FirstName, LastName, Age) VALUES('Memory', 'Cache', '100');

In http document root put these php codes (you can create subdirectory)
db.php

 
<?php
 $dbhost = '127.0.0.1';
 $dbuser = 'user';
 $dbpass = 'password';
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
 
 $dbname = 'memcache';
 mysql_select_db($dbname);
?>

mc.php

 
<?php
$memcache = new Memcache;
$memcache->connect('127.0.0.1', 11211) or die ("Could not connect");
 
include('./db.php');
 
$key = md5("SELECT * FROM memc where FirstName='Memory'");
$get_result = array();
$get_result = $memcache->get($key);
 
if ($get_result) {
        echo "<pre>\n";
        echo "FirstName: " . $get_result['FirstName'] . "\n";
        echo "LastName: " . $get_result['LastName'] . "\n";
        echo "Age: " . $get_result['Age'] . "\n";
        echo "Retrieved From Cache\n";
        echo "</pre>\n";
} else {
        // Run the query and get the data from the database then cache it
        $query="SELECT * FROM memc where FirstName='Memory';";
        $result = mysql_query($query);
 
        $row = mysql_fetch_array($result);
        echo "<pre>\n";
        echo "FirstName: " . $row[1] . "\n";
        echo "LastName: " . $row[2] . "\n";
        echo "Age: " . $row[3] . "\n";
        echo "Retrieved from the Database\n";
        echo "</pre>\n";
        $memcache->set($key, $row, MEMCACHE_COMPRESSED, 20); // Store the result of the query for 20 seconds
 
        mysql_free_result($result);
}
?>
 

When all done, point your browser to http://www.example.com/mc.php

First time access, query will be access directly from database and displayed to the browser.

FirstName: MemoryLastName: Cache
Age: 100
Retrieved from the Database

Reload the browser, now query will be pulled from memcached and displayed to the browser.

FirstName: MemoryLastName: Cache
Age: 100
Retrieved From Cache

Timing test result

# time lynx -dump http://www.example.com/test/mc.php
FirstName: Memory
LastName: Cache
Age: 100
Retrieved from the Database
 
real    0m0.286s
user    0m0.018s
sys     0m0.022s

Second hit was from cache

# time lynx -dump http://www.example.com/test/mc.php
FirstName: Memory
LastName: Cache
Age: 100
Retrieved From Cache
 
real    0m0.050s
user    0m0.013s
sys     0m0.014s

Sumber: http://www.kutukupret.com/2011/05/04/memcached-and-php-caching-mysql-query-result/

How to install Memcached on Windows machine

2012-01-03T19:47:00.000-08:00

After some research I found that there is a Win32 binary version of memcached available here.

So go there and download win32 binary version to your computer. When downloaded, extract the file to folder memcahced on your C partition.

You should get only one file named memcached.exe. Now we need to install this as a service, as memCached is daemon and should be run only as service.

To install Memcached as a service, follow these steps (please change all forward slashes to backslashes):

If you’re running Vista, you should set memcached.exe to Run as administrator
Install the service using the command:
1 c:/memcached/memcached.exe -d install
from the command prompt
Start the server from the Services or by running one of the following commands:
1 c:/memcached/memcached.exe -d start
or
1 net start "memcached Server"

That is it, now your memcached server is installed and is listening to port 11211. There is one more thing left to do. By default, server is set to 64Mb memory limit, which is too small. To correct, go to Registry Editor and find key

`1`	`HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/memcached Server .`

Change the ImagePath entry to:

`1`	`“C:/memcached/memcached.exe” -d runservice -m 512`

This way you will use 512 Mb memory limit for your server.

If you are using WAMP, then just turn on php_memcache extension in it. Now we are ready to go testing.

The simplest test is to write something to memcache that will expire in 30 seconds, and then try to retrieve the value from cache.

So write this into your test file:

01<?php
02    $memcache = new Memcache; // instantiating memcache extension class
03    $memcache->connect("localhost",11211); // try 127.0.0.1 instead of localhost
04                                           // if it is not working 
05
06    echo "Server's version: " . $memcache->getVersion() . "<br />\n";
07
08    // we will create an array which will be stored in cache serialized
09    $testArray = array('horse', 'cow', 'pig');
10    $tmp       = serialize($testArray);
11
12    $memcache->add("key", $tmp, 30);
13
14    echo "Data from the cache:<br />\n";
15    print_r(unserialize($memcache->get("key")));
16?>

If everything went ok, you should see your array written out of memcache. Now you can use this powerful caching mechanism to develop really fast PHP applications. If you need to know more, go to PHP manual Memcached page

Sumber: http://www.codeforest.net/how-to-install-memcached-on-windows-machine

Installing Memcached on Xampp and Windows 7

2012-01-03T18:51:00.000-08:00

Installing Memcached on Xampp and Windows 7

1a. Go to your php.ini file usually located in C:/xampp/php/php.ini
find this line:

;extension=php_memcache.dll

and replace it with:

extension=php_memcache.dll

1b. If you cannot find this line simply add the following line to below where all the ;extension= lines.

extension=php_memcache.dll

2. Add the following to just below the new line

[Memcache]
memcache.allow_failover = 1
memcache.max_failover_attempts=20
memcache.chunk_size =8192
memcache.default_port = 11211

3. Download the necessary php_memecache.dll file from the following location.

http://downloads.php.net/pierre/

For windows 7 I used the following file:

http://downloads.php.net/pierre/php_memcache-cvs-20090703-5.3-VC6-x86.zip

4. Unzip the php_memcache.dll file and put it into your php ext folder. Usually C:/xampp/php/ext/

5. Download memcached for windows here (make sure it’s the win32 binary):

http://code.jellycan.com/memcached/

6. Unzip and put the memcache.exe file into a desired directory (e.g. c:/memcached/)

7. Open command line in Windows Administrator Mode.

Click start, type in ‘Search programs and Files’ box, wait for the program cmd.exe to appear, right hand click on the icon and select run as administrator

8. Install the memcache service

Type the following into the command line

c:\memcached\memcached.exe -d install

If you dont get any errors it means it’s worked.

9. Start memcached

Type the following into the command line

c:\memcached\memcached.exe -d start, or net start “memcached Server”

10. Restart Xampp Apache

11. Test Memcache

Create a php file and paste the following code. Then go to the page. If you do not see any errors then it has worked.

<?php
$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");
$version = $memcache->getVersion();
echo "Server's version: ".$version."<br/>\n";
$tmp_object = new stdClass;
$tmp_object->str_attr = 'test';
$tmp_object->int_attr = 123;
$memcache->set('key', $tmp_object, false, 10) or
die ("Failed to save data at the server");
echo "Store data in the cache (data will expire in 10 seconds)
<br/>\n";
$get_result = $memcache->get('key');
echo "Data from the cache:<br/>\n";
var_dump($get_result);
?>

12. Just in case it doesn’t work: One other thing I did was to run the C:/memcached/memcached.exe file as administrator. This opens the ports on the windows firewall which might solve some problems.

Sumber: http://www.leonardaustin.com/technical/how-to-install-memcached-on-xampp-on-windows-7