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 <[email protected]> >> >> 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 : [email protected] >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > 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
signature.asc
Description: OpenPGP digital signature
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
