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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > > 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/[EMAIL PROTECTED] > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
