[development] MySQL query cache

[Gerhard Killesreiter]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hans Wolters schrieb:
> Hi all,
> 
> With all the talking about caching I would like to ask some things.
> 
> Currently I am using MyISAM tables. I have a small moodle site and was
> puzzled about
> the amount of memory being used. Tinkered with apache and enabled the
> mysql query_cache.
> 
> The last tweak, the cache, is a MySQL only thing, PostgreSQL does not
> use it.
> 
> Running the site a few days I kept wondering why it still does not
> really speed up. I think I now
> found part of the problem. There are some tables that are unfit to be
> used with the
> query cache. This is not due to drupal but more to the specifics the
> query cache is using.
> MySQL will optimize queries and will store select queries, when
> possible, in the cache. There
> for it does not need to recompile/optimize the query the next time it is
> called. Whenever a value
> is updated or inserted in a table the queries that are questioning these
> tables will be invalidated.
> 
> For a few Drupal tables this will only generate extra cpu cycles without
> any benefit. Both the session and cache tables are updated when enabled
> in the config on almost every page view.

Much worse is that the user table will also be updated for access
logging. That means that all queries that include the user table can't
be cached. That's a lot of queries.

> A solution would be to add SQL_NO_CACHE after the selects on these 
> queries. This however
> would break the queries for PostgreSQL.

Right.

> What would be the best way to submit a patch for this?

First of all you should hack you local Drupal site to include
SQL_NO_CACHE and provide benchmarks that prove that there is much to be
gained from omitting the query cache.

Cheers,
	Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG1vdXfg6TFvELooQRAsA8AJ91Mzp/vWdoS8naCPT2nWwT3segGwCgwrF/
2GxwnadICXgYbLr6r5QA7m4=
=gMZj
-----END PGP SIGNATURE-----