MySQL Forums
Forum List  »  Performance

Mysqltuner output - advice
Posted by: rol and
Date: April 01, 2014 01:16PM

i have inherited the task of maintaining a mysql DB with amazon.
we're using mysql as an rds service from AWS.
i'm suffering from high lag between master and slave + max connections, so i decided to check my instance with mysqltuner.
i got interesting result, though some of them i did not get.
i hope you can guide me in the right direction in understanding whats given to me by mysqltuner:

------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 21)
[--] Data in InnoDB tables: 113G (Tables: 915)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 485
-------- Performance Metrics -------------------------------------------------
[--] Up for: 31d 3h 53m 41s (302M q [112.267 qps], 70M conn, TX: 3882B, RX: 255B)
[--] Reads / Writes: 40% / 60%
[--] Total buffers: 11.1G global + 3.1M per thread (1263 max threads)
[!!] Maximum possible memory usage: 15.0G (102% of installed RAM)
[OK] Slow queries: 0% (130K/302M)
[!!] Highest connection usage: 100% (1266/1263)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.8G
[OK] Key buffer hit rate: 99.0% (12B cached / 123M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 66M sorts)
[!!] Joins performed without indexes: 22671697
[OK] Temporary tables created on disk: 1% (1M on disk / 63M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 1M opened)
[OK] Open file limit used: 0% (47/65K)
[OK] Table locks acquired immediately: 99% (700M immediate / 700M locks)
[!!] InnoDB data size / buffer pool: 113.0G/11.1G

-------- Recommendations -----------------------------------------------------
General recommendations:
(truncated to just show what i'm confused about)
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits



Any advice or hint on understanding the above, would be greatly appreciated



Edited 1 time(s). Last edit at 04/01/2014 01:22PM by rol and.

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysqltuner output - advice
2940
April 01, 2014 01:16PM
1416
April 04, 2014 11:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.