M
M
Maxim2016-09-12 20:52:45
caching
Maxim, 2016-09-12 20:52:45

How to debug queries in query-cache?

Hey!
Noticed this interesting thing.
Input data:

5.6.30-76.3-log Percona Server (GPL), Release 76.3, Revision 3850db5

pymysqlpool==0.4.1
API in python and, of course, mysql-client.
The task is to configure the query cache.
The problem is this.
The API does something like:
SELECT something FROM somethings WHERE something;
When I first (actually second) make such a request through mysql-client
SET AUTOCOMMIT = 0;
SELECT something FROM somethings WHERE something;

And then the same thing only with AUTOCOMMIT = 1.
Then both requests are taken from the cache.
After I make exactly the same request, through an API call, cache-hit is gone.
And what's most interesting.
If, after jerking, the APIs return and repeat the same requests through mysql-client, then only those before which I set AUTOCOMMIT to 1.
A piece of API code get into the cache.
from pymysqlpool import pool
import pymysql
...
...
...
if kwargs.has_key('max_active_connections'):
    self.ConnectionPool.maxActiveConnections = kwargs.get('max_active_connections')

def getConnection(self):
    return self.ConnectionPool.GetConnection()

def query(self, query):
    connection = self.getConnection()
    cursor = connection.getCursor()
    rresult = None
        try:
           cursor.execute(query)

...
...
...

PS
C man dev.mysql.com/doc/refman/5.6/en/query-cache-operat... got acquainted.
The problem is reproduced on 5.6 different revisions.
pymysqlpool installs AUTOCOMMIT = 0by default.
For the purity of the experiment, although this is not important, I tried to make requests without the ConnectionPool.
Well, the most important question is how to find out why some requests get into the cache, and some do not.
Ideas are over. Tell me where to dig.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question