Re: Understanding Slow Query Log
2012/9/5 Adarsh Sharma eddy.ada...@gmail.com Actually that query is not my concern : i have a query that is taking so much time : Slow Log Output : # Overall: 195 total, 16 unique, 0.00 QPS, 0.31x concurrency _ # Time range: 2012-09-01 14:30:01 to 2012-09-04 14:13:46 # Attribute total min max avg 95% stddev median # === === === === === === === # Exec time 80887s 192us 2520s415s 1732s612s 80s # Lock time 13ms 0 133us68us 103us23us69us # Rows sent430.89k 0 17.58k 2.21k 12.50k 3.96k 49.17 # Rows examine 32.30M 0 466.46k 169.63k 440.37k 186.02k 117.95k # Query size65.45k 6 577 343.70 563.87 171.06 246.02 In the logs output : # Query_time: 488.031783 Lock_time: 0.41 Rows_sent: 50 Rows_examined: 471150 SET timestamp=1346655789; SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id FROM WF_1 t0 WHERE t0.bean_type = 'Workflow' ORDER BY t0.created_time DESC LIMIT 0, 50; The table is near about 30 GB and growing day by day. Just out curiosity, is that table too fragmented? 471k rows are quite a lot, but 488 of query time is insane. Seems you're reading from disk too much! Attaching the table definition indexes output. I have a index on bean type column but cann't understand why it examined the all rows of table. Where's the table's schema so we can give it a try? Manu
Re: Understanding Slow Query Log
I already attached the list. Attaching one more time thanks for the interest. Cheers On Wed, Sep 5, 2012 at 11:44 AM, Manuel Arostegui man...@tuenti.com wrote: 2012/9/5 Adarsh Sharma eddy.ada...@gmail.com Actually that query is not my concern : i have a query that is taking so much time : Slow Log Output : # Overall: 195 total, 16 unique, 0.00 QPS, 0.31x concurrency _ # Time range: 2012-09-01 14:30:01 to 2012-09-04 14:13:46 # Attribute total min max avg 95% stddev median # === === === === === === === # Exec time 80887s 192us 2520s415s 1732s612s 80s # Lock time 13ms 0 133us68us 103us23us69us # Rows sent430.89k 0 17.58k 2.21k 12.50k 3.96k 49.17 # Rows examine 32.30M 0 466.46k 169.63k 440.37k 186.02k 117.95k # Query size65.45k 6 577 343.70 563.87 171.06 246.02 In the logs output : # Query_time: 488.031783 Lock_time: 0.41 Rows_sent: 50 Rows_examined: 471150 SET timestamp=1346655789; SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id FROM WF_1 t0 WHERE t0.bean_type = 'Workflow' ORDER BY t0.created_time DESC LIMIT 0, 50; The table is near about 30 GB and growing day by day. Just out curiosity, is that table too fragmented? 471k rows are quite a lot, but 488 of query time is insane. Seems you're reading from disk too much! Attaching the table definition indexes output. I have a index on bean type column but cann't understand why it examined the all rows of table. Where's the table's schema so we can give it a try? Manu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
true Michael, pasting the output : CREATE TABLE `WF_1` ( `id` varchar(255) NOT NULL, `app_name` varchar(255) DEFAULT NULL, `app_path` varchar(255) DEFAULT NULL, `conf` text, `group_name` varchar(255) DEFAULT NULL, `parent_id` varchar(255) DEFAULT NULL, `run` int(11) DEFAULT NULL, `user_name` varchar(255) DEFAULT NULL, `bean_type` varchar(31) DEFAULT NULL, `auth_token` text, `created_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `external_id` varchar(255) DEFAULT NULL, `last_modified_time` datetime DEFAULT NULL, `log_token` varchar(255) DEFAULT NULL, `proto_action_conf` text, `sla_xml` text, `start_time` datetime DEFAULT NULL, `status` varchar(255) DEFAULT NULL, `wf_instance` mediumblob, PRIMARY KEY (`id`), KEY `I_WF_1_DTYPE` (`bean_type`), KEY `I_WF_1_END_TIME` (`end_time`), KEY `I_WF_1_EXTERNAL_ID` (`external_id`), KEY `I_WF_1_LAST_MODIFIED_TIME` (`last_modified_time`), KEY `I_WF_1_STATUS` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | show indexes from WF_1; +-++--+--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--++---+-+--++--++-+ |WF_1 | 0 | PRIMARY |1 | id | A | 551664 | NULL | NULL | | BTREE | | |WF_1 | 1 | I_WF_1_DTYPE |1 | bean_type | A | 18 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_END_TIME |1 | end_time | A | 551664 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_EXTERNAL_ID|1 | external_id | A | 551664 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_LAST_MODIFIED_TIME |1 | last_modified_time | A | 551664 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_STATUS |1 | status | A | 18 | NULL | NULL | YES | BTREE | | +-++--+--++---+-+--++--++-+ Thanks On Wed, Sep 5, 2012 at 8:43 PM, Michael Dykman mdyk...@gmail.com wrote: The attachments do not appear to be coming through. I am more curious what the cardinality of bean_type is. What is the result of select count(*) as cnt, bean_type from WS_1 group by bean_type ? Low cardinality can render an index usrless. On 2012-09-05 5:19 AM, Adarsh Sharma eddy.ada...@gmail.com wrote: I already attached the list. Attaching one more time thanks for the interest. Cheers On Wed, Sep 5, 2012 at 11:44 AM, Manuel Arostegui man...@tuenti.com wrote: 2012/9/5 Adar... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Understanding Slow Query Log
* SHOW VARIABLES LIKE 'innodb%'; -- some of them may be hurting performance. * More that 20% of the table has bean_type = 'Workflow'? -- if so, it is more efficient to do a table scan than to use the index. * KEY `I_WF_1_DTYPE` (`bean_type`), -- KEY bean_time (`bean_type`, created_time) Compound index may be your cure. * Fields with low cardinality (bean_type, status) make very poor INDEXes. * Consider using an ENUM instead of VARCHAR for status and bean_type, (and others?) * VARCHAR(255) is an awful PRIMARY KEY. The PK is included implicitly (in InnoDB) in every secondary key. * LIMIT 0, 50 -- are you doing pagination via OFFSET? Bad idea. Lots more about these topics is discussed in similar questions in http://forums.mysql.com/list.php?24 Lots more tips here: http://mysql.rjweb.org/doc.php/ricksrots -Original Message- From: Adarsh Sharma [mailto:eddy.ada...@gmail.com] Sent: Wednesday, September 05, 2012 11:27 AM To: Michael Dykman Cc: mysql@lists.mysql.com Subject: Re: Understanding Slow Query Log true Michael, pasting the output : CREATE TABLE `WF_1` ( `id` varchar(255) NOT NULL, `app_name` varchar(255) DEFAULT NULL, `app_path` varchar(255) DEFAULT NULL, `conf` text, `group_name` varchar(255) DEFAULT NULL, `parent_id` varchar(255) DEFAULT NULL, `run` int(11) DEFAULT NULL, `user_name` varchar(255) DEFAULT NULL, `bean_type` varchar(31) DEFAULT NULL, `auth_token` text, `created_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `external_id` varchar(255) DEFAULT NULL, `last_modified_time` datetime DEFAULT NULL, `log_token` varchar(255) DEFAULT NULL, `proto_action_conf` text, `sla_xml` text, `start_time` datetime DEFAULT NULL, `status` varchar(255) DEFAULT NULL, `wf_instance` mediumblob, PRIMARY KEY (`id`), KEY `I_WF_1_DTYPE` (`bean_type`), KEY `I_WF_1_END_TIME` (`end_time`), KEY `I_WF_1_EXTERNAL_ID` (`external_id`), KEY `I_WF_1_LAST_MODIFIED_TIME` (`last_modified_time`), KEY `I_WF_1_STATUS` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | show indexes from WF_1; +-++--+--+- ---+---+-+--++- -++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+- ---+---+-+--++- -++-+ |WF_1 | 0 | PRIMARY |1 | id | A | 551664 | NULL | NULL | | BTREE | | |WF_1 | 1 | I_WF_1_DTYPE |1 | bean_type | A | 18 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_END_TIME |1 | end_time | A | 551664 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_EXTERNAL_ID|1 | external_id | A | 551664 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_LAST_MODIFIED_TIME |1 | last_modified_time | A | 551664 | NULL | NULL | YES | BTREE | | |WF_1 | 1 | I_WF_1_STATUS |1 | status | A | 18 | NULL | NULL | YES | BTREE | | +-++--+--+- ---+---+-+--++- -++-+ Thanks On Wed, Sep 5, 2012 at 8:43 PM, Michael Dykman mdyk...@gmail.com wrote: The attachments do not appear to be coming through. I am more curious what the cardinality of bean_type is. What is the result of select count(*) as cnt, bean_type from WS_1 group by bean_type ? Low cardinality can render an index usrless. On 2012-09-05 5:19 AM, Adarsh Sharma eddy.ada...@gmail.com wrote: I already attached the list. Attaching one more time thanks for the interest. Cheers On Wed, Sep 5, 2012 at 11:44 AM, Manuel Arostegui man...@tuenti.com wrote: 2012/9/5 Adar... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
Ok, this raises a question for me - what's a better way to do pagination? On 9/5/12 2:02 PM, Rick James wrote: * LIMIT 0, 50 -- are you doing pagination via OFFSET? Bad idea. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Understanding Slow Query Log
Remember where you left off. Your Next button now says something like ?page=5size=50 When you get there, you are doing something like SELECT ... ORDER BY ... LIMIT 250, 50 Instead... Make it say ?after_id=12345size=50 and then do SELECT ... WHERE id 12345 ORDER BY ... LIMIT 51 With 51, you get 3 things: * the 50 items (or fewer) for the page * a clue that there will be a Next page * the id of the first item for that Next page 'Exercises for the reader': * 'Prev' * each of the next 5 * each of the previous 5 * go to last page * go to first page * Knowing whether to have those links or 'gray them out'. A sample UI layout (you've probably seen web pages like this): GoTo Page [1] ... [13] [14] 15 [16] [17] ... [last] Where * [] represents a link. * You are currently (for this example) on page 15 * It is showing you only the Next/Prev 2 pages. I have encountered multiple cases where a crawler (eg, search engine) brought a site to its knees because of pagination via OFFSET. Pagination via OFFSET is Order(N) to fetch a page; Order(N*N) to scan the entire list. The first page takes 1 unit of effort. The second takes 2; etc. By the time the entire list has been paged through, about N*N/2 units of work have been done. My technique is Order(1) for a page, Order(N) for a complete scan. N is the number of pages. Some implementations have more than 10,000 pages. 10,000 * 10,000 = 100 million ! -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Wednesday, September 05, 2012 2:05 PM To: mysql@lists.mysql.com Subject: Re: Understanding Slow Query Log Ok, this raises a question for me - what's a better way to do pagination? On 9/5/12 2:02 PM, Rick James wrote: * LIMIT 0, 50 -- are you doing pagination via OFFSET? Bad idea. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
Thanks, Rick - definitely something to think about. I've been troubled by the pagination stuff in our code. This looks like something I can definitely use! andy On 9/5/12 2:40 PM, Rick James wrote: Remember where you left off. Your Next button now says something like ?page=5size=50 When you get there, you are doing something like SELECT ... ORDER BY ... LIMIT 250, 50 Instead... Make it say ?after_id=12345size=50 and then do SELECT ... WHERE id 12345 ORDER BY ... LIMIT 51 With 51, you get 3 things: * the 50 items (or fewer) for the page * a clue that there will be a Next page * the id of the first item for that Next page 'Exercises for the reader': * 'Prev' * each of the next 5 * each of the previous 5 * go to last page * go to first page * Knowing whether to have those links or 'gray them out'. A sample UI layout (you've probably seen web pages like this): GoTo Page [1] ... [13] [14] 15 [16] [17] ... [last] Where * [] represents a link. * You are currently (for this example) on page 15 * It is showing you only the Next/Prev 2 pages. I have encountered multiple cases where a crawler (eg, search engine) brought a site to its knees because of pagination via OFFSET. Pagination via OFFSET is Order(N) to fetch a page; Order(N*N) to scan the entire list. The first page takes 1 unit of effort. The second takes 2; etc. By the time the entire list has been paged through, about N*N/2 units of work have been done. My technique is Order(1) for a page, Order(N) for a complete scan. N is the number of pages. Some implementations have more than 10,000 pages. 10,000 * 10,000 = 100 million ! -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Wednesday, September 05, 2012 2:05 PM To: mysql@lists.mysql.com Subject: Re: Understanding Slow Query Log Ok, this raises a question for me - what's a better way to do pagination? On 9/5/12 2:02 PM, Rick James wrote: * LIMIT 0, 50 -- are you doing pagination via OFFSET? Bad idea. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Understanding Slow Query Log
100 is tantamount to turning off the log. I prefer 2.
select count(ENTITY_NAME)
from ALERT_EVENTS
where EVENT_TIME date_sub(now(),INTERVAL 60 MINUTE)
and status=upper('failed')
and ENTITY_NAME='FETL-ImpressionRC-conversion';
begs for the _compound_ index
INDEX(ENTITY_NAME, EVENT_TIME)
This would be even better:
INDEX(ENTITY_NAME, status, EVENT_TIME)
COUNT(*) should be used if you don't need to check the column for being NULL.
Rows_examined: 141145
That is probably the entire table. Will the table grow? If so, the query will
get slower. Meanwhile, the index I suggested will (probably) be much faster.
-Original Message-
From: Suresh Kuna [mailto:sureshkumar...@gmail.com]
Sent: Saturday, September 01, 2012 1:03 AM
To: Adarsh Sharma
Cc: mysql@lists.mysql.com
Subject: Re: Understanding Slow Query Log
Disable log-queries-not-using-indexes to log only queries 100 sec.
Just do /var/lib/mysql/slow-queries.log it will clear the log.
On Sat, Sep 1, 2012 at 12:34 PM, Adarsh Sharma
eddy.ada...@gmail.comwrote:
Hi all,
I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow
query log by setting below parameters in my.cnf :
log-slow-queries=/usr/local/mysql/slow-query.log
long_query_time=100
log-queries-not-using-indexes
I am assuming from the inf. from the internet that long_query_time is
in seconds , but i see the slow query log , there are lots of
statements ( queries ) :
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.052784 Lock_time: 0.43 Rows_sent: 1
Rows_examined:
141145
SET timestamp=1346409734;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:14
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.053599 Lock_time: 0.79 Rows_sent: 1
Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-click-enhancer-deferred';
# User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.054185 Lock_time: 0.86 Rows_sent: 1
Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:22
# User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.000163 Lock_time: 0.45 Rows_sent: 1
Rows_examined:
13
I don't understand the query time unit in slow query log because i
expect queries to be logged that takes 100 s. I tested with sleep
command for 60s , it doesn't logged in slow query log and when i
sleep
for 120 s it logged but i don't why the other queries are logging in
slow log.
# Query_time: 120.000259 Lock_time: 0.00 Rows_sent: 1
Rows_examined:
0
SET timestamp=1346443103;
SELECT SLEEP(120);
And also my slow log is increasing and decided to purge thorogh below
command :
cat /dev/null /var/lib/mysql/slow-queries.log
Anyone any ideas about this.
Thanks
--
Thanks
Suresh Kuna
MySQL DBA
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
Actually that query is not my concern :
i have a query that is taking so much time :
Slow Log Output :
# Overall: 195 total, 16 unique, 0.00 QPS, 0.31x concurrency _
# Time range: 2012-09-01 14:30:01 to 2012-09-04 14:13:46
# Attribute total min max avg 95% stddev median
# === === === === === === ===
# Exec time 80887s 192us 2520s415s 1732s612s 80s
# Lock time 13ms 0 133us68us 103us23us69us
# Rows sent430.89k 0 17.58k 2.21k 12.50k 3.96k 49.17
# Rows examine 32.30M 0 466.46k 169.63k 440.37k 186.02k 117.95k
# Query size65.45k 6 577 343.70 563.87 171.06 246.02
In the logs output :
# Query_time: 488.031783 Lock_time: 0.41 Rows_sent: 50 Rows_examined:
471150
SET timestamp=1346655789;
SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name,
t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time,
t0.external_id FROM WF_1 t0 WHERE t0.bean_type = 'Workflow' ORDER BY
t0.created_time DESC LIMIT 0, 50;
The table is near about 30 GB and growing day by day.
Attaching the table definition indexes output. I have a index on bean
type column but cann't understand why it
examined the all rows of table.
Thanks
On Wed, Sep 5, 2012 at 12:24 AM, Rick James rja...@yahoo-inc.com wrote:
100 is tantamount to turning off the log. I prefer 2.
select count(ENTITY_NAME)
from ALERT_EVENTS
where EVENT_TIME date_sub(now(),INTERVAL 60 MINUTE)
and status=upper('failed')
and ENTITY_NAME='FETL-ImpressionRC-conversion';
begs for the _compound_ index
INDEX(ENTITY_NAME, EVENT_TIME)
This would be even better:
INDEX(ENTITY_NAME, status, EVENT_TIME)
COUNT(*) should be used if you don't need to check the column for being
NULL.
Rows_examined: 141145
That is probably the entire table. Will the table grow? If so, the query
will get slower. Meanwhile, the index I suggested will (probably) be much
faster.
-Original Message-
From: Suresh Kuna [mailto:sureshkumar...@gmail.com]
Sent: Saturday, September 01, 2012 1:03 AM
To: Adarsh Sharma
Cc: mysql@lists.mysql.com
Subject: Re: Understanding Slow Query Log
Disable log-queries-not-using-indexes to log only queries 100 sec.
Just do /var/lib/mysql/slow-queries.log it will clear the log.
On Sat, Sep 1, 2012 at 12:34 PM, Adarsh Sharma
eddy.ada...@gmail.comwrote:
Hi all,
I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow
query log by setting below parameters in my.cnf :
log-slow-queries=/usr/local/mysql/slow-query.log
long_query_time=100
log-queries-not-using-indexes
I am assuming from the inf. from the internet that long_query_time is
in seconds , but i see the slow query log , there are lots of
statements ( queries ) :
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.052784 Lock_time: 0.43 Rows_sent: 1
Rows_examined:
141145
SET timestamp=1346409734;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:14
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.053599 Lock_time: 0.79 Rows_sent: 1
Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-click-enhancer-deferred';
# User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.054185 Lock_time: 0.86 Rows_sent: 1
Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:22
# User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111] #
Query_time: 0.000163 Lock_time: 0.45 Rows_sent: 1
Rows_examined:
13
I don't understand the query time unit in slow query log because i
expect queries to be logged that takes 100 s. I tested with sleep
command for 60s , it doesn't logged in slow query log and when i
sleep
for 120 s it logged but i don't why the other queries are logging in
slow log.
# Query_time: 120.000259 Lock_time: 0.00 Rows_sent: 1
Rows_examined:
0
SET timestamp=1346443103;
SELECT SLEEP(120);
And also my slow log is increasing and decided to purge thorogh below
command :
cat /dev/null /var/lib/mysql/slow-queries.log
Anyone any ideas about this.
Thanks
--
Thanks
Suresh Kuna
MySQL DBA
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
Understanding Slow Query Log
Hi all,
I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow query log
by setting below parameters in my.cnf :
log-slow-queries=/usr/local/mysql/slow-query.log
long_query_time=100
log-queries-not-using-indexes
I am assuming from the inf. from the internet that long_query_time is in
seconds , but i see the slow query log , there are lots of statements (
queries ) :
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.052784 Lock_time: 0.43 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409734;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:14
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.053599 Lock_time: 0.79 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-click-enhancer-deferred';
# User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.054185 Lock_time: 0.86 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:22
# User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.000163 Lock_time: 0.45 Rows_sent: 1 Rows_examined: 13
I don't understand the query time unit in slow query log because i expect
queries to be logged that takes 100 s. I tested with sleep command for
60s , it doesn't logged in slow query log and when i sleep for 120 s it
logged but i don't why the other queries are logging in slow log.
# Query_time: 120.000259 Lock_time: 0.00 Rows_sent: 1 Rows_examined: 0
SET timestamp=1346443103;
SELECT SLEEP(120);
And also my slow log is increasing and decided to purge thorogh below
command :
cat /dev/null /var/lib/mysql/slow-queries.log
Anyone any ideas about this.
Thanks
Re: Understanding Slow Query Log
Hi
Because of that, those queries don't use index.
log-queries-not-using-indexes works even if query time less than
long-query-time.
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes
regards,
yoku
2012/9/1 Adarsh Sharma eddy.ada...@gmail.com:
Hi all,
I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow query log
by setting below parameters in my.cnf :
log-slow-queries=/usr/local/mysql/slow-query.log
long_query_time=100
log-queries-not-using-indexes
I am assuming from the inf. from the internet that long_query_time is in
seconds , but i see the slow query log , there are lots of statements (
queries ) :
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.052784 Lock_time: 0.43 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409734;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:14
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.053599 Lock_time: 0.79 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-click-enhancer-deferred';
# User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.054185 Lock_time: 0.86 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:22
# User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.000163 Lock_time: 0.45 Rows_sent: 1 Rows_examined: 13
I don't understand the query time unit in slow query log because i expect
queries to be logged that takes 100 s. I tested with sleep command for
60s , it doesn't logged in slow query log and when i sleep for 120 s it
logged but i don't why the other queries are logging in slow log.
# Query_time: 120.000259 Lock_time: 0.00 Rows_sent: 1 Rows_examined: 0
SET timestamp=1346443103;
SELECT SLEEP(120);
And also my slow log is increasing and decided to purge thorogh below
command :
cat /dev/null /var/lib/mysql/slow-queries.log
Anyone any ideas about this.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
Disable log-queries-not-using-indexes to log only queries 100 sec.
Just do /var/lib/mysql/slow-queries.log it will clear the log.
On Sat, Sep 1, 2012 at 12:34 PM, Adarsh Sharma eddy.ada...@gmail.comwrote:
Hi all,
I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow query log
by setting below parameters in my.cnf :
log-slow-queries=/usr/local/mysql/slow-query.log
long_query_time=100
log-queries-not-using-indexes
I am assuming from the inf. from the internet that long_query_time is in
seconds , but i see the slow query log , there are lots of statements (
queries ) :
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.052784 Lock_time: 0.43 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409734;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:14
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.053599 Lock_time: 0.79 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-click-enhancer-deferred';
# User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.054185 Lock_time: 0.86 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:22
# User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.000163 Lock_time: 0.45 Rows_sent: 1 Rows_examined: 13
I don't understand the query time unit in slow query log because i expect
queries to be logged that takes 100 s. I tested with sleep command for
60s , it doesn't logged in slow query log and when i sleep for 120 s it
logged but i don't why the other queries are logging in slow log.
# Query_time: 120.000259 Lock_time: 0.00 Rows_sent: 1 Rows_examined:
0
SET timestamp=1346443103;
SELECT SLEEP(120);
And also my slow log is increasing and decided to purge thorogh below
command :
cat /dev/null /var/lib/mysql/slow-queries.log
Anyone any ideas about this.
Thanks
--
Thanks
Suresh Kuna
MySQL DBA
Re: Enabling Slow query log in Mysql 5.0
Hi Machiel, The below link will help you. mk-query-digesthttp://www.xaprb.com/blog/category/maatkit/ Regards, Krishna On Tue, Feb 16, 2010 at 12:51 PM, Machiel Richards machi...@rdc.co.zawrote: Hi All I hope that someone can assist me with this. We have a client with a production MySQL database running MySQL 5.0. Their slow query counts have skyrocketed over the last week and I found that their slow query logs are not enabled. However when trying to configure this I get the following message: mysql set global log_slow_queries=ON; ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable mysql I did the same thing many times before on other databases but this specific one gives me this message. Can anyone perhaps give me some insight as to why i'm getting this and how to enable it (preferably without having to restart the database seeing it is a high availability production system)? Help is much appreciated. Regards
Re: Enabling Slow query log in Mysql 5.0
Machiel Richards wrote: Hi All I hope that someone can assist me with this. We have a client with a production MySQL database running MySQL 5.0. Their slow query counts have skyrocketed over the last week and I found that their slow query logs are not enabled. However when trying to configure this I get the following message: mysql set global log_slow_queries=ON; ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable mysql I did the same thing many times before on other databases but this specific one gives me this message. Can anyone perhaps give me some insight as to why i'm getting this and how to enable it (preferably without having to restart the database seeing it is a high availability production system)? You can do what you described in version 5.1 but not in 5.0 . For 5.0 that variable is not dynamic. That means you cannot change it while the system is running: http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_log-slow-queries One way around this is to setup the machine with the Slow Query Log enabled but to use a very large value of --long-query-time to essentially ignore every query. Then, when you want to capture slow queries, you reset --long-query-time to a reasonable value. Unfortunately, this requires a restart to initialize. After that you can adjust the --long-query-time to throttle the contents of the log. http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_long_query_time -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Enabling Slow query log in Mysql 5.0
Hi All I hope that someone can assist me with this. We have a client with a production MySQL database running MySQL 5.0. Their slow query counts have skyrocketed over the last week and I found that their slow query logs are not enabled. However when trying to configure this I get the following message: mysql set global log_slow_queries=ON; ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable mysql I did the same thing many times before on other databases but this specific one gives me this message. Can anyone perhaps give me some insight as to why i'm getting this and how to enable it (preferably without having to restart the database seeing it is a high availability production system)? Help is much appreciated. Regards
RE: MYSQL slow query log in table.
Use an etl tool like Talend to load the slow query log into a table. We do something similar with the general query log very successfully with mysql 5 Even with 5.1 i would use this approach over the built in log tables as the built in log tables impact performance quite significantly. Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: Brown, Charles cbr...@bmi.com Sent: Thursday, October 08, 2009 4:37 PM To: Jaime Crespo Rincón jcre...@warp.es; mysql@lists.mysql.com mysql@lists.mysql.com Subject: RE: MYSQL slow query log in table. Hello. Thanks for quick response. I'm running Mysql 5.0. We have no plans to upgrade to 5.1 that supports slow-log table. Do you know of any work around to get my slow query into a table -- just wondering. Desperate for a resolution or circumvention. -Original Message- From: Jaime Crespo Rincón [mailto:jcre...@warp.es] Sent: Thursday, October 08, 2009 10:17 AM To: Brown, Charles Cc: Daevid Vincent; mysql@lists.mysql.com Subject: Re: MYSQL slow query log in table. 2009/10/7 Brown, Charles cbr...@bmi.com: Hello All. I would like to implement MYSQL slow query log in table. Can someone kindly assist me with the table definition and implementation. SHOW CREATE TABLE mysql.slow_log; http://dev.mysql.com/doc/refman/5.1/en/log-tables.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. [The entire original message is not included] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MYSQL slow query log in table.
2009/10/7 Brown, Charles cbr...@bmi.com: Hello All. I would like to implement MYSQL slow query log in table. Can someone kindly assist me with the table definition and implementation. SHOW CREATE TABLE mysql.slow_log; http://dev.mysql.com/doc/refman/5.1/en/log-tables.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MYSQL slow query log in table.
Hello. Thanks for quick response. I'm running Mysql 5.0. We have no plans to upgrade to 5.1 that supports slow-log table. Do you know of any work around to get my slow query into a table -- just wondering. Desperate for a resolution or circumvention. -Original Message- From: Jaime Crespo Rincón [mailto:jcre...@warp.es] Sent: Thursday, October 08, 2009 10:17 AM To: Brown, Charles Cc: Daevid Vincent; mysql@lists.mysql.com Subject: Re: MYSQL slow query log in table. 2009/10/7 Brown, Charles cbr...@bmi.com: Hello All. I would like to implement MYSQL slow query log in table. Can someone kindly assist me with the table definition and implementation. SHOW CREATE TABLE mysql.slow_log; http://dev.mysql.com/doc/refman/5.1/en/log-tables.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you.
MYSQL slow query log in table.
Hello All. I would like to implement MYSQL slow query log in table. Can someone kindly assist me with the table definition and implementation. Thanks so much in advance This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Questions on un-index searches and slow-query-log
Questions Folks: (1) What do you about un-index searches. How can one report and monitor them? (2) What do you do with the slow-query log. Are there any utilities or scripts out there to filter and manage this log? Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Questions on un-index searches and slow-query-log
See log-queries-not-using-indexes option in my.cnf, used with the slow log. http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html Regards, Gavin Towey -Original Message- From: Brown, Charles [mailto:cbr...@bmi.com] Sent: Monday, October 05, 2009 12:59 PM To: Mysql List Cc: John Meyer; Mark Phillips Subject: Questions on un-index searches and slow-query-log Questions Folks: (1) What do you about un-index searches. How can one report and monitor them? (2) What do you do with the slow-query log. Are there any utilities or scripts out there to filter and manage this log? Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions on un-index searches and slow-query-log
And an answer to 2): http://www.maatkit.org/doc/mk-query-digest.html On Tue, Oct 6, 2009 at 02:59, Brown, Charles cbr...@bmi.com wrote: Questions Folks: (1) What do you about un-index searches. How can one report and monitor them? (2) What do you do with the slow-query log. Are there any utilities or scripts out there to filter and manage this log? Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query log for just one database
I am trying to monitor a specific issue, and I know it is related to only one database. There is a lot of other noise in the logs if I enable query logging. Is there any way to limit query logging to just one database? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
updates in slow query log
Is there anyway to keep updates and deletes from showing up in the slow query logs?
Re: slow query log
It executes in 0 sec when you run it. It might be in the query cache. Try it with SQL_NO_CACHE. But even then it might run faster than it did when it got logged in the slow log, because the table's data might be in memory and therefore faster to access. The point is that the slow query log shows you how long the statement took to execute at the time of logging. It doesn't say anything about how fast the query will execute at other times, or WHY it was a long query at the time of logging. It could be a lot of things including locking, other queries running at the same time, a background Patrol Read on your RAID controller, etc. That's why I asked you to paste the slow query log entry for this query. On Fri, Jan 2, 2009 at 12:14 AM, Ananda Kumar anan...@gmail.com wrote: mysql explain SELECT SUM(COUNTER_VALUE) FROM STO_LIS sl, - SCAT_LIS sfl WHERE sl.STO_LIS_ID = - sfl.LIS_ID AND sfl.CAT_ID = '-1'; ++-+---+--+---+---+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+---+-+-+--+-+ | 1 | SIMPLE | sfl | ref | PRIMARY,sfcatlist_lst | PRIMARY | 4 | const | 23 | Using index | | 1 | SIMPLE | sl| ref | STO_LIST_UK | STO_LIS_UK | 4 | sm15.sfl.lis_id |1 | Using where | ++-+---+--+---+---+-+-+--+-+ 2 rows in set (0.31 sec) mysql SELECT SUM(COUNTER_VALUE) FROM STORES_LISTING sl, SFCATEGORY_LISTING sfl WHERE sl.STORES_LISTING_ID = sfl.LISTING_ID AND sfl.CATEGORY_ID = '-1'; ++ | SUM(COUNTER_VALUE) | ++ | NULL | ++ 1 row in set (0.00 sec) On 12/31/08, Baron Schwartz ba...@xaprb.com wrote: Hi, On Wed, Dec 31, 2008 at 8:44 AM, Ananda Kumar anan...@gmail.com wrote: Hi All, I have enabled slow query log. Generally this file will have sql's which take more than long-query time to execute and also sql's not using indexes. But i see sql's which does not come under the above condition. I have set the long-query time to 1 Sec . The query takes less than 1 sec to execute and also uses indexes, but still its recored in slow query log. What is the execution time shown in the slow query log? What is the query? If you can paste the whole log entry here, that would be good. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: slow query log
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 150 SELECT SUM(COUNTER_VALUE) FROM STO_LIS sl, SCAT_LIS sfl WHERE l.STO_LIS_ID =sfl.LIS_ID AND sfl.CAT_ID = '-1'; This is what is there in the slow-query log On 1/2/09, Baron Schwartz ba...@xaprb.com wrote: It executes in 0 sec when you run it. It might be in the query cache. Try it with SQL_NO_CACHE. But even then it might run faster than it did when it got logged in the slow log, because the table's data might be in memory and therefore faster to access. The point is that the slow query log shows you how long the statement took to execute at the time of logging. It doesn't say anything about how fast the query will execute at other times, or WHY it was a long query at the time of logging. It could be a lot of things including locking, other queries running at the same time, a background Patrol Read on your RAID controller, etc. That's why I asked you to paste the slow query log entry for this query. On Fri, Jan 2, 2009 at 12:14 AM, Ananda Kumar anan...@gmail.com wrote: mysql explain SELECT SUM(COUNTER_VALUE) FROM STO_LIS sl, - SCAT_LIS sfl WHERE sl.STO_LIS_ID = - sfl.LIS_ID AND sfl.CAT_ID = '-1'; ++-+---+--+---+---+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+---+-+-+--+-+ | 1 | SIMPLE | sfl | ref | PRIMARY,sfcatlist_lst | PRIMARY | 4 | const | 23 | Using index | | 1 | SIMPLE | sl| ref | STO_LIST_UK | STO_LIS_UK | 4 | sm15.sfl.lis_id |1 | Using where | ++-+---+--+---+---+-+-+--+-+ 2 rows in set (0.31 sec) mysql SELECT SUM(COUNTER_VALUE) FROM STORES_LISTING sl, SFCATEGORY_LISTING sfl WHERE sl.STORES_LISTING_ID = sfl.LISTING_ID AND sfl.CATEGORY_ID = '-1'; ++ | SUM(COUNTER_VALUE) | ++ | NULL | ++ 1 row in set (0.00 sec) On 12/31/08, Baron Schwartz ba...@xaprb.com wrote: Hi, On Wed, Dec 31, 2008 at 8:44 AM, Ananda Kumar anan...@gmail.com wrote: Hi All, I have enabled slow query log. Generally this file will have sql's which take more than long-query time to execute and also sql's not using indexes. But i see sql's which does not come under the above condition. I have set the long-query time to 1 Sec . The query takes less than 1 sec to execute and also uses indexes, but still its recored in slow query log. What is the execution time shown in the slow query log? What is the query? If you can paste the whole log entry here, that would be good. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: slow query log
mysql explain SELECT SUM(COUNTER_VALUE) FROM STO_LIS sl, - SCAT_LIS sfl WHERE sl.STO_LIS_ID = - sfl.LIS_ID AND sfl.CAT_ID = '-1'; ++-+---+--+---+---+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+---+-+-+--+-+ | 1 | SIMPLE | sfl | ref | PRIMARY,sfcatlist_lst | PRIMARY | 4 | const | 23 | Using index | | 1 | SIMPLE | sl| ref | STO_LIST_UK | STO_LIS_UK | 4 | sm15.sfl.lis_id |1 | Using where | ++-+---+--+---+---+-+-+--+-+ 2 rows in set (0.31 sec) mysql SELECT SUM(COUNTER_VALUE) FROM STORES_LISTING sl, SFCATEGORY_LISTING sfl WHERE sl.STORES_LISTING_ID = sfl.LISTING_ID AND sfl.CATEGORY_ID = '-1'; ++ | SUM(COUNTER_VALUE) | ++ | NULL | ++ 1 row in set (0.00 sec) On 12/31/08, Baron Schwartz ba...@xaprb.com wrote: Hi, On Wed, Dec 31, 2008 at 8:44 AM, Ananda Kumar anan...@gmail.com wrote: Hi All, I have enabled slow query log. Generally this file will have sql's which take more than long-query time to execute and also sql's not using indexes. But i see sql's which does not come under the above condition. I have set the long-query time to 1 Sec . The query takes less than 1 sec to execute and also uses indexes, but still its recored in slow query log. What is the execution time shown in the slow query log? What is the query? If you can paste the whole log entry here, that would be good. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
slow query log
Hi All, I have enabled slow query log. Generally this file will have sql's which take more than long-query time to execute and also sql's not using indexes. But i see sql's which does not come under the above condition. I have set the long-query time to 1 Sec . The query takes less than 1 sec to execute and also uses indexes, but still its recored in slow query log. Any idea why this happens. Thanks for your help. regards anandkl
Re: slow query log
I'm just guessing, but if the slow query log time resolution is seconds, perhaps 0.5 and higher rounds up? Or, perhaps it has an index, but it can't be used in that query. What does EXPLAIN [paste query here] tell you? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: slow-query log analysis
hi again, for those that are interested: the problem was indeed the filesystem with slow lookups of BIG directories (this had nothing to do with mysql but caused much iowait and therefore the mysql process had been heavily impacted). Soenke Ruempler - NorthClick wrote: I assume that those slow queries occur because there's too much IO load on the machines caused by other processes. Any hints? [...] -soenke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow-query log analysis
Hi, On Mon, Mar 17, 2008 at 12:59 PM, Soenke Ruempler - NorthClick [EMAIL PROTECTED] wrote: hi again, for those that are interested: the problem was indeed the filesystem with slow lookups of BIG directories (this had nothing to do with mysql but caused much iowait and therefore the mysql process had been heavily impacted). I'd be interested to know what filesystem you're using and how big the directories are. When you say big, do you mean number of entries in the directory, or space used? Thanks Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow-query log analysis
Hi Baron, Baron Schwartz wrote: I'd be interested to know what filesystem you're using and how big the directories are. When you say big, do you mean number of entries in the directory, or space used? There were about 70k files in /tmp (caused by a mistake). the web application on this server had many lookups to tmp and those were slowed down. Filesystem is ext3 with dir_index turned-on, noatime on an hardware raid1. -soenke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow-query log analysis
Wow! 70k files in /tmp. Hell of a mistake :) I hope it doesn't happen often. Arthur On 3/17/08, Soenke Ruempler - NorthClick [EMAIL PROTECTED] wrote: Hi Baron, There were about 70k files in /tmp (caused by a mistake). the web application on this server had many lookups to tmp and those were slowed down. -soenke
slow-query log analysis
hi, I've just ran into some problems analyzing the slow-query-log. 1. I have many entries like: # Time: 080312 13:07:33 # [EMAIL PROTECTED]: cms[cms] @ localhost [] # Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 COMMIT; # [EMAIL PROTECTED]: cms[cms] @ localhost [] # Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 COMMIT; # [EMAIL PROTECTED]: cms[cms] @ localhost [] # Query_time: 21 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 COMMIT; # [EMAIL PROTECTED]: cms[cms] @ localhost [] # Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 COMMIT; # [EMAIL PROTECTED]: cms[cms] @ localhost [] # Query_time: 12 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 COMMIT; # [EMAIL PROTECTED]: cms[cms] @ localhost [] # Query_time: 13 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 COMMIT; Is there any possibility to get some verbose information about those COMMITs? 2. We encountered some deadlocks with innodb transactions. After tuning some options: innodb_file_per_table innodb_buffer_pool_size=2G innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=2 transaction-isolation=READ-COMMITTED innodb_log_buffer_size = 4M they went away, but we're still seeing slow queries that are very simple but on tables that are often written. for example: # Time: 080312 13:15:17 # [EMAIL PROTECTED]: cms[cms] @ localhost [] # Query_time: 18 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET timestamp=1205324117; INSERT INTO login_try (website_id, login_tries) VALUES (96406, 1) ON DUPLICATE KEY UPDATE login_tries = login_tries + 1; here the table schema: mysql show create table login_try\G *** 1. row *** Table: login_try Create Table: CREATE TABLE `login_try` ( `website_id` int(11) unsigned NOT NULL, `login_tries` int(10) NOT NULL, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`website_id`), KEY `modified` (`modified`), CONSTRAINT `fk_logintry_1` FOREIGN KEY (`website_id`) REFERENCES `website_config` (`basisID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) I assume that those slow queries occur because there's too much IO load on the machines caused by other processes. Any hints? Thanks, -soenke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can slow-query-log option only record select statement?
I want to know how to configurate slow-query-log to let it not record the update sql. I just want to know how the slow select statement ,not the update or insert. Anybody's reply is appreciated,thanks. -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Mysql server general query log
Hi List, Anybody knows a tool for viewing mysql server general query log in linux. Thanks Ashok -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
purge slow query log
I would like to empty the slow query log without restarting MySQL. If I simply delete lines, the server will no longer write to the file. I have tried leaving the header, but still no writes unless I restart MySQL. I am on RHEL4. The group and owner of the file are correct: 4 -rw-rw 1 mysql mysql 157 Aug 7 06:32 slow_query_log Thanks in advance. CONFIDENTIALITY NOTICE: This email attached documents may contain confidential information. All information is intended only for the use of the named recipient. If you are not the named recipient, you are not authorized to read, disclose, copy, distribute or take any action in reliance on the information and any action other than immediate delivery to the named recipient is strictly prohibited. If you have received this email in error, do not read the information and please immediately notify sender by telephone to arrange for a return of the original documents. If you are the named recipient you are not authorized to reveal any of this information to any other unauthorized person. If you did not receive all pages listed or if pages are not legible, please immediately notify sender by phone.
Re: purge slow query log
On 8/7/07, Boyd Hemphill [EMAIL PROTECTED] wrote: I would like to empty the slow query log without restarting MySQL. If I simply delete lines, the server will no longer write to the file. I have tried leaving the header, but still no writes unless I restart MySQL. I am on RHEL4. The group and owner of the file are correct: 4 -rw-rw 1 mysql mysql 157 Aug 7 06:32 slow_query_log Thanks in advance. Just rename slow_query_log, then mysqladmin flush-logs... http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html Quote The server creates a new binary log file when you flush the logs. However, it just closes and reopens the general and slow query log files. To cause new files to be created on Unix, rename the current logs before flushing them. At flush time, the server will open new logs with the original names. For example, if the general and slow query logs are named mysql.log and mysql-slow.log, you can use a series of commands like this: shell cd mysql-data-directory shell mv mysql.log mysql.old shell mv mysql-slow.log mysql-slow.old shell mysqladmin flush-logs At this point, you can make a backup of mysql.old and mysql-slow.log and then remove them from disk. /Quote -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Query Log -- No Timestamp
On 6/26/07, Brown, Charles [EMAIL PROTECTED] wrote: The MYSQL general query log does not include timestamp of queries that it logs because queries are logged many many seconds before they are executed. Which version of MySQL are you running? I'm running 5.0.22 on my desktop, but I'm fairly sure that older versions included timestamps in the general query log. I just enabled mine to test this (by adding log = /var/log/mysql.log to /etc/my.cnf) and it looks something like: 070629 8:17:44 6 Connect [EMAIL PROTECTED] on monitoring 6 Query set autocommit=0 6 Query select * from urls where active=y 070629 8:17:45 6 Query INSERT INTO results VALUES (NULL,5,now(),0.5833,35267) 6 Query INSERT INTO results VALUES (NULL,6,now(),0.0137,0) 6 Query INSERT INTO results VALUES (NULL,8,now(),0.7762,28130) 6 Query INSERT INTO results VALUES (NULL,9,now(),0.0348,4217) -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Query Log -- No Timestamp
The MYSQL general query log does not include timestamp of queries that it logs because queries are logged many many seconds before they are executed. Can someone help me associated queries found in the query log with wall clock? I am trying to get a list of queries that were executed within a given timeslot. I would like to get the description of these queries so that I can give to my webmaster. Based on the description of these queries, he will be able to determine which area the application is causing this on going looping problem that generates 5000 request in 5 mins. Our normal request volume is about 50/min. Can someone help me? Without the timestamp, I wouldn't be able track or identify the queries that came in during the problem time frame. With all things considered, MySQL should be able to append timestamp as it writes these General query records -- I would think. Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Red Hat slow query log
Before I do this, I just wanted to check with you all to see if this is the correct command: /etc/rc.d/init.d/mysqld restart --log-slow-queries If so, where exactly will I find the slow query log? Will the slow query log be turned off by default next time I restart it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Red Hat slow query log
Brian Dunning wrote: Before I do this, I just wanted to check with you all to see if this is the correct command: /etc/rc.d/init.d/mysqld restart --log-slow-queries If so, where exactly will I find the slow query log? Will the slow query log be turned off by default next time I restart it? That's not going to work. The init script only recognises restart, stop, start as valid parameters. Once it sees one of those commands that's it. You could either hack the init.d/mysqld script or edit the my.cnf properly and set a log file: log-slow-queries= /path/to/log -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query log: administrator command: quit ?
My MySQL server (4.0.20, Linux) was running slowly. I checked the slow queries log, and found many of these during the problem period: # Time: 060730 20:44:40 # [EMAIL PROTECTED]: xxx [] # Query_time: 68 Lock_time: 0 Rows_sent: 0 Rows_examined: 2 # administrator command: Quit; # [EMAIL PROTECTED]: xxx [] # Query_time: 67 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Quit; # [EMAIL PROTECTED]: xxx [] # Query_time: 67 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 # administrator command: Quit; Any idea what these administrator commands refer to? What would be trying to quit, and why would it be taking 67 seconds? Thanks in advance for any help, TK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query log: administrator command: quit ?
TK wrote: My MySQL server (4.0.20, Linux) was running slowly. I checked the slow queries log, and found many of these during the problem period: # Time: 060730 20:44:40 # [EMAIL PROTECTED]: xxx [] # Query_time: 68 Lock_time: 0 Rows_sent: 0 Rows_examined: 2 # administrator command: Quit; # [EMAIL PROTECTED]: xxx [] # Query_time: 67 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Quit; # [EMAIL PROTECTED]: xxx [] # Query_time: 67 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 # administrator command: Quit; Any idea what these administrator commands refer to? What would be trying to quit, and why would it be taking 67 seconds? That's the only thing in the logs? I get a different format (v 4.0.24): # Time: 060731 5:46:48 # [EMAIL PROTECTED]: blah # Query_time: 56 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 use dbname; CHECK TABLE `tablename`; ie the command isn't commented out (maybe that's a cut/paste error). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[5] Starting Up General Query Log
Mac OS X 10.4.6 (Tiger), MySQL 5.0.21. Hi folks. I'm needing to start up my general query log to see what's ticking me off. I've looked into safe_mysqld but it's confusing as ... something that's confusing. Anybody know how I can easily turn this thing on for a day, then turn it off? I'm assuming put the following command has to be entered: --log = myqueries.log Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
put log or log=/path/to/file in your config file (my.cnf) and restart the server. To turn it off you have to take it out of the my.cnf and restart the server. I've put in a request to make the general log something that can be dynamically turned on. -Sheeri On 5/19/06, Rich [EMAIL PROTECTED] wrote: Mac OS X 10.4.6 (Tiger), MySQL 5.0.21. Hi folks. I'm needing to start up my general query log to see what's ticking me off. I've looked into safe_mysqld but it's confusing as ... something that's confusing. Anybody know how I can easily turn this thing on for a day, then turn it off? I'm assuming put the following command has to be entered: --log = myqueries.log Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. -Sheeri On 5/19/06, Rich [EMAIL PROTECTED] wrote: Hi there. I added the my.cnf file (it wasn't there) and put this into it: log=/var/log/myrequests.log Now MySQL won't start. Any ideas? Cheers On May 19, 2006, at 11:35 AM, sheeri kritzer wrote: put log or log=/path/to/file in your config file (my.cnf) and restart the server. To turn it off you have to take it out of the my.cnf and restart the server. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
Why this list goes private I'll never know. I guess that's why I always get two copies. In the errors log: Found option without preceding group in config file: /etc/my.cnf at line: 1 Fatal error in defaults handling. Program aborted /var/log/ does indeed exist root runs mysqld On May 19, 2006, at 12:01 PM, sheeri kritzer wrote: Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
That means that your options have no group. Options should go under the program they're intended to be run under, for instance [mysqldump] user=root [mysql.client] user=guest [mysqld] log=/path/to/logfile You want the mysqld program (mysql server) to use the general log, so put it under a section marked [mysqld] in your config file. -Sheeri On 5/19/06, Rich [EMAIL PROTECTED] wrote: Why this list goes private I'll never know. I guess that's why I always get two copies. In the errors log: Found option without preceding group in config file: /etc/my.cnf at line: 1 Fatal error in defaults handling. Program aborted /var/log/ does indeed exist root runs mysqld On May 19, 2006, at 12:01 PM, sheeri kritzer wrote: Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query log
Hello, Is there a way to enable the Slow Query Log on the fly without having to restart mysqld Regards, Marc.
Re: Slow query log
On Wed, 2006-04-05 at 11:38 +0200, Mechain Marc wrote: Is there a way to enable the Slow Query Log on the fly without having to restart mysqld No. Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query log
Hi, Thank you for your answer. But is there a chance to be able to do it one day? I think it could be a nice feature. Marc. -Message d'origine- De : Petr Chardin [mailto:[EMAIL PROTECTED] Envoyé : mercredi 5 avril 2006 13:06 À : Mechain Marc Cc : MySQL Objet : Re: Slow query log On Wed, 2006-04-05 at 11:38 +0200, Mechain Marc wrote: Is there a way to enable the Slow Query Log on the fly without having to restart mysqld No. Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query log
Mechain Marc wrote: Hi, Thank you for your answer. But is there a chance to be able to do it one day? I think it could be a nice feature. Marc. That should be asked to one of the devs. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query log
2006/4/5, Mechain Marc [EMAIL PROTECTED]: Hi, Thank you for your answer. But is there a chance to be able to do it one day? I think it could be a nice feature. You still have the option to sponsor that feature ;-D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. I noticed the same in the logs of a 4.1 test server. I put it down to MySQL Administrator which was monitoring the server at the time. I believe MYSQL Admin issues these SHOW STATUS commands periodically to refresh it's status info screen. Out of curiosity were/are you using MySQL Administrator or something like a monitoring app with your MySQL servers? Im -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
On 1/27/06, Imran Chaudhry [EMAIL PROTECTED] wrote: Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. I noticed the same in the logs of a 4.1 test server. I put it down to MySQL Administrator which was monitoring the server at the time. I believe MYSQL Admin issues these SHOW STATUS commands periodically to refresh it's status info screen. Out of curiosity were/are you using MySQL Administrator or something like a monitoring app with your MySQL servers? Yep! It's a test environment, and MySQL-Administrator is often open for long periods in the background. Thanks; -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
2006/1/25, Nathan Gross [EMAIL PROTECTED]: Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. If you have a linux box (or any acceptable shell) cat query.log | grep -i -v 'show innodb status' query_clean.log grep -i : case insensitive grep -v : everything but the patterm given cat : well a cat is a cat... Thanks -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
Aye. -v. thanks! -nat On 1/26/06, Pooly [EMAIL PROTECTED] wrote: 2006/1/25, Nathan Gross [EMAIL PROTECTED]: Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. If you have a linux box (or any acceptable shell) cat query.log | grep -i -v 'show innodb status' query_clean.log grep -i : case insensitive grep -v : everything but the patterm given cat : well a cat is a cat... Thanks -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How NOT to log SHOW INNODB STATUS in the query log.
Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. Thanks -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inconsistent rows returned examined in slow query log
I have entries in my slow query log for identical queries but, as you can see from the log entries below (including one irrelevant query), the number rows examined and returned varies. The tables are _not_ being updated. The query cache is 'on demand', so I'm also not sure why the subsequent queries were not dealt with by the query cache. Any ideas? Thanks, James Harvard # Time: 060103 9:45:12 # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 86 Lock_time: 0 Rows_sent: 12 Rows_examined: 6733255 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; # Time: 060103 9:45:46 # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 70 Lock_time: 0 Rows_sent: 7 Rows_examined: 3737912 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 50 Lock_time: 0 Rows_sent: 4 Rows_examined: 1585713 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 2000 group by dates.date_month order by dates.date_month; # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 117 Lock_time: 0 Rows_sent: 9 Rows_examined: 5196480 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 113 Lock_time: 0 Rows_sent: 9 Rows_examined: 5196063 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL slow query log
Hi, Could anyone explain what might be the possible reasons that in the slow query log(running read-only queries) the most very slow queries(taking 200-300sec) were the queries: 1. create table ... type = MyISAM 2. show slave status Thanks, Jenny
Re: ~mysql query log~
Hi Gleb, Thanks a lot. On Mon, 2005-12-12 at 23:44, Gleb Paharenko wrote: Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html abdulazeem wrote: Hi, Iam running a mysql server version 5.0.15. My mysql query log is occupying nearly 21 GB of disk space. how do i truncate the same ? Thanks in advance, Abdul. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~mysql query log~
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html abdulazeem wrote: Hi, Iam running a mysql server version 5.0.15. My mysql query log is occupying nearly 21 GB of disk space. how do i truncate the same ? Thanks in advance, Abdul. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
~mysql query log~
Hi, Iam running a mysql server version 5.0.15. My mysql query log is occupying nearly 21 GB of disk space. how do i truncate the same ? Thanks in advance, Abdul. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Named Pipe for General Query Log
Hi Jake, all, please note that the following remarks are about communicating via named pipes in general, not specific for MySQL. I do _not_ comment whether the idea is good or bad, will work, alternatives, ... Jake Peavy wrote: Hey, I sent this a while ago, but never received a response. This still seems to exist under 5.0.15-standard (at least under mysql-standard-5.0.15-linux-i686-glibc23) Can anyone from MySQL comment on this or should I open it as a bug? Thanks, JP On 6/11/05, Jake Peavy [EMAIL PROTECTED] wrote: Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. Sure: Works as designed. man 2 open will tell you that an open() call on a named pipe synchronizes: Any such call will block until there is a corresponding call at the other end of the pipe. So the general technique is: mkfifo the_pipe reader_command the_pipe writer_command the_pipe Note that the pipe has a limited buffer capacity, so the writer cannot produce more info than the reader has processed: If your reader is slow (say, more and a human watching), the writer has to wait. Also, writing to the pipe fails if there is no reader attached. So if your reader terminates (crash, q input to more, ...), your writer cannot write any more, this may be fatal (depends on error handling). I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. For any log of a MySQL server, this is IMHO useful _only_ in a test environment, because of the speed and stability restrictions described above. I am running 5.0.2-alpha-standard on linux on i386. These pipe semantics hold for any Unix since pipes were introduced: For anonymous pipes (open() implicit in pipe()), since the early 1970s; for named pipes (aka FIFOs), since ATT Unix System V in the late 1980s (AFAIR). HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Named Pipe for General Query Log
Hey, I sent this a while ago, but never received a response. This still seems to exist under 5.0.15-standard (at least under mysql-standard-5.0.15-linux-i686-glibc23) Can anyone from MySQL comment on this or should I open it as a bug? Thanks, JP On 6/11/05, Jake Peavy [EMAIL PROTECTED] wrote: Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. I am running 5.0.2-alpha-standard on linux on i386. Thanks. F
Named Pipe for General Query Log
Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. I am running 5.0.2-alpha-standard on linux on i386. Thanks. F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query log?
Hello. You have an application which executes prepared statements. See: http://dev.mysql.com/doc/mysql/en/c-api-prepared-statements.html MySQL doesn't log to the slow log a prepared statement. You can enable general query log which logs prepared statements. Andrea Gangini [EMAIL PROTECTED] wrote: I've added the following line to my.cnf in order to activate the logging of slow queries: [mysqld] log-long-format log_slow_queries=/var/log/slow-queries.log long_query_time=20 The mysql server version is 4.1.9, but all I see in slow-queries.log the following: # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 61 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:29:15 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 53 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:29:17 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 67 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:32:32 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 28 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; I thought that the slow query log would dump the SQL of the queries causing slow-downs. Did I made some mistake in configuring my server? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query log?
I've added the following line to my.cnf in order to activate the logging of slow queries: [mysqld] log-long-format log_slow_queries=/var/log/slow-queries.log long_query_time=20 The mysql server version is 4.1.9, but all I see in slow-queries.log the following: # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 61 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:29:15 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 53 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:29:17 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 67 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:32:32 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 28 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; I thought that the slow query log would dump the SQL of the queries causing slow-downs. Did I made some mistake in configuring my server? -- Andrea Gangini, Mimesi S.r.l. [EMAIL PROTECTED] Tel. 0521/463811 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: does anyone know of a utility that will processes the query log to rerun the queries?
I am familiar with mysqlbinlog that is used to process the binary log to produce a text file that can then be feed back into mysql. That appears to be what you are referring to. But I'm not referring to the binary log, or any derivative of it, at all. I am referring to the actual general query log. Does anyone know of a utility that will processes the general query log (not the binary log or the binary log text file from mysqlbinlog) to rerun the queries? The program would need to... ...strip file header information ...strip the leading non query info from the line ...handle queries that span multiple lines ...change databases when appropriate before queries ...add the ; to the end of the query an option to only reprocess unique queries might also be nice. has anyone already done this? daniel -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 8:55 PM To: Daniel Gaddis; mysql@lists.mysql.com Subject: Re: does anyone know of a utility that will processes the query log to rerun the queries? Yes. The mysql client is good for this. I used to restore from disasters this way, eg: - full backup every night - transaction log ( the text one, not the binary one ) gets reset each night by restarting mysql after the backup Then when our disaster happened, we'd drop all databases, import from last night's backups, and then run the transaction log: mysql /path/to/transaction/log -p Unfortunately this becomes a little more complicated if you use temporary tables ... especially if you're updating the DB from the contents of the temporary tables, as the recovery process will run as 1 user instead of all the original users that ran it to start with. I believe the binary transaction log is good in this case, but I haven't bothered to check up on how to use it yet. But if you don't create temporary tables, then the above 1-liner should do. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
does anyone know of a utility that will processes the query log to rerun the queries?
does anyone know of a utility that will processes the query log to rerun the queries? The program would need to... ...strip the leading non query info from the line ...handle queries that span multiple lines ...change databases when appropriate before queries ...add the ; to the end of the query an option to only reprocess unique queries might also be nice. has anyone already done this? daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does anyone know of a utility that will processes the query log to rerun the queries?
Daniel Gaddis wrote: does anyone know of a utility that will processes the query log to rerun the queries? The program would need to... ...strip the leading non query info from the line ...handle queries that span multiple lines ...change databases when appropriate before queries ...add the ; to the end of the query an option to only reprocess unique queries might also be nice. has anyone already done this? Yes. The mysql client is good for this. I used to restore from disasters this way, eg: - full backup every night - transaction log ( the text one, not the binary one ) gets reset each night by restarting mysql after the backup Then when our disaster happened, we'd drop all databases, import from last night's backups, and then run the transaction log: mysql /path/to/transaction/log -p Unfortunately this becomes a little more complicated if you use temporary tables ... especially if you're updating the DB from the contents of the temporary tables, as the recovery process will run as 1 user instead of all the original users that ran it to start with. I believe the binary transaction log is good in this case, but I haven't bothered to check up on how to use it yet. But if you don't create temporary tables, then the above 1-liner should do. Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: is there a utility like mysqlbinlog but instead processes the query log?
The program would also need to handle...
...queries that span multiple lines
...change databases when appropriate before queries
yes I could program such a beast but I thought someone else might have
already done it.
daniel
-Original Message-
From: Andy Davidson [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 02, 2005 3:58 AM
To: mysql@lists.mysql.com mysql@lists.mysql.com
Subject: Re: is there a utility like mysqlbinlog but instead processes
the query log?
On 30 Dec 2004, at 13:26, Daniel Gaddis wrote:
is there a utility like mysqlbinlog but instead processes the query
log?
I would like to reprocess the queries from the query log.
I don't see another reply to this on the list, so I hope it helps - the
query log is already in plain-text, so you don't need something to fish
the queries out of an unfriendly format.
This bit of perl should be a good starting point.
elephant:/var/log/mysql# cat pullqueries.pl
#!/usr/bin/perl -w
use strict;
while (my $line = ) {
if ($line =~ /Query/) {
my (undef, undef, undef, undef, $display) = split(/ /,
$line, 5);
print $display;
}
}
example :
elephant:/var/log/mysql# tail -n 20 mysql.log | perl pullqueries.pl
SELECT fname from images where groupid='4' order by viewno desc
limit 0,1
SELECT fname from images where groupid='3' order by viewno desc
limit 0,1
SELECT fname from images where groupid='2' order by viewno desc
limit 0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
SELECT id,dirname,fname,viewno,groupid FROM images where
id='10035' limit 0,1
UPDATE images set viewno='1',lastlook=NOW('') where id='10035'
SELECT entry,whoby FROM ucaptions where picid='10035'
SELECT dirname,fname,caption from images where id='10035' limit
0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
--
Regards, Andy Davidson
http://www.fotoserve.com/
Great quality prints from digital photos.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is there a utility like mysqlbinlog but instead processes the query log?
On 30 Dec 2004, at 13:26, Daniel Gaddis wrote:
is there a utility like mysqlbinlog but instead processes the query
log?
I would like to reprocess the queries from the query log.
I don't see another reply to this on the list, so I hope it helps - the
query log is already in plain-text, so you don't need something to fish
the queries out of an unfriendly format.
This bit of perl should be a good starting point.
elephant:/var/log/mysql# cat pullqueries.pl
#!/usr/bin/perl -w
use strict;
while (my $line = ) {
if ($line =~ /Query/) {
my (undef, undef, undef, undef, $display) = split(/ /,
$line, 5);
print $display;
}
}
example :
elephant:/var/log/mysql# tail -n 20 mysql.log | perl pullqueries.pl
SELECT fname from images where groupid='4' order by viewno desc
limit 0,1
SELECT fname from images where groupid='3' order by viewno desc
limit 0,1
SELECT fname from images where groupid='2' order by viewno desc
limit 0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
SELECT id,dirname,fname,viewno,groupid FROM images where
id='10035' limit 0,1
UPDATE images set viewno='1',lastlook=NOW('') where id='10035'
SELECT entry,whoby FROM ucaptions where picid='10035'
SELECT dirname,fname,caption from images where id='10035' limit
0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
--
Regards, Andy Davidson
http://www.fotoserve.com/
Great quality prints from digital photos.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is there a utility like mysqlbinlog but instead processes the query log?
is there a utility like mysqlbinlog but instead processes the query log? I would like to reprocess the queries from the query log. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is there a utility like mysqlbinlog but instead processes the query log?
is there a utility like mysqlbinlog but instead processes the query log? I would like to reprocess the queries from the query log. additional features that would be nice would include: listing unique queries the number of times each unique query is executed Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow-query log
I noticed something interesting with our slow-query log and am looking for an answer. Our slow-query log is set for to record anything over 7 seconds. In monitoring the server I ran the show full processlist I occasionally see entries like | ID | USER | HOST | DATABASE | Query | 120| sleep | QUERY | Where the 120 is the time; which is well over are threshold of 7 seconds. However the query is not written to our slow-query.log file and to the best of my knowledge is not counted as a slow query. Why does this occur? My guess it only counts active query time and not sleep time. Jeff
Re: Query Log
Well look at that. I didn't look back to the Connect statement that starts the ID. Just when I think I'm getting a handle, I dive back into the quicksand! Ugh. Thanks Lou - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 5:25 AM Subject: Re: Query Log Lou Olsten [EMAIL PROTECTED] wrote: I'm pretty sure that the answer to this is No, you cannot but I figured I'd check anyway... As I go back through my query log, I'd like to know the user that issued the statement. If the user is still connected, I can cross reference it with the SHOW PROCESSLIST ID, but if they have signed off, is there a way to get the user then? If you look in the general query log file you can see Id column where thread id is specified and username and host in the Argument column. For update log and slow query log use --log-long-format option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Log
Lou Olsten [EMAIL PROTECTED] wrote: I'm pretty sure that the answer to this is No, you cannot but I figured I'd check anyway... As I go back through my query log, I'd like to know the user that issued the statement. If the user is still connected, I can cross reference it with the SHOW PROCESSLIST ID, but if they have signed off, is there a way to get the user then? If you look in the general query log file you can see Id column where thread id is specified and username and host in the Argument column. For update log and slow query log use --log-long-format option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Log
Which query log are you referring to? The user and the host are both logged in the slow query and general logs. -Original Message- From: Lou Olsten To: [EMAIL PROTECTED] Sent: 5/3/04 4:59 PM Subject: Query Log I'm pretty sure that the answer to this is No, you cannot but I figured I'd check anyway... As I go back through my query log, I'd like to know the user that issued the statement. If the user is still connected, I can cross reference it with the SHOW PROCESSLIST ID, but if they have signed off, is there a way to get the user then? Thanks, Lou -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Log
I'm pretty sure that the answer to this is No, you cannot but I figured I'd check anyway... As I go back through my query log, I'd like to know the user that issued the statement. If the user is still connected, I can cross reference it with the SHOW PROCESSLIST ID, but if they have signed off, is there a way to get the user then? Thanks, Lou
Re: Slow query log setting
Hi, I think log-slow-queries belongs in the [mysqld] section of my.cnf, not [mysqld_safe]. Hope that helps. Matt - Original Message - From: MaFai To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 9:00 PM Subject: Slow query log setting Hello, mysql, I have set the slow query parameter in the my.cnf as the following. [safe_mysqld] err-log=/var/log/mysqld.log log-slow-queries=/var/log/mysqlslow.log pid-file=/var/run/mysqld/mysqld.pid After I check the status of mysql today,we found 6 slow query occur. But there is no log in /var/log/mysqlslow.log,why? any idea appreciated. Mysql Version 4.0.12. Best regards. MaFai [EMAIL PROTECTED] 2003-11-06 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query log setting
Hello, mysql, I have set the slow query parameter in the my.cnf as the following. [safe_mysqld] err-log=/var/log/mysqld.log log-slow-queries=/var/log/mysqlslow.log pid-file=/var/run/mysqld/mysqld.pid After I check the status of mysql today,we found 6 slow query occur. But there is no log in /var/log/mysqlslow.log,why? any idea appreciated. Mysql Version 4.0.12. Best regards. MaFai [EMAIL PROTECTED] 2003-11-06 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: genereal query log
Moritz Steiner [EMAIL PROTECTED] wrote: I want to turn on and off the general query log from time to time. Is there a possibility to do this without changing the my.cnf file and restarting the server. I thought for example set option log=ON You can do SET SQL_LOG_OFF=1; to turn off logging for this client. Client must have SUPER privilege: http://www.mysql.com/doc/en/SET_OPTION.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
genereal query log
I want to turn on and off the general query log from time to time. Is there a possibility to do this without changing the my.cnf file and restarting the server. I thought for example set option log=ON Thanks, Moritz
Re: Query log/binlog inconsistency
Thanks for your response (and yours also, Jeremy). Both are spot on in terms of recognising it as a transaction commit issue. It turns out a developer was opening a transaction long before the query in question executed and had neglected to either commit it or roll it back: subsequently, the PHP page completed execution, the connection was dropped, and the transaction was implicitly rolled back. It's amazing how long it can take to find such a simple result: I only wish I had thought a little more a little earlier about what the binlog actually stores (namely records of transactions that have been committed!). Thanks again, Chris Heikki Tuuri wrote: Chris, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, May 29, 2003 10:17 AM Subject: Re: Query log/binlog inconsistency On Wed, May 28, 2003 at 05:05:38PM -0700, Chris Tucker wrote: Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. Hmm. The fact that the it doesn't show up in the binlog *and* it never affects you data is good. That means the binlog is working properly. :-) At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). Agreed. My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). The first thing that comes to mind is that the abstraction layer you're using forgets to COMMIT the data, so InnoDB rolls it back and never write the query to the binlog. Jeremy's explanation is plausible. If the PHP library runs in the AUTOCOMMIT=0 mode, then the query is executed and reports modified rows, but when the connection ends mysqld rolls back the transaction because it was not explicitly committed. Also note that a deadlock or a lock wait timeout error rolls back the WHOLE current transaction. But I assume you did not get any of these errors or other errors? It would help if you could post the relevant query log excerpt. Jeremy Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,615,610 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query log/binlog inconsistency
Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). If anybody has an idea as to what may be happening, or better yet has seen this problem and maybe even has a solution, I'd be delighted to hear it. Further, if anyone can provide more details on what happens between the query log being written and the bin log being written (a rough process flow of what happens in the DB internals) that would be of great help (even if just to improve my knowledge of this stuff). Thanks in advance, Chris Tucker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query log/binlog inconsistency
On Wed, May 28, 2003 at 05:05:38PM -0700, Chris Tucker wrote: Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. Hmm. The fact that the it doesn't show up in the binlog *and* it never affects you data is good. That means the binlog is working properly. :-) At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). Agreed. My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). The first thing that comes to mind is that the abstraction layer you're using forgets to COMMIT the data, so InnoDB rolls it back and never write the query to the binlog. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,615,610 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query log/binlog inconsistency
Chris, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, May 29, 2003 10:17 AM Subject: Re: Query log/binlog inconsistency On Wed, May 28, 2003 at 05:05:38PM -0700, Chris Tucker wrote: Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. Hmm. The fact that the it doesn't show up in the binlog *and* it never affects you data is good. That means the binlog is working properly. :-) At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). Agreed. My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). The first thing that comes to mind is that the abstraction layer you're using forgets to COMMIT the data, so InnoDB rolls it back and never write the query to the binlog. Jeremy's explanation is plausible. If the PHP library runs in the AUTOCOMMIT=0 mode, then the query is executed and reports modified rows, but when the connection ends mysqld rolls back the transaction because it was not explicitly committed. Also note that a deadlock or a lock wait timeout error rolls back the WHOLE current transaction. But I assume you did not get any of these errors or other errors? It would help if you could post the relevant query log excerpt. Jeremy Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,615,610 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query execution times in general query log?
I have a need to find out how long each of the queries executed against a mysql server are taking. I found this post, which indicates that this information can be found in the general query log: http://www.phpbuilder.com/mail/php-general/2002122/0876.php The manual note about it says the binary update log includes execution times for writes only, and if you want timing info for general queries, to use the general query log. However, the output in my general query log (v3.23.54) only has a timestamp, not an execution time. So, a few questions... 1) Has this changed in 4.0? 2) Are there options to get this info put in the log (in 3.23 or otherwise)? 3) Does anybody have a better way to get this info from a running instance (not necessarily in realtime)? -- - Adam - Adam Fields, Managing Partner, [EMAIL PROTECTED] Surgam, Inc. is a technology consulting firm with strong background in delivering scalable and robust enterprise web and IT applications. http://www.adamfields.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query execution times in general query log?
At 16:52 -0500 2/26/03, 1LT John W. Holmes wrote: I have a need to find out how long each of the queries executed against a mysql server are taking. Why can't there just be a function that returns this? I mean, it's already printed to the screen when you're running from the command line. Hopefully I've missed something and it's already there, but if not, please add it to the feature request. There can't be a function that returns this because execution time such as you're seeing is measured on the client side and the query is executed on the server side. If you mean, why can't there be a SQL function to return this, well, that's kind of like your eye trying to see itself. :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
analyzing mysql general query log
Hello, Can anyone recommend a script to analyze a mysql server general query log. My goal is to determine which users are putting the most load on the server. Any other methods to achieve this same goal would be appreciated as well. Thank you. _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
general query log
MySQL gurus: I'm a big fan of the general query log for taking the occasional audit of activity against our database server, as well as a tool for checking on the efficiency of new (mostly PHP) projects that interact with MySQL. What I don't like is that I have to stop and restart the server process twice (once to turn the log on and once to turn it back off) in order to get the output. I've read through the documentation at mysql.com and would have searched through the list archives if I wasn't getting document contains no data messages from lists.mysql.com at the moment. We use mytop (http://jeremy.zawodny.com/mysql/mytop/) to keep some track of the queries that are being run, but the output pales in comparison (for this use) to that from the general query log. (1) Is there a way to turn logging on for brief periods (no more than 15 minutes) without having to stop and restart? (2) Is there some way to get similar output with a different tool? [mysql query] TIA, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How disable query log?
Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How disable query log?
At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex The log isn't enabled by default, so it must be getting turned on *somewhere* at startup time. Check all your option files, not just one. Run this command to check what options are getting passed to it from option files: mysqld --print-defaults - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: How disable query log?
It's *nothing* about query logging in configuration files! # mysqld --print-defaults mysqld would have been started with the following arguments: --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-locking --set-variable=max_connect_errors=1000 --set-variable=max_connections=600 --default-character-set=win1251 --set-variable=key_buffer=128M --set-variable=max_allowed_packet=1M --set-variable=table_cache=512 --set-variable=sort_buffer=2M --set-variable=record_buffer=2M --set-variable=thread_cache=12 --set-variable=thread_concurrency=6 --set-variable=myisam_sort_buffer_size=64M --set-variable=query_cache_size=64M --set-variable=query_cache_limit=1M --set-variable=query_cache_type=1 --innodb_data_home_dir=/var/lib/mysql/ --innodb_data_file_path=ibdata1:1500M;ibdata2:1500M;ibdata3:1500M;ibdata4:1500M;ibdata5:1500M:autoextend --innodb_log_group_home_dir=/var/lib/mysql/ --innodb_log_arch_dir=/var/lib/mysql/ --set-variable=innodb_buffer_pool_size=256M --set-variable=innodb_additional_mem_pool_size=20M --set-variable=innodb_log_file_size=128M --set-variable=innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=0 --set-variable=innodb_lock_wait_timeout=20 --set-variable=innodb_thread_concurrency=6 Friday, January 10, 2003, 10:25:00 PM, you wrote: PD At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex PD The log isn't enabled by default, so it must be getting turned on PD *somewhere* at startup time. Check all your option files, not just PD one. Run this command to check what options are getting passed to PD it from option files: PD mysqld --print-defaults - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: How disable query log?
At 23:45 +0300 1/10/03, Andrey V. Ignatov wrote: It's *nothing* about query logging in configuration files! # mysqld --print-defaults mysqld would have been started with the following arguments: --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-locking --set-variable=max_connect_errors=1000 --set-variable=max_connections=600 --default-character-set=win1251 --set-variable=key_buffer=128M --set-variable=max_allowed_packet=1M --set-variable=table_cache=512 --set-variable=sort_buffer=2M --set-variable=record_buffer=2M --set-variable=thread_cache=12 --set-variable=thread_concurrency=6 --set-variable=myisam_sort_buffer_size=64M --set-variable=query_cache_size=64M --set-variable=query_cache_limit=1M --set-variable=query_cache_type=1 --innodb_data_home_dir=/var/lib/mysql/ --innodb_data_file_path=ibdata1:1500M;ibdata2:1500M;ibdata3:1500M;ibdata4:1500M;ibdata5:1500M:autoextend --innodb_log_group_home_dir=/var/lib/mysql/ --innodb_log_arch_dir=/var/lib/mysql/ --set-variable=innodb_buffer_pool_size=256M --set-variable=innodb_additional_mem_pool_size=20M --set-variable=innodb_log_file_size=128M --set-variable=innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=0 --set-variable=innodb_lock_wait_timeout=20 --set-variable=innodb_thread_concurrency=6 Okay, that's strange. Next step: - What's the name of the log file that the server is logging to? - Does logging occur if you shut down the server (with mysql.server stop, for example), and then start mysqld manually? Friday, January 10, 2003, 10:25:00 PM, you wrote: PD At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex PD The log isn't enabled by default, so it must be getting turned on PD *somewhere* at startup time. Check all your option files, not just PD one. Run this command to check what options are getting passed to PD it from option files: PD mysqld --print-defaults - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[3]: How disable query log?
1) logs file names: hostname.log , hostname-bin.001-4 and all of them created in datadir. hostname = sql3 2) i am delete all log files from /var/lib/mysql and run mysqld --user=mysql And all files create again :( -rw-rw1 mysqlmysql 14686 Jan 11 01:40 sql3-bin.001 -rw-rw1 mysqlmysql 15 Jan 11 01:39 sql3-bin.index -rw-rw1 mysqlmysql 149 Jan 11 01:39 sql3-slow.log -rw-rw1 mysqlmysql 11640 Jan 11 01:40 sql3.001 -rw-rw1 mysqlmysql 143581 Jan 11 01:40 sql3.log -rw-rw1 mysqlmysql 5 Jan 11 01:39 sql3.pid Saturday, January 11, 2003, 2:16:29 AM, you wrote: PD At 23:45 +0300 1/10/03, Andrey V. Ignatov wrote: It's *nothing* about query logging in configuration files! PD Okay, that's strange. Next step: PD - What's the name of the log file that the server is logging to? PD - Does logging occur if you shut down the server (with mysql.server stop, PDfor example), and then start mysqld manually? Friday, January 10, 2003, 10:25:00 PM, you wrote: PD At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? PD The log isn't enabled by default, so it must be getting turned on PD *somewhere* at startup time. Check all your option files, not just PD one. Run this command to check what options are getting passed to PD it from option files: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
question about query log
hi all, anyone knows if it is possible to create the query log in a mysql table instead of in a text file? Otherwise ... is there a way to import it into a table? Thanks to all. Natale Babbo sql, query __ Yahoo! Cellulari: scarica i loghi e le suonerie per le tue feste! http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question about query log
A little complicated solution is to create the log in a file then setup a php/perl program that reads that file continously and insert/process that data . - Original Message - From: Natale Babbo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 12:25 PM Subject: question about query log hi all, anyone knows if it is possible to create the query log in a mysql table instead of in a text file? Otherwise ... is there a way to import it into a table? Thanks to all. Natale Babbo sql, query __ Yahoo! Cellulari: scarica i loghi e le suonerie per le tue feste! http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
