0

I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit.

MySQL 5.1.58 community edition is installed on Windows servers while MySQL 5.1.73 is installed on Oracle Linux. Same database is installed on both servers - i..e same number of tables with same data. At a time, if I run `show global status like 'Qcache%' on both servers and below is the comparison result:

Status variables       Linux           Windows
Qcache_free_blocks     24              54301
Qcache_free_memory     53025440        151737216
Qcache_hits            162509          44648032
Qcache_inserts         33073           6866308
Qcache_lowmem_prunes   0               657080
Qcache_not_cached      11291            994889
Qcache_queries_in_cache 7446            112378
Qcache_total_blocks     14959           279520

Below is the result of show global variables like 'query_cache%';:

Status variables                Linux        Windows
query_cache_limit               2097152      4194304         
query_cache_min_res_unit        1024         4096
query_cache_size                67108864     367001600   
query_cache_type                ON           ON
query_cache_wlock_invalidate    OFF          OFF
  • Why is there huge differences?
  • Also, In Linux, Qcache_free_blocks is only 3 while Qcache_free_memory is about 53MB?

closed as off-topic by Mark Storey-Smith, RLF, Paul White, Max Vernon, Michael - sqlbot Dec 20 '14 at 18:51

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, RLF, Paul White, Max Vernon, Michael - sqlbot
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Linux being more efficient? :-) Is there anything else going on on these servers? – Vérace Nov 20 '14 at 13:38
  • On Linux, there is another application installed. But, that application is very small and don't use frequently. – ursitesion Nov 20 '14 at 14:13
  • qcache_free_blocks is a count of contiguous blocks and tells you nothing about the block size; a totally empty query cache on a newly started server has exactly 1 free block. dba.stackexchange.com/questions/33811/… – Michael - sqlbot Dec 20 '14 at 18:54
4

Since you started both servers, you have executed (approximately) 162509 + 33073 + 11291 = 206,873 queries on the Linux server and 44648032 + 6866308 + 994889 = 52,509,229 queries on Windows. Why would you expect similar numbers when one has done more work than the other?

The ratios, however, are similar with:

162509 / (162509 + 33073) ~= 83%
44648032 / (44648032 + 6866308) ~=87%

80-90% ratio of query cache hits. You should only compare statistics over the same period of time, for example, by using pt-mext -- mysqladmin ext

In order to answer specific questions about size, you should provide the output of SHOW GLOBAL VARIABLES like 'query_cache%'; and if the OSs and mysql versions are 32 or 64 bits.

UPDATE: You have complete different configuration on Linux and Windows, window's query cache is 30x larger. Why would you expect equal or similar statistics from both, when you are not even executing the same queries? I do not understand why you are surprised that you get different "numbers" when you input different ones: free blocks is the amount of blocks that are not in use by query results. The Windows one is not yet full, or it has deallocated more than the Linux one.

  • I have updated my question. – ursitesion Nov 21 '14 at 7:47
  • Also, I am more curious to know about why Qcaceh_free_blocks is too low? – ursitesion Nov 21 '14 at 8:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.