Help With Reducing Mysql Load

[marcelfb]

Hi

I'm developing a website that has been suspended twice now for causing too high MySQL load. It's a Drupal 7 site with a few relatively common modules installed + CiviCRM, PHP software for NGO's to manage members, contacts, donations etc. CiviCRM has its own MySQL database and it also interacts directly with the Drupal MySQL database (AFAIK).

 

In the hope that it might help, I have updated Drupal and all modules to the most recent version (with the exception of CiviCRM). However I have no clue how/where I can see if the MySQL load has been reduced?

 

The technical details of the site are:

Before: Drupal 7.14, now Drupal 7.15

 

Modules installed as of today:

Administration menu 7.x-3.0-rc3

Chaos tool suite (ctools) 7.x-1.2

Date 7.x-2.6

Entity API 7.x-1.0-rc3

Entity reference 7.x-1.0-rc3

Relation 7.x-1.0-rc3

Views 7.x-3.5

Wysiwyg 7.x-2.1

CiviCRM 4.1.2

 

The theme is a sub theme of Bartik with minor changes.

 

 

Suggestions on how to analyse the problem and/or clues as to what might be causing them are very welcome.

 

Thanks

[Ice IT Support]

Some applications open and close multiple MySQL connections on one document. Reducing this to opening the connection once at the top of the document and closing it at the bottom of the document will help greatly. For example, in PHP:

<?php
$con1 = mysql_connect("localhost","user","password");
// MySQL query 1
mysql_close($con1);
$con2 = mysql_connect("localhost","user","password");
// MySQL query 2
mysql_close($con2);
$con3 = mysql_connect("localhost","user","password");
// MySQL query 3
mysql_close($con3);
$con4 = mysql_connect("localhost","user","password");
// MySQL query 4
mysql_close($con4);
?>

Should be

<?php
$con = mysql_connect("localhost","user","password");
// MySQL query 1
// MySQL query 2
// MySQL query 3
// MySQL query 4
mysql_close($con);
?>

[marcelfb]

Thanks for your suggestion. Traversing through the code base of these relatively large projects is probably beyond my scope.

However, I noticed that I hadn't turned on Drupal's built-in caching functionality. I suppose this should significantly reduce the database load, as most anonymous requests will now be served from the cache.

 

Do you know of any way that I can monitor the load that my site puts on the MySQL server?

Is it possible to get a warning before the site is abruptly suspended, as it's - well - in use, serving content. Frankly, it's quite stressful when it's suddenly shut down, although I certainly appreciate the quick response to reopen it in the support forums.

 

Thanks for your time.