Memcache & MySQL PHP Session Handler

Posted on April 8, 2009. Filed under: Memcache, mysql+, PHP | Tags: , , , , |

LAST UPDATED MAY 17, 2009

I have recently read Cal Henderson’s book, Building Scalable Web Sites, and was inspired 6 ways from Sunday on just about every approach I take to web development. I highly recommend you purchase this book, and read through it asap… it is a must read [even though it was published back in 2006]. My friend Erik Kastner recommended it to me, and now I’m recommending it to you :-)

Anyways, back on topic… One of the major concepts I picked up was that of Write Through Caches. Write Through Caches are explained in depth all throughout the internetS, so I leave you with this: tinyurl.com/cgeobs. This script also solves the scalability issue you will experience when you move your web site / application to more than one web server. If you put a Memcache and MySQL daemon on one box and have all your web servers connect to it, your sessions are in one centralized place [though, this doesn’t account for fail over].

I also built in a simple check to see if the session data had changed before writing it to the DB. Most of the time it doesn’t, so this should offer some more performance.

This was written for PHP5, if you’re still using PHP4, click here.

This script assumes you have already connected to your database.


<?php
    class SessionHandler {
        public $lifeTime;
        public $memcache;
        public $initSessionData;

        function __construct() {
            # Thanks, inf3rno
            register_shutdown_function("session_write_close");

            $this->memcache = new Memcache;
            $this->lifeTime = intval(ini_get("session.gc_maxlifetime"));
            $this->initSessionData = null;
            $this->memcache->connect("127.0.0.1",11211);

            return true;
        }

        function open($savePath,$sessionName) {
            $sessionID = session_id();
            if ($sessionID !== "") {
                $this->initSessionData = $this->read($sessionID);
            }

            return true;
        }

        function close() {
            $this->lifeTime = null;
            $this->memcache = null;
            $this->initSessionData = null;

            return true;
        }

        function read($sessionID) {
            $data = $this->memcache->get($sessionID);
            if ($data === false) {
                # Couldn't find it in MC, ask the DB for it

                $sessionIDEscaped = mysql_real_escape_string($sessionID);
                $r = mysql_query("SELECT `sessionData` FROM `tblsessions` WHERE `sessionID`='$sessionIDEscaped'");
                if (is_resource($r) && (mysql_num_rows($r) !== 0)) {
                    $data = mysql_result($r,0,"sessionData");
                }

                # Refresh MC key: [Thanks Cal :-)]
                $this->memcache->set($sessionID,$data,false,$this->lifeTime);
            }

            # The default miss for MC is (bool) false, so return it
            return $data;
        }

        function write($sessionID,$data) {
            # This is called upon script termination or when session_write_close() is called, which ever is first.
            $result = $this->memcache->set($sessionID,$data,false,$this->lifeTime);

            if ($this->initSessionData !== $data) {
                $sessionID = mysql_real_escape_string($sessionID);
                $sessionExpirationTS = ($this->lifeTime + time());
                $sessionData = mysql_real_escape_string($data);

                $r = mysql_query("REPLACE INTO `tblsessions` (`sessionID`,`sessionExpirationTS`,`sessionData`) VALUES('$sessionID',$sessionExpirationTS,'$sessionData')");
                $result = is_resource($r);
            }

            return $result;
        }

        function destroy($sessionID) {
            # Called when a user logs out...
            $this->memcache->delete($sessionID);
            $sessionID = mysql_real_escape_string($sessionID);
            mysql_query("DELETE FROM `tblsessions` WHERE `sessionID`='$sessionID'");

            return true;
        }

        function gc($maxlifetime) {
            # We need this atomic so it can clear MC keys as well...
            $r = mysql_query("SELECT `sessionID` FROM `tblsessions` WHERE `sessionExpirationTS`<" . (time() - $this->lifeTime));
            if (is_resource($r) && (($rows = mysql_num_rows($r)) !== 0)) {
                for ($i=0;$i<$rows;$i++) {
                    $this->destroy(mysql_result($r,$i,"sessionID"));
                }
            }

            return true;
        }
    }

    ini_set("session.gc_maxlifetime",60 * 30); # 30 minutes
    session_set_cookie_params(0,"/",".myapp.com",false,true);
    session_name("MYAPPSESSION");
    $sessionHandler = new SessionHandler();
    session_set_save_handler(array (&$sessionHandler,"open"),array (&$sessionHandler,"close"),array (&$sessionHandler,"read"),array (&$sessionHandler,"write"),array (&$sessionHandler,"destroy"),array (&$sessionHandler,"gc"));
    session_start();
?>

And here’s the SQL for the DB portion of the Session Handler:


