The difference between 1.9G and 2.2G table when create Index
Hello MySQL, I have a MYISAM table, which is about 2.2G and 19,000,000 records. When I create indexs for it, I couldn't finish after 4 hours. But when I reduce the records to 16,000,000, about 1.9G, the same index can be finished within 35 minutes. Can anyone explain it? Thanks in advance. I am using PIII 550, RedHat 8.0, with 1G Memory and IDE Hard Disk.The database is stored at ext3 file system. When I use show processlist for the 2.2G indexs, it tell me Copying to tmp table. James [EMAIL PROTECTED] _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The difference between 1.9G and 2.2G table when create Index
On Sat, Jul 05, 2003 at 01:03:17PM +, James Yang wrote: I have a MYISAM table, which is about 2.2G and 19,000,000 records. When I create indexs for it, I couldn't finish after 4 hours. You mean it was still running? Was there still disk or CPU activity after 4 hours? But when I reduce the records to 16,000,000, about 1.9G, the same index can be finished within 35 minutes. Can anyone explain it? Thanks in advance. That's much shorter than 4 hours! Maybe your disk was full when creating the 2.2G table's index? MySQL waits until disk space becomes available again. You wouldn't see much disk activity while it is waiting. Keep in mind that MySQL makes a copy of your table while it is adding the index. You'll need twice the space of your initial table, plus space for the additional index. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The difference between 1.9G and 2.2G table when create Index
On Sat, Jul 05, 2003 at 03:16:11PM +0200, Fred van Engen wrote: On Sat, Jul 05, 2003 at 01:03:17PM +, James Yang wrote: I have a MYISAM table, which is about 2.2G and 19,000,000 records. When I create indexs for it, I couldn't finish after 4 hours. You mean it was still running? Was there still disk or CPU activity after 4 hours? But when I reduce the records to 16,000,000, about 1.9G, the same index can be finished within 35 minutes. Can anyone explain it? Thanks in advance. That's much shorter than 4 hours! Maybe your disk was full when creating the 2.2G table's index? MySQL waits until disk space becomes available again. You wouldn't see much disk activity while it is waiting. Keep in mind that MySQL makes a copy of your table while it is adding the index. You'll need twice the space of your initial table, plus space for the additional index. Check this URL for details: http://www.mysql.com/doc/en/Full_disk.html There seems to be an exception to this rule when an index is added, so I may be wrong on this. The adding of an index happens in two phases and the docs are not clear to me about the first (copying) phase however. Your earlier comment indicates it is in the first phase still. I am using PIII 550, RedHat 8.0, with 1G Memory and IDE Hard Disk.The database is stored at ext3 file system. When I use show processlist for the 2.2G indexs, it tell me Copying to tmp table. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The difference between 1.9G and 2.2G table when create Index
Thanks guys, Actaully my Hard disk is 10G, just for one table. There is enough table space. I noticed when I created index for 1.9G table, at the first phase, mysql copy table, but mysql doesn't write to the tmp index file, I means the filesize of the tmp index didn't increase. When I create index for 2.2G table, the tmp index file is increased at the first phase. Any reason? James [EMAIL PROTECTED] I have a MYISAM table, which is about 2.2G and 19,000,000 records. When I create indexs for it, I couldn't finish after 4 hours. You mean it was still running? Was there still disk or CPU activity after 4 hours? But when I reduce the records to 16,000,000, about 1.9G, the same index can be finished within 35 minutes. Can anyone explain it? Thanks in advance. That's much shorter than 4 hours! Maybe your disk was full when creating the 2.2G table's index? MySQL waits until disk space becomes available again. You wouldn't see much disk activity while it is waiting. Keep in mind that MySQL makes a copy of your table while it is adding the index. You'll need twice the space of your initial table, plus space for the additional index. Check this URL for details: http://www.mysql.com/doc/en/Full_disk.html There seems to be an exception to this rule when an index is added, so I may be wrong on this. The adding of an index happens in two phases and the docs are not clear to me about the first (copying) phase however. Your earlier comment indicates it is in the first phase still. I am using PIII 550, RedHat 8.0, with 1G Memory and IDE Hard Disk.The database is stored at ext3 file system. When I use show processlist for the 2.2G indexs, it tell me Copying to tmp table. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
