← Back to team overview

maria-discuss team mailing list archive

Re: mysqltuner

 


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@xxxxxxxx>
>>   >>  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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> 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

Attachment: signature.asc
Description: OpenPGP digital signature


References