I have been developing and maintaining plugins for Moodle for the last ~2 years, and have recently decided to try using my local desktop again, rather than my institutions ESX VMs, as a development environment. My desktop is, comparatively, a beast i7 with 16Gb of RAM and plenty of storage; getting the equivalent VM would be near impossible.
In the process of writing a build script (Ant) to automate the installation of Moodle, I found myself wating far longer than normal for the Moodle installation script to complete. Tracking down the source has taken me on quite an adventure through MySQL troubleshooting tools and forums and has finally led me here.
When installing Moodle, one of the final steps is a script that executes the DDL statements that create the database schema, followed by INSERTS and UPDATES that populate the schema with initial data.
On my system, this step takes on the order of 5 minutes to complete, whereas on my colleague's system, the step takes on the order of 30 seconds.
My colleague is running Ubuntu 14.04 on a VM in our organization's ESX environment. That VM is provisioned with a single processor and 4Gb RAM. platform and versions
- 8 processor cores
- Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz
mysql> STATUS Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x8664) using readline 6.
apachectl -v Apache/2.4.7 (Ubuntu)
php -v PHP 5.5.9-1ubuntu4.2 (cli) (built: Jun 25 2014 17:17:37) Copyright (c) 1997-2014 The PHP Group Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies with Zend OPcache v7.0.3, Copyright (c) 1999-2014, by Zend Technologies with Xdebug v2.2.3, Copyright (c) 2002-2013, by Derick Rethans
- Cloned from github.com/moodle/moodle and checked out the MOODLE_27_STABLE branch
some of the output from hdparm -I /dev/sda is as follows:
Model Number: ST500DM002-1BD142
cache/buffer size = 16384 KBytes
Nominal Media Rotation Rate: 7200
- increase mysql priority with nice and ionice following a tip found in the Ubuntu forums: http://ubuntuforums.org/showthread.php?t=1845995
- alter MySQL configuration settings (my.cnf) following the percona configuration wizard and MySQLTuner 1.1.1 by Major Hayden.
- purge and reinstall MySQL using fresh, default my.cnf and no change to nice/ionice
None of these has increased the speed of the Moodle DB installation script.
Using the following tools, I've tried to get an idea of where my bottleneck is during the Moodle DB install script:
pt-diskstat ![This image is representative of the ouput from pt-diskstat throught script execution.]
busy is always near 100%.
io_s almost always at or near 30.0, but with occasionally spikes to ~100.0.
mytop ![This image is representative of the MySQL state during script execution. ]
- Always a single query at a time.
htop ![This image is representative of the system state (filtered to show only MySQL) during script execution.]
- Always minimal proc or memory usage.
Using the sysbench benchmarking application, and following instructions given at HowToForge, I've run a test against MySQL and found striking differences in the ouput of the foregoing tools, but I'm not convinced this evidence is useful, as sysbench is perofming mainly reads, and no writes.
![pt-diskstat] ![htop] ![mytop]
At this point, I suspect that
- My hard drive may not be optimized for writing. There may be some
- PHP(.ini) configuration issue regarding MySQL. Some aspect of Ubuntu
- desktop (versus server) prevents good MySQL performance.
I'd be interested to hear if anyone has encountered similarly degraded performance in a similar setup.
And I'd be especially glad to have anyone point out where I've completely missed the obvious.