How to log slow queries in shared hosting MySQL?

Question

I have a shared hosting where I have my website and MySQL database. I've installed a open source script for statistics (phpMyVisites) and it started to work very slow lately. It's written using some kind of framework and has many PHP files. I know that to find slow queries I can use slow query log functionality in MySQL. But on this shared hosting I can not use this method because I can not change my.cnf. I don't want to change my statistics script to other and I don't want to mess around with all files of this script to find out where to put diagnostics code to log queries manually. I would like to do it without changes in PHP code.

So my question is:

How to log slow queries in these coditions?:

• Can't change my.cnf to enable slow query log
• Can't change statistics script to other
• Don't know how scrpt is written and where mysql commands are issued
• Can't ask my provider for slow query log

Is there any method to do this in simple, easy, fast way?

Answer 1

Hmm, perhaps make a function that checks how long a sql query took before it returned a value? Then if it was over a specified amount log it... eg in php i'd do it like this

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

That emulates the slog query logger, i dont think theres a way of enabling it on shared hosting.

Hope this helps :)

Answer 2

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

Answer 3

I took a quick look 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 SAVE_DB_LOG might be a good starting point, so I would try to turn DEBUG mode on.

Answer 4

Is transferring a data dump to a local test system where you can activate the slow query log an option?