We are currently working with a table containing ~650MB of data in
700,000 rows. In attempting to select from this, we would typically find
long holdups with process states listed as creating temporary tables.
By using SQL_SMALL_RESULT we were able to greatly reduce the time taken
for select operations; all seemed merry, but we then began accumulating
apparently hung processes in a 'Sending data' state. Looking for
solutions to this, I came across the 'Locked tables while "Sending
data"' thread of March last year -
(begins: http://ep33.tp4.ruhr-uni-bochum.de/mlists/MySQL/Mar.2000/1029.html )
- which eventually led to the creation of the SQL_BUFFER_RESULT option.
However, using both SQL_SMALL_RESULT and SQL_BUFFER_RESULT brings us
back to holdups creating temporary tables (it seems the reasoning in the
aforementioned thread was that results are buffered when using temp
tables, and this is a nicer way of forcing that than various hacks).
Which is all very well, if you're not handling quite so much data.
What we really need is for the query results to be buffered for sending
to the client *without* forcing a temporary table.
But, hang on - from
http://ep33.tp4.ruhr-uni-bochum.de/mlists/MySQL/Mar.2000/1445.html , it
shouldn't be using one anyway if SQL_SMALL_RESULT is set.
Perhaps we should be looking instead at just killing off select
processes after a certain timeout; unless I've missed it there's no
option for this, so I was considering writing a daemon client to poll
the processlist and kill off the geriatrics. They then sit there in
state 'Killed' rather than 'Sending data' though. May be harmless either
way, of course.
Anyway, this is as far as I've got for now with investigating - advice
at this point would be greatly appreciated.
Thanks in advance,
Martin
---------------------------------------------------------------------
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
