List:General Discussion« Previous MessageNext Message »
From:Philip Lane Date:December 11 2004 8:39pm
Subject:Load Index Into Cache is broken! ... and a workaround
View as plain text  
Hi,
I saw a couple of posts about this and thought I'd join the chorus.  Running version 4.1.7
on a WinXP system.

I have a table "mindata" containing just over 1 million lines of price/volume data for
exchange-traded stocks.  It's for a .NET web application.  There are about 3500 different
stocks in the table.  Each has it's own ID number, and each has about 300 lines
corresponding to different dates:

mysql> describe mindata;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| ID        | int(5)     | YES  | MUL | NULL    |       |
| TimeStamp | date       | YES  | MUL | NULL    |       |
| Close     | float      | YES  |     | NULL    |       |
| Chg       | float      | YES  |     | NULL    |       |
| Open      | float      | YES  |     | NULL    |       |
| Volume    | bigint(20) | YES  |     | NULL    |       |
| Low       | float      | YES  |     | NULL    |       |
| High      | float      | YES  |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+

mysql> show index from mindata;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality
| Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| mindata |          1 | TimeStamp |            1 | TimeStamp   | A         |         320
|     NULL | NULL   | YES  | BTREE      |         |
| mindata |          1 | iID       |            1 | ID          | A         |        3538
|     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


When I first turn on the computer queries are disturbingly slow!  
mysql> Select * from mindata where ID = 2345;
+------+------------+-------+-------+-------+--------+-------+-------+
| ID   | TimeStamp  | Close | Chg   | Open  | Volume | Low   | High  |
+------+------------+-------+-------+-------+--------+-------+-------+
| 2345 | 2003-09-15 | 17.55 |  -0.1 | 17.64 |  19200 |  17.5 | 17.64 |
| 2345 | 2003-09-16 | 17.58 |  0.03 |  17.5 |   9300 | 17.49 | 17.65 |
| 2345 | 2003-09-17 |  17.5 | -0.08 | 17.53 |   6500 | 17.46 |  17.6 |
<snip>
321 rows in set (1.58 sec)

Does this make sense?  I could swear I've had much larger tables in the past that were
much faster.  Anyway...

Next I attempt to load the keys into the cache:
mysql> load index into cache mindata;
+---------------------+--------------+----------+----------+
| Table               | Op           | Msg_type | Msg_text |
+---------------------+--------------+----------+----------+
| megascanner.mindata | preload_keys | status   | OK       |
+---------------------+--------------+----------+----------+
1 row in set (0.00 sec)

Hmmm... It looks like it worked, but the index file on the disk is 34MB, obviously it
would take longer that 0.00 sec to load it.

Here's another query to see if the speed has improved (picking a different ID number this
time to avoid caching the results):
mysql> Select * from mindata where ID = 3345;
+------+------------+-------+-------+-------+---------+-------+-------+
| ID   | TimeStamp  | Close | Chg   | Open  | Volume  | Low   | High  |
+------+------------+-------+-------+-------+---------+-------+-------+
| 3345 | 2003-09-15 | 42.56 |  0.52 | 42.05 |  274800 | 42.02 | 42.63 |
| 3345 | 2003-09-16 | 42.89 |  0.33 |  42.2 |  253866 |  42.2 | 42.99 |
| 3345 | 2003-09-17 | 40.27 | -2.62 |  40.7 | 1582500 | 39.55 | 41.35 |
| 3345 | 2003-09-18 | 40.78 |  0.51 | 40.27 |  397300 | 40.01 | 40.83 |
<snip>
321 rows in set (1.63 sec)
The query speed hasn't improved.  It looks like Load Index Into Cache didn't do anything
at all.

But here's something that does get the indexes loaded into memory, apparently as a
side-effect:  I can disable, then re-enable the keys.  After doing this the queries are
blindly fast.  Unfortunately there's no telling how long things will remain in the cache.
 Sometimes it stays there all day, but sometimes not.  I don't see any way to get control
over it.  I can't find any clear explanation of what's going.

mysql> alter table mindata disable keys;alter table mindata enable keys;  (two queries
> on one line)
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (5.66 sec)

Now the queries are blindly fast:
mysql> Select * from mindata where ID = 1001;
+------+------------+-------+-------+-------+---------+-------+-------+
| ID   | TimeStamp  | Close | Chg   | Open  | Volume  | Low   | High  |
+------+------------+-------+-------+-------+---------+-------+-------+
| 1001 | 2003-09-15 | 28.72 |  -0.2 | 28.95 |  290600 |  28.6 | 29.03 |
| 1001 | 2003-09-16 | 28.85 |  0.13 | 28.72 |  454200 | 28.72 | 28.92 |
| 1001 | 2003-09-17 | 28.25 |  -0.6 | 28.96 |  511800 | 28.17 | 28.96 |
| 1001 | 2003-09-18 | 28.63 |   0.4 | 28.43 |  403900 | 28.35 | 28.73 |
| 1001 | 2003-09-19 | 28.61 | -0.06 | 28.67 |  343333 | 28.49 |  28.8 |
<snip>
321 rows in set (0.02 sec)

So I'm hoping somebody who knows the inner workings of MySql can explain why Load Index
Into Cache doesn't work, and why my inadvertent work-around does?  Should I report it as
a bug???

Best rgds
Phil 

Thread
Load Index Into Cache is broken! ... and a workaroundPhilip Lane11 Dec
  • Re: Load Index Into Cache is broken! ... and a workaroundDan Nelson11 Dec
    • Re: Load Index Into Cache is broken! ... and a workaroundKevin A. Burton11 Dec