List:General Discussion« Previous MessageNext Message »
From:Kevin A. Burton Date:December 11 2004 9:59pm
Subject:Re: Load Index Into Cache is broken! ... and a workaround
View as plain text  
Dan Nelson wrote:

>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".
>  
>
Just a note... your query times here can't be trusted. You need to do

SELECT SQL_NO_CACHE * FROM ...

Because if you don't the second time you run the query it will take 
0.0ms to return it.

Also loading the cache into memory won't speed up the SELECT * portion 
becuase it needs to read rows from disk and disk is evil.

The second time you run the query the filesystem buffer cache will 
probably have the blocks in memory. Note sure about WinXP as this is a 
red flag. But Linux will certainly do the right thing.

If you're still seeing performance hits try to do an OPTIMIZE TABLE to 
get contiguous blocks on disk.

If the table is small enough (32M did you say?) just use a HEAP table 
and load it all in memory. This will CERTAINLY keep your index in memory 
and all the rows will be in memory too so you'll get constant fast times...

Read up on memory tables... there are some gotchas you need to be 
careful of.. (like the data won't be there when you restart).

Kevin

-- 

Use Rojo (RSS/Atom aggregator).  Visit http://rojo.com. Ask me for an 
invite!  Also see irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

If you're interested in RSS, Weblogs, Social Networking, etc... then you 
should work for Rojo!  If you recommend someone and we hire them you'll 
get a free iPod!
    
Kevin A. Burton, Location - San Francisco, CA
       AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412


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