• Registered: 2006-09-12
  • Posts: 36

Topic: MySQL Query Caches?

Hi

idea: Why don't you use the MySQL query cache to speed up queries? Especially static queries like the query that fetches the category names, would speed up.
I know, this is MySQL-specific, but I'm sure the other database systems have something comparable.

To optimize PunBB for both, query_cache_type = ON(caching all queries) and query_cache_type = DEMAND(only caching special marked queries), you could give each query flag:

    * SQL_CACHE for queries that speed up much, when cached, e.g. the following query is executed 10x faster on my system after being cached

SELECT c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster FROM categories AS c INNER JOIN forums AS f ON c.id=f.cat_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=1) WHERE fp.read_forum IS NULL OR fp.read_forum=1 ORDER BY c.disp_position, c.id, f.disp_position

* SQL_NO_CACHE for queries that don't speed up or even slow down, e.g. queries containing the current UNIX-timestamp

SELECT o.* FROM online AS o WHERE o.logged<1205706380

In this case, the system would NOT slow down under any circumstances, because "dangerous" queries are marked with NO_CACHE. The system can only speed up, because queries are being cached when the MySQL is ON or in DEMAND mode. If the query cache is disabled, MySQL simply ignores the cache-flag.

second idea: To optimize time-based queries like the one above, which fetches users, that were active during the last 5 minutes (time()-300), we have to make the condition change as less as possible. The trick is the following:
No condition like:

a < time()

the idea: create 5-minute-steps

a < time() - (date('i')%5)*60 - date('s')

this piece of code subtracts the time passed since the last 5min-mark from the current timestamp - result:
we get 16:45, on 16:45, 46, 47, 48 and 49. It requires only one cache update every 5min.

The bad side of it: on 16:46, it checks activity during the last minute.. By subtracting another 5min of it, we get the online activity for the last 5 - 9min. I think that timerange is acceptable.
Final code:

$last_step = $time - (date('i')%5)*60 - 300 - date('s');
  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: MySQL Query Caches?

1. It's my understanding that if the query cache is enabled, applications need to do nothing special to take advantage of it. Is that wrong?
2. The query you're referring to has inherently volatile data, since the table is updated anytime someone views a page. I'm not sure how much of a speedup you'll get even if you could try to cache the result of the query. Also, as you pointed out, there's decreasing accuracy as the online timeout increases, which is less than ideal

  • Registered: 2006-09-12
  • Posts: 36

Re: MySQL Query Caches?

Smartys wrote:

1. It's my understanding that if the query cache is enabled, applications need to do nothing special to take advantage of it. Is that wrong?

You understood it right, it's my fault that I forgot to mention one thing: Only by using the cache-flags, the positive cache effect, can be achieved, when the user set query_cache_type = DEMAND - elsewise nothing would be cached, because that cache_type only caches on DEMAND
By using those flags, PunBB can work with both cache_types great.

Smartys wrote:

2. The query you're referring to has inherently volatile data, since the table is updated anytime someone views a page. I'm not sure how much of a speedup you'll get even if you could try to cache the result of the query. Also, as you pointed out, there's decreasing accuracy as the online timeout increases, which is less than ideal

Well, that was the worst example i could choose.. (online uses the MEMORY engine under MySQL, there is no huge benefit by caching such a table) It think it would be best to forget the second half of my post ;-)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: MySQL Query Caches?

OK, so give me an example of how PunBB would set the proper query cache variables as well as an example of how a query has to be modified for caching? smile

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: MySQL Query Caches?

I do wonder, with the caching, how would you tell MySQL to cache that query? Would you need to add something infront of it to have it cache, or add something infront of it to make it not cache?

  • Registered: 2006-09-12
  • Posts: 36

Re: MySQL Query Caches?

It depends on the MySQL configuration of the user's system:

1) query_cache_type = ON
MySQL caches every query, except ones flagged with SQL_NO_CACHE (e.g. query always changes SELECT SQL_NO_CACHE * FROM online WHERE logged<NOW()-60*5)

2) query_cache_type = DEMAND
MySQL only caches queries flagged with SQL_CACHE (e.g. query fetches rarely changed data SELECT SQL_NO_CACHE cat_name FROM categories)

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: MySQL Query Caches?

I see.

So it could be beneficial to add the functionality to the query builder, and having it available as an extension instead of putting it in the core where it'll have little use for most forums and clog up the code.

  • Registered: 2006-09-12
  • Posts: 36

Re: MySQL Query Caches?

I think it's important that this function is included in the query builder, but it's even more important that the core queries are all provided with a cache-flag to optimize PunBB for systems with an enabled query cache and speed it up on those systems.