Analysis of a weeks worth of general log

I have 7 days worth of general log data totalling 4.4GB.

I want to analyze this data to get:

a) queries per second, minute, hour and day
b) a count of the number of selects versus write statements (delete,
insert, replace and update)
c) a variation of the above with "select, replace, delete and insert"
versus "update"

How can I do this?

I've looked at mysqlsla which is complex, works well but does not
quite get what I want. [1]

I looked at MyProfi 0.18 which looks like it will get some of the
answers but runs out of memory working on the smallest log file
(mysql.log) even with memory_limit in php.ini set to 1024MB [2]

-rw-r----- 1 imran imran 268M 2010-04-19 13:03 mysql.log
-rw-r----- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1
-rw-r----- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2
-rw-r----- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3
-rw-r----- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4
-rw-r----- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5
-rw-r----- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6

Any pointers please? If all else fails, I will prolly write a perl
script to munge it.

[1] http://hackmysql.com/mysqlsla
[2] http://myprofi.sourceforge.net

--
GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Analysis of a weeks worth of general log

--001636c933dabed0ac0484a9e7ba
Content-Type: text/plain; charset=ISO-8859-1

Maybe one of the maatkit tools will do it, but I tend to graph that kind of
data live in Munin from the internal counters.

On Tue, Apr 20, 2010 at 1:02 PM, Imran Chaudhry wrote:

> I have 7 days worth of general log data totalling 4.4GB.
>
> I want to analyze this data to get:
>
> a) queries per second, minute, hour and day
> b) a count of the number of selects versus write statements (delete,
> insert, replace and update)
> c) a variation of the above with "select, replace, delete and insert"
> versus "update"
>
> How can I do this?
>
> I've looked at mysqlsla which is complex, works well but does not
> quite get what I want. [1]
>
> I looked at MyProfi 0.18 which looks like it will get some of the
> answers but runs out of memory working on the smallest log file
> (mysql.log) even with memory_limit in php.ini set to 1024MB [2]
>
> -rw-r----- 1 imran imran 268M 2010-04-19 13:03 mysql.log
> -rw-r----- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1
> -rw-r----- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2
> -rw-r----- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3
> -rw-r----- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4
> -rw-r----- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5
> -rw-r----- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6
>
> Any pointers please? If all else fails, I will prolly write a perl
> script to munge it.
>
> [1] http://hackmysql.com/mysqlsla
> [2] http://myprofi.sourceforge.net
>
> --
> GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636c933dabed0ac0484a9e7ba--

Re: Analysis of a weeks worth of general log

--0016e6d7f0b0732fc00484aa14ef
Content-Type: text/plain; charset=ISO-8859-1

Has anyone tried using the log_output option in mysql 5.1 to have the
general log put into a table and not a flat file? I used it for a while
before having to downgrade back to 5.0 but thought it was a great idea. I'm
curious to see if anyone feels it helps analysis.

On Tue, Apr 20, 2010 at 6:02 AM, Imran Chaudhry wrote:

> I have 7 days worth of general log data totalling 4.4GB.
>
> I want to analyze this data to get:
>
> a) queries per second, minute, hour and day
> b) a count of the number of selects versus write statements (delete,
> insert, replace and update)
> c) a variation of the above with "select, replace, delete and insert"
> versus "update"
>
> How can I do this?
>
> I've looked at mysqlsla which is complex, works well but does not
> quite get what I want. [1]
>
> I looked at MyProfi 0.18 which looks like it will get some of the
> answers but runs out of memory working on the smallest log file
> (mysql.log) even with memory_limit in php.ini set to 1024MB [2]
>
> -rw-r----- 1 imran imran 268M 2010-04-19 13:03 mysql.log
> -rw-r----- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1
> -rw-r----- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2
> -rw-r----- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3
> -rw-r----- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4
> -rw-r----- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5
> -rw-r----- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6
>
> Any pointers please? If all else fails, I will prolly write a perl
> script to munge it.
>
> [1] http://hackmysql.com/mysqlsla
> [2] http://myprofi.sourceforge.net
>
> --
> GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6d7f0b0732fc00484aa14ef--

Re: Analysis of a weeks worth of general log

--001636284b4ebf19db0484ac38ce
Content-Type: text/plain; charset=ISO-8859-1

Hi Imran,

you can have a look at mysqldumpslow utility to analyze the data..

Thanks
Anand

On Tue, Apr 20, 2010 at 5:48 PM, Jim Lyons wrote:

> Has anyone tried using the log_output option in mysql 5.1 to have the
> general log put into a table and not a flat file? I used it for a while
> before having to downgrade back to 5.0 but thought it was a great idea.
> I'm
> curious to see if anyone feels it helps analysis.
>
> On Tue, Apr 20, 2010 at 6:02 AM, Imran Chaudhry
> wrote:
>
> > I have 7 days worth of general log data totalling 4.4GB.
> >
> > I want to analyze this data to get:
> >
> > a) queries per second, minute, hour and day
> > b) a count of the number of selects versus write statements (delete,
> > insert, replace and update)
> > c) a variation of the above with "select, replace, delete and insert"
> > versus "update"
> >
> > How can I do this?
> >
> > I've looked at mysqlsla which is complex, works well but does not
> > quite get what I want. [1]
> >
> > I looked at MyProfi 0.18 which looks like it will get some of the
> > answers but runs out of memory working on the smallest log file
> > (mysql.log) even with memory_limit in php.ini set to 1024MB [2]
> >
> > -rw-r----- 1 imran imran 268M 2010-04-19 13:03 mysql.log
> > -rw-r----- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1
> > -rw-r----- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2
> > -rw-r----- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3
> > -rw-r----- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4
> > -rw-r----- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5
> > -rw-r----- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6
> >
> > Any pointers please? If all else fails, I will prolly write a perl
> > script to munge it.
> >
> > [1] http://hackmysql.com/mysqlsla
> > [2] http://myprofi.sourceforge.net
> >
> > --
> > GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
> >
> >
>
>
> --
> Jim Lyons
> Web developer / Database administrator
> http://www.weblyons.com
>

