Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues.

It seems to be a good option to use.

However I cannot seem to find any disadvantages about it. Is it an option which should be used most of the time?

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

SQL_BUFFER_RESULT creates a temporary table on the server for every result set. This is not a temporary table like CREATE TEMPORARY TABLE - it is an implicit temporary table as would be created when using a GROUP BY clause or with a subquery. As such, all the same rules apply.

First, let's talk the problem that SQL_BUFFER_RESULT is designed to solve:

When the client requests data from the server, until the entire result set has been transmitted to the client the query is still "running" and some locks may still be held. While the data is being transmitted, it will appear in the Sending data state. It is library dependent on whether the client just gets all the data at once when the query is executed, or if it trickles in as you select rows, but an example of a problem is as follows:

resultset = conn["SELECT * FROM bigtable"]
resultset.each do |row|
  data[:value] = row[:value]
  sleep 10 # do something expensive here
end

Obviously contrived, but in the case above if there are 1000 rows, the query will still be actively running for 10,000 seconds. That may seem far fetched, but a lot of applications have "think time" in-between fetching each row because they do some processing. This is "a very bad thing to do." Another case where this type of trickle effect could occur is with a large result set over a slow connection. Ultimately, the problem is the trickling of data to the client causing the query to remain in an active state. SQL_BUFFER_RESULT resolves this problem by buffering the result into a temporary table first, which makes the query end faster, thus releasing all of its locks (what locks?). The result set is then fed to the client from the temporary table rather than the query itself.

That sounds great!

But...

  • Temporary tables take up memory and other resources on the server. Lots of temporary tables == lots of resources.
  • Tables that exceed the minimum of tmp_table_size or max_heap_table_size will end up being converted to on-disk temporary tables, which means additional I/O. It also means that your queries take longer, as once the size of the MEMORY table reaches tmp_table_size, it is then converted to an on-disk MyISAM table.
  • BLOB/TEXT fields cannot be stored as in-memory temporary tables (the MEMORY storage engine does not support them), thus will always be created on disk.
  • creating temporary tables is expensive, especially when they are on-disk.
  • Consider that one of the first things we try to optimize away when looking at an explain plan is "Using temporary." SQL_BUFFER_RESULT literally makes every query include "Using temporary", (nearly) all the time. My little bit of testing showed that there were cases where MySQL did not use a temporary table even with this hint, but they were limited (single row lookups on primary key seemed to be the only case).

Here are a few examples to show the effect:

-- unindexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select  * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where
1 row in set (0.00 sec)

-- unindexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- indexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)


-- indexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where; Using temporary
1 row in set (0.00 sec)


-- primary key lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where actor_id = 200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

What is the problem being solved for? "locks." Since this is a hint for SELECT queries, unless you are using FOR UPDATE or LOCK IN SHARED MODE, there are very few locks taken on reads anyhow and they are very fast, so you are mostly solving for a problem that doesn't exist. Exceptions always exist, but the overhead of creating a temporary table for nearly every query will far outweigh any benefit gained from making locks go away faster.

MySQL's recommendation is to use the SQL_BUFFERED_RESULT hint when retrieving a very large result set over a network connection to the client. I cannot see any value in using it in another context.

All that being said, you can test and set this "globally" by setting sql_buffer_result=1 at the beginning of every session. In an environment with any concurrency, I would predict poor results.

share|improve this answer
    
Thank you for the detailed answer. –  Haluk Jun 24 '12 at 23:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.