Help - Search - Members - Calendar
Full Version: The Slow Query Log mysql 5.0
Linuxhelp > Support > Technical Support
andy1974
I need step by step instructions to get the The Slow Query Log for mysql up and running.. I am running Fedora core 5 with the packaged mysql. Also my email is [Removed by DS2K3] please help. I cant tell if my problem is mysql , php , or apache. And i am hopeing the The Slow Query Log will give me hints
Robert83
Hi,

MySQL has a feature that allows you to log slow running queries to a file. To enable you just need to add some lines to your my.cnf file, and restart. Add the following:

log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
The above will log queries taking longer than one second to the specified log file.

You can read more in the MySQL Reference Manual http://dev.mysql.com/doc/refman/5.0/en/slo...-query-log.html

Sincerely
Robert B
andy1974
thanks for the responce ... i did as you stated...but still not logging...here is the site....www.clickerpower.net...go to message board you can see the lag there. 15 seconds or more

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql-slow]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
Robert83
Hi,

maybe you should create the log file, and see if that helps, also make sure mysql can write into it.

Sincerely
Robert B
andy1974
hmmmm.....I made the file.....but i must be doing somthing wrong.....still no data in the file....does it write to the file in real time??....and the odd thing is i dont get ANY errors in the mysql logs....just loads the pages slow..10 -15 seconds lag..... Every thing else in the apache envirnroment is running speedy....thats why i am geared to mysql....how bout php? logs for that ?? how can i tell if php is haveing trouble?....before i upgrade the server i was using mysql 3.23.58 with NO LAG WHAT SO EVER...the 5.0 came with the distro
Robert83
Hi,

according to the manual pages, if there is nothing showing up in the mysql slow log , and you have used 1 sec as the default time, that means that all mysql queries take less then 1 seccond to complete, which would mean that mysql is not the problem here.

Php would be my guess next or the message board itself(the software).

But unfortunatelly I'm not that good with php, and not familiar with that message board either, I've used PhpBB sofar.

But the last time I checked your site 5 minutes ago, it felt quiet responsive, the message board. (or maybe you fixed it smile.gif ).

Sorry , maybe try irc freenode php for more info.

Sincerely
Robert B
andy1974
No not fixed yet ... but thanks for the info
DS2K3
Try this:

Add the following line of PHP to the top oif the PHP file (just after the first <?php tag)

CODE
set_time_limit(5);


Then load it. If PHP is trying to run for more than 5 seconds, then you' ll get an error. If it is the MySQL connection that's being slow, you wont get the error because PHP doesnt include calls to MySQL when it works out how long it has been running for.

D
andy1974
I will try that ...been outa town for a few days....so i need to play the catch up thing ...but i will try that
andy1974
Add the following line of PHP to the top oif the PHP file (just after the first <?php tag)

CODE

set_time_limit(5);



Then load it. If PHP is trying to run for more than 5 seconds, then you' ll get an error. If it is the MySQL connection that's being slow, you wont get the error because PHP doesnt include calls to MySQL when it works out how long it has been running for..

Where do i put that code??
DS2K3
Probably in index.php - After the first "<?php"

D
andy1974
ok i got that added what am i looking for after i restart?
DS2K3
Visit the forum, and see if you get an error message about the script taking too long. If you dont, and it takes more than 5 seconds to load, then you cna rule out PHP as the source of the problem, since if PHP was taking more than 5 seconds to carry out its part of the job, it would get cut off.
andy1974
Well i am bout outta options looks like. I have done everything that i know and still slow....I dont get any errors on the slow-mysql log and php doesnt get cut off.....this is on a development server. on a side note this problem is the safe with 3 diffrent form software... PHPBB.. and PUNPHP..the site address is www.clickerpower.net/forums

Added at 7:41 PM

Ok here is something totaly odd...today i was sitting at my desk and thought i was gonna check the formus and see if i had a responce, well after making this orig responce i check my devloping forum. to my surprise it was fast i mean like one sec and forums were responding and the page was up, so i was wth .....i was surprised and shocked at the same time due to the fact i have been working on this problem for so long.. this was at 2 pm today , so the rest of the afternoon i was on the pc at work checking on the forums (shhhh dont tell my boss). well anyways i checked sevral time before i made it home at 5 pm ......well when i got home and got on the pc to check and find out what the problem was it was slow again sad.gif this was at 6:30 pm...so i am cussing...i made sure it wasnt just my LOCAL MACHINE but unfortunitly not the case...Wel the main prob is NO ERRORS to clue me in , just slow.....so what would cause it to be fast and ok for a few hours and crap th next..... I HAVE TO BE MISSING SOMTHING..... i have bee looking in the php forums and the mysql forums with not too much luck. did see a few post about slowness due buffer issues but i am good. very frustrating
Termina
Are you sure it isn't the local machine (or the local network)?

If the people here didn't see any problems, and at work you didn't see any problems, yet in ONE PLACE you see problems... well it's probably that one place. wink.gif
andy1974
so you didnt see any problems?? didnt load slow for you?? dry.gif
Termina
QUOTE (andy1974 @ May 16 2006, 06:46 AM)
so you didnt see any problems?? didnt load slow for you?? dry.gif

I apologize. When I last tried it, it was working just fine.

I tried going to it now, and it does take a rather long time for things to load.

Have you tried running 'top' on the machine that hosts this, when you're loading pages?

Have you checked your error.log file for apache?

Could you PM me your AIM SN or Email addy?
andy1974
I will give you my AIM and addy here...damn PM wont work ...it says email error


[Removed by Termina: PM works, just spits out an error :/]


I would apreciate any help your willing to give...this problem been bugging me for weeks
andy1974
OK i managed to get the slow logs running and i am getting data but dont know how to read it

here is some of the log

/usr/libexec/mysqld, Version: 5.0.21-log. started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 060523 17:38:14
# User@Host: admin[admin] @ [192.168.1.40]
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use forums;
UPDATE online SET logged=1148420294 WHERE ident='192.168.1.1';
# User@Host: admin[admin] @ [192.168.1.40]
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 7
SELECT g_id, g_title FROM groups WHERE g_id!=3 ORDER BY g_id;
# Time: 060523 17:40:20
# User@Host: admin[admin] @ [192.168.1.40]
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE online SET logged=1148420420 WHERE ident='192.168.1.1';
# Time: 060523 17:40:35
# User@Host: admin[admin] @ [192.168.1.40]
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE online SET logged=1148420435 WHERE ident='192.168.1.1';
# User@Host: admin[admin] @ [192.168.1.40]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
SELECT id, username FROM users ORDER BY registered DESC LIMIT 1;
# Time: 060523 17:43:14
# User@Host: root[root] @ localhost []
# Query_time: 1 Lock_time: 1 Rows_sent: 1 Rows_examined: 1
SHOW VARIABLES LIKE 'datadir';
# User@Host: root[root] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
SHOW VARIABLES LIKE 'log_error';
unsure.gif
andy1974
Well i guess i am giving up on this for now.......i just dont know what else to do ....but i did want to thank those for thier responces.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2017 Invision Power Services, Inc.