MySQL Query cache hits stay zero

[Beegee]

Has anybody been observing the same thing? For a couple of weeks now I
have a website running build with Django. I was looking for way to
improve the performance. One way of improving the performance is
turning on the Query Cache for MySQL. By default this is activated. It
also was on my MySQL installation.

However the number of Query cache hits (an indication of how well the
cache is used) is always zero! And this while the number
Qcache_queries_in_cache increases while I am browsing my website. So,
none of the queries from django are being fetched from the MySQL Query
cache!

Why not?

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 661 |
| Qcache_inserts | 474382 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1080657 |
| Qcache_free_memory | 18609912 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1333 |
+-------------------------+----------+
8 rows in set (0.00 sec)

When I manually type in a query on the MySQL command line and execute
it several times, the Qcache_hits indeed increases. So, the cache
functionality is working.

Thanks very much for your help.

Berry

[Adrian Holovaty]

On 4/20/06, Beegee <berry.gr...@gmail.com> wrote:
> When I manually type in a query on the MySQL command line and execute
> it several times, the Qcache_hits indeed increases. So, the cache
> functionality is working.

That's strange. I don't use MySQL, so I haven't encountered this
issue, but one strategy you might take to debug would be to perform
queries via the Python MySQLdb API, to see whether the lack of cache
happens at that level.

Untested example code:

>>> import MySQLdb
>>> connection = Database.connect(user='foo', db='foo', passwd='foo')
>>> cursor = connection.cursor()
>>> cursor.execute('select ...')

Adrian

--
Adrian Holovaty
holovaty.com | djangoproject.com

[Beegee]

No, then it also doesn't work. When I repeat the following query:

cursor.execute('select * from wifidog_businesses limit 10')

using MySQLdb the number of Cache hits do not increase. However, the
number of Qcache_queries_in_cache also do not increase. Strange.

I did find an issue on the MySQL for Python Sourceforge website that
seems to be describing the exact same issue:
http://sourceforge.net/tracker/index.php?func=detail&aid=1287716&group_id=22307&atid=374932

However, the issue is closed, but not because the problem was solved,
but because the problem was not described well enough. I did put a
comment at this issue describing my experience.

Berry

[Adrian Holovaty]

On 4/20/06, Beegee <berry.gr...@gmail.com> wrote:

> No, then it also doesn't work. When I repeat the following query:
>
> cursor.execute('select * from wifidog_businesses limit 10')
>
> using MySQLdb the number of Cache hits do not increase. However, the
> number of Qcache_queries_in_cache also do not increase. Strange.
>
> I did find an issue on the MySQL for Python Sourceforge website that
> seems to be describing the exact same issue:

Ah, so it's a MySQLdb issue -- good to know. Andy Dustman, who's
involved in maintaining MySQLdb, hangs out on the Django mailing list,
so he might be able to shed more light on the issue.

[Andy Dustman]

I've reopened that bug, but I am very skeptical that anything in
MySQLdb can be causing it. All the caching logic happens on the server
side, and the only thing the client is required to do is to send the
exact (byte-for-byte) same query, and have query_cache_type (either
for SESSION or GLOBAL) set to 1, or else set query_cache_type to 2 and
use SELECT SQL_CACHE. For Django you'd want the former. Plus, updating
the tables used by the query invalidates the cache, but it doesn't
sound like you're doing that, or at least not in your example.

Do statements generated by Django have columns in a deterministic order?

--
The Pythonic Principle: Python works the way it does
because if it didn't, it wouldn't be Python.

[Andy Dustman]

On 4/20/06, Andy Dustman <farc...@gmail.com> wrote:
> I've reopened that bug, but I am very skeptical that anything in
> MySQLdb can be causing it.

And I'm probably right, since it seems he is using MySQL-4.0 which,
while it does have the query cache, it is disabled for transactions on
InnoDB. 4.1 and newer query cache supports transactions.

[Adrian Holovaty]

On 4/20/06, Andy Dustman <farc...@gmail.com> wrote:

> Do statements generated by Django have columns in a deterministic order?

Just for the record, yes, the columns will always be in the same order.

[Beegee]

This is a part from the manual of MySQL: "In MySQL 4.0, the query cache
is disabled within transactions (it does not return results). Beginning
with MySQL 4.1.1, the query cache also works within transactions when
using InnoDB tables (it uses the table version number to detect whether
its contents are still current)."

So, are the following statements correct?
1. Apparently all Django generated or MySQLdb generated statements are
transactional. Even ordinary select statements? Because, only
transactional statements are not cached. (Still, it is a bit strange
because the value of Qcache_queries_in_cache does increase over time.)

2. To be able to use the query cache of MySQL when using MySQL as a
backend database for Django, you need to use at least version 4.1.1. of
MySQL. And you need to be using InnoDB tables.

Berry

