Re: LOAD DATA INFILE performance testing
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa Jeff Tanner writes: I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( valueCHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique values time in seconds 100.05 100 0.05 1K0.10 10K 0.75 100K10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA Sinisa No, but you could crate a table without index and add primary key Sinisa after loading of data. Actually MySQL is already doing this when you do LOAD DATA INFILE to a table without any rows. Jeff, what you can do is to start mysqld with a much bigger value for 'myisam_sort_buffer_size' and see if this helps. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA INFILE performance testing
I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( value CHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique values time in seconds 10 0.05 100 0.05 1K 0.10 10K 0.75 100K 10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE performance testing
Jeff Tanner writes: I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( valueCHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique valuestime in seconds 10 0.05 1000.05 1K 0.10 10K0.75 100K 10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA No, but you could crate a table without index and add primary key after loading of data. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE performance testing
DELAY_KEY_WRITE=1 Tim. On Mon, Aug 06, 2001 at 07:36:17AM -0700, Jeff Tanner wrote: I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( valueCHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique valuestime in seconds 10 0.05 1000.05 1K 0.10 10K0.75 100K 10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LOAD DATA INFILE performance testing
Thanks Tim for your reply But I am unfamiliar with DELAY_KEY_WRITE. I looked at both Tuning Server Parameters and LOAD DATA INFILE sections. Jeff Tanner Viathan Seattle, WA -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Monday, August 06, 2001 9:00 AM To: Jeff Tanner Cc: '[EMAIL PROTECTED]' Subject: Re: LOAD DATA INFILE performance testing DELAY_KEY_WRITE=1 Tim. On Mon, Aug 06, 2001 at 07:36:17AM -0700, Jeff Tanner wrote: I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( valueCHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique valuestime in seconds 10 0.05 1000.05 1K 0.10 10K0.75 100K 10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LOAD DATA INFILE performance testing
Thanks Sinisa for your reply, Interesting alterative. I tried: a) ALTER TABLE foo DROP PRIMARY KEY b) LOAD DATA INFILE file INTO TABLE foo c) ALTER TABLE foo ADD PRIMARY KEY (value) However, there was no noticable improvement in performance. Even though loading the file into a table sans primary key was very much faster, but adding back the primary key negated any performance improvement than if just loading the file into a table with a primary key. Jeff Tanner Viathan Seattle, WA -Original Message- From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] Sent: Monday, August 06, 2001 8:33 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: LOAD DATA INFILE performance testing Jeff Tanner writes: I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( valueCHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique valuestime in seconds 10 0.05 1000.05 1K 0.10 10K0.75 100K 10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA No, but you could crate a table without index and add primary key after loading of data. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LOAD DATA INFILE performance testing
DELAY_KEY_WRITE=ON by default Jeff -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Monday, August 06, 2001 9:00 AM To: Jeff Tanner Cc: '[EMAIL PROTECTED]' Subject: Re: LOAD DATA INFILE performance testing DELAY_KEY_WRITE=1 Tim. On Mon, Aug 06, 2001 at 07:36:17AM -0700, Jeff Tanner wrote: I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( valueCHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique valuestime in seconds 10 0.05 1000.05 1K 0.10 10K0.75 100K 10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
