TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
These forums are read-only and for archival purposes only!
Please join our new forums at discourse.kohanaframework.org
ORM file-based column cache
  • I thought I'd throw this bit of code out here in case anyone wants it (it may eventually be added to trunk). It adds support to ORM for caching of column information to the file-based Kohana cache. This should provide improvements in speed for production applications as it prevents repeated SQL queries for describing tables. The IN_PRODUCTION define is not specific to KO3 and should be substituted with whatever you use.


    class ORM extends Kohana_ORM
    {
    /**
    * Adds file-based column caching when in production mode
    */
    public function _initialize()
    {
    if (IN_PRODUCTION)
    {
    if ( ! isset(ORM::$_column_cache[$this->_object_name]))
    {
    $cache = Kohana::cache('orm.column_cache.'.$this->_object_name);

    if ($cache !== NULL)
    {
    // Use cached column data
    ORM::$_column_cache[$this->_object_name] = $cache;
    }
    else
    {
    // Initialize the model, which will reload columns
    parent:GDN_initialize();

    // Cache the column data
    Kohana::cache('orm.column_cache.'.$this->_object_name, ORM::$_column_cache[$this->_object_name]);

    return;
    }
    }
    }

    parent:GDN_initialize();
    }
    }
  • Sorry for digging up an old thread, but SHOW FULL COLUMNS is just unnecessary overhead. I have 4 tables which run a show full columns query on each page request. On localhost I wouldn't be bothered, but 4 unnecessary round trips to an IP based DB should be avoided.

    The above solution shows a slight improvement using memcache as the caching mechanism. I haven't tried a file based caching system but would imagine serialization would mean it's also slow.

    The below is the fastest I have come up with without actually hard coding the definitions into the model.

    public function reload_columns($force = FALSE)
    {
        if ($force === TRUE OR empty($this->_table_columns))
        {
            $table_column_cache_name = APPPATH . "cache/model_table_columns_" . $this->_object_name . ".php";
            // Check if we have a cache file
            if(file_exists($table_column_cache_name)) 
            {
                                //Grab our table columns from the include
                $this->_table_columns = include  $table_column_cache_name;
            } 
            else 
            {
                                //Grab column information from database
                $this->_table_columns = $this->list_columns(TRUE);
    
                                //Export our table columns as php source to the cache file
                file_put_contents($table_column_cache_name, "<?php return ". var_export($this->_table_columns, true) . ";");
            }
        }
    
        return $this;
    }
    

    edit: Forgot to say this is 3.0 - I'm not sure if this solution applies to 3.0+

  • Combined with APC that'll be a nice improvement. Should consider clearing APC and warming the cache on deployment then as well, just a simple script that instantiates every model would do I guess.

  • @ocallagh, Have you actually done any benchmarking? I did some testing a while back and loading the column data from cache was actually slower than querying the database. It might make a enough difference if you are using a remote database over a slow link. But most of the time there is no reason to cache this information.

  • @Isaiah, The difference with the approach above is that nothing is cached, no expiry to check, no Cache library to load, and no serialization etc. The table column array is printed as php to the file, so it's just a regular php include. I have done benchmarking. I don't have the figures to hand but I do remember the outcome.

    Querying a DB on localhost is fast and unless you have problems with your DB I wouldn't bother caching the columns in any form.

    When your DB is accessed by IP, then the resulting network delay (and this is even seen even over a 100mb connection) does add up especially if the site is busy. From tests on a fairly congested network from slow to fast:

    - Using Kohana::Cache with file cache was slowest
    - Using ORM out of box was still a lot faster than file cache
    - Using Kohana::Cache with memcache (marginally faster* and probably pointless as it's one more fail point, but definitely preferable if your DB has issues and/or if you rely on memcache elsewhere in your app)
    - Using PHP inclusion as above was the fastest.

    As cs268 said, with APC this would improve again.

    *Other parts of my site already open a connection to memcache server.

    Downside to this approach is you have to remember to delete the appropriate file when you change your table structure!
  • @ocallagh Don't remember, automate ;)

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion