Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

DBAs:

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.

problem

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

Xubuntu dekstop

  • 16Gb
  • 8 processor cores
  • Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz

MySQL

mysql> STATUS

Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x8664) using readline 6.

Apache

apachectl -v

Apache/2.4.7 (Ubuntu)

PHP

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

Moodle v2.7.x

  • Cloned from github.com/moodle/moodle and checked out the MOODLE_27_STABLE branch

Hard Drive

  • 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

troubleshooting attempts

  • 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.][1]

  • 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. ][2]

  • Always a single query at a time.

htop ![This image is representative of the system state (filtered to show only MySQL) during script execution.][3]

  • 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][4] ![htop][5] ![mytop][6]

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.

Thanks much!

share|improve this question
    
Have you tried running your colleague's VM image and seeing if you still have problems or does performance improve? –  Vérace Jul 10 at 15:51
    
I certainly can run my own instance of that image, which is created by our infrastructure group and is more or less vanilla Ubuntu. I expect it would perform as well as his, but my main goal is to avoid using the virtual infrastructure, because they charge for RAM, processors and storage. To get a VM with resources similar to my desktop, the cost would be unreasonable, given that this is just development and staging. –  jp5ive Jul 10 at 18:34
    
Yes - I got that. I was just thinking that if it works as well on your machine as on your colleague's, then your idea about "disk not optimized for writing" would be unlikely. I note with interest that you are also using Xubuntu (I do too, can't stand unity!). I don't think a desktop/server difference could account for your poor figures - I might expect to see a few percent - not what you're seeing. –  Vérace Jul 10 at 18:54
    
I recently opted for Xubuntu thinking I would spend my machine's resources where it would matter more to me, which is not in the UI. <br/>I could probably have been clearer about my colleague's machine, the one I've used for the comparison: he's using one of the institutional ESX VMs and not his desktop. <br/>It sounds like you're suggesting I run his image as a VM under my own local Xubuntu; I have not considered that, but it's a great option, will try to get VMWare workstation running and post back... –  jp5ive Jul 10 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.