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?

@Col.Shrapnel I am a developer for 9 years now and while this time I have many practice, and face very narrow problems like this. I would like to be a academic theoretist that has only book-handled issues, but I'm not. I hope that you can engage to understand my point of view and this problem to figure out what for this question was asked for. Hope that you can help, and if not I still appreciate that you presented here you opinion of what makes you angry in people asking narrow questions. Hope you will acomplish a personal goal of going on over questions you hesitate to accept. — Tom Smykowski, Apr 17 '10 at 18:52
So if You have so many practice, You should know the answer: You need a dedicated server. That's all. And @Col. Shrapnel has right. You don't want You, but You need to. — Michał Mech, Apr 17 '10 at 19:53
"Can't ask my provider for slow query log" - If we take anything else for granted (just for argument's sake), why is _that_? They may say "no, you can't have it" but what keeps you from asking for it? — VolkerK, Apr 17 '10 at 21:07
@Reynevan I'm asking how to get to the other city without gas, and you tell me that I should buy a car. Thank you very much for your help than! — Tom Smykowski, Apr 18 '10 at 13:50
@VolkerK I've asked him twice and still try to get this from him, but he is not as interested in this as I am — Tom Smykowski, Apr 18 '10 at 13:51
@tomaszs You should answer yourself if traveling without gas is possible if You have only car on gasoline. Answer is still the same: You can't do this. — Michał Mech, Apr 19 '10 at 07:14
@Reynevan Yes, you are right - if you can travel only with car - you have a big problem. I don't think this discussion has any purpose in this point. I understand your point of view and have no need to change it. Live well. — Tom Smykowski, Apr 19 '10 at 10:15

score 4 · Accepted Answer · answered Apr 17 '10 at 18:07

4

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 :)

answered Apr 17 '10 at 18:07

studioromeo

1,563
12
18

i know this doesnt match what you've said in point 3 but its as gooder answer as i could think of :\ – studioromeo Apr 17 '10 at 18:08
Answer "can't do" is still an answer ;) Thanks – Tom Smykowski Apr 17 '10 at 18:27

score 3 · Answer 2 · answered Apr 17 '10 at 19:43

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.

score 2 · Answer 3 · answered Apr 17 '10 at 21:13

2

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

answered Apr 17 '10 at 21:13

VolkerK

95,432
20
163
226

YEs, it's a solution, but it takes time. – Tom Smykowski Apr 18 '10 at 13:51
Well, you said you're interested in solving the problem. And as a bonus you will also test your backup strategy at the same time, now that's a bargain ;-) – VolkerK Apr 18 '10 at 14:25

score 0 · Answer 4 · answered Apr 17 '10 at 19:14

0

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

answered Apr 17 '10 at 19:14

elias

1,481
7
13

How to log slow queries in shared hosting MySQL?

4 Answers4