MySQL Lists are EOL. Please join:

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



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