I'm trying to optimise my MySQL database. I've got around 90 tables most of which are hardly ever used. Only 10 or so do the vast bulk of the work running my website.
MySQL status statistics show approx 2M queries over 2.5 days and reports "Opened_tables" of 1.7k (with Open_tables 256). I have the table_cache set at 256, increased from 32.
I presume most of the opened tables are either multiple instances of the same tables from different connections or some temporary tables.
In the same period it reports "Created_tmp_tables" of 19.1 k and more annoyingly Created_tmp_disk_tables of 5.7k. I have max_heap_table_size and tmp_table_size both set at 128M.
I've tried to optimise my indexes & joins as best i can, and i've tried to avoid BLOB and TEXT fields in the tables to avoid disk usage.
Is there anything you can suggest to improve things?
First of all, don't conclude your MySQL database is performing poorly based on these internal statistics. There's nothing wrong with tmp tables. In fact, queries involving ordering or summaries require their creation.
It's like trying to repair your vehicle after analyzing the amount of time it spent in second gear. Substantially less than 1% of your queries are generating tmp tables. That is good. That number is low enough that these queries might be for backups or some kind of maintenance operation, rather than production.
If you are having performance problems, you will know that because certain queries are working too slowly, and certain pages on your web app are slow. Can you figure out which queries have problems? There's a slow query log that might help you.
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
You might try increasing tmp_table_size if you have plenty of RAM. Why not take it up to a couple of megabytes and see if things get better? But, they probably won't change noticeably.