why does mysql have so many more open and fragmented tables than tables in the DB?
- by kswift
I've been working making our database run a little smoother and had good results over the past week. But there are still some things I dont understand.
For one thing, the database has 25 tables. But mysql status shows 512 are open:
mysqladmin status
Uptime: 212854 Threads: 1 Questions: 43041 Slow queries: 7 Opens: 2605 Flush tables: 1 Open tables: 512 Queries per second avg: 0.202
I've read that isam opens extra file descriptors and a few other reasons why the number of open tables might be higher than 25, but I am guessing that 512 is not a good thing. Any suggestions on why this might be or what I should be looking into?
I've also been using mysqltuner and its been helpful. But it has consistently listed the number of fragmented tables at 207. In phpmyadmin I've selected all the tables and optimized them several times. It hasn't reduced the number of fragmented tables that mysqltuner reports.
I think I am missing some important concept about how this all works.
Does anyone have any suggestions to point me in the right direction or narrow down google searches or just generally help me be less clueless?
Thanks!