0

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!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.20-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1M (Tables: 254)
[--] Data in InnoDB tables: 3M (Tables: 199)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 200

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 12h 5m 8s (142K q [0.472 qps], 11K conn, TX: 105M, RX: 37M)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 202.0M global + 2.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 452.0M (48% of installed RAM)
[OK] Slow queries: 0% (8/142K)
[OK] Highest usage of available connections: 9% (9/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/741.0K
[OK] Key buffer hit rate: 99.9% (404K cached / 513 reads)
[OK] Query cache efficiency: 72.1% (61K cached / 84K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 41% (6K on disk / 14K total)
[OK] Thread cache hit rate: 99% (9 created / 11K connections)
[!!] Table cache hit rate: 15% (512 open / 3K opened)
[OK] Open file limit used: 55% (633/1K)
[OK] Table locks acquired immediately: 100% (40K immediate / 40K locks)
[!!] Connections aborted: 7%
[OK] InnoDB data size / buffer pool: 3.3M/16.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    table_cache (> 512)
4
  • I must ask, are you sure you just have 25 tables ? I would do a mysqldump --all-databases and wipe mysql data dir then reload all data. Also, you should put the output of mysqltunner.pl here.
    – drcelus
    Jul 6, 2012 at 5:57
  • dumping everything and reloading it would be an arduous task, I'd had to get a lot permission and then coordinate with several groups of people, so I am hoping to avoid that. I've added the output from mysqltuner. The suggestion to edit the table cache further is new.
    – karmet
    Jul 6, 2012 at 10:28
  • you are right about the tables. I wasnt counting the information_schema which has 37 tables but I dont seem to have access to. this gives me something to investigate.
    – karmet
    Jul 6, 2012 at 10:41
  • "fragmented" is a much-maligned term in this context. Ignore it.
    – Rick James
    Dec 24, 2019 at 7:04

1 Answer 1

1

This has probably to do with table_cache and/or max_connections.

With MyISAM tables each new thread/connection needs to open the table. In other words, if you have one table and ten clients accessing that table, you have ten open tables according to MySQL status line. The table_cache helps to reduce that constant open/close table work from happening by keeping tables open for threads.

Your table cache seems to be working quite well, since there has been 43 041 queries, but tables were actually needed to open only 2605 times.

InnoDB has its own connection pool and behaves a bit differently.

For more information, see how MySQL opens and closes tables.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .