Why do I have temporary tables created on disk ?

[SecondSight]

Hello ! :)

I'm trying to understand how to configure my server. I also installed mysqltuner and tuning-primer. They tell me that I should increase tmp_table_size and/or max_heap_table_size :

Current max_heap_table_size = 2.00 G

Current tmp_table_size = 2.00 G

Of 3434 temp tables, 27% were created on disk

Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables

I keep on increasing it, but it doesn't change anything, so I believe this is not the solution...

How can I prevent tables from being created on disk ?

Thank you ! :)

Here is my my.cnf ( my server is a Bi Xeon Quad 8x 2.33 with 16 Go DDR2 and 2x 1000 Go SAS) :

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-slow-queries = /var/log/mysql-slow.log

long_query_time = 3

log-long-format

skip-locking

skip-innodb

query_cache_limit=6M

query_cache_size=512M

query_cache_type=1

tmp_table_size=2048M

max_heap_table_size=2048M

max_user_connections=500

max_connections=500

interactive_timeout=100

wait_timeout=200

connect_timeout=100

thread_cache_size=512

key_buffer=3072M

join_buffer=2M

max_allowed_packet=2M

table_cache=3072

record_buffer=4M

sort_buffer_size=4M

read_buffer_size=1M

read_rnd_buffer_size=1M

max_connect_errors=10

thread_concurrency=8

myisam_sort_buffer_size=64M

server-id=1

#[mysql.server]

#user=mysql

#basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/lib/mysql/mysql.pid

open_files_limit=8192

[altenerg]

you ever figured this out?

[Clickfinity]

http://dev.mysql.com...ary-tables.html

"Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• Presence of a BLOB or TEXT column in the table
• Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes
• Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used
  

The SHOW COLUMNS and The DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table."

So you're likely always going to see them.

Mine runs at around 48% created on disk - both for IPB and Xenforo, so don't worry about it and certainly don't go throwing away memory trying to solve it ... use the memory where it is more useful and can reduce bottlenecks.

Cheers,
Shaun :D