when using percona 5.6 with tokudb and using a query which looks like this:
SELECT ID,COL1,COL2,COL3,COL4
FROM TABLE
WHERE (ID=1 OR ID=201 OR ID=221 OR ID=1023) <= for about 1000 IDs
AND COL1 IN (1,76,34,23)
the primary key for the table consists of 5 columns of which ID is the first one and there is another index only for COL1
when running this query it can run for 3 or 4 seconds with no increase in tablecache: miss and the following run will take 20 milliseconds
with profiling=1 we can see that the time is spent in the statistics stage.
i read that this could mean that mysql is disk bound but i cannot see what access to disk is causing the delay .
can you explain in more detail what is happening during the statistics stage?
how can i check what is the disk access which is causing the dellay or maybe io is not the reason?
because this table is very large (almost 1 TB) i considered that maybe tokudb decompresses the pages during the statistics stage, could this be the reason for the delay?
any help would be appreciated.
Ishay Wayner