List:General Discussion« Previous MessageNext Message »
From:<vpendleton Date:February 11 2004 11:12pm
Subject:RE: Indexing Woes
View as plain text  
If I am reading your parameter correctly, MySQL will limit the size of 
the temporary file created to 30GB. If the file exceeds this limit, then 
MySQL will use key cache to create the index. What is the footprint of 
your MYD and MYI files?

>>>>>>>>>>>>>>>>>> Original Message
> <<<<<<<<<<<<<<<<<<

On 2/11/04, 4:54:21 PM, Chris Fossenier <chris@stripped> wrote 
regarding RE: Indexing Woes:


> Can you provide a better explanation of these variables? I have yet to 
find
> a thorough explanation of each one of them.

> My myisam_max_sort_file_size = 30000M

> I am running the indexes with an "ALTER TABLE ENABLE KEYS" command after 
I
> load the data into the tables.

> Chris.

> -----Original Message-----
> From: vpendleton@stripped [mailto:vpendleton@stripped]
> Sent: Wednesday, February 11, 2004 4:50 PM
> To: Chris Fossenier
> Cc: 'Peter Zaitsev'; 'MySQL List'
> Subject: RE: Indexing Woes


> Performing your indexing in one batch will create a temp table only once
> as opposed to n-times.
> What is the current value of your myisam_max_sort_file_size?

> >>>>>>>>>>>>>>>>>> Original
> Message <<<<<<<<<<<<<<<<<<

> On 2/11/04, 2:53:34 PM, Chris Fossenier <chris@stripped> wrote
> regarding RE: Indexing Woes:


> > I have checked these...but I don't know what to set them too. Can I
> > get
> more
> > input? Should I only run 1 index at a time? I have 2 machines (both
> > quad Xeon)..one is running a singel indexing job, the other is running
> > 6 jobs.
> It
> > looks like they will end up completing in the same amount of total
> > time (i.e. If I create them 1 by one on the first computer it will
> > take just
> as
> > long as creating them concurrently on the second computer).

> > Can I tell MySQL to use more CPU or I/O bandwidth to my disks?

> > myisam_max_sort_file_size
> >  - what should it be set to.
> >  - what does it mean?
> >  - Does this relate to any paths that I set with my config?
> >  - Does this represent a temporary file created on my system?

> > myisam_max_extra_sort_file_size
> >  - what should it be set to.
> >  - what does it mean?
> >  - Does this relate to any paths that I set with my config?
> >  - Does this represent a temporary file created on my system?

> > myisam_sort_buffer
> >  - I have 8GB of RAM, what should I set this to?
> >  - my machine is dedicated to MySQL

> > Thanks.

> > Chris.


> > -----Original Message-----
> > From: Peter Zaitsev [mailto:peter@stripped]
> > Sent: Wednesday, February 11, 2004 2:03 PM
> > To: Chris Fossenier
> > Cc: 'MySQL List'
> > Subject: Re: Indexing Woes


> > On Wed, 2004-02-11 at 09:29, Chris Fossenier wrote:
> > > Hello,
> > >
> > > I had a question about indexing a while back and everyone screamed
> > > "normalize"!!
> > >
> > > Well...I've normalized much as I'm going to, and at most I have 3
> > > indexes on any one table. My database has 120 million records in it
> > > and the index creation is taking a ridiculous amount of time. I can
> > > create the same indexes on MS SQL or Oracle in a fraction (a small
> > > fraction) of the time.
> > >
> > > Any tips? If I look at the PROCESSLIST, I can see that MySQL is
> > > using Key Cache instead of File Sort. I've read that File Sort is
> > > faster but have no idea how to force MySQL to use this method.
> > >
> > > When MySQL indexes, does it actually create a copy of the table
> > > first (same size as original .MYD) and then prune it back to a
> > > smaller size for the .MYI? The reason I ask is because one table
> > > that I'm indexing has been running for a long time and the .MYI is
> > > only 3GB and the .MYD is 12GB....not a good sign.
> > >

> > Check myisam_max_sort_file_size,  myisam_max_extra_sort_file_size and
> > myisam_sort_buffer  description and values.

> > You shall be able to make Repair happening by Sort unless it is unique
> > index, which is much faster.


> > --
> > Peter Zaitsev, Senior Support Engineer
> > MySQL AB, www.mysql.com

> > Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL)
> >   http://www.mysql.com/uc2004/


> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1




> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
Thread
Indexing WoesChris Fossenier11 Feb
  • Re: Indexing Woesvpendleton11 Feb
    • RE: Indexing WoesChris Fossenier11 Feb
  • Re: Indexing WoesPeter Zaitsev11 Feb
    • RE: Indexing WoesChris Fossenier11 Feb
      • RE: Indexing Woesvpendleton11 Feb
        • RE: Indexing WoesChris Fossenier11 Feb
          • RE: Indexing Woesvpendleton12 Feb
            • RE: Indexing WoesChris Fossenier12 Feb
              • RE: Indexing Woesvpendleton12 Feb
                • RE: Indexing WoesChris Fossenier12 Feb