List:General Discussion« Previous MessageNext Message »
From:Eric B. Date:February 21 2004 3:53am
Subject:Re: Help! How to handle Massive index file???
View as plain text  
Sorry - forgot to mention it.  I've already tried both an OPTIMIZE TABLE and
ANALYZE TABLE to try to improve performance, but with no result.

Any other ideas?

Thanks,

Eric


"Chris Nolan" <chris@stripped> wrote in message
news:4036D588.4040209@ style="color:#666">stripped...
> 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
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
>
>



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