Tool for Performance - MySQLTuner
Credit where credit is due:
# High Performance MySQL Tuning Script
# Copyright (C) 2006-2016 Major Hayden - major@mhtx.net
Preliminary from 'author of this posting perspective ... most the Moodles run by schools for whom I assist are running their Moodle's single instances on a VMWare machine CentOS 5 or 6 where server is the web interface and the DB server. Most are 16 Gig memory and more than one has 1TB partition for moodledata. These are K12 entities with anywhere from about 6,000 users to over 10,000 registered on the system (doesn't mean they use Moodle daily if it 'sounds/looks' un-powered as much depends upon usage).
Remember ... the following from the perspective of a Linux box.
Get mysqltuner update in /usr/local/bin/
cd /usr/local/bin/
(if you have no /usr/local/bin/ then any bin type directory in your command line/shell environment
will do).
wget --no-check-certificate https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
make mysqltuner.pl executable
chmod u+x mysqltuner.pl
Create a script for it ... not required just easier to type 'command' and run as superuser for DB server.
nano tuner (contains this one-liner)
/usr/local/bin/mysqltuner.pl --user=[SUPERUSER] --pass=[YOURSUPERUSERPASSWORD] --nocolor
Ok, before anyone points it out ... yes, know that putting passwords in scripts is not recommended,
but will say this ... if someone can get to the box via ssh then what difference does that make.
Sooooo ... to each their own. I use the --nocolor option because I run tuner from Webmin.
Then make the 'tuner' script executable by root user:
chmod u+x tuner
Now that 'tuner' is in the environmental path, one can be anywhere on the Moodle server
and run it by typing in the script name 'tuner' and hit enter.
The newest version (Version 1.6.14) is much improved.
Credit where credit is due:
# High Performance MySQL Tuning Script
# Copyright (C) 2006-2016 Major Hayden - major@mhtx.net
The output ... example is real with only some information shown here 'ob-secured' ...
Comments of mine designated by **.
Symbols by mysqltuner ...
[--] informational
[OK] checks out
[!!] things to path attention to
The script also has Metrics broken into sections of the display:
MyISAM Metrics is now separate from InnoDB Metrics. Handy ... the older version used to report
many tables needing optimization but that was due to treating all tables (even InnoDB) as MyISAM.
Example clips of output:
Performance Metrics
[--] Physical Memory : 15.7G
[--] Max MySQL memory : 13.5G
[--] Other process memory: 3.5G
[--] Total buffers: 13.1G global + 2.8M per thread (151 max threads)
** pushing the limits on this box! :\ Yes, I need to tweak it.
[OK] Maximum reached memory usage: 13.1G (83.71% of installed RAM)
[!!] Maximum possible memory usage: 13.5G (86.26% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Highest usage of available connections: 1% (2/151)
[OK] Aborted connections: 0.15% (1/667)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.4% (3M used / 15M cache)
[OK] Key buffer size / total MyISAM indexes: 15.0M/9.9M
[OK] Read Key buffer hit rate: 96.0% (4K cached / 177 reads)
[!!] Write Key buffer hit rate: 49.0% (308 cached / 157 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 13.0G/363.5M
[OK] InnoDB buffer pool instances: 13
[!!] InnoDB Used buffer: 2.26% (19287 used/ 851962 total)
[OK] InnoDB Read buffer efficiency: 98.05% (957813 hits/ 976851 total)
[!!] InnoDB Write Log efficiency: 34.54% (571 hits/ 1653 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1082 writes)
** when the script finishes this section at the end. Will be different for your server.
Recommendations
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (6000) variable
should be greater than table_open_cache ( 600)
Variables to adjust:
query_cache_type (=0)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 40M)
max_heap_table_size (> 40M)
table_open_cache (> 600)
Done!
** note the handy bit.ly link ... which goes to Percona's Blog on MySQL DB administration.
Handy script and well worth running, IMHO!
If anyone else has a tool they use to help with performance, feel free to share here.
'spirit of sharing', Ken