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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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: [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]
