Welcome Guest, Not a member yet? Register   Sign In
Database query caching not working
#1

[eluser]fchristant[/eluser]
hi,

I've been trying to get CI's query caching to work, but got stuck somewhere.

I'm on Debian Linux, have setup the database.php file to enable query caching and I have pointed it to a writable cache dir in my application folder. I do not manually enable/disable query caching anywhere in my code.

When I open a page (which executes 3 queries), I can see the cache result files being written to the dir I specified. The content of these files look good: they contain the result expected. So far, so good.

However, when I refresh the page, this cache is not used at all:

- The profiler shows 3 queries executed (should have been zero!)
- The MySQL query log shows the same
- When I insert new data, it is shown (should not be the case due to cache)

Enough proof to conclude that the cached results are not used at all. Why not?

PS: my test page contains SELECT queries only.
#2

[eluser]fchristant[/eluser]
I've done some further research and I think I found the problem, check out this code from CI's DB_driver.php file:

Code:
// Is query caching enabled?  If the query is a "read type"
// we will load the caching class and return the previously
// cached query if it exists
if ($this->cache_on == TRUE AND stristr($sql, 'SELECT'))
  {
  if ($this->_cache_init())
  {
   $this->load_rdriver();
   if (FALSE !== ($cache = $this->CACHE->read($sql)))
   {
   return $cache;
   }
  }
}
        
// Compile binds if needed
if ($binds !== FALSE)
{
$sql = $this->compile_binds($sql, $binds);
}

The query binding is done AFTER the cache checking. This seems to me like a bug, right? The cache checking mechanism can only know what cache file to look for when it knows the actual query. The actual query can only be known AFTER binding, not before.

As a quick check, I swapped these two blocks, performing cache checking after binding. This does solve the problem, the queries are cached now. However, a slight other problem occurs: connections to MySQL are still being made, although no queries are run:

87 Connect os4w@localhost on
87 Init DB os4w
87 Query SET NAMES 'utf8' COLLATE 'utf8_general_ci'
87 Quit
88 Connect os4w@localhost on
88 Init DB os4w
88 Query SET NAMES 'utf8' COLLATE 'utf8_general_ci'
88 Quit
89 Connect os4w@localhost on
89 Init DB os4w
89 Query SET NAMES 'utf8' COLLATE 'utf8_general_ci'
89 Quit

Am I right into thinking this is a serious bug? Any thoughts on how to make sure the MySQL connections are not being opened when a cached result is found?
#3

[eluser]TheFuzzy0ne[/eluser]
I think you're right. It does look like a bug, and is certainly worth reporting along with a suggested fix.
#4

[eluser]fchristant[/eluser]
Thanks for confirming, I have filed a report:

http://codeigniter.com/bug_tracker/bug/7696/

The other thing I mentioned, CI still opening connections despite using cached results was not related to this. I had autoloaded the database class which forces a connection to open for every page. I do wish CI was smarter in opening connections:

- open a connection automatically when I perform a query that has no cached results.
- reuse that same connection for multiple queries from the same page. my research shows a new connection is opened for each query.

The current options do not solve the above:

- if I autoload the database, I open connections even when not needed. the database class really needs a lazy loading connection initializer
- if I open and close connections manually, that's a lot of work, and I need to break into the db_driver class, since it depends on caching whether I should open a connection or not.

I think managing connections smarter is crucial for scalability, or am I overlooking what is currently possible in CI?

But again, those are other issues not related to this problem.
#5

[eluser]TheFuzzy0ne[/eluser]
[quote author="fchristant" date="1242928168"]- open a connection automatically when I perform a query that has no cached results.[/quote]

That's a nice idea, but I'm not sure it's necessary. Simply connecting to a database should not cause any performance issues.

[quote author="fchristant" date="1242928168"]- reuse that same connection for multiple queries from the same page. my research shows a new connection is opened for each query.[/quote]

Have you set pconnect to TRUE in database.php for your database connection?

