- Context
- Resolving the _MAX(updatetime) problem
- Taking things further, Caching pages with multiple models
Note: Before reading this wiki you should have read the caching part in the Yii guide
Context ¶
I have a website whith a lot of visitors and a poor DB server. This is why I strongly needed to use some caching technique! The first thing I thought about was to use the CDbCacheDependency along with a request selecting the maximum update time of my table as shown in the Yii guide:
$dependency = new CDbCacheDependency('SELECT MAX(update_time) FROM table_a ');
$rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();
The main problem of this request is it doesn't get the modifications when we delete a row: If the deleted row isn't the maximum update time then MAX(update_time) won't change and the cache won't be refreshed! Another problem is that the MAX request performance decreases on really big tables.
Resolving the _MAX(updatetime) problem ¶
The Database ¶
To resolve this problem we can use SQL triggers and another table. We are going to store the modifications of the different tables as rows in a new table.
So let's create the table:
[mysql]
CREATE TABLE `last_table_modifications` (
`table_name` varchar(50) NOT NULL,
`last_modification` datetime DEFAULT NULL,
PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Then for each table we want to track we are going to create a row in this table:
[mysql]
INSERT INTO `last_table_modifications` (
`table_name` , `last_modification`
) VALUES ('table_a', NOW( )), ('table_b', NOW( ));
The last step on the Db side is to create 3 triggers for each table that are going to be tracked. Those triggers are going to update the modification table on : INSERT, UPDATE and DELETE:
[mysql]
DELIMITER $$
CREATE TRIGGER `trigger_table_a_delete` AFTER DELETE ON `table_a` FOR EACH ROW BEGIN
UPDATE last_table_modifications SET last_modification=NOW() WHERE table_name="table_a";
END$$
CREATE TRIGGER `trigger_table_a_update` AFTER UPDATE ON `table_a` FOR EACH ROW BEGIN
UPDATE last_table_modifications SET last_modification=NOW() WHERE table_name="table_a";
END$$
CREATE TRIGGER `trigger_table_a_insert` AFTER INSERT ON `table_a` FOR EACH ROW BEGIN
UPDATE last_table_modifications SET last_modification=NOW() WHERE table_name="table_a";
END$$
DELIMITER ;
The php side ¶
Now each time we add a new model to the db, update one or delete it the changes will be tracked in last_table_modifications We can set the new dependency as:
$dependency = new CDbCacheDependency('SELECT last_modification FROM last_table_modifications WHERE table_name="table_a"');
$rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();
Of course we can also use it for fragment caching!
...other HTML content...
<?php if($this->beginCache($id, array('dependency'=>array(
'class'=>'system.caching.dependencies.CDbCacheDependency',
'sql'=>'SELECT last_modification FROM last_table_modifications WHERE table_name="table_a"')))) { ?>
...content to be cached...
<?php $this->endCache(); } ?>
...other HTML content...
Taking things further, Caching pages with multiple models ¶
Then I came across another problem: I have some pages with a lot of differents models (some pages have more than ten, but let's say ten). To cache the data yii had to perform ten queries, one for each model type, and for me that's too much since all the requests are pretty similars.
This is why I decided to go with the following workflow:
- Before the action is executed I get the last_table_modifications rows and store it
- I change the CDbDependency to a CExpressionDependency and give it the data that I stored accordingly to the model type.
Storing the retrieved data ¶
The data that I'm going to store are needed during the time of the request, but there is no need it perdure during time. This is why I'm going to use Yii built-in param to store them. To take car of the case our request to fetch modifications times failed we can initate the params in the config file by setting:
'params' => array(
...
'lastModifications' => array(
'table_a' => 0,
'table_b' => 0,
),
....
),
Creating the filter ¶
Now we need to create a filter that is going to be executed before the controller actions. In my case I created it in the Controller class, the class all my other Controllers inherit from.
public function filterCacheInit($filterChain)
{
$caches = Yii::app()->db->createCommand()
->select('table_name, last_modification')
->from('last_table_modifications')
->queryAll();
$cachesArray = array();
foreach ($caches as $key => $cache) {
$cachesArray[$cache['table_name']] = $cache['last_modification'];
}
//Note I'm using an intermediate array because Yii::app()->params
// is a virtual attribute so you can't assign data as we are doing above
Yii::app()->params["lastModifications"] = $cachesArray;
$filterChain->run();
}
Now that we defined the filter we can apply it to the desired actions by setting:
public function filter()
{
return array(
'cacheInit',
....
);
}
If you want the filter to apply to only some actions you can add the desired actions after a + in the filter declaration 'cacheInit + edit, create'. If you want the filter to apply to all actions except some you can add the desired action after a - in the filter declaration 'cacheInit - delete'.
In my case I declared it int the Parent Controller class so all the inherited class have this cache filter (I'm using cache on almost all of my pages).
Using the news dependency ¶
Now that we have stored the modfications time, we just have to call it in the CExpressionDependency:
$dependency = new CExpressionDependency('Yii::app()->params["lastModifications"]["table_a"]');
$rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();
A big advantage of using Yii::app()->params is that you can get it from almost everywhere in your app so you can use it:
- In the Controllers
- in the Views
- In the models
- In the widgets
- ...
This wiki was inspired by a post on the forum from redguy and talking about triggers and creating a table to handle update times.
Please don't hesitate to ask some question or comment on the flaw of this solution, I'm always looking for ways to improve the code of my application!
Simple?
YII :-)
Meet AVG
[html] The main problem of this request is it doesn't get the modifications when we delete a row: If the deleted row isn't the maximum update time then MAX(update_time) won't change and the cache won't be refreshed! Another problem is that the MAX request performance decreases on really big tables.
I beg to disagree, AVG is your friend in this case :)
$dependency = new CDbCacheDependency('SELECT AVG(UNIX_TIMESTAMP(whatever_date_time_field)) FROM table_a ');
Re: Meet AVG
@twisted1919 Yeah I was aware of the avg alternative, but the AVG command on my server is taking 0.08s on a table with less than 30 000 records!
The alternative is quicker (but it's true you have to create a new table and add some triggers make some operation slower only on the db side).
And with the new table I can't quickly select the last update time for all the tables!
Doesn't worth the effort
@darkheir
If you ask me, it doesn't worth the effort to code like so when you have AVG because of the portability and maintainability.
Those 0.008 ms i am sure could be improved with proper indexes.
Just my 2 cents :)
Triggers bite!
Watch out for triggers, they will bite you. Just kidding of course, but I have for all my database work tried to avoid triggers. Did you investigate the possibility to add a behaviour to the models so that they would update last_table_modifications ?
Also, avg() will always be slow because it requires a table scan, whatever the index you put on it. (or maybe just an index scan if you put the value in the index) Avg() really isn't an option.
RE: Triggers bite!
I agree with you, I don't like the idea to use AVG() to get the update time!
To be fair, I really hesitated between triggers and a behavior, I know it's feasible using both.
I chose triggers because this way the php server has one request less to perform before returning : it doesn't have to update last_table_modifications.
On the other side, the advantage of a behavior is that you don't have to create n*3 Triggers in your database (n = the number of table you want to track), you just attach the behavior to the model and it's running smoothly!
Maybe another advantage is that all the application logic is on the php side, the database stands here just storing data, but not doing anything on it's own.
You can use MAX with different scheme
I never delete records from important tables. Usually I add is_deleted column on table. When I delete some record, I update update_time.
Deleted records sometimes can be useful.
Another SQL solution
This forum thread could be interesting to you all.
Cdbcachedependency Effective Sql That Handles Modifications And Deletions?
I like this one:
SELECT CONCAT(CAST(MAX(modified) AS CHAR), COUNT(*)) FROM post
information_schema
The main problem of this request is it doesn't get the modifications when we delete a row: If the deleted row isn't the maximum update time then MAX(update_time) won't change and the cache won't be refreshed!
information_schema.TABLES.UPDATE_TIME?
If you have any questions, please ask in the forum instead.
Signup or Login in order to comment.