List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:June 1 2010 5:05pm
Subject:Re: Temporary tables created on disk: 99%
View as plain text  
On Tue, Jun 1, 2010 at 8:40 AM, John G. Heim <jheim@stripped> wrote:
> On my db server, mysql has 2 gigabytes for temporary tables and yet its
> creating 99% of temporary tables on disk.
>
> According to mysqltuner, 99% of temporary tables are created on disk.

Probably blobs:
"Instances of BLOB or TEXT columns in the result of a query that is
processed using a temporary table causes the server to use a table on
disk rather than in memory because the MEMORY storage engine does not
support those data types (see Section 7.5.10, “How MySQL Uses Internal
Temporary Tables”). Use of disk incurs a performance penalty, so
include BLOB or TEXT columns in the query result only if they are
really needed. For example, avoid using SELECT *, which selects all
columns. "

From http://dev.mysql.com/doc/refman/5.0/en/blob.html


If you can get us a query, explain, and table/index info we may be
able to help you more.

Also, you might want to consider creating a ram disk so that filesorts
that must be done on disk might be done inexpensively.

-- 
Rob Wultsch
wultsch@stripped
Thread
Temporary tables created on disk: 99%John G. Heim1 Jun
  • Re: Temporary tables created on disk: 99%Rob Wultsch1 Jun