Greetings to all, and thanks for the excellent resource! I have a question regarding indexing large tables (150M+ rows, 2.6G).
The tables in question have a format like this: word_id mediumint unsigned doc_id mediumint unsigned Our indexes are as follows: PRIMARY KEY (word_id, doc_id) INDEX (doc_id) The heart of the question is this: When calling ALTER IGNORE TABLE doc_word ADD PRIMARY KEY(doc_id, word_id), ADD INDEX(doc_id), MySQL proceeds to create a working copy of the table. This process takes over an hour to perform. During this time, disk I/O for the rest of the database (live) reaches a bottleneck, and slows to an unacceptable crawl. Once the copy has been created, MySQL is able to do the actual index build very quickly and efficiently. This process must occur three times daily. A) MySQL creates these temporary tables in the same directory as the original datafile. Is there a way to cause it to use an alternate directory (i.e., on a separate mounted disk)? B) Is there a way to "nice" this process in such a way that the amount of I/O it consumes in performing the copy is restricted to a manageable level so that other requests to the disks can be served in a timely fashion? C) Would abandoning ext3 in favor of ext2 create a substantial difference? D) We're reluctant to upgrade to 4.0 at this point, but were we do so, are there any significant gains in this situation? E) The ALTER TABLE query is performed using perl DBI. Is there a lower level call available which would improve performance? F) Any other ideas or suggestions? The system in question has the following setup: Dual Xeon 2.8, 4G RAM, 2 x 146GB U160 SCSI (10,000 RPM) on RAID 1 (hardware). Redhat 8.0, 2.4.18 kernel, using ext3 fs. MySQL 3.23.56, with myisam tables. Relevant variables: myisam_sort_buffer_size=512M tmp_table_size=128M This is a master, so bin_log is on Thanks in advance for your help, and please keep up the excellent work! Best, Brendan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
