Linux Help
guides forums blogs
Home Desktops Distributions ISO Images Logos Newbies Reviews Software Support & Resources Linuxhelp Wiki

Welcome Guest ( Log In | Register )



Advanced DNS Management
New ZoneEdit. New Managment.

FREE DNS Is Back

Sign Up Now
 
Reply to this topicStart new topic
> The Slow Query Log mysql 5.0, The Slow Query Log mysql 5.0
andy1974
post May 1 2006, 02:47 PM
Post #1


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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

This post has been edited by DS2K3: May 1 2006, 03:08 PM
Go to the top of the page
 
+Quote Post
Robert83
post May 1 2006, 03:10 PM
Post #2


Its GNU/Linuxhelp.net
*******

Group: Support Specialist
Posts: 1,439
Joined: 3-January 04
From: Germany
Member No.: 2,069



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


--------------------
Robert Becskei
robert83@linuxhelp.net
--------------------
May the source be with us!
--------------------
AMD X2-3800 @ 2400Mhz
2048MB DDR 400Mhz
DFI Lanparty UT4 NF4 ULTRA-D
GeForce 7800GT
250GB+250GB
Pioneer DVD-RW
17inch Samsung Syncmaster 757NF
WinXP Pro (SP2)/ CentOS 4.3
--------------------
Go to the top of the page
 
+Quote Post
andy1974
post May 1 2006, 04:17 PM
Post #3


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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
Go to the top of the page
 
+Quote Post
Robert83
post May 1 2006, 05:24 PM
Post #4


Its GNU/Linuxhelp.net
*******

Group: Support Specialist
Posts: 1,439
Joined: 3-January 04
From: Germany
Member No.: 2,069



Hi,

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

Sincerely
Robert B


--------------------
Robert Becskei
robert83@linuxhelp.net
--------------------
May the source be with us!
--------------------
AMD X2-3800 @ 2400Mhz
2048MB DDR 400Mhz
DFI Lanparty UT4 NF4 ULTRA-D
GeForce 7800GT
250GB+250GB
Pioneer DVD-RW
17inch Samsung Syncmaster 757NF
WinXP Pro (SP2)/ CentOS 4.3
--------------------
Go to the top of the page
 
+Quote Post
andy1974
post May 1 2006, 08:05 PM
Post #5


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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
Go to the top of the page
 
+Quote Post
Robert83
post May 2 2006, 11:15 AM
Post #6


Its GNU/Linuxhelp.net
*******

Group: Support Specialist
Posts: 1,439
Joined: 3-January 04
From: Germany
Member No.: 2,069



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


--------------------
Robert Becskei
robert83@linuxhelp.net
--------------------
May the source be with us!
--------------------
AMD X2-3800 @ 2400Mhz
2048MB DDR 400Mhz
DFI Lanparty UT4 NF4 ULTRA-D
GeForce 7800GT
250GB+250GB
Pioneer DVD-RW
17inch Samsung Syncmaster 757NF
WinXP Pro (SP2)/ CentOS 4.3
--------------------
Go to the top of the page
 
+Quote Post
andy1974
post May 2 2006, 02:11 PM
Post #7


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



No not fixed yet ... but thanks for the info
Go to the top of the page
 
+Quote Post
DS2K3
post May 2 2006, 02:19 PM
Post #8


Its GNU/Linuxhelp.net
*******

Group: Support Specialist
Posts: 1,284
Joined: 14-November 04
From: Southampton, Hampshire
Member No.: 4,165



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


--------------------
Fixed your problem? Let us know!
richard@linuxhelp.net

www.Gathr.co.uk Online Event Management
Go to the top of the page
 
+Quote Post
andy1974
post May 6 2006, 10:13 PM
Post #9


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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
Go to the top of the page
 
+Quote Post
andy1974
post May 12 2006, 11:26 AM
Post #10


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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??
Go to the top of the page
 
+Quote Post
DS2K3
post May 13 2006, 05:50 AM
Post #11


Its GNU/Linuxhelp.net
*******

Group: Support Specialist
Posts: 1,284
Joined: 14-November 04
From: Southampton, Hampshire
Member No.: 4,165



Probably in index.php - After the first "<?php"

D


--------------------
Fixed your problem? Let us know!
richard@linuxhelp.net

www.Gathr.co.uk Online Event Management
Go to the top of the page
 
+Quote Post
andy1974
post May 13 2006, 12:00 PM
Post #12


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



ok i got that added what am i looking for after i restart?
Go to the top of the page
 
+Quote Post
DS2K3
post May 14 2006, 05:47 AM
Post #13


Its GNU/Linuxhelp.net
*******

Group: Support Specialist
Posts: 1,284
Joined: 14-November 04
From: Southampton, Hampshire
Member No.: 4,165



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.


--------------------
Fixed your problem? Let us know!
richard@linuxhelp.net

www.Gathr.co.uk Online Event Management
Go to the top of the page
 
+Quote Post
andy1974
post May 15 2006, 01:24 PM
Post #14


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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
Go to the top of the page
 
+Quote Post
Termina
post May 15 2006, 11:17 PM
Post #15


RMS is my Hero
******

Group: Support Specialist
Posts: 862
Joined: 18-February 04
From: Wisconsin
Member No.: 2,404



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


--------------------
*Points finger at the author above him* They're a witch! Burn them!
---
Vist my website!
Join me in IRC! Server: st0rage.org Channel: #UnhandledExceptions
Go to the top of the page
 
+Quote Post
andy1974
post May 16 2006, 06:46 AM
Post #16


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



so you didnt see any problems?? didnt load slow for you?? dry.gif
Go to the top of the page
 
+Quote Post
Termina
post May 17 2006, 11:14 AM
Post #17


RMS is my Hero
******

Group: Support Specialist
Posts: 862
Joined: 18-February 04
From: Wisconsin
Member No.: 2,404



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?


--------------------
*Points finger at the author above him* They're a witch! Burn them!
---
Vist my website!
Join me in IRC! Server: st0rage.org Channel: #UnhandledExceptions
Go to the top of the page
 
+Quote Post
andy1974
post May 17 2006, 01:37 PM
Post #18


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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
Go to the top of the page
 
+Quote Post
andy1974
post May 23 2006, 04:28 PM
Post #19


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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
Go to the top of the page
 
+Quote Post
andy1974
post May 30 2006, 06:44 PM
Post #20


./configure
***

Group: Members
Posts: 133
Joined: 24-February 04
Member No.: 2,454



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.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 13th December 2017 - 01:57 PM