[rt-users] Attachments table constantly fragmenting

Rob Munsch rob.munsch at gmail.com
Tue Feb 17 18:23:45 EST 2009


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090217/02cdced9/attachment.htm>


More information about the rt-users mailing list