SQL Cache

Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here.
Forum rules
Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here. Feature requests are closed.
Post Reply
markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

SQL Cache

Post by markus_petrux »

Hi,

I just noticed something with the Cache method used in Olympus that I'm not sure if that may be a problem on high loaded boards.

As far as can see, this is how it works:

Code: Select all

1: IF get from cache THEN
2:   return cached data
3: ENDIF
4: perform query
5: put data into cache
6: return data
When this is code is executed on a board with lots of visits it might happen that:

1) visitor A reaches line 1, cache is not found or expired.
2) visitor A performs the query.
3) visitor B reaches line 1, but cache has not been updated by visitor A yet.
4) visitor B performs the same query.
5) visitor C, D, and so on may follow the same road than visitor B.

I'm wondering if there's anything that could be done to prevent more than one visitor triggering the same query (more than one visitor creating the cache file at the same time). Maybe using some kind of locking? ...anyone had problems with this caching algorithm?

Anyway, caching still provides a nice performance boost, as per the monitoring reports. I have just implemented this method on a phpBB2 based board that has a constant rate of 400 to 600 users online (the tipical number that appears on forum index, past 5 minutes). Still I'm wondering if it would worth to put something to detect how many times it happens that more than process does the same job.

Have I missed something and that could not really happen?

Cheers

User avatar
Acyd Burn
Posts: 1838
Joined: Tue Oct 08, 2002 5:18 pm
Location: Behind You
Contact:

Re: SQL Cache

Post by Acyd Burn »

markus_petrux wrote: Hi,

I just noticed something with the Cache method used in Olympus that I'm not sure if that may be a problem on high loaded boards.

As far as can see, this is how it works:

Code: Select all

1: IF get from cache THEN
2:   return cached data
3: ENDIF
4: perform query
5: put data into cache
6: return data
When this is code is executed on a board with lots of visits it might happen that:

1) visitor A reaches line 1, cache is not found or expired.
2) visitor A performs the query.
3) visitor B reaches line 1, but cache has not been updated by visitor A yet.
4) visitor B performs the same query.
5) visitor C, D, and so on may follow the same road than visitor B.


If the resultset for visitor A, B, C and D would be different than this would be a problem, yes. But then you would have problems anyway, due to a resultset for user A being retrieved by user B. ;) The resultsets are identical - so there is no problem in obtaining it twice. Furthermore on writing the file it gets locked. Once the data is retrieved it gets no longer written.

If there are problems has to be seen - the person most likely able to tell something in regard to this is BartVB. :)

Image

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: SQL Cache

Post by markus_petrux »

The problem I'm thinking is more about several users hitting the DB server to perform a potentially heavy query (otherwise it wouldn't worth caching? ) at the same time. Such queries might degrade the connections involved, but also the overall board performance, if running them forces other data that was in the buffer pool to be reread from disk again, maybe.

I'm caching 2 different kind of queries:
1) static config, smilies, ranks and similar things.
2) dynamic config, online users, some board and forums stats.

For case 1, cache is updated whenever a change is made in the ACP, otherwise data doesn't change.

For case 2, cache is updated based on expiration, where the number of seconds to expire depends on an argument that's passed to each query (our cache method is implemented behind sql_query, similar than Olympus).

The described problem may happen in case 2, where cache is updated dynamically by a regular user request. So I was thinking about adding some kind of locking, something like this:

Code: Select all

a: LOCK (semaphore)
1: IF get from cache THEN
b:   UNLOCK (semaphore)
2:   return cached data
3: ENDIF
4: perform query
5: put data into cache
c: UNLOCK (semaphore)
6: return data
The question is how to implement this lock. ...or maybe it doesn't really worth?

Adding more memory (buffer pool, etc.) wouldn't help much with certain queries, the board I'm talking about has more the 5 million posts and more than 185000 users. Using the same cache method that the one used in Olympus has helped a lot, but the board is growing...

It would be nice to hear from BartVB ...or maybe anyone else with experience on big boards.

PS: I wasn't sure where to open such a discussion, I guess it was ok here :)

User avatar
Eelke
Registered User
Posts: 606
Joined: Thu Dec 20, 2001 8:00 am
Location: Bussum, NL
Contact:

Re: SQL Cache

Post by Eelke »

Acyd Burn wrote: If the resultset for visitor A, B, C and D would be different than this would be a problem, yes.

I don't think the concern is there will be a functional problem, but that the caching system won't work as effectively as it possibly could.

However, creating a locking mechanism for this might be troublesome. Even if PHP would offer a way to make certain parts of the code lockable by some kind of synchronization/semaphore method (no idea), you'd somehow have to implement a locking mechanism that locks on the actual query performed (you wouldn't want to lock out the caching code for any and all queries it could handle, that would probably have a bigger performance hit than the one you're trying to correct, because any thread wanting to access the cache would have to wait for any other thread that was in that section of the code).

User avatar
Acyd Burn
Posts: 1838
Joined: Tue Oct 08, 2002 5:18 pm
Location: Behind You
Contact:

Re: SQL Cache

Post by Acyd Burn »

dynamic config


IMO this is better retrieved directly because it changes constantly (hence the dynamic bit).
online users


Since this is changing at a constant rate i do not see any reason why the underlying sql queries should be cached. The overhead regarding file operations would be heavy.
some board and forums stats.


These are better stored hardcoded within the database - you do not need to recalculate it that often since they can only change on a few occassions.

Image

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: SQL Cache

Post by markus_petrux »

Oh, let me explain a little. All tables are MySQL/InnoDB, so we can count on some hits for certain queries to be resolved from the query cache or from the innodb buffer pool. Still we have found that caching improves the overall performance of the board. We're using 2 dedicated servers (apache and mysql), so besides the processes, there's a little network overhead. Files are cached locally, on the web server.

For static data, there's no expiration time. It is however updated whenever a change is made from the ACP (config, smilies, ranks, etc.). Here, regarding static data, I believe a possible way to improve phpBB3 performance would be to store what's called dynamic data on a separate table, so changes there would not affect the table where static data would be stored. Static config could be cached by mysql itself in the query cache, but when dynamic data changes it forces mysql to invalidate the static config query from the query cache. For that reason, we're storing dynamic config on a separate table. Static config is bigger than dynamic config.

Regarding dynamic data, there's an expiration time (depending on each query), so for instance, instead of reading dynamic config or board stats for forum index from the database for each page hit, we can do it just (say) once per minute. Of course, only the process that triggers the cache update will get accurate results, but we found that the number of users, posts or the online users list was not so critical. At least, it might give us some margin if the hardware configuration gets little, by just altering the expiration time of these cached queries.

We have a couple of forum categories that have a lot of subforums (hundrends) that are not displayed on forum index. For these subforums, we're showing on forum index information about the number of topics/posts and last message posted. The queries involved here are cached with an expiration time of 60 seconds. This is a similar scenario as caching the users online or, basically, any other dynamic data.

We have yet to see if the fact that not locking when cache need is evaluated generates performance problems or not. Just thought it would be nice to share this concern anyway.

code reader
Registered User
Posts: 653
Joined: Wed Sep 21, 2005 3:01 pm

Re: SQL Cache

Post by code reader »

if you are looking for ways to implement a semaphor, and asking advise for which is the best way of doing it (at least this is what i think you are asking), and since you use innoDB, i would suggest you look into devising a semaphor around database transaction mechanism, rather than a pure php thing.
just my 2c.
if however, you insist on using semaphores purely in php, you will need to compile php with shared memory (or install the shared memory extension as a module) and then use one of its functions, most probably sem_acquire().
not 100% sure, but i vaguely remember discussion of some security issues associated with shared memory and php. may be pure fud. search it.
you can devise "home made" locking mechanism by using file or socket locking. the problem with those methods is that if a process dies while owning a resource, the resource remains locked indefinitely, as threre is very limited (read: non-existent) exception handling capability in php.

as to your original question:
if what bothers you with multiple generation of cached data is performance, i think you worry about the wrong thing.
the cache is meant to alleviate some of the load by reducing the number of queries. the scenario you describe will (most probably) happen quite rarely, and its effect on performance is negligible. if, otoh, it will happen a lot, it means the cache is not very effective anyway.
if it could lead to errors, then it would be a serious thing, but as acyd pointed out, this is not the case.

hope it helps.

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: SQL Cache

Post by markus_petrux »

There was another option, it is to create the cache file using cron jobs. That would ensure the job is done just once, however it makes the whole thing a bit more complex. In fact, there was already a cron in place that was reading the users online query into a temporary table of type MEMORY, but it was causing locks and deadlocks affecting online requests. Also, the phpBB code was adapted to read from the MEMORY table rather than the usual sessions+users join. I had to change that (at least to avoid the deadlocks), though I decided to abandon the cron and try the phpBB3 caching method.

If there was an easier way... maybe locking when reading the cache file itself?

Code: Select all

open the cache file with exclusive lock
IF file exists AND NOT expired THEN
   close the file (release lock)
   return cached data
ENDIF
perform query
write data into cache file
close the file (release lock)
return data
Without locking, when the cached data has expired, if you have several requests per second, the query will get executed several times. That's a potential hole for performance problems.

I believe Eelke, above, said it more simple than me:
Eelke wrote: I don't think the concern is there will be a functional problem, but that the caching system won't work as effectively as it possibly could.

..or not, that's the question.

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: SQL Cache

Post by markus_petrux »

Hi,

Just found an article about MySQL function GET_LOCK. For those that use MySQL 4.x, it looks like a good alternative to do the locks with, probably, little performance impact (the mysql connection is already there) ...and looks pretty easy to implement within the sql_query method itself (at least, at a first sight). Not sure which version exactly, the MySQL manual doesn't tell which version introduced GET_LOCK. We're still using 4.0.x on that server, so I'll have to experiment. We have to plan an upgrade to 4.1.x sometime soon anyway.

The server is currently running smoothly without locks around the SQL cache. Such a locking strategy may not worth for a certain level of activity, however as the number of concurrent connections grow, it might impact the overall performance, I think, so I'll try to experiment in that direction during the next weeks (depends on workload that I'm not completely sure yet). Time will tell...

Cheers

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: SQL Cache

Post by markus_petrux »

Hi,

After a couple of weeks of testing, I would like to post our experience...

During peaks, looking at SHOW PROCESSLIST I noticed some of those "cached" queries running at the same time. All our tables are InnoDB, so that functions such as COUNT() or SUM() need to traverse the whole data space, meaning other buffer pool pages need to be flushed, causing a performance impact on some other places that used to be resolved from buffer pool.

I finally opted to use SELECT GET_LOCK('cache_filename'); and SELECT RELEASE_LOCK('cache_filename'); to control access to the cached files. Even our webserver and mysql are different boxes, this method just added 1 millisecond or less overhead to each cached query operation. Another good thing for us with this method is that implementation was pretty simple. Even if something goes wrong, locks are released as soon as the client connection ends.

Another thing we're doing now is using /dev/shm (shared memory) to store cache files (templates and sql). Accesses to cache files are resolved in less than 1 millisecond.

During peaks, we were getting load averages up to 28. Now it is about 6/8.

If curious about some information related to our level of activity, you may want to take a look at big boards stats.

Even when not directly related to Olympus, I hope it might be of interest to some.

Cheers

Post Reply