CREATE TABLE `tblsessions` (
    `sessionID` VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    `sessionExpirationTS` INT(10) UNSIGNED NOT NULL,
    `sessionData` TEXT COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`sessionID`),
    KEY `sessionExpirationTS` (`sessionExpirationTS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

As always, I welcome any and all CONSTRUCTIVE criticism. If you have something to add, or a bug fix or any suggestions I fully welcome them here. Trolls need not apply.

Read Full Post | Make a Comment ( 35 so far )

Using Memcache with MySQL and PHP

Posted on May 21, 2008. Filed under: Memcache, mysql+, PHP | Tags: , , , , , |

Memcache is a great caching tool available for nearly every scripting or programming environment. I use it with PHP to speed up some applications I have written by completely avoiding asking the database for information. I wanted a very clean way of implementing this in my various projects, with as little change to the existing code as possible. Since memcache is an OBJECT caching system, you can’t simply drop your mysql_query resource into memcache, ask for it at another time, and get your results back … Which is how I had originally thought it worked the first time I read about it. The good news is that it can also store Strings, Integers, Floats, Arrays etc … Below I have a few other things I’ve learned whilst using memcache as well as the caching script I use for database queries.

What I’ve learned:

  1. memcache is great for storing slow queries that return small data sets [1 – 50 results, depending on the average row weight]
  2. memcache is not so great for any query that returns large data sets [100 – ∞, depending on the average row weight]

… in fact, I’ve found that memcache can occasionally be slower than running high-yield queries again [that is, if you have your MySQL server caching queries as well]. It all really boils down to benchmarking the scripts yourself: test every situation with and without the cache! [the more realistic the DB load, the better]

Caching is faster? Yay! Cache it!
DB query is faster? Yay! DON’T cache it!

So, basically, this isn’t a plug-and-play solution for ALL slow page loads / queries, just some of them.

Here’s the code I use to cache MySQL queries:


<?php
    # Connect to memcache:
    global $memcache;
    $memcache = new Memcache;

    # Gets key / value pair into memcache ... called by mysql_query_cache()
    function getCache($key) {
        global $memcache;
        return ($memcache) ? $memcache->get($key) : false;
    }

    # Puts key / value pair into memcache ... called by mysql_query_cache()
    function setCache($key,$object,$timeout = 60) {
        global $memcache;
        return ($memcache) ? $memcache->set($key,$object,MEMCACHE_COMPRESSED,$timeout) : false;
    }

    # Caching version of mysql_query()
    function mysql_query_cache($sql,$linkIdentifier = false,$timeout = 60) {
        if (($cache = getCache(md5("mysql_query" . $sql))) !== false) {
            $cache = false;
            $r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
            if (is_resource($r) && (($rows = mysql_num_rows($r)) !== 0)) {
                for ($i=0;$i<$rows;$i++) {
                    $fields = mysql_num_fields($r);
                    $row = mysql_fetch_array($r);
                    for ($j=0;$j<$fields;$j++) {
                        if ($i === 0) {
                            $columns[$j] = mysql_field_name($r,$j);
                        }
                        $cache[$i][$columns[$j]] = $row[$j];
                    }
                }
                if (!setCache(md5("mysql_query" . $sql),$cache,$timeout)) {
                    # If we get here, there isn't a memcache daemon running or responding
                }
            }
        }
        return $cache;
    }
?>

The function mysql_query_cache() will return an array filled with the results. Since I don’t use this for large result sets, I don’t free the MySQL resource … you may want to free the resource after it’s been used if you get larger data sets.

Like I had mentioned before, I wanted the code to be as easy-to-use as possible when using it. So, I’ve set up a before and after test scenario showing how to retrofit your code with the new caching code:


<?php
    $sql = "
        SELECT `dataID`, `dataTitle`
        FROM `tbldata`
        WHERE `dataTypeID` BETWEEN 2 AND 2093
        AND `dataStatusID` IN (1,2,3,4)
        AND `dataTitle` LIKE '%something%'
        ORDER BY `dataDate` DESC
        LIMIT 10
    ";

    # Before: [without memcache]
    $rSlowQuery = mysql_query($sql);
    # $rSlowQuery is a MySQL resource
    $rows = mysql_num_rows($rSlowQuery);
    for ($i=0;$i<$rows;$i++) {
        $dataID = intval(mysql_result($rSlowQuery,$i,"dataID"));
        $dataTitle = mysql_result($rSlowQuery,$i,"dataTitle");

        echo "<a href=\"/somewhere/$dataID\">$dataTitle</a><br />\n";
    }


    # After: [with memcache]
    $rSlowQuery = mysql_query_cache($sql);
    # $rSlowQuery is an array
    $rows = count($rSlowQuery);
    for ($i=0;$i<$rows;$i++) {
        $dataID = intval($rSlowQuery[$i]["dataID"]);
        $dataTitle = $rSlowQuery[$i]["dataTitle"];

        echo "<a href=\"/somewhere/$dataID\">$dataTitle</a><br />\n";
    }

?>

Easy, huh? Run print_r() on the returned array to get an idea of how the array is structured if need be.

As always, if you have a better, more efficient, objective, more adaptable solution than mine, please leave a comment! I am 100% open to constructive criticism :-)

Read Full Post | Make a Comment ( 63 so far )

Make your website completely UTF-8 friendly

Posted on March 23, 2008. Filed under: mysql+, PHP, UTF-8 | Tags: , , , , |

LAST UPDATED JUNE 15, 2009

Running an Internationalization / Localization [or i18n / L10n] friendly website can be tricky, and sometimes downright maddening for those who haven’t yet delved into the world of Unicode. Allowing your users to post in whichever language and / or characters of their choice to your site is crucial for any modern website.

Here are a few things I have very painfully learned over the last 5 or so years on this topic … specifically with PHP and MySQL.

There are hundreds of character sets representing most of the languages on Earth, usually one per geographic location [Latin, Cyrillic, Greek, Arabic, Korean, Chinese etc…]. One character set that covers all of these is UTF-8. So how can you put ‘UTF-8‘ to practical use? Easy … here’s how I’ve done it:

 

Headers! Get your headers!
The most important area to implement UTF-8 is in your charset header within your outgoing HTML headers. This tells the browser that you have multi-byte characters in your HTML and you’d like it do display them as such [and not as the default ISO-8859-1].
To do this, put this at the very top of your PHP scripts [with the headers and before any HTML is echoed]:


<?php
    header("Content-Type: text/html; charset=utf-8");
?>

And this in your HTML <head> section:


<?php
    echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />\n";
?>

 

MySQL / UTF-8 love
The second most important thing is to make sure your database is also UTF-8 friendly. Be sure to set all your table / column collations [char / text] to utf8_unicode_ci. This tells MySQL to treat this data as UTF-8.

Once you’ve done that, you’ll need to tell PHP to connect to the MySQL daemon under a UTF-8 connection [otherwise the default is latin1 … and your data will be stored in MySQL as such — no good!]. Run this right after you connect to MySQL:


<?php
    mysql_query("SET NAMES 'utf8'");
    mysql_query("SET CHARACTER SET utf8");
?>

 

Multibyte fun
Last, take advantage of PHP’s Multibyte String Functions! Oftentimes this is as easy as prefixing your string comparison functions with mb_. But, before you start using these functions you’ll need to tell PHP which character set to use [once again!] because the default is ISO-8859-1:


<?php
    mb_internal_encoding("UTF-8");
?>

 

Forms
One often neglected method is ensuring that the data the server gets is UTF-8 encoded. One way to try and do this with HTML forms is to include the accept-charset attribute in your form tag. I say “try” because it’s just a suggestion to the client which submits the form. Be aware that some clients may not pay much attention to the attribute, especially older browsers. [Thanks to Alejandro for the heads up :-)]


<form action="/action" method="post" accept-charset="utf-8">

Also see here: www.w3schools.com/TAGS/att_form_accept_charset.asp.

If you’ve gotten this far you should see some dramatic improvements to your web site’s accessibility and usability, drawing in users from around the world.

NOTE: This is a work in progress and I fully welcome any new ideas to this cocktail of methods. If you have anything to add, PLEASE DO SO!

Read Full Post | Make a Comment ( 14 so far )

Never use ORDER BY RAND() again!

Posted on March 5, 2008. Filed under: mysql+, PHP | Tags: , , , , , |

I have been guilty of using ORDER BY RAND() in MySQL queries to return random records from time to time, but everyone knows to avoid it like the plague. For those who agree with me, aside from using it on very small tables [1 – 500 records], I have an elegant workaround using PHP.


<?php
    $condition = true;
    while ($condition) {
        $randID = rand(1,$totalRecordsInTblExample);
        $r = mysql_query("SELECT * FROM `tblexample` WHERE `exampleID`=$randID LIMIT 1");
        if (mysql_num_rows($r) == 1) {
            $condition = false;
        }
    }
    $exampleTitle = mysql_result($r,0,"exampleTitle");
?>

The very first thing we need to know is how many records there are within the table in question, if the table is MyISAM, you’re in luck. Getting the total number of records is quick and inexpensive since MyISAM keeps an index of how many records a given table contains:


<?php
    $r = mysql_query("SELECT COUNT(*) AS `count` FROM `tblexample`");
    $numRecords = mysql_result($r,0,"count");
?>

However, don’t attempt this on an InnoDB table! InnoDB would need to count each and every row every time, which would be expensive on large tables. I keep a reference of record counts in a MyISAM table [for stats purposes], so I query that table to get the record count instead.

Once we have determined how many records are in the table, we can use PHP to [duh] select a random number between 1 and the total number of records.

Once we have that random number, query the DB to see if the record exists, and if not try again. Since the column in the where clause is the Primary Key [I assume], the lookups will be very fast and cheap.

Read Full Post | Make a Comment ( 19 so far )

Liked it here?
Why not try sites on the blogroll...