MySQL Forums
Forum List  »  Performance

Re: enable keys, force sort, not keycache
Posted by: Rick James
Date: August 25, 2009 11:58PM

I'd like to know the answer, too.

"The maximum allowable setting for myisam_sort_buffer_size is 4GB. As of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). "

"The maximum allowable setting for sort_buffer_size is 4GB. As of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). "

"If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk. "

"For MyISAM tables, you can speed up the index re-creation operation (which is the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value. "

"myisam_sort_buffer_size
The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE."

"sort_buffer_size
Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations."

"myisam_max_sort_file_size
The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.
The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. "

"Unique indexes are not build by sort (use large key_buffer_size)"

Tentative conclusions...

* sort_buffer_size is irrelevant, and might get in the way; decrease it.

* myisam_sort_buffer_size can be more than 3GB, but not with your hardware.

* Your parameters are threatening to cause swapping -- have you tuned them too high? Swapping would be much worse than having lower settings.

* Do you have TEXT or BLOB fields? If so, the estimate of how big the sort file will become may be artificially inflated, and blow out myisam_max_sort_file_size; suggest you set that value very high--even bigger than your disk space. 14M rows with just one TEXT field could pessimistically estimated at 1TB. MEDIUMTEXT/LONGTEXT would be even worse.

Please let me know if you solve the problem.

Options: ReplyQuote


Subject
Views
Written By
Posted
5206
P R
August 24, 2009 08:06PM
Re: enable keys, force sort, not keycache
2890
August 25, 2009 11:58PM
2626
P R
August 26, 2009 02:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.