Am 22.01.2014 15:43, schrieb Jean Weisbuch:
I am maintening a fork of mysqlreport which is a tool that gives stats and infos about MariaDB/MySQL and that has been integrated on the Debian packages of MySQL and MariaDB for years (i dont know about other distros), you can find it on the debian/additions/ directory of the sources.
i know mysqlreport and tried it longer ago but the output is not near as compact compared to mysqltuner agreed, the detail infos are nice, but the output i posted below does not come up with scrolling and gives a healthy overview if caches and buffers are too small over the time if your server now hosts 5000 tables with the config made for 1000 tables
The version i am maintening and that should be included on Maria10 soon : https://mariadb.atlassian.net/browse/MDEV-573
good to know
Its not giving "advices" as mysqltuner does but i never was fond of these advices that are sometimes risky as they usually ask you to set biggers cache size in case they are fill (or almost filled) and peoples tend to follow them without even trying to understand the possible consequences or how they could end up wasting resources.
as said: blindly follow is always dumb on the other hand "[OK] Maximum possible memory usage: 2.6G (26% of installed RAM)" years ago leaded in my case to realize which buffers are per connection and make some smaller
On 22/01/2014 15:14, Reindl Harald wrote:
mysqltuner seems to be no longer maintained
while you should not take any recommendation blindly it was really usefull over the years, with MariaDB some values are looking wrong
Up for: 3d 17h 7m 44s (15M q [47.927 qps], 477K conn, TX: 33B, RX: 3B) [OK] Query cache efficiency: 48.1% (10M cached / 21M selects)
21M selects out of 15M queries as example :-)
it would be pretty cool have a compareable tool (maybe native code) in the MariaDB setup - sadly Bash/PHP are my skills :-( ______________________________________
MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password:
-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.34-MariaDB-log [OK] Operating on 64-bit architecture
-------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 472M (Tables: 4995) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 141
-------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned
-------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 17h 7m 44s (15M q [47.927 qps], 477K conn, TX: 33B, RX: 3B) [--] Reads / Writes: 92% / 8% [--] Total buffers: 1.8G global + 1.8M per thread (500 max threads) [OK] Maximum possible memory usage: 2.6G (26% of installed RAM) [OK] Slow queries: 0% (2/15M) [OK] Highest usage of available connections: 8% (44/500) [OK] Key buffer size / total MyISAM indexes: 256.0M/189.1M [OK] Key buffer hit rate: 100.0% (328M cached / 71K reads) [OK] Query cache efficiency: 48.1% (10M cached / 21M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 1% (737 temp sorts / 55K sorts) [!!] Joins performed without indexes: 1140 [OK] Temporary tables created on disk: 22% (45K on disk / 201K total) [OK] Thread cache hit rate: 99% (44 created / 477K connections) [OK] Table cache hit rate: 36% (5K open / 14K opened) [OK] Open file limit used: 1% (10K/750K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm