SQLite3 architecture

Use SQLite3 with Magento

  • Sharebar

 

Why would you want to use SQLite3 with Magento?

Just for fun… well actually there is a good reason. The reason is that Magento spends a lot of time in the autoload finding files. Do this in your terminal:

strace -c php index.php

That should have given you a lot of information, here is an excerpt of mine:

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 50.27    0.011016           2      4657           gettimeofday
  9.86    0.002160           3       659        13 stat
  7.09    0.001554           4       393       224 lstat
  6.14    0.001346           3       503           fstat
  5.00    0.001095         156         7         1 futex
  4.91    0.001075           3       344           mmap
  4.23    0.000927           4       248        23 openutex

Magento tried to address this by using the magento compiler (read moving all the classes to one common directory). So let’s call this compiler v2.0, although it is not even close.

I wanted to measure how fast it would be to use a SQLite3 database against a regular file include. The results weren’t that impressive using a regular flat-file database but when I used SQLite3 in memory the results were better. No ground breaking, just better and by better I  mean just enough to justify the time spent doing the research.

Installing SQLite3 and the PHP library in Ubuntu is very simple:

sudo apt-get install sqlite3 php5-sqlite

Copy the following code:

<?php
class Varien_Autoload {
    //database handler
    static protected $_dbHandler = null;
    static protected $_statement = 'SELECT filename from classes where classname like
        "%s" and file_exists = 1;';
    /**
     * Singleton pattern implementation
     *
     * @return Varien_Autoload
     */
    static public function instance() {
        if (!self::$_instance) {
            self::$_instance = new Varien_Autoload();
        }
        if (!self::$_dbHandler) {
            $db = MAGENTO_ROOT . DS . 'lib' . DS . 'Varien' . DS . 'classes.db';
            $db = file_exists($db) ? $db : 'memory:';
            self::$_dbHandler = new PDO("sqlite:$db");
            self::$_dbHandler--->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            // Create table classes
            self::$_dbHandler-&gt;exec("CREATE TABLE IF NOT EXISTS classes(
                classid INTEGER PRIMARY KEY AUTOINCREMENT,
                classname TEXT UNIQUE NOT NULL,
                filename TEXT UNIQUE NOT NULL,
                file_exists INTEGER DEFAULT 1,
                last_check date);");
        }
        return self::$_instance;
    }
    /**
     * Load class source code
     *
     * @param string $class
     */
    public function autoload($class) {
        $stmt = sprintf(self::$_statement, $class);
        $result = self::$_dbHandler-&gt;query($stmt);
        if (!$result) {
            $filename = $this-&gt;_autoload($class);
        } else {
            $result = $result-&gt;fetch();
            if (isset($result['filename'])) {
                $filename = $result['filename'];
            } else {
                $filename = $this-&gt;_autoload($class);
                $insert = "INSERT INTO classes VALUES (null,'$class', '$filename',1,0);";
                $result = self::$_dbHandler-&gt;exec($insert);
                if (!$result) {
                    //do something here
                }
            }
        }
        return include $filename;
    }
    protected function _autoload($class) {
        if ($this-&gt;_collectClasses) {
            $this-&gt;_arrLoadedClasses[self::$_scope][] = $class;
        }
        if ($this-&gt;_isIncludePathDefined) {
            $classFile = $class;
        } else {
            $classFile = str_replace(' ', DIRECTORY_SEPARATOR, ucwords(str_replace('_', ' ', $class)));
        }
        $classFile.= '.php';
        //echo $classFile;die();
        return $classFile;
    }
}

For the purpose of the blog that class only contains the variables and methods I changed or added – make sure to add everything from the regular Autoload class.

Save it in app/code/local/Varien as Autoload.php

During my testing I had my classes.db under lib/Varien/ so you may want to change that as well.

Something very important is that in order for the insert to work you need to have the parent directory chmod’ed to 777 otherwise it won’t work. At the very least have the web server needs to be able to write to that directory.

You can then siege the fort with:

siege -t1m -c20 -d -b -i magento.dev

You should compare sieging magento’s default autoload vs SQLite file and memory based.

Oh almost forgot:

If creating the indexes for the filename and classname table don’t work you can create them directly with the sqlite3 utility. Open a terminal and type

Let’s add some indexes manually to the table before sieging it. Open a terminal and type:

sqlite3 classes.db
create index idx_classname on classes (classname);
create index idx_filename on classes (filename);

Verify that your indexes are created:

.indices

My System specs:

  • Ubuntu Server 12.04
  • 2GB of Ram
  • 2 CPU @ 1.3 GHZ
  • Nginx

Here is my output from:

strace -c php index.php
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 41.47    0.011133           2      4662           gettimeofday
  8.31    0.002230           3       662        13 stat
  6.98    0.001874           5       353           fcntl
  6.83    0.001833           2       767           fstat
  5.07    0.001362           6       230           read
  4.48    0.001204           5       259       245 access
  4.24    0.001138           3       394       224 lstat
  3.47    0.000932           4       253        23 open
  3.36    0.000901         129         7         1 futex
  3.09    0.000829           2       352           mmap
  2.92    0.000785           3       264           close
  1.93    0.000517           3       168           mprotect
  1.85    0.000498          83         6         5 connect
  1.64    0.000439          17        26           openat
  1.10    0.000296           6        52           getdents
  1.09    0.000292           1       214           munmap
  0.94    0.000252           5        52        23 recvfrom
  0.68    0.000183           7        27        16 ioctl

You can see the time is spent is more spread out now. Still no as good as I had hoped but better than the initial testing.

My siege results are below:
siege -t1m -c20 -d -b -i magento.dev

SQLite3 file based:

Transactions:                    54 hits
Availability:                100.00 %
Elapsed time:                 59.12 secs
Data transferred:              0.23 MB
Response time:                19.48 secs
Transaction rate:              0.91 trans/sec
Throughput:                    0.00 MB/sec
Concurrency:                  17.79
Successful transactions:         54
Failed transactions:              0
Longest transaction:          22.61
Shortest transaction:         17.61

Magento:

Transactions:                    60 hits
Availability:                100.00 %
Elapsed time:                 60.00 secs
Data transferred:              0.26 MB
Response time:                18.05 secs
Transaction rate:              1.00 trans/sec
Throughput:                    0.00 MB/sec
Concurrency:                  18.05
Successful transactions:         60
Failed transactions:              0
Longest transaction:          21.87
Shortest transaction:         14.83

SQLite3 memory based:

Transactions:                      67 hits
Availability:                  100.00 %
Elapsed time:                   60.00 secs
Data transferred:                1.26 MB
Response time:                  11.05 secs
Transaction rate:                1.00 trans/sec
Throughput:                      0.00 MB/sec
Concurrency:                    19.95
Successful transactions:           67
Failed transactions:                0
Longest transaction:            12.87
Shortest transaction:           8.83

You see no that impressive. But at the very least now I know it doesn’t work as well as I thought it would. Next I am thinking to implement the same approache directly in redis or memcache and compare the results.

On a subsequent article I will show Here’s you can verify that a file still exists using the system resources with a cron job and vala to continue our pursuit of a system completely optimized

Now based on these results I am not quite convinced that to use sqlite3 with Magento is a great idea. Of course more testing on a better computer and more ram is to come. I will try to know the files required per page and load them all at once and see how that works, of course the main problem with this approach is future classes won’t be included as easily unless you “recompile” the data.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
 Use SQLite3 with Magento

About Luis Tineo

Husband, Father, performance improvement junkie, biker and video gamer, Linux user and in my day job I'm a Systems Architect at Blue Acorn.

Tags: , , ,

  • Pingback: Update your SQLite3 database with Vala

  • fbrnc

    Hi!

    nice blog post and great idea! I picked up your idea and created a file-based cache: http://www.fabrizio-branca.de/magento-class-path-cache.html

    Bye, and have a nice day,…

    Fabrizio

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • Luis Tineo

      Hey Fabrizio,

      Good to see you again – thanks for stopping by.

      I like your solution as well. The only reason I am using SQLite3 is because I can decouple php and the autoload and use Vala to update the database consistently.

      Thanks again and have a great day as well

      Luis

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VA:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  • parkbenchbruce

    Hi,
    Great article. I was wanting to use Magento with a sqlite database. My first approach was to convert it then I was looking at if Magento2 will be switching to sqlite for speed improvement. Did you wind up hosting this on any web server or was it only on a home server?
    Thanks

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • Luis Tineo

      Hey Bruce,

      Thanks for stopping by – I tried it in a virtual machine with the same specs magento recommends for hosting but didn’t use it production yet, I am a bit concerned how SQLite may behave with real concurrent users going everywhere but since it is just “reading” it should be fine. Fabrizio made a great extension in PHP that can be plugged in and works really good.

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VA:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
      • parkbenchbruce

        Luis,

        Thanks for the tips. I like the blob in sqlite which stores images so I think that it will creep into more common use if there is a speed case. I’ll check out Fabrizio, I liked your production type approach – it is refreshing.

        Bruce

        VA:F [1.9.22_1171]
        Rating: 0.0/5 (0 votes cast)
        VA:F [1.9.22_1171]
        Rating: 0 (from 0 votes)