aug 232013
 

mysql-logoMySQLTuner is een programma geschreven in Perl waarmee je je MySQL configuratie kan nalopen en op aanwijzing kan wijzigen voor betere prestaties

Ga naar de map /usr/local/bin

cd /usr/local/bin

Download MySQLTuner

wget http://mysqltuner.pl/mysqltuner.pl

Geef het uitvoerrechten

chmod +x mysqltuner.pl

En start het script

/usr/local/bin/mysqltuner.pl

De output zal hierop lijken:

>>  MySQLTuner 1.0.1 - 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.1.41-3ubuntu12-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 696K (Tables: 117)
[--] Data in InnoDB tables: 9M (Tables: 134)
[!!] Total fragmented tables: 139

-------- Performance Metrics -------------------------------------------------
[--] Up for: 57s (594 q [10.421 qps], 168 conn, TX: 229K, RX: 84K)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 130.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 398.7M (26% of installed RAM)
[OK] Slow queries: 1% (10/594)
[OK] Highest usage of available connections: 1% (1/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/540.0K
[OK] Key buffer hit rate: 100.0% (12 cached / 0 reads)
[!!] Query cache efficiency: 12.2% (48 cached / 393 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts)
[OK] Temporary tables created on disk: 24% (51 on disk / 206 total)
[OK] Thread cache hit rate: 99% (1 created / 168 connections)
[!!] Table cache hit rate: 13% (192 open / 1K opened)
[OK] Open file limit used: 27% (283/1K)
[OK] Table locks acquired immediately: 100% (222 immediate / 222 locks)
[OK] InnoDB data size / buffer pool: 9.6M/16.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
 Run OPTIMIZE TABLE to defragment tables for better performance
 MySQL started within last 24 hours - recommendations may be inaccurate
 Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
 query_cache_limit (> 1M, or use smaller result sets)
 table_cache (> 192)

Let op dat dit script uitgevoerd wordt op een databaseserver die al meerdere dagen draait. Hiermee worden onnodige voorstellen weggelaten om zo goed de performance van de configuratie te meten.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)