Mysql – Extract query fingerprints and tables from large log files using pt-query-digest

logsMySQL

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:

How can I extract the query fingerprints from a large log file generated by general_log ?
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.

Best Answer

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.

UPDATE 2012-04-25 17:37 EDT

Diskspace is needed to write shutdown information into the mysql error log. During any mysql operation that produces temp tables (which are always MyISAM), diskspace is always needed. What happens if there is no diskspace for temp tables?

According to MySQL 5.0 Certification Study Guide Page 408,409 Section 29.2 bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

Trying a standard shutdown of mysqld with no available diskspace complicates the problem. Just kill the process, delete the 18G log file, start mysql back up again, and live with InnoDB crash recovery. Trust me, it is better than looking for alternatives in a Windows environment which will not work because of the same disk issue.

Mysql – Extract some columns data from one table and insert into multiple tables

Assuming that you are preserving the id assocaited with each name and lastname, run this:

INSERT INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
INSERT INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If tabless tbl_2 and tbl_3 already have data and you wish to overwrite it, run this:

REPLACE INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If you are not preserving the ID, then run this:

INSERT INTO tbl_2 (name) SELECT name FROM tbl_1;
INSERT INTO tbl_3 (lastname) SELECT lastname FROM tbl_1;

Best Answer

Related Solutions

Mysql – Very Large Log Files

UPDATE 2012-04-25 17:37 EDT

Mysql – Extract some columns data from one table and insert into multiple tables

Related Question