General log not analyzed for one particular user

Polyglot Projects Percona Toolkit
1

Hello,

I’m trying to get some insights about the queries that a particular db user (“mas”) realizes in order to a redis cache layer to be created.

I issue (as per the docs):

pt-query-digest --type genlog --filter ‘($event->{user} || “”) =~ m/mas/’ general.log

and all I get is:

# 27.1s user time, 30ms system time, 39.71M rss, 43.69M vsz
# Current date: Mon Jun 13 13:37:39 2022
# Hostname: ip-10-180-10-224
# Files: general.log
# Overall: 4.35k total, 1 unique, 0 QPS, 0x concurrency __________________
# Time range: 2022-06-13T10:48:50.217242Z to 2022-06-13T10:49:24.164127Z
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time              0       0       0       0       0       0       0
# Query size       127.53k      30      30      30      30       0      30

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0x7417646A9FE969365D51E5F01B88B79E   0.0000  0.0%  4353 0.0000  0.00 ADMIN CONNECT

# Query 1: 0 QPS, 0x concurrency, ID 0x7417646A9FE969365D51E5F01B88B79E at byte 164196047
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-06-13T10:48:50.217242Z to 2022-06-13T10:49:24.164127Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100    4353
# Exec time      0       0       0       0       0       0       0       0
# Query size   100 127.53k      30      30      30      30       0      30
# String:
# Databases    **********
# Hosts        172.31.201.33 (1054/24%)... 8 more
# Users        mas
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Connect\G

It says there are 4353 queries, which is correct:

# grep -cP 'Connect\tmas@172.31' general.log
4353

But I’m not getting a breakdown of those queries. Because of course there are queries. Example:

2022-06-13T10:48:50.217242Z     21705465 Connect        mas@172.31.179.111 on ******** using TCP/IP
2022-06-13T10:48:50.217582Z     21705465 Query  SET NAMES utf8
2022-06-13T10:48:50.219411Z     21705465 Query  SELECT wishlist_item.product_id,  UNIX_TIMESTAMP(wishlist_item.added_at) as added_at
            FROM wishlist_item LEFT JOIN wishlist ON wishlist_item.wishlist_id = wishlist.wishlist_id
                WHERE wishlist_item.store_id = '1'
                    AND wishlist.customer_id = '5411220'
                    AND wishlist_item.product_id = '1744382'

or

2022-06-13T10:48:50.289738Z     21705469 Connect        mas@172.31.218.120 on ******* using TCP/IP
2022-06-13T10:48:50.290329Z     21705469 Query  SET NAMES utf8
2022-06-13T10:48:50.295906Z     21705469 Query  SELECT
                    p.entity_id as product_id, cpsl.parent_id
                FROM
                    catalog_product_entity p
                        LEFT JOIN
                    catalog_product_super_link cpsl on p.entity_id = cpsl.product_id
                WHERE p.entity_id IN (1507196)

(and thousands more)

What am I doing wrong?

Server version: 5.7.37-log MySQL Community Server (GPL)

# pt-query-digest --version
pt-query-digest 3.3.1

Thanks.

1 Like
2

Someone please? I can’t believe I don’t have an answer to something like this after 2 weeks.

1 Like
3

Hi @Ruben_Cardenal,

I believe you might have already found the answer and surely enough everyone appears busy around here but for the sake of completeness I think following should answer this as it works for slow log:

pt-query-digest --filter '$event->{user} eq "USERNAME"' slow.log > slow.log.digest

Thanks,
K