Hello,
After reading that article again, it made more sense, thanks!
However, I can only apply part of your technique - the PARTITIONs table schema. Progressive distance scans is not always applicable to my case as the sorting goes by either distance to target or by time of the table entry (the 'time' column from my table). Took your advice on adding uid to every index though - data will at one point grow larger than that buffer pool size.
>>Is there a "LIMIT 100" that you have failed to mention??
Oops! Sorry about that!
I am curious though why the "rows" from EXPLAIN EXTENDED output would show 4x the LIMIT argument. So, 400 in this case. Tried with other values as well and still 4x rows reported. Oh well... back to more important issues:
So I might employ mySQL PARTITIONS, or I might employ a space filling curve instead. But that's for later. These would not help much at the moment as innodb_buffer_pool_size seems to hold all of the data in the database. Tested this with iostat and seen how there were virtually no reads (< 1%) from disk, mostly writes.
Also there is now a second HDD so that /var/lib/mysql and /tmp partitions reside on separate physical disks. The main issue from which this whole thing started - getting lots of "SORT ABORTED" due to /tmp filling up 2GB fast (due to sorting) is now gone - but, still not happy about it as I have to keep a sort_buffer_size of 64M in order to get a decent test result - say, 50 users / second concurrency.
Everywere I read about it people insisted of never ever setting it above 256K, but if I did that then my tests would just fail in an instant. So I was thinking about setting it to an intermediary value of 8M. Common sense tells me it should be fine (?). Thing is at 8M I still get a spike somewhere during the load test (without a definite moment for its manifestation measured from the test's starting point). This is how the spike looks:
http://imageshack.us/photo/my-images/96/motn.jpg/
Spike is at 3500 - 4000 seconds, don't mind the later and higher spikes. That is the response time graph for "transactions". tr_geoloc_get encompasses all the *FETCH* queries from the link below while tr_geoloc_set comprises just the INSERT query from the link below. These are tsung transactions (so, not SQL transactions, but simulated questions against a chat server that ulitmately uses MySQL as backend).
Queries ran during the load tests were:
http://pastebin.com/E9wttkHE
HDD activity seemed normal, Sort_merge_passes was at 0, I did a manual query while in a spike time window and it came back just slightly delayed, taking about 0.7 seconds instead of the usual 0.3. That's like 2.3x but won't justify the reponse time increase of more than 1000x from the graph in the link above.
So.. any idea to why this happens ? OK to up sort_buffer_size to 64M (this is a dedicated mySQL instance; only session will be from chat server) ?
My guess is that for this kind of test (high hysteresis), even that slight <1% disk read accesses is enough to cause performance to dwindle, and that is why not allowing the sorts to use HDD at all do help.
Thank you!
