List:General Discussion« Previous MessageNext Message »
From:Chris Fossenier Date:February 11 2004 10:54pm
Subject:RE: Indexing Woes
View as plain text  
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