MySQL Optimising Table Cache & tmp disk tables -

i'm trying optimise mysql database. i've got around 90 tables of hardly ever used. 10 or vast bulk of work running website.

mysql status statistics show approx 2m queries on 2.5 days , reports "opened_tables" of 1.7k (with open_tables 256). have table_cache set @ 256, increased 32.

i presume of opened tables either multiple instances of same tables different connections or temporary tables.

in same period reports "created_tmp_tables" of 19.1 k , more annoyingly created_tmp_disk_tables of 5.7k. have max_heap_table_size , tmp_table_size both set @ 128m.

i've tried optimise indexes & joins best can, , i've tried avoid blob , text fields in tables avoid disk usage.

is there can suggest improve things?

ta

phil

first of all, don't conclude mysql database performing poorly based on these internal statistics. there's nothing wrong tmp tables. in fact, queries involving ordering or summaries require creation.

it's trying repair vehicle after analyzing amount of time spent in second gear. substantially less 1% of queries generating tmp tables. good. number low enough these queries might backups or kind of maintenance operation, rather production.

if having performance problems, know because queries working slowly, , pages on web app slow. can figure out queries have problems? there's slow query log might you.

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

you might try increasing tmp_table_size if have plenty of ram. why not take couple of megabytes , see if things better? but, won't change noticeably.