Community Add-Ons

Monitor performance mysql

Just want to let you know that I found a serious bug in this mod. The insert statement in database.php to insert the logs info into qlog table will cause serious bug with your checkout_process.php page.
The checkout_process.php page when trying to get the new insert_id of the order submited will get the insert_id from this qlog table instead from the orders table. Please double check your checkout_process.php file. I got customer submitted order and no order has been created because of the mismatch order id causing by this mod.

This adds a delete button to clear the qlog table. Update only, Not full package.

In catalog/admin/mysqlperformance.php:

FIND:
require('includes/application_top.php');

ADD AFTER:
switch ($HTTP_GET_VARS['action']) {
case 'deleterecords':
tep_db_query("delete from " . TABLE_MYSQL_PERFORMANCE);
tep_redirect(tep_href_link(FILENAME_MYSQL_PERFORMANCE));
break;
}

FIND:
<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
</tr>
</table></td>
</tr>

ADD AFTER:
<?php if ( $HTTP_GET_VARS['action'] == 'deleteconfirm'){ // add a delete button ?>
<tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td align="center" class="main"><br><?php echo TEXT_DELETE ?></td>
</tr>
<tr>
<td class="main" align="right"><?php echo '<a href="' . tep_href_link(FILENAME_MYSQL_PERFORMANCE, 'action=deleterecords'). '">' . tep_image_button('button_delete.gif', IMAGE_BUTTON_DELETE) . '</a>'; ?></td>
<td width="5"><?php echo tep_draw_separator('pixel_trans.gif', '5', '1'); ?></td>
<td class="main" align="left"><?php echo '<a href="' . tep_href_link(FILENAME_MYSQL_PERFORMANCE). '">' . tep_image_button('button_cancel.gif', IMAGE_BUTTON_CANCEL) . '</a>'; ?></td>
</tr>
</table></td>
</tr>
<?php }else{ // add a delete button ?>

FIND:
<tr>
<td>
<?php echo TEXT_NOTE_MYSQL_PERFORMANCE; ?>
</td>
</tr>

CHANGE TO:
<tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td><?php echo TEXT_NOTE_MYSQL_PERFORMANCE; ?></td>
<td><?php echo '<a href="' . tep_href_link(FILENAME_MYSQL_PERFORMANCE, 'action=deleteconfirm'). '">' . tep_image_button('button_delete.gif', IMAGE_BUTTON_DELETE) . '</a>'; ?></td>
</tr>
</table></td>
</tr>

FIND:
<?php echo TEXT_NOTE_2_MYSQL_PERFORMANCE; ?>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
<!-- body_text_eof //-->
</tr>

ADD AFTER:
<?php } // add a delete button ?>


In catalog/admin/includes/languages/english/mysqlperformance.php:

ADD BEFORE ?>:
define('TEXT_DELETE','Delete all records?');
define('IMAGE_BUTTON_DELETE','Delete all records');
define('IMAGE_BUTTON_CANCEL','Do not delete records');

First of all, I would like to thank JS de BOER ( first author) for giving us this contribution

even though it's tiny little content. :)

I called this contribution as v.1.0 becuase, as you may notice the first file package

does not contain any files for us to check and manage the bottleneck queries in admin backend.

as the first author mentioned, we have to have "a database viewer to see which queries were slow and when".

For most of us, this could be a major problem to see the slow queries, Therfore, I have come up this contribution.

The major contributions in this version (v.1.0):


- ability to check and see the bottleneck queries in the admin backend under the tools menu.

- ability to see the location of the queries.(filename in which the query run- this is cool becuase now we can easily spot the queries location)


This is a full package.

USE AT YOUR OWN RISK


biznetstar.com

This little piece of code helps you to find which queries are slow. Of course
you can play a bit with MYSQL_SLOW_QUERIES but I like to have the info
about slow queries in the database so I can sort and find the slowest querie
in a flash. Also not everybody has access to their MySQL settings.

When you know which queries are slow you can have another look
at your database structure and add some indexes or change your code a bit.
Not all queries are that optimized.....

IMPORTANT:
* This contribution will not make your webshop faster! It only helps you find
bottlenecks.
* Please keep an eye on the size of the qlog table, if you run the script on a
slow server or set the log treshold low the size of the qlog table can grow very
big causing perfomance issues
* When you are ready debuggin set back the function to the old values or set the
treshold high that only queries about 2 seconds are logged.
* ONLY USE THIS CONTRIBUTION IF YOU KNOW WHAT INDEXES / QUERIES ARE, ELSE THIS
CONTRIBUTION IS OF NO USE TO YOU!