How to run the database performance script in Moodle

How to run the database performance script in Moodle

by Mari Cruz García -
Number of replies: 14

Hello,

In our recently created Moodle 2.5 site, running in a CentOS 6.3 server, it can take up to 60 seconds to upload some pages (administrator role).

I have checked that the hardware requirements are OK in terms of RAM memory and disk space, so I think it may be a software configuration problem, although I followed the recommendations in Moodle.org -http://docs.moodle.org/23/en/Performance_recommendations- when I created the website.

The Moodle site and the MySQL server are installed in the same machine.


One of the options I am pondering on is to install a php accelerator, but I would like to do some further testing first.

In order to check the database performance Moodle.org recommends to run the script dbperformance.php

_____________________________________________________________________________________

Moodle contains a script which will display some key database performance statistics from the ADOdb performance monitor. Run the script in your browser as in the following example:

http://www.mymoodle.com/admin/dbperformance.php

_____________________________________________________________________________________

I am bit confused about how to generate the file dbperformance.php as the link to ADOdb

 

For php 5.5.3 and MySQL 5.3 is this the files that I need to use:

http://sourceforge.net/projects/adodb/files/adodb-php5-only/

 

can you please clarify how I can create the file dbperformance.php to run from Moodle?

Thank you very much for your advice.

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I'm reasonably sure that was removed from Moodle a long time ago and I wouldn't be concerned about it not being there.

Let's go back to basics. What's the numbers (users etc) for your Moodle site and what hardware are you running it on? It certainly won't hurt to use an accelerator. They tend to reduce server load more than make page load times a lot faster.

If your site is big and/or you have loads of plugins the admin experience can be very slow. What is it like for an ordinary user?

What have you done to tune Apache and MySQL to take advantage of the hardware you are using? They are rarely (well, never) optimally configured out of the box.

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

First, CentOS 6.3 is probably in need of upgrading (current version is 6.5).

Admin users access will always be slower (pulls more data than other levels of access).   An optimizer would speed things up.   With version 5.3 of PHP one can install APC or it is possible to acquire and install Zend OpCache.   Future versions of PHP will probably include Zend OpCache so would recommend the later, but the later requires manual installation.   One won't find either one in the standard repos of CentOS - ie, install via yum using the 'standard' repos'.

For APC, see: http://www.tecmint.com/install-apc-alternative-php-cache-in-rhel-centos-fedora/

For Zend OpCache ... it requires manual installation.   Make sure the tar.gz you download matches your server ... ie, don't use 32 bit with 64 bit box.

http://www.zend.com/en/products/guard/downloads

For checking/tweaking DB ... http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: How to run the database performance script in Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

Here are some notes on CentOS and either APC or ZendOpcache

APC - can be installed as a pecl extension which means one has to have php-pear and pcre-devel installed.

http://www.tecmint.com/install-apc-alternative-php-cache-in-rhel-centos-fedora/

yum install php-pear php-devel httpd-devel pcre-devel gcc make

NOTE: you might already have some of the above.  If you issue the command as shown, yum might inform you that it's already installed.   No harm done.   All of these/this as root user.

pecl install apc

echo "extension=apc.so" > /etc/php.d/apc.ini

cp /usr/share/pear/apc.php /var/www/html/

Add rules to /etc/php.d/apc.ini

edit apc.ini
apc.enabled=1
apc.shm_size=128M
apc.ttl=3600
apc.user_ttl=7200
apc.gc_ttl=3600
apc.max_file_size=1M
apc.stat=1

Restart Apache: /sbin/service httpd restart

Check apc.php page you've installed at document root of web site (above assumes typical CentOS install and web root is /var/www/html/).

Suggest also editing the apc.php page and setting a user/password for it.

Handy curl command to clear APC - i.e. flush it and begin building new opcache.

curl --user [user]:[password] "http://URL/apc.php?CC=1&OB=1&`date +%s`"

Open the apc.php page in one browser instance.   Open your Moodle site in another browser instance and login.   Go to admin areas that might have to generate lots - users/courses, etc.   Check how much memory apc is using and what it's optimizing.   Might have to adjust and allocate more memory for it (apc.shm_size).

Zend OpCache (a little harder):

For 64 Bit CentOS 5.10 - php 5.3.x

wget http://pecl.php.net/get/zendopcache-7.0.3.tgz
tar zxvf zendopcache-7.0.3.tgz
cd zendopcache-7.0.3
phpize
./configure --with-php-config=/usr/bin/php-config
make
make install
nano /etc/php.d/opcache.ini
to check settings for opcache ... might increase the memory allocated to it from 64M default to something higher (128M?).   Check how much memory you do have free if running apache/mysql on same server.   Command: top will show.