[Ned Batchelder]

I don't know if this is helpful at this point, but on my MySQL 5 server,
the query cache hit count is quite high for Django queries.

--Ned.

--
Ned Batchelder, http://nedbatchelder.com

[Leeuw van der, Tim]

Hi BeeGee,

My impression is that your first statement is correct, but not your
second:

To use the query-cache with MySQL, you either need to use MyISAM tables,
or if you use InnoDB tables and transactions, then you need to use at
least MySQL version 4.1.1
This is not related to Django.

[Geert Vanderkelen]

Hi Berry,

Beegee wrote:
> This is a part from the manual of MySQL: "In MySQL 4.0, the query cache
> is disabled within transactions (it does not return results). Beginning
> with MySQL 4.1.1, the query cache also works within transactions when
> using InnoDB tables (it uses the table version number to detect whether
> its contents are still current)."

For reference, this comes from:
http://dev.mysql.com/doc/refman/4.1/en/query-cache-how.html

> So, are the following statements correct?
> 1. Apparently all Django generated or MySQLdb generated statements are
> transactional. Even ordinary select statements? Because, only
> transactional statements are not cached. (Still, it is a bit strange
> because the value of Qcache_queries_in_cache does increase over time.)

Django MySQL backend is doing a
SET autocommit=0
when connecting, and when successful make a COMMIT.

This is regardless of used Storage Engine, working.

SELECT statements which involve InnoDB in transactions will get cached
in MySQL 4.1 and higher. This has been noted before.

> 2. To be able to use the query cache of MySQL when using MySQL as a
> backend database for Django, you need to use at least version 4.1.1. of
> MySQL. And you need to be using InnoDB tables.

Wrong! Tim already replied, but:
Query Cache is available as of MySQL 4.0, and will also work for SELECT
statements for InnoDB tables in transactions as of MySQL 4.1.
(Depends on storage engine, MySQL Cluster will not cache in transactions).

You never said, or I missed it, which MySQL server you are running, but
if you are using MySQL 5.0, you will need to use following statement to
get the real count for the complete server:
mysql> SHOW GLOBAL STATUS LIKE 'Qcache\_%';

The GLOBAL keyword is important, as it will default to SESSION, and of
course that stays zero as you have another session in the MySQL client.

Hope this helps!

Cheers,

Geert

--

Geert Vanderkelen
http://www.some-abstract-type-shit.com

[Beegee]

Currently I am using MySQL version 4.0.24. Looking at all the answers
the best thing to do is to try MySQL v5. I will do some testing over
the weekend and post my experiences.

[Geert Vanderkelen]

Just FYI, binary upgrading 4.0 to 5.0 is a bad idea: dump data using
mysqldump of MySQL 5.0, and reimport is best.

Going first 4.1 might be better, and check all ChangeLogs in the
manual.. might be important for your stuff!

[Beegee]

My test results. I installed
mysql-standard-5.0.20-linux-x86_64-glibc23. Created a completely new
database and restored a backup from my 'old' django database into the
newly created MySql 5 instance.

Restarted Apache and everthing was up and running again. Great!

I checked with SHOW variables LIKE '%cache\_%'; the current settings.
query_cache_size was zero. So, I set it to: set global query_cache_size
= 20000000;

Then I browsed my website and watched this query: SHOW GLOBAL STATUS
LIKE 'Qcache\_%';

And now the Qcache_hits does increase. Great!

mysql> SHOW GLOBAL STATUS LIKE 'Qcache\_%';

+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 18626072 |
| Qcache_hits | 5774 |
| Qcache_inserts | 679 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 7 |
| Qcache_queries_in_cache | 641 |
| Qcache_total_blocks | 1293 |

+-------------------------+----------+
8 rows in set (0.00 sec)

So, it seems that the best thing to do to make use of the query cache
of MySQL is to use MySQL 5.0.

I want to thank everybody for helping me.

Berry

[Geert Vanderkelen]

Hi Berry,

Beegee wrote:
> My test results. I installed
> mysql-standard-5.0.20-linux-x86_64-glibc23. Created a completely new
> database and restored a backup from my 'old' django database into the
> newly created MySql 5 instance.
>
> Restarted Apache and everthing was up and running again. Great!

..

I think a 'Whooot!' would be well placed here :)

> So, it seems that the best thing to do to make use of the query cache
> of MySQL is to use MySQL 5.0.
>
> I want to thank everybody for helping me.

Great to hear all went great! But for reference, Query Cache is working
since MySQL 4.0. I don't know what exactly happened here, but apparently
the restriction of InnoDB within transactions not using Query Cache was
the culprit, which was 'solved' in MySQL 4.1.

Welcome to world of MySQL 5.0 ;)

Btw, try to use the latest Python MySQLdb module, which has was recently
being 'upgraded' to use more MySQL 5.0 stuff.