Hello,
We're are a little worried about the ratio of tmp_disk_tables to
tmp_tables. We are assuming the created_tmp_tables from `mysqladmin
extended-status` represents the total number of temporary tables created
and created_tmp_disk_tables from `mysqladmin extended-status` represents
the number of temporary tables that exceeded tmp_table_size, and
therefore had to be written to disk. Is this correct? If so, then the
smaller this ratio the better, correct?
Over 80% of the temporary tables are being written to disk. This seem
WAY too high. Curious to find out what ratio others are seeing.
If this is high, then what is a good way to lower it? Which variables
to bump up? Are some variables related/dependent on eachother? For
example, since most temp tables are created due to group by's and sort
by's, are one or more sort buffers used per temp table? If a query that
uses a temp table table runs out of sort buffer space, then is the temp
table automatically written to disk? Are buffers such as sort buffers
included in the space used for a temp table? The reason I ask is
because a 16M tmp_table_size seems to be plenty large considering the
size of the tables we are working with?
I've read the optimization chapter in the MySQL manual, but only some of
the configurable variables are described. Is there a resource that
describes ALL of these in better detail?
A snapshot of `mysqladmin extended-status` from our installation:
Created_tmp_disk_tables = 274790
Created_tmp_tables = 338616
We've overriden these variables for a machine with 512M memory:
set-variable = key_buffer=128M
set-variable = table_cache=256
set-variable = max_allowed_packet=1M
set-variable = max_connections=250
set-variable = record_buffer=1M
set-variable = tmp_table_size=16M
set-variable = max_heap_table_size=32M
set-variable = sort_buffer=8M
Redhat Linux: 2.2.14-5.0smp
MySQL: Ver 10.12 Distrib 3.23.27-beta, for pc-linux-gnu (i686) (still on
a beta version is bad, I know)
Thanks,
Erik
---------------------------------------------------------------------
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