/sbin/service httpd restart

Then get
https://gist.github.com/ck-on/4959032/?ocp.php

and place at web root

https://gist.github.com/ck-on/4959032/download

is a tar.gz
mv download ocp.tar.gz
tar zxvf ocp.tar.gz
cd gistdir
cp ocp.php /var/www/html/

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: How to run the database performance script in Moodle

by Mari Cruz García -

Thank you very much to both Howard and Ken for replying.

It is not feasible to change the centOS version,  as this is something that department in charge of the hosting would have to do.

Choosing between APC and ZendOpcache, which one do you recommend to use, Ken?

 

Regards,

Mari Cruz

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Whichever one you can get to work wink

The ideal situation is now PHP 5.5+ and OpCache. I've never got it to work properly with earlier versions even though it should. APC seems to be reasonably reliable but I don't think it's actively developed any more. So, APC for pre-5.5 and move to OpCache when you have PHP 5.5 (or newer) would be my vote

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

Agree with Howard.  If you can get ZendOpcache installed do that one - that will 'future proof' somewhat.  Having said that, however, because your hosting provider won't upgrade OS (they should) am wondering if you can install either one as both require 'make' or, in the case of APC, pcel.

APC won't be updated as I understand it and is ok for your PHP 5.3.x box but probably best to start learning ZendOpcache.

'spirit of sharing', Ken

 

Average of ratings: -
In reply to Ken Task

Re: How to run the database performance script in Moodle

by Mari Cruz García -

Thank you again to both for the information.

I have been able to run this line:

yum install php-pear php-devel httpd-devel pcre-devel gcc make

from the Putty terminal without problems, but when I try to run the line:

pecl install apc

I get the error message:

No releases available for package "pecl.php.net/apc"
install failed

Does it mean that there are not releases available or it is the firewall which is causing the problem.

I can only install certain repositories using Yum for certain depedency packages that are not blocked by the firewall.

 

Regards

Mari Cruz

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

Yum installations and pecl installs are not the same ... don't use the same repo's.

To see the repos active in yum: yum repolist

That shows only the repos that have been enabled by default.   To actually see if there are more:

cd /etc/yum.repos.d/

ls

or

yum --enablerepo=* repolist

If the provider installed Moodle via directions from Moodle docs, you might see webtatic repo listed.

For pecl/pear:

pecl help gets help and shows options/switches etc. for pecl.

pear update-channels

Then the command:

pecl install apc

again

I installed apc some months ago and haven't had the need to install recently.   Could be it has been removed from pecl channels ... but that would be strange.   So .... only thing left is upstream firewall.

Just for fun ... maybe you didn't know you could update your OS ... find out:

yum update [ENTER]

If none of the above works for you, the network/server administrators will have to be consulted so you can acquire what is needed.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: How to run the database performance script in Moodle

by Mari Cruz García -

Hello Ken,

Thank you again for the comprehensive instructions.

I believe that I have updated the OS by running the suggested command as the final message after the update was:

completed!

 

 

However, when I run the command

pear update-channels

I am getting several error messages

 

-Channel "pear.php.net" is not responding over http://, failed with message: Connection to `pear.php.net:80' failed: Connection timed out
Trying channel "pear.php.net" over https:// instead

I believe that the firewall does not allow the connection to pear.php.net:80

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

To check to see if firewall allows or not, install lynx:

yum -y install lynx

After installed type: lynx http://pear.php.net/

If you are blocked should see some sort of message from the firewall.   If not, you'll be looking at a text based version of pear.php.net.   Blocked?

To quit lynx, type: 'q' and when asked if you want to quit, y

Ok, this is getting risky ... but as long as you know that (and the fact this forum should not be used for a beginner sys-op training).   Google IS your friend ... matter of fact, what's listed below this point was taken from some web page (I didn't bookmark) - with the 'work-around' for getting an .ini file more easily.

Before we go further, you said the yum update command did update the system.   So you probably need this info for future questions/anwsers, etc..

php -v

shows version of PHP - your version is now?

php -m

show extensions/modules that PHP has loaded.

mysql -V

shows mysql version ... if on localhost.

Rather than APC ... let's try ZendOpCache (reasons previously given).

Here's a how 2:

As root user in /root (roots home directory) - rest of you sys-ops don't shudder! ;)

wget http://pecl.php.net/get/zendopcache-7.0.3.tgz
tar zxvf zendopcache-7.0.3.tgz
cd zendopcache-7.0.3
phpize
./configure --with-php-config=/usr/bin/php-config
make
make install

cd /etc/php.d/
wget http://sos.tcea.org/opcache.ini
nano opcache.ini - set memory and make sure it's 'on'.
to check settings for opcache ... might increase the memory allocated to it from 64M default to something higher.   Check how much memory you do have free if running apache/mysql on same server.   Command: top will show.

/sbin/service httpd restart

check error log:
tail /var/log/httpd/error_log

php -m
one should see:

[Zend Modules]
Zend OPcache

at the bottom of the listing of mods for PHP.

Then get an ocp.php script.
Reference: https://gist.github.com/ck-on/4959032/?ocp.php

and place at web root

wget https://gist.github.com/ck-on/4959032/download

is a tar.gz file saved as a file called 'download'.

mv download ocp.tar.gz
tar zxvf ocp.tar.gz
this will create a gistdiretory like below:

cd gist4959032-77fb6cfa1adc45358746ba072d1edd8ca7402347 (this directory will be different)

ls to see this long directory name, then use copy to get the long directory name into clipboard.

cp [paste_long_directory_name]/ocp.php /var/www/html/ OR your document root.

The use browser to go to http://site/ocp.php

This is offered as is ... ;)

'spirit of sharing', Ken

 

Average of ratings: -
In reply to Ken Task

Re: How to run the database performance script in Moodle

by Mari Cruz García -

Thanks again for the explanation.

I thought that in order to install ZendOpCache, you need to run Apache 2.4, which is not our case.

We are running php 5.3.3.

I would like to try first APC, I have opened  a ticket in the helpdesk of our provider so that they allow me to complete the installation.

We had a technical meeting at the beginning of January and I did ask for opening so man  firewall and proxy restrictions that are always interfering with the updates and new installations.

However, the College has a very specific policy regarding Security, so there is not much we can do.

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

No mention of apache version requirements here:

http://www.php.net/manual/en/opcache.installation.php

APC is no longer being developed.  Version 5.3.x of PHP might be the last version of PHP where one could run APC.   Sooner or later, you will have to upgrade php to a version that includes Zendopcache so why not get familiar with it's behavior/settings/tweaks now rather than later?

Here's what PHP folks have on APC:

http://us3.php.net/manual/en/book.apc.php

http://us3.php.net/manual/en/apc.installation.php

And just a comment ... sounds like entity is pretty much Windows based.  Too bad.  Bet there wouldn't be a problem in getting updates for Windows.   If that's the case, an institution of "higher learning' should be a place for *all* learning ... especially the 'other' operating systems ... which collectively are chipping away at M$'s throne.   The way I see it, that throne is on very un-stable legs.

'spirit of sharing', Ken

 

Average of ratings: -
In reply to Ken Task

Re: How to run the database performance script in Moodle

by Mari Cruz García -

Hello Ken,

I just let you know that I have managed to install APC 3.1.9 and it has increased drastically the loading times of all the pages, not like before which was an agony.

Thank you very much for your help.

I have added in php.in the following line:

extension=apc.so

as I was advised to do so when I restarted apache.

I was working with a network technician this morning who unblocked ports 80 and 443, so I am now able to see the updates for Moodle and connect to Turnitin.

I will look into the requirements for upgrading to php 5.5 so that I can use Opcache.

Just one last thing, the centOS version I have installed is 6. 3(final)

when I run yum update, I get the message

'No Packages marked for Update'

I can only see the repositories which are in the college of life science repositories, so when I run yum repolist, I can only see this update:

centos6.3-x86_64.updates    

Best regards,

Mari Cruz

Average of ratings: -
In reply to Mari Cruz García

Re: How to run the database performance script in Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

Congrats!   Glad to be of assistance.   Trust you'll be running only one instance of Moodle - APC doesn't fair so well when running multiiple instances of Moodle ... even if same version/build date.

Wow!  So the college of life sciences runs their own local repo system, huh?  That's quite a chore.  Guess they are doing so for the 'safety' of the college network and 'protecting' server admins from themselves.  Well, that keeps at least one more person on the payroll, I guess. :|

For future reference:

http://webtatic.com/packages/php55/

One of the characteristics of CentOS ... they are always a little behind ... not unusual to use 3rd party repo's  - such as the one above - to get versions of PHP or MySQL not in Cent repos.

'spirit of sharing', Ken

Average of ratings: -