Need a clarification on --query-source option either perfschema or slowlog

Percona Monitoring and Management (PMM)
1

Hi Folks,

I am suppose to configure pmm2 QAN dashboard for Production. I have enabled both perfomance_schema and slow_log tables. But I am confusing in between these two options for --query-source option.

Which one I should use and which one would be better to identify the exact slow queries?

  1. If I use --query-source=perfschema, what happens?
  2. If I use --query-source=slowlog, what happens?

Can I get proper slow queries if I use either perfschema or slowlog option?
Can somebody help me any disadvantages or advantages?

2

Can someone help me on this?

1 Like
3

If you use slow log, then PMM will be doing a tail -f on your slow log, reading query data as it is written to the slow log and processing it.

If you use perf-schema, then PMM will query certain performance_schema tables every few seconds and grab the data and process it.

The main reason for these two options is that if you are using, say AWS RDS, or some other DBaaS, then you don’t have access to the disk where slow log files exist. Thus, the only option is perfschema. If this is not a DBaaS situation, I would stick with slow-log.

2 Likes
4

NO NO NO NO NO!!! NO NO NO!! Do NOT enable slow log as a table! NO NO NO! Keep this as a file on disk. You will suffer horrible performance issues by having the slow log as a table.

1 Like
5

Hi @matthewb

Thanks for the help.
Do I need to enable both perfschema with disable table stats and slowlog with enable table stats, will it be fine. As I already enabled the slow_log table in the prod servers.

Can I add like below?

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pmm --disable-tablestats – metrics monitoring

pmm-admin add mysql --query-source=slowlog --username=pmm --password=pmm --disable-tablestats-limit=2000 – slow query analytics

1 Like
6

@Naresh9999, you only need 1 source for slow log information. Only 1 source. This is all you need:

pmm-admin add mysql --query-source=slowlog --size-slow-logs=1GiB --username=pmm --password=pmm

Then, in mysql, make sure you have the following:

long_query_time=0
slow_query_log=ON
log_slow_verbosity=full
log_output='FILE'

That’s it. You are very much over-complicating one of the simplest settings in PMM.

1 Like
7

Hi @matthewb

Thanks for the help.
Last one, do we have any option like read from slow_log table instead of slow log file.? As I am already enabled the slow/_log table in the production.

1 Like
8

@Naresh9999 You should disable slow_log as a table IMMEDIATELY! It comes with a HUGE PERFORMANCE PENALTY!

2 Likes
9

Sure @matthewb , thanks for the help.

1 Like