List:General Discussion« Previous MessageNext Message »
From:Chris Nolan Date:February 21 2004 3:50am
Subject:Re: Help! How to handle Massive index file???
View as plain text  
Hmm...if there's lots of thrashing, it might be to do with 
fragmentation. Have you tried running OPTIMIZE TABLE on the table in 
question?

Does anyone on the list have anything to say about putting the MYD and 
MYI files on seperate disks or using RAID MyISAM tables??

Regards,

Chris

Eric B. wrote:

>Help!
>
>Okay - so I've been working around on my indexing of a table.  My table is
>currently over 5M rows  (close to 500Mb) and growing at a quick rate.  In
>order to handle different types of queries, I am forced to create multiple
>indexes for the table.  But by doing so, I end up with an MYI index file of
>over 2Gig!!
>
>Now the problem is that my query is still taking way to long to execute (ie:
>30 secs).  If I try an "Explain" on the query, it tells me that it only
>needs to examine 30 000 rows (which is not bad considering there are over 5
>million in the table), however, when I actually execute it, I can see the
>disk thrashing an enormous amount.  Is there any way to know if the thrasing
>because it is actually reading through the DB and retrieving the rows or
>trying to read through this gigantic index file?
>
>Is there anything I can do to help optimize this?  Loading a 2G index file
>into RAM doesn't seem realistic since this index file will grow with time,
>and I can't imagine needing to constantly add more RAM to handle a bigger
>and bigger index file.
>
>Is there any way to determine what the I/O is due to?  Is the thrashing
>MySQL reading the DB or reading the index file?  Or is there anything else I
>can do to help optimize my queries further?  I'm using MyISAM tables, if it
>makes any difference....
>
>Thanks for any insight!
>
>Eric
>
>
>
>
>
>
>  
>


Thread
Help! How to handle Massive index file???Eric B.21 Feb
  • Re: Help! How to handle Massive index file???Chris Nolan21 Feb
  • Re: Help! How to handle Massive index file???Eric B.21 Feb
    • Re: Help! How to handle Massive index file???Chris Nolan21 Feb
  • Re: Help! How to handle Massive index file???Eric B.21 Feb
    • Re: Help! How to handle Massive index file???Sasha Pachev23 Feb
  • Re: Help! How to handle Massive index file???Eric B.24 Feb