Re: enable keys, force sort, not keycache
Posted by:
P R
Date: August 26, 2009 02:57AM
well, Ive tried just about everything, both drastically lowering & raising each & every variable... nothing has worked...
the most ridiculous thing is that I even completely turned off the keycache, by setting myisam_key_buffer_size to zero...the docs claimed that this would force 'disk-only' operations & totally disable the keycache...yet, low & behold...the 'repair with keycache' still appeared, which made absolutely no sense whatsoever...
the problem column & index are a 'spatial point' column & 'spatial index'...I have no freakin' clue how to estimate the theoretical length of such a column based on row count...the 'point' column is like a blob that stores 2 double values (lat&lng) in a mysql internal binary format, yet I dont know how the index optimizer/rebuilder interprets this column&index as; whether it sees it as a giant, size-less, endless blob, or whether it knows how to calculate its working size based on row count. yet there's no doubt that the total size of all table and index data (including tmp tables&files) can easily fit on disk, since its way under 10GB and I have almost a freakin' TB available...I dont know how,where,or why mysql is getting the notion that 'there's not enough space available, lets do keycache instead...'
btw, I tried switching to 'myisamchk -n' in order to rebuild the indexes using a forced filesort, but this also doesnt work; apparently, myisamchk doesnt work with spatial indexes, because it keeps creating a useless, broken index & also 'corrupting'/trashing both my table & index..fast, yes, but worthless in my case (also dont know if this is a bug, or by design: the fact that myisamchk can't handle spatial indexes).
Im getting extremely frustrated by this, and its even more frustrating to know that this issue/question about 'how the heck do I know if mysql will perform a keycache or filesort repair?" has been showing up in my google results dating back many years to mysql version 3...its honestly ridiculous that the developers havent provided a mechanism to allow the user to over-ride & force a file sort. :(
And, to all the mysql team/developers, please dont say "just set the system variables, cross your fingers, wait&see...and if it doesnt work, just say 'oops!' and kill/hack the 'repair with keycache' process to death & start over..." thats simply not an acceptable answer for tables with row counts that are in the millions, which take a lot of time&effort in order to load and to monitor while their being populated.
Im on the verge of diving into the mysql source code myself, searching for the routine(s)/section of code relating to 'repair with keycache', and permanently nuking it/hacking it out for good...its seriously retarded that Ive had to waste so much time on such a trivial problem that should've already been fixed a long time ago by the mysql team...