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=arch...@jab.org
Re: Analysis of a weeks worth of general log
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 ichaud...@gmail.com 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=vegiv...@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
Re: Analysis of a weeks worth of general log
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 ichaud...@gmail.com 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=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Analysis of a weeks worth of general log
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 jlyons4...@gmail.com 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 ichaud...@gmail.com 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=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
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 ichaud...@gmail.com 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=jlyons4...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.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=arch...@jab.org
Re: Analysis of a weeks worth of general log
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 cars...@bitbybit.dkwrote: 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=vegiv...@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
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=arch...@jab.org