[quote author="fchristant" date="1242928168"]- if I autoload the database, I open connections even when not needed. the database class really needs a lazy loading connection initializer[/quote]

One of the best things about CodeIgniter is that it's almost fully extendible. Unfortunately, "almost" means that there are several core files (including some of the database files), that can't be easily extended. However, it should be possible to abstract it as a library and extend it.

You'll need to call your library something other than database, unless you extend the loader too.

[quote author="fchristant" date="1242928168"]I think managing connections smarter is crucial for scalability, or am I overlooking what is currently possible in CI?[/quote]

I guess it depends. I think CodeIgniter scales well, but that's not from experience. I say that because usually as an application grows larger, resources are expanded to cope with more requests.

CodeIgniter is built with simplicity in mind, and whilst some things may be better implemented a differently, it's still very efficient compared to a lot of other frameworks. If you look through the source code, you will see that a huge effort has been made to keep bloat out of the code. I think that's why CodeIgniter leaves such a small memory footprint.

But again, those are other issues not related to this problem.[/quote]
#6

[eluser]fchristant[/eluser]
Quote:That’s a nice idea, but I’m not sure it’s necessary. Simply connecting to a database should not cause any performance issues.

I agree, performance issues no, scalability issues yes. The number of open MySQL connections is one of the most important bottlenecks in scaling it.

Quote:Have you set pconnect to TRUE in database.php for your database connection?

This is a nice suggestion, but not what I'm looking for. I want a connection to be open for all queries of a controller, not beyond the life cycle of the page. Plus, persistent connections cause a lot of problems with multiple databases and transactions.

Quote:CodeIgniter is built with simplicity in mind, and whilst some things may be better implemented a differently, it’s still very efficient compared to a lot of other frameworks.

I agree about the simplicity and efficiency, and I'm a CI fan for sure. Still, it is important that robustness and making things work correctly are not sacrificed for these goals. Two examples:

- The bug I reported above. when using the db class as designed (using parameter replacement, a standard practice) and enabling caching, it does not work. I cannot believe this is not tested or found by someone else. Is everybody hardcoding SQL?

- Transactions. A previous disappointment I had was with transactions. Cache seems to influence it and nested transactions don't work, which is a showstopper if you need transactions, you want those for reliability. I had to go in and patch the core to make that work.

Things like this make me doubtful about the robustness of CI and whether I should use it for big/important sites. My point in summary: Yes, keep things simple and light, but above all...make sure it works first.
#7

[eluser]TheFuzzy0ne[/eluser]
[quote author="fchristant" date="1242945044"]- The bug I reported above. when using the db class as designed (using parameter replacement, a standard practice) and enabling caching, it does not work. I cannot believe this is not tested or found by someone else. Is everybody hardcoding SQL?[/quote]

As far as I know, Active Record is very popular, but there are some hardcore minimalists who prefer to use straight SQL. I'm willing to bet that there are even fewer people who use query bindings (perhaps this would be an interesting poll?).

[quote author="fchristant" date="1242945044"]- Transactions. A previous disappointment I had was with transactions. Cache seems to influence it and nested transactions don't work, which is a showstopper if you need transactions, you want those for reliability. I had to go in and patch the core to make that work.[/quote]

I'm a bit confused by that. Nested transactions shouldn't be a problem, so long as you're database storage engine supports transactions. If you're using transactions, you're making write-type database queries. Cache on the other hand relates to read-type queries. I fail to see the correlation between transactions and cache. Am I missing something?
#8

[eluser]fchristant[/eluser]
"I’m a bit confused by that. Nested transactions shouldn’t be a problem, so long as you’re database storage engine supports transactions"

A few months back I concluded they are a problem due to a nesting depth bug in CI, there are multiple topics about it, but this one explains it shortly:

http://ellislab.com/forums/viewthread/107794/

It could be fixed in 1.7, haven't checked yet. My point remains, I would not expect such an important feature to be so poorly implemented, in this case not implemented at all.




Theme © iAndrew 2016 - Forum software by © MyBB