Hi, Your record_buffer and sort_buffer seem far too high. Don't forget MySQL could eat up ((sort_buffer + record_buffer) * max_connections + key_buffer) Mo of memory. Even with a max_connections set to 1, MySQL could eat in your case up to 7 Go of memory =)
----- Original Message ----- From: "Wai Lee" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 26, 2001 4:18 PM Subject: Indexing Problem > Hi all, > > Can someone tell me how to speed up the index creation??? > > I am trying to build an index for a 13,875,354 records(13 million) table > with 176,322 distinct vendor_id(VARCHAR(40)) in the table. > > the existing size of the transaction table: > transaction.MYD = 2,128,954,624 bytes > transaction.MYI = 1,096,510,464 bytes (there are already 5 indexes built in > this table) > transaciton.frm = 9,450 bytes > > I dig through the manual and changed any possible settings > > set-variable = max_heap_table_size=2000M > set-variable = key_buffer=2500M > set-variable = max_allowed_packet=1M > set-variable = table_cache=512 > set-variable = sort_buffer=2500M > set-variable = join_buffer=2500M > set-variable = record_buffer=2500M > set-variable = myisam_sort_buffer_size=2500M > set-variable = myisam_max_sort_file_size=2500M > set-variable = myisam_max_extra_sort_file_size=2500M > set-variable = thread_cache=8 > > > We are running mySQL in a 4G memory linux box, I believe the buffer size I > allocated to mySQL should cache the whole table. Instead, I am wrong, while > building a new index, I saw there was temporary files created for the > transaction table (the write disk activities were very source consuming) and > the single index creation took 4.5 hours to finished!!!!!!!!!!!!!!!!!!!! > Unbelievable > > Any suggestions will be greatly helpful!!! > > Lee > Zeborg Inc. > > > --------------------------------------------------------------------- > 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
