Hello, I've been using the mysqltuner script to check up on things, tune mem use to our resources, etc. One thing remains consistent: no matter what i have things set to, within a few hours of restart or full stop / start (at most), the Attachments table becomes fragmented. Here's the most recent report, and i realize it's only a few hours since last restart:
-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 6M (Tables: 15) [--] Data in InnoDB tables: 453M (Tables: 86) [!!] Total fragmented tables: 1 -------- Performance Metrics ------------------------------------------------- [--] Up for: 3h 9m 37s (29K q [2.583 qps], 157 conn, TX: 56M, RX: 5M) [--] Reads / Writes: 64% / 36% [--] Total buffers: 1.8G global + 2.6M per thread (100 max threads) [OK] Maximum possible memory usage: 2.0G (25% of installed RAM) [OK] Slow queries: 0% (275/29K) [OK] Highest usage of available connections: 14% (14/100) [OK] Key buffer size / total MyISAM indexes: 512.0M/489.4M [!!] Key buffer hit rate: 37.8% (386 cached / 240 reads) [OK] Query cache efficiency: 59.0% (13K cached / 23K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 695 sorts) [!!] Temporary tables created on disk: 38% (366 on disk / 960 total) [OK] Thread cache hit rate: 91% (14 created / 157 connections) [OK] Table cache hit rate: 95% (120 open / 126 opened) [OK] Open file limit used: 3% (67/2K) [OK] Table locks acquired immediately: 100% (17K immediate / 17K locks) [OK] InnoDB data size / buffer pool: 453.2M/512.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses CPU's a quad-core Xeon, there's 8GB of phys RAM, and the disks are 10k SAS in hardware RAID-1. Even when used after the 24hr minimum (or 3 days, or 8 days), three things remain: a fragmented table which always turns out to be attachments; the Key Buffer Hit Rate; and the temp tables on disk stat. I don't fully understand the latter two and haven't figured it out yet by online docs and commentary, but i get the impression it's the nature of our DB structure and queries used. Important note about that - the MySQL server on this box is hosting two DBs. rt3 is one, but an internal DB we use for mumble mumble is also on the box. I'm not sure if that custom-written DB and its UI used across the office by all employees is the reason for the key buffer / temp table alerts; the programmer for that one is working on the two recommendations of reducing result sets (may not be possible) and making sure there are LIMITs on his queries wherever possible. Should I be worried about the constantly-fragmenting Attachments table? Can i get more fine-grained info about it from within MySQL? Thanks, Rob -- /chown -R us:us /yourbase
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
