I've been tweaking a my.cnf file for the last couple weeks, using the tuning primer sh script as a guide. You know the routine, I make my change, restart mysqld, then run the script in a day or two to check improvements.
I'm understanding all of it except for Temp Tables. I always get something like this.
Of 20971 temp tables, 60% 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 tablesI keep my max_heap_table_size and tmp_table_size the same, and it doesn't matter if I set those values as 64M, or 1G, it always tells me 40-60% of my temp tables were created on disk.
The system has 8GB of memory and the only other my.cnf setting that is over 64M is my key_buffer_size at 1G.
Any ideas on how to get the temp tables on disk % down?
My system is an all in one setup running CentOS 32 bit, Dual Xeon 2.8Ghz, 8GB of memory.
Here's the my.cnf
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_connections = 200
key_buffer = 1G
max_allowed_packet = 1M
table_cache = 2K
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
join_buffer_size = 3M
thread_cache_size = 8
query_cache_size = 32M
query_cache_limit = 32M
max_heap_table_size = 64M
tmp_table_size = 64M
thread_concurrency = 8
Comments
Interesting... I've also
Interesting... I've also been seeing similar results from the tuning script you mention. I'll be interested to see if there is anyone else looking at this.
text?
I have a feeling that text temporary tables have to be created on disk regardless of this setting.
tmpfs
Check out: http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html
for conditions that MySQL will use disk-based temporary tables.
One way around that is to set MySQL variable tmpdir to use a tmpfs (or ramfs) filesystem.
A fly in the ointment though seems to be MySQL bug #30287: http://bugs.mysql.com/bug.php?id=30287
which seems to undermine the use of tmpdir=/tmpfsdir in affected MySQL versions.
Cheers
Problem as I understand it
The problem as I understand it is that blob and text column types are always written to disk:
The article quoted above has a way of dealing with this apparently...links to this patch: http://drupal.org/node/109513
This is fixed in the current
This is fixed in the current version of D6 ([#109513]), but it doesn't help. Only the search module uses it.
TEMP TABLESCurrent max_heap_table_size = 512 M
Current tmp_table_size = 512 M
Of 199088 temp tables, 76% were created on disk
I set 512M to be safe, it needs indeed less than 128M. Anyone knows which modules cause too many temp table? Views? Is there any pratical recommendations?
this depends on the rest of
sorry wrong post