--001636284b4ebf19db0484ac38ce--

Re: Analysis of a weeks worth of general log

Jim Lyons skrev:
> Has anyone tried using the log_output option in mysql 5.1 to have the
> general log put into a table and not a flat file? I used it for a while
> before having to downgrade back to 5.0 but thought it was a great idea. I'm
> curious to see if anyone feels it helps analysis.

I tried that once, and ran into some problems. Depending on your exact
version, you might experience the same.

http://www.bitbybit.dk/carsten/blog/?p=115

(also has a number of good comments on analysis tools)

And yes, having the data available in a table is a Good Thing for analysis.

/ Carsten


>
> On Tue, Apr 20, 2010 at 6:02 AM, Imran Chaudhry wrote:
>
>> I have 7 days worth of general log data totalling 4.4GB.
>>
>> I want to analyze this data to get:
>>
>> a) queries per second, minute, hour and day
>> b) a count of the number of selects versus write statements (delete,
>> insert, replace and update)
>> c) a variation of the above with "select, replace, delete and insert"
>> versus "update"
>>
>> How can I do this?
>>
>> I've looked at mysqlsla which is complex, works well but does not
>> quite get what I want. [1]
>>
>> I looked at MyProfi 0.18 which looks like it will get some of the
>> answers but runs out of memory working on the smallest log file
>> (mysql.log) even with memory_limit in php.ini set to 1024MB [2]
>>
>> -rw-r----- 1 imran imran 268M 2010-04-19 13:03 mysql.log
>> -rw-r----- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1
>> -rw-r----- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2
>> -rw-r----- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3
>> -rw-r----- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4
>> -rw-r----- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5
>> -rw-r----- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6
>>
>> Any pointers please? If all else fails, I will prolly write a perl
>> script to munge it.
>>
>> [1] http://hackmysql.com/mysqlsla
>> [2] http://myprofi.sourceforge.net
>>
>> --
>> GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
>>
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Analysis of a weeks worth of general log

Carsten Pedersen skrev:
> Jim Lyons skrev:
>> Has anyone tried using the log_output option in mysql 5.1 to have the
>> general log put into a table and not a flat file? I used it for a while
>> before having to downgrade back to 5.0 but thought it was a great
>> idea. I'm
>> curious to see if anyone feels it helps analysis.
>
> I tried that once, and ran into some problems. Depending on your exact
> version, you might experience the same.
>
> http://www.bitbybit.dk/carsten/blog/?p=115
>
> (also has a number of good comments on analysis tools)
>
> And yes, having the data available in a table is a Good Thing for analysis.
>
> / Carsten

Minor correction: The post i point to is about the slow log, but I
presume also relevant for the general log. And the good comments I
mentioned come in the followup posting at
http://www.bitbybit.dk/carsten/blog/?p=116

/ Carsten


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Analysis of a weeks worth of general log

--0050450163d50ecc190484acf2b6
Content-Type: text/plain; charset=ISO-8859-1

Well, first thing I'd do, is symlink the log table files onto a separate set
of spindles. No use bogging the main data spindles down with logwrites.



On Tue, Apr 20, 2010 at 5:33 PM, Carsten Pedersen wrote:

> Carsten Pedersen skrev:
>
> Jim Lyons skrev:
>>
>>> Has anyone tried using the log_output option in mysql 5.1 to have the
>>> general log put into a table and not a flat file? I used it for a while
>>> before having to downgrade back to 5.0 but thought it was a great idea.
>>> I'm
>>> curious to see if anyone feels it helps analysis.
>>>
>>
>> I tried that once, and ran into some problems. Depending on your exact
>> version, you might experience the same.
>>
>> http://www.bitbybit.dk/carsten/blog/?p=115
>>
>> (also has a number of good comments on analysis tools)
>>
>> And yes, having the data available in a table is a Good Thing for
>> analysis.
>>
>> / Carsten
>>
>
> Minor correction: The post i point to is about the slow log, but I presume
> also relevant for the general log. And the good comments I mentioned come in
> the followup posting at http://www.bitbybit.dk/carsten/blog/?p=116
>
> / Carsten
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0050450163d50ecc190484acf2b6--

Re: Analysis of a weeks worth of general log

> Minor correction: The post i point to is about the slow log, but I presume
> also relevant for the general log. And the good comments I mentioned come in
> the followup posting at http://www.bitbybit.dk/carsten/blog/?p=116
>
> / Carsten

Thanks Carsten, I read the comments and Sheeri mentions mysqlsla which
I have already tried. Back to square one.

I might look at munin again and see if someone has written a plug-in
that graphs query type but that seems too much hassle for my
situation. I have the raw data and I want the appropriate tool to
analyze it.

Part of the reason is that the data is from a MyISAM based web app and
I am writing a report recommending it be moved to a transactional
storage engine. AIUI a rule of thumb is that if between 15% - 20% of
statements are non SELECT/INSERT then one can obtain equal or better
performance with something like InnoDB. That being said, the benefits
of InnoDB (good recovery features, transactions, advanced indexes,
foreign key contraints) make it a good default choice and I will
recommend it anyway. Plus we have order processing stuff going on and
it seems right to have "atomicity" in that process.

It would be a bit better though to confidently state that the
query-mix skews it towards InnoDB... if I can only prove it :-)

--
GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org