How to register slow queries in a shared MySQL hosting?

Question

I have shared hosting, where I have my website and MySQL database. I installed an open source script for statistics (phpMyVisites) and it started to work very slowly lately. It is written using some kind of structure and has many PHP files. I know that to search for slow queries, I can use the slow query log functions in MySQL. But on this shared hosting I cannot use this method because I cannot change my.cnf. I don’t want to change my script statistics to another, and I don’t want to go through all the files of this script to find out where to put the diagnostic code to record requests manually. I would like to do this without changing the PHP code.

So my question is:

How to register slow requests in these codes ?:

• Cannot modify my.cnf to enable slow query log
• Unable to change script stats to other
• I do not know how the script is written and where mysql commands are issued.
• Unable to request a slow query log from my provider

Is there a way to do this in a simple, easy, and fast way?

Answer 1

Hmm, maybe create a function that checks how long it took a sql query before it returned a value? Then, if it was over a certain amount, write it down ... for example, in php I would do it like this

$before = time(); //run your query $after = time(); $difference = $after - $before if($difference > 5000) //Log query 

What the slog request logger emulates, I don’t think that this is a way to enable it on shared hosting.

Hope this helps :)

Answer 2

I quickly looked at phpMyVisites. In config.inc.php, I found the following:

 // Other if(!defined('DEBUG')) define('DEBUG', false); define('DEFAULT_ACTION', false); error_reporting( E_ALL ); if(DEBUG) { define('PRINT_TIME', false); define('PRINT_QUERY_COUNT', true); define('SAVE_DB_LOG', true); define('PRINT_QUERY', true); } 

I suspect that SAVE_DB_LOG might be a good starting point, so I will try to enable DEBUG mode.

Answer 3

Transferring a data dump to a local test system, where can you activate the slow query log?

Answer 4

You can write a script that will track the mysql processlist while your slow script is running.