Hi,
> 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?
Well, common sense dicatates that in situations like this, having as many of
your temporary tables as possible created in memory rather than on disk is
better, because memory is always faster than any disk drive.
> 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.
On my fairly-used MySQL server, the ratio is at about 34%. I'd say 80% is
indeed way too high.
> 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?
There's only one variable that will affect this situation, which is
tmp_table_size. I have mine currently set to 16Mb also. However, your usage
may be the key here. Are your queries optimised and using indexes in the
proper places? Not having queries use indexes is bound to result in temp
tables being created all over the place. Also, maybe your server is running
low on memory because of other processes?
Perhaps you should take a look at all your most commonly used queries. Then,
if all that seems okay, maybe you do need to bump up your tmp_table_size.
> 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?
Try looking at the documentation for the SHOW VARIABLES command, section
7.28.4:
http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html
> 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
These look pretty sensible for 512Mb. You might want to increase the
key_buffer size, if you're processing large amounts of records in your
tables though (I'm talking about millions of records here, though).
Regards,
------------------------------------------------
Basil Hussain ([EMAIL PROTECTED])
---------------------------------------------------------------------
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
