MySQL Forums :: Performance :: enable keys, force sort, not keycache


Advanced Search

enable keys, force sort, not keycache
Posted by: P R ()
Date: August 24, 2009 08:06PM

ok, Ive googled this alot & cant find something that definitively works...
please help..

problem:
relatively large myisam db table (14million+ rows, approx 4GB on disk), cant seem to force 'repair with filesort' after enabling keys...
mysql 5.08 on linux...

method:
1) create empty table, specifying:
--> data directory='/some-path/with-over-150GB/free-space'
--> index directory='/some-other-path/with-over-150GB/free-space'
--> 2 indexes (neither unique)
----> 1 regular 3-column index, on 1 int(11) & 2 double columns
----> 1 spatial index on a 'point' column
2) 'alter table `x` disable keys;'
3) execute multiple 'LOAD DATA INFILE...' statements to load the data from different sources, 14,205,000 rows
4) 'alter table `x` enable keys;'

...and...
mysql *insists* on performing a (very slow) 'repair with keycache'

I have:
1) assured that I have plenty of disk space in my index & db directories
2) changed the tmpdir variable to another disk with over 200GB free space
3) pretty much 'maxed out' the relevant system variables:
bulk_insert_buffer_size=500M
tmp_table_size=150G
max_heap_table_size=3G
key_buffer_size=500M
sort_buffer_size=3G
join_buffer_size=500M
table_cache=150
myisam_sort_buffer_size=3G
myisam_max_sort_file_size=160G
myisam_repair_threads=4
-I increased these values almost to their practical limits pretty much out of pure desperation...nothing else was working...

I have 4GB RAM & alot of disk space (nothing else consuming memory on the box).
can someone please help me understand WHY mysql simply REFUSES to use filesort when rebuilding the indexes after my 'enabling keys' statement???
I know that I can resort to using using 'myisamchk -n', but that's a hacked work-around IMHO...
according to the docs & everything I've read, adjusting the system variables *SHOULD* cause mysql to preferably choose the filesort method over the keycache method...

so, what gives???



Edited 6 time(s). Last edit at 08/24/2009 08:12PM by P R.

Options: ReplyQuote


Subject Views Written By Posted
enable keys, force sort, not keycache 4086 P R 08/24/2009 08:06PM
Re: enable keys, force sort, not keycache 2593 Rick James 08/25/2009 11:58PM
Re: enable keys, force sort, not keycache 2351 P R 08/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.