Sunday, October 25, 2015

mysql5 - why does mysql have so many more open and fragmented tables than tables in the DB?

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)

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...