FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » General » Database discussions » MySQL Tuning for FUDForum
Show: Today's Messages :: Polls :: Message Navigator
Switch to threaded view of this topic Create a new topic Submit Reply
MySQL Tuning for FUDForum [message #28224] Fri, 14 October 2005 05:02 Go to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
Hi,

I've increased the MySQL query_cache_size parameter from 0 to 64K to see if I can improve FUDForum's performance.

mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_size             | 67108864 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)


However, looking at the stats (hits/inserts), it's not really helping.

mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 4374     |
| Qcache_inserts          | 51522    |
| Qcache_hits             | 51411    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 433      |
| Qcache_free_memory      | 58983528 |
| Qcache_free_blocks      | 1342     |
| Qcache_total_blocks     | 10229    |
+-------------------------+----------+
8 rows in set (0.00 sec)


Any idea why this might be the case? Maybe I'm not interpreting the stats correctly?

Best regards.

Frank
Re: MySQL Tuning for FUDForum [message #28237 is a reply to message #28224] Fri, 14 October 2005 13:51 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
Seems to work, you has 51k cache hits.

FUDforum Core Developer
Re: MySQL Tuning for FUDForum [message #28245 is a reply to message #28237] Fri, 14 October 2005 14:39 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
True, however, I didn't expect the high number of cache inserts. Is it because the forum doesn't use bind variables.

Best regards.

Frank


Re: MySQL Tuning for FUDForum [message #28247 is a reply to message #28245] Fri, 14 October 2005 14:42 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
The MySQL API (ext/mysql) does not support prepared statments, so caching would only work for identical queries or queries differing through LIMIT clauses.

FUDforum Core Developer
Re: MySQL Tuning for FUDForum [message #28248 is a reply to message #28245] Fri, 14 October 2005 14:42 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
Actually that wouldn't help either since query caching relates to caching of QUERY RESULTS, so you can only cache identical operations.

FUDforum Core Developer
Re: MySQL Tuning for FUDForum [message #28249 is a reply to message #28248] Fri, 14 October 2005 15:09 Go to previous message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
OK, I've mistakenly thought that SQL statements are cached to prevent re-parsing.

Thanks for the reply!

Frank
  Switch to threaded view of this topic Create a new topic Submit Reply
Previous Topic: Profile setting adjustments for mailing-list-only users
Next Topic: FUDForum charset problems
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Nov 27 18:13:49 GMT 2024

Total time taken to generate the page: 0.02171 seconds