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

Home » General » Database discussions » MySQL Tuning for FUDForum
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
MySQL Tuning for FUDForum [message #28224] Fri, 14 October 2005 01:02 Go to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
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 09: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
add to buddy list
ignore all messages by this user
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 10:39 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
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 10: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
add to buddy list
ignore all messages by this user
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 10: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
add to buddy list
ignore all messages by this user
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 11:09 Go to previous message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
OK, I've mistakenly thought that SQL statements are cached to prevent re-parsing.

Thanks for the reply!

Frank
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
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: Fri Oct 20 17:51:39 EDT 2017

Total time taken to generate the page: 0.00761 seconds