Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tried to parse a large log file using pt-query-digest but it gives "Out of memory!" when I try to read the file. I have 3GB RAM, 40GB empty space on HDD, the file is 20GB. I did not found anything in the documentation about memory.

I reduce the command at:

pt-query-digest --group-by fingerprint --print --no-report /path/to/largeLogFile.log
pt-query-digest --table-access /path/to/largeLogFile.log

I have 2 questions:

  1. How can I extract the query fingerprints from a large log file generated by general_log ?

  2. How can I extract all the tables that were used ?

Edit:
Tried @DTest --sample option (with 2 and 1), but the result is the same. Just before it reaches 50% memory in htop it dies every time (see screnshoot): enter image description here

I did an strace just before it dies I get:

read(3, "phone from teams as t ri"..., 4096) = 4096
mremap(0x5b27a000, 1543499776, 1543503872, MREMAP_MAYMOVE) = -1 ENOMEM (Cannot allocate memory)
mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
brk(0x64c2d000)                         = 0x8c2e000
mmap2(NULL, 1543634944, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
mmap2(NULL, 2097152, PROT_NONE, MAP_PRIVATE|MAP_ANONYMOUS|MAP_NORESERVE, -1, 0) = 0x5b07a000
munmap(0x5b07a000, 548864)              = 0
munmap(0x5b200000, 499712)              = 0
mprotect(0x5b100000, 135168, PROT_READ|PROT_WRITE) = 0
mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
write(2, "Out of memory!\n", 15Out of memory!
)        = 15
munmap(0x5b27a000, 1543499776)          = 0
... [output removed] ...

The only option I can think so far is splitting the file "bite size" pieces (let's say 20 files of 1GB each), digest them and merge the result.

share|improve this question
    
Does the pt-query-digest --group-by fingerprint --print --no-report /path/to/largeLogFile.log run out of memory? –  Derek Downey Jul 20 '12 at 16:55
    
@DTest Yes, this was the first thing I tried, then I tried different combination of options --table-access, --no-report etc ... without success. –  Radu Maris Jul 22 '12 at 16:40
add comment

2 Answers 2

I would try to use the --sample option:

pt-query-digest --sample 2 --print --no-report /path/to/largeLogFile.log

This would filter out all but the first 2 occurrences of a query fingerprint. --no-report removes the memory and cpu overhead of aggregating.

share|improve this answer
add comment

After reading the manual again and after some tests I have found answer for 2/3 problems:

0. "Out of memory" - caused by incorrect file type ( --type ) specified to pt-query-digest. The log file is from general_log, pt-query-digest by default parse it as it was slow query log, and of course the data is loaded in memory until it find a pattern that is not there. Corect usage:

pt-query-digest --type genlog --group-by fingerprint --print --no-report /path/to/largeLogFile.log

2. Extract tables used, the --table-access work as expected (if the type is correctly used)

pt-query-digest --type genlog --table-access /path/to/largeLogFile.log

1. Extract fingerprints. Still did not found the correct usage. Everything I tried prints the full query with spaces, tabs, parameters. I tried --group by, --sample, --no-report, pt-query-fingerprint ...

For question 1. I am trying to extract just the query fingerprint, nothing else but:

pt-query-digest --type genlog --fingerprints --group-by fingerprint --print --no-report /path/to/largeLogFile.log

prints something like:

update some_table set some_column=0 where another_column='some_value';
# Time: 120716 11:08:27
# Thread_id: 201
# Query_time: 0.000000  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
use some_database;
insert into some_table (col1, col2, col3) VALUES ('val1','val2','val2');
# Time: 120716 11:08:27
# Thread_id: 202
# Query_time: 0.000000  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0

and:

pt-fingerprint --match-embedded-numbers /path/to/largeLogFile.log

prints something like:

?:ca:?a:cc???????????value_a??:c0:a8:f7:?f:???????value_b??:?:b0:?:fd:???????value_c??c:af:f7:?:?:?d??????value_d??:c0:a8:f7:?f:??value_e??c:af:f7:?:?:?d??????w' where col_a=? ? query update table_a set col_a=now(), col_b=?, col_c=?, col_d=?, col_e=?, col_f=?, col_g=?, col_h=? where col_i=? and col_j=? ? quit ? query commit ?

It gets it right for some queries, but for the most it fails :(

Still waiting for a solution for Q1.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.