List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 11 2004 9:37pm
Subject:Re: Load Index Into Cache is broken! ... and a workaround
View as plain text  
In the last episode (Dec 11), Philip Lane said:
> 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:
> 
> 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)

I'd say 95% of that time is spent reading the records, not reading the
index.  Try timing "select count(*) from mindata where ID = 2345".
 
> 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)        

I can't explain this.  Could be a bug.  Happens on my 4.1.7/FreeBSD
server too.

> 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.

ALTER TABLE commands rebuild the entire table, and because your table
is so small, both the index and your data are still in your
filesystem's cache after the rebuild is finished.
 
> 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???

Another workaround would be to run a couple queries that pull the
entire table into memory.  Something like "select * from mindata having
1=0", which will return no rows but force it to read each record,
should run pretty quick.

-- 
	Dan Nelson
	dnelson@stripped
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