MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Chris Fossenier Date:February 11 2004 11:57pm
Subject:RE: Indexing Woes
View as plain text  
Okay,

So I should be good with a 30GB setting. My temp dir is on a volume with
over 300GB of free space.

What about the other settings?

Has anyone on this list actually dealt with a database of this magnitude and
MySQL? Most people I talk to are running pretty small databases and usually
nothing over 20mill for records.

Chris.



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


These files will be created in the /tmp directory or where ever your temp 
directory was declared. One temp table will be created for each ALTER or 
CREATE INDEX statement.

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

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


> None of my individual tables are larger than 12GB, however, I have no
idea
> if MySQL creates a separate TMP file for each indexing job or if it
creates
> a new one for each instance.

> Also, where would it create this file? In the tmp dir?

> Chris.

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


> 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