I’ve just wrapped another project using the Yii framework (my fourth in the past year) and so I’ve got a few more topics to write about. I really like the Yii framework (obviously) and think it’s very accessible, but there are still things that take me some time to figure out, so those topics make for good blog posts! In this one, I’ll walk through what I had to do to cache the database schema using memcached. For that matter, I’ll explain why you’d want to do this in the first place…The Yii framework bases its Models on a couple of different base classes. The most common for Models derived from database tables is ActiveRecord. ActiveRecord is a lovely design (it’s also used by Ruby on Rails) but isn’t so kind when it comes to performance: you see, in order for the framework to do anything with a particular Model, it must first run two queries:
- SHOW COLUMNS FROM tablename
- SHOW CREATE TABLE tablename
Again, to be clear, any use of, say, an Employees table requires these two commands be run first. If you have the related Employee and Department Models, a join across them requires four queries before any data is retrieved at all! And this is the downside of ActiveRecord: it does a lot of the work for you, but at a cost of performance. The hardest performance hit a Web site has comes from database queries, so minimizing or optimizing those is crucial. You can improve your Yii application’s performance by limiting how often these queries are run. To do so, you’ll need to cache the database schema.
Caching, to be clear, is the process of saving something for later so that it doesn’t need to be regenerated. For example, a dynamic PHP page can be cached as HTML so that the server can just send the static HTML instead of re-executing the PHP (and, most likely, the underlying database queries). Yii supports caching in many ways, from caching specific bits of data to parts of a page to entire pages. One example of data you can cache is the database schema: how the database is defined. By caching that information, Yii won’t have to retrieve it by running two SHOW commands.
Before telling you how to do that, let’s tell Yii to reveal its inner workings to you. First of all, run your application in debugging mode by having these two lines in your index.php bootstrap file:
defined('YII_DEBUG') or define('YII_DEBUG',true);
defined('YII_TRACE_LEVEL') or define('YII_TRACE_LEVEL',3);
Those are written into the file by default but should be commented out or deleted entirely on a live server.
Then, in your protected/config/main.php configuration file, enable the WebLog:
'log'=>array(
'class'=>'CLogRouter',
'routes'=>array(
array(
'class'=>'CFileLogRoute',
'levels'=>'error, warning',
),
array(
'class'=>'CWebLogRoute',
),
),
),
Now when you access any page in the Web browser, it’ll report a slew of information at the bottom of the screen. This is a log of whatever Yii is doing, from the loading of modules to the execution of database queries. Normally I’d show you an image of this here, but it’d be too revealing for my live sites! In this log, you’ll see the SHOW queries I already mentioned. To stop those from happening (most of the time), you’ll need to first tell Yii to cache the database schema. This is also done in the main.php configuration file, in the section for configuring the “db” component:
'db'=>array(
'connectionString' => 'mysql:dbname=test',
'schemaCachingDuration' => 3600,
'emulatePrepare' => true,
'username' => 'user',
'password' => 'pass',
'charset' => 'utf8',
),
The schemaCachingDuration line needs to be added there. Assign to it a logical value, such as an hour (the value is in seconds, so 60 seconds times 60 minutes equals 3600). That line will enable caching of the database schema but only if Yii has a caching component registered! This is very important: if you follow these steps and don’t see the SHOW commands disappear from the Web trace log, the caching isn’t working.
To enable a cache, register a “cache” component (like “db”, this is also in the “components” section of the main.php configuration file):
'cache' => array (
'class' => 'CMemCache'
),
The class value indicates which kind of cache you want to use. Options include CMemCache (memcached), CApcCache (APC), and CDbCache (which uses SQLite). A small book could be written about each cache type and how to get the most of them. For me, I went with memcached, which is a tool for storing data in memory. So I added this to my configuration file:
'cache' => array (
'class' => 'CMemCache',
'servers'=>array(
array(
'host'=>'localhost',
'port'=>11211,
),
),
),
Like connecting to a database, I specify a host and a port. To connect to memcached on the same computer, I use “localhost” and 11211 as the port (this is the standard). Once I’ve done this, if I re-run my Yii application, the SHOW queries will disappear (on the second request, because the first has to retrieve the data so that it can be cached).
So if you follow these steps, you can have Yii show you what it’s doing, then make some of that activity go away, but what does this mean in real-world performance? To test that, I turned to ApacheBench, a command-line benchmarking tool. Assuming you have ApacheBench installed on your computer, you can use it with the command (note that you’re testing some other server from your computer):
ab -n # -c # http://www.example.com
The -n flag indicates the number of requests to make. The -c flag indicates the number of concurrent requests to make. This is great because no matter how fast you are with your browser, you can’t single handedly make multiple simultaneous requests of a server. You can also indicate a timelimit, send POST data along with each request, and so forth. As a basic test, I can see the results of my site for 100 requests in groups of 5:
ab -n 100 -c 5 http://www.example.com
You’ll get a bunch of information back, after a few seconds or minutes. You’ll see the number of complete and failed requests, which is nice, but the requests per second (RPS) is the most common benchmark for a site’s performance. In other words, this server can handle X number of requests for this URL per second. The more RPS, the better: it’s that simple.
There are a couple last tips to add, as these things can be confusing. First, memcached is the caching software that needs to be installed and running on the server. Second, PHP must be configured with support for memcache (no “d”), which is PHP’s extension for working with memcached. And, of course, memcached must be running. When I first tried to enable this (using Mac OS X Server), I wasn’t seeing any caching taking place, nor any error messages. So my next step was to use an example from the PHP manual to test PHP-memcache-memcached outside of Yii. That example indicated that PHP wasn’t able to connect to memcached. It turns out the reason was that the server didn’t have memcached installed! Once I installed and started memcached, I got the results I wanted.
Hopefully this will help you improve the performance of your Yii applications, too! Thanks for reading and let me know if you have any questions or comments.
Larry
