Community Add-Ons

-----------------------------------
Monitor performance mysql v.2.3
Date: 2010-12-26
Author : Guenther Bosch (limex), solfd.com
Upgraded by: limex, based on previous modifications by lildog, kenle, biznetstar.com, yoja
-----------------------------------

This contrib solves all previous reported issues
PLUS:
- adds a config value to the admin to set the treshold value. Very useful to disable the logging. Set to 1000secs and so entry will be written. I promise :)
- Some of us saw errors when no logfile is there for opening depending on your php error reporting level. Now solved, as the existense of the file is checked before opened.
- layout improvements: CSS formats, rounding to 4 digits, added a trash icon

I HAVE FOUND 3 ERROR:

1 for check line
2 for check tab
3 for path of file



Please note follow line in Catalog/includes/functions/database.php:

$slow_query=tep_db_input($query)."tt".$qlocation."tt".$parsetime."tt".$slow_when . "r";

replace with:

$slow_query=tep_db_input($query)."t".$qlocation."t".$parsetime."t".$slow_when . "rn";

on admin/mysqlperformance.php:

replace all DIR_WS_CATALOG

in DIR_FS_CATALOG

EXPLAIN:
=======
1. Well, we can write text one per line in a file by using carriage return, 'n' or 'r'.
If you are using PC, use "n". For Macintosh, use "r". But just to be on the safe side, you can use both together like this "rn" regardless of what system you're running your code on. This will guarantee it works under both PCs and Macintosh.

2. t must be only one because in admin/mysqlperformance.php you have $Data[$performance_numrows] = explode("t", $Value);

and

<td class="dataTableContent" align="left"><?php echo $Data[$performance][0]; ?></td>
<td class="dataTableContent" align="right"><?php echo $Data[$performance][1];?></td>
<td class="dataTableContent" align="right"><?php echo $Data[$performance][2];?></td>
<td class="dataTableContent" align="right"><?php echo $Data[$performance][3];?></td>
END EXPLAIN========

file contains only correction.

English___________
Added small enhancement to the file location and improvement slow_query_log.txt for selects stored in the .txt.

Added tabs and each sentence is written in a different line, not all in one line.

Español__________
Añadida pequeña mejora para la ubicación del fichero slow_query_log.txt y la mejora para las selects guardadas en el .txt.

Añadidas tabulaciones y cada sentencia se escribirá en una linea diferente, no todo en una misma linea.

FULL PACKAGE

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!