RE: Having success with 20 million rows
We have a large database with two tables of around 31.5 million rows each. When I did an alter table, again to modify a column, it took 17 hours. I noticed that the table was still readable and writes were blocked. However, during the ALTER table MySQL repaired the table using a keysort. This seemed to lock the table for reading as well. As you can't know before hand that MySQL will try a repair, don't count on the fact that the table will be available. Robin -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: 10 May 2001 05:00 To: Christian Jaeger; David J. Potter; [EMAIL PROTECTED] Subject: Re: Having success with 20 million rows At 11:00 PM +0200 5/9/01, Christian Jaeger wrote: Was the table inaccessible during these 4 days? Or is mysql able to still write to the table while it's being altered? Those are not mutually exclusive possibilities. :-) According to the manual, reads can be done on the original table while the new table is being constructed. Updates are stalled and then applied to the new table after the alteration. http://www.mysql.com/doc/A/L/ALTER_TABLE.html Christian. At 12:08 Uhr -0700 9.5.2001, David J. Potter wrote: occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column) - 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 -- Paul DuBois, [EMAIL PROTECTED] - 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: Having success with 20 million rows
I would be very intested in finding out more about the hardware being used here, as well as load on the server (queries/sec, etc.), and maybe the amount of data (Megabytes, Gigabytes, Terabytes). Twenty million records is a lot but that number in itself may not give the application the credit that it desrves. [EMAIL PROTECTED] -Original Message- From: mysql-digest-help [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 3:09 PM To: mysql Cc: david100 Subject: Having success with 20 million rows Just wanted to say that we are having success using MySql. Even though our tables are large, over 20 million rows, selects, updates and inserts occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column), but that is OK since we don't change columns often. Overall MySql is turning to be a great fast database. David - 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: Having success with 20 million rows
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: jueves, 10 de mayo de 2001 6:00 To: Christian Jaeger; David J. Potter; [EMAIL PROTECTED] Subject: Re: Having success with 20 million rows At 11:00 PM +0200 5/9/01, Christian Jaeger wrote: Was the table inaccessible during these 4 days? Or is mysql able to still write to the table while it's being altered? Those are not mutually exclusive possibilities. :-) According to the manual, reads can be done on the original table while the new table is being constructed. Updates are stalled and then applied to the new table after the alteration. http://www.mysql.com/doc/A/L/ALTER_TABLE.html Yep, and that stalling is (IMHO) one of the primary problems with MyISAM ISAM tables. Ofcourse it's possible to design oneself out of these problems, but the only real good solution is locking on row level rather than the whole table. InnoDB tables has row level locking AFAIR. My databases have around 52 insert requests per second, so even a small alter build up a huge amout of pending requests. Regarding the time spent. 4 days seems to be alot to me. what kind of hardware is it running on ? I have MySQL databases with more than 100 million rows and they take only about 4 hours to alter on a 700 MHz, 8 MB cache, EV67 Alpha (with lots of RAM). Even though I run on SMP machines with lots of CPUs, altering is a singlethreaded task (or so it seems), so I don't have any benefit out of it. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
Having success with 20 million rows
Just wanted to say that we are having success using MySql. Even though our tables are large, over 20 million rows, selects, updates and inserts occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column), but that is OK since we don't change columns often. Overall MySql is turning to be a great fast database. David
RE: Having success with 20 million rows
Well normally its not the best to add columns after the fact if the design met the specs. But this is not always the case. As we all now the specs change about every hour. -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 2:49 PM To: [EMAIL PROTECTED] Subject: Re: Having success with 20 million rows FOUR days!?!? THAT'S patience. But I'm glad to hear the success story. j- k On Wednesday 09 May 2001 11:08, David J. Potter wrote: Just wanted to say that we are having success using MySql. Even though our tables are large, over 20 million rows, selects, updates and inserts occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column), but that is OK since we don't change columns often. Overall MySql is turning to be a great fast database. David -- Joshua Kugler Associated Students of the University of Alaska Fairbanks Information Services Director [EMAIL PROTECTED] 907-474-7601 - 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: Having success with 20 million rows
I take that back the word not the best shouldnt be used. I should of said in a perfect world we shouldnt have to add columns. -Original Message- From: Robert Henkel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 2:56 PM To: 'Joshua J. Kugler'; [EMAIL PROTECTED] Subject: RE: Having success with 20 million rows Well normally its not the best to add columns after the fact if the design met the specs. But this is not always the case. As we all now the specs change about every hour. -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 2:49 PM To: [EMAIL PROTECTED] Subject: Re: Having success with 20 million rows FOUR days!?!? THAT'S patience. But I'm glad to hear the success story. j- k On Wednesday 09 May 2001 11:08, David J. Potter wrote: Just wanted to say that we are having success using MySql. Even though our tables are large, over 20 million rows, selects, updates and inserts occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column), but that is OK since we don't change columns often. Overall MySql is turning to be a great fast database. David -- Joshua Kugler Associated Students of the University of Alaska Fairbanks Information Services Director [EMAIL PROTECTED] 907-474-7601 - 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 - 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: Having success with 20 million rows
Can you put your my.cnf for windows and linux? Will be nice to compare you setting again others. TIA, Ernesto Vargas - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 3:49 PM Subject: Re: Having success with 20 million rows FOUR days!?!? THAT'S patience. But I'm glad to hear the success story. j- k On Wednesday 09 May 2001 11:08, David J. Potter wrote: Just wanted to say that we are having success using MySql. Even though our tables are large, over 20 million rows, selects, updates and inserts occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column), but that is OK since we don't change columns often. Overall MySql is turning to be a great fast database. David -- Joshua Kugler Associated Students of the University of Alaska Fairbanks Information Services Director [EMAIL PROTECTED] 907-474-7601 - 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 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.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: Having success with 20 million rows
4 days! Perhaps it would be best to create a new table and transfer data to it. When you have almost everything transferred, lock the old table, sync the tables and then replace the old table with the new. Just a thought... --zak - Original Message - From: Robert Henkel [EMAIL PROTECTED] To: Robert Henkel [EMAIL PROTECTED]; 'Joshua J. Kugler' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 2:03 PM Subject: RE: Having success with 20 million rows I take that back the word not the best shouldnt be used. I should of said in a perfect world we shouldnt have to add columns. -Original Message- From: Robert Henkel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 2:56 PM To: 'Joshua J. Kugler'; [EMAIL PROTECTED] Subject: RE: Having success with 20 million rows Well normally its not the best to add columns after the fact if the design met the specs. But this is not always the case. As we all now the specs change about every hour. -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 2:49 PM To: [EMAIL PROTECTED] Subject: Re: Having success with 20 million rows FOUR days!?!? THAT'S patience. But I'm glad to hear the success story. j- k On Wednesday 09 May 2001 11:08, David J. Potter wrote: Just wanted to say that we are having success using MySql. Even though our tables are large, over 20 million rows, selects, updates and inserts occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column), but that is OK since we don't change columns often. Overall MySql is turning to be a great fast database. David -- Joshua Kugler Associated Students of the University of Alaska Fairbanks Information Services Director [EMAIL PROTECTED] 907-474-7601 - 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 - 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: Having success with 20 million rows
Was the table inaccessible during these 4 days? Or is mysql able to still write to the table while it's being altered? Christian. At 12:08 Uhr -0700 9.5.2001, David J. Potter wrote: occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column) - 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: Having success with 20 million rows
At 11:00 PM +0200 5/9/01, Christian Jaeger wrote: Was the table inaccessible during these 4 days? Or is mysql able to still write to the table while it's being altered? Those are not mutually exclusive possibilities. :-) According to the manual, reads can be done on the original table while the new table is being constructed. Updates are stalled and then applied to the new table after the alteration. http://www.mysql.com/doc/A/L/ALTER_TABLE.html Christian. At 12:08 Uhr -0700 9.5.2001, David J. Potter wrote: occur very fast. We use both Windows and Linux. Adding a column is the only action that is slow (it took 4 days once to add a column) - 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 -- Paul DuBois, [EMAIL PROTECTED] - 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
