MySQL Performance: MySQL => General query log performance

Members

Help

Home

Home » Performance » MySQL » General query log performance

Show: Today's Messages :: Show Polls :: Message Navigator

General query log performance [message #67]

Wed, 16 August 2006 09:46

radek
Messages: 7
Registered: August 2006
Location: London

Junior Member

Hi

I'm wondering how big impact on performance would have turning on the general query log. I read in manual it should be turned off in production environment to increase the performance but I didn't find any information about performance gain (how much cpu/disk usage it would take). Is it like 1% performance lost or could it be bigger? Basically I just want to turn it on for a week to analyze later that query log.

Thanks in advance
Radek

[Updated on: Wed, 16 August 2006 09:50]

Report message to a moderator

Re: General query log performance [message #68 is a reply to message #67 ]

Wed, 16 August 2006 16:02

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Enabling general log may have different effect on server performance depending on your query complexity - if you run 10000 simple queries/sec log overhead will be much larger than if you run 5 complicated queries/sec

Generally I would not expect it to cause more than few percent overhead. I guess should be some 1% for queries of medium complexity up to 10% for very simple queries.

My main concern with full query logging is it is eating up space so quickly Smile

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: General query log performance [message #69 is a reply to message #68 ]

Thu, 17 August 2006 09:24

radek
Messages: 7
Registered: August 2006
Location: London

Junior Member

Cool, thanks again for your help! I hope that query log won't eat my disk space in one week Smile

cheers
Radek

Report message to a moderator

Re: General query log performance [message #70 is a reply to message #69 ]

Thu, 17 August 2006 09:37

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

If it grows too large you can rename it and use mysqladmin flush-logs so new logs are generated on regular basics.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: General query log performance [message #125 is a reply to message #70 ]

Thu, 31 August 2006 05:49

radek
Messages: 7
Registered: August 2006
Location: London

Junior Member

I have general query log but I just noticed it's not very usable Sad

It doesn't include time of queries. I guess the only way to check the reall usage is to lower the threshold of slow query log to 1s

Report message to a moderator

Re: General query log performance [message #126 is a reply to message #125 ]

Thu, 31 August 2006 06:15

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Right,

General query log does not include query times. Furthermore queries with syntax and other errors will be logged.

General query log can't include query times because queries are logged before they are executed - so if some query crashes MySQL during execution it can be found in general query log.

It should not be hard to patch MySQL to log all queries to slow query log if slow_query_time is set to 0.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: General query log performance [message #128 is a reply to message #126 ]

Thu, 31 August 2006 10:23

radek
Messages: 7
Registered: August 2006
Location: London

Junior Member

Yeah, I think it should be done. Without it it's not possible to do performance and usage modeling of production systems (it's possible to do only queries which takes more than 1s atm) Sad

Report message to a moderator

Re: General query log performance [message #129 is a reply to message #128 ]

Thu, 31 August 2006 18:25

vadimtk
Messages: 12
Registered: August 2006

Junior Member

Hi,

I've created the patch which allows to set --long-query-time to 0, so all queries will be logged in slow-log.

You can get it there:
http://www.mysqlperformanceblog.com/files/patches/patch-slow 0.5.0.24a.diff

Report message to a moderator

Re: General query log performance [message #131 is a reply to message #129 ]

Thu, 31 August 2006 19:18

vadimtk
Messages: 12
Registered: August 2006

Junior Member

Hi again,

I decided to aggregate my patch with Georg Richter's patch
and final version is here:
http://www.mysqlperformanceblog.com/files/patches/patch.slow -micro.5.0.24a.diff

Georg made modification so time in slow-log is showed in microsec
(--long-query-time also should be used in microsecs)

[Updated on: Thu, 31 August 2006 19:18]

Report message to a moderator

Re: General query log performance [message #132 is a reply to message #131 ]

Fri, 01 September 2006 04:47

radek
Messages: 7
Registered: August 2006
Location: London

Junior Member

Great! Indeed it's like two lines of code Smile

Thanks for it. Do you think it should be possible to integrate this patch into official release as well?

cheers
Radek

Report message to a moderator

Re: General query log performance [message #133 is a reply to message #132 ]

Fri, 01 September 2006 04:54

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

We can submit the patch but there is no guaranties when (if at all) it will be included.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	Query cache not working
Next Topic:	Sleeping Queries

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 9 19:46:26 EDT 2009

Total time taken to generate the page: 0.01480 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.