# log\_slow\_rate\_limit for slow logging

**URL:** <https://forums.percona.com/t/log-slow-rate-limit-for-slow-logging/3740>\
**Category:** Other MySQL® Questions\
**Created:** [September 15, 2014, 7:08pm UTC](https://forums.percona.com/t/log-slow-rate-limit-for-slow-logging/3740 "2014-09-15T19:08:49Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![cmcgrail](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/cmcgrail/32/15_2.png) [@cmcgrail](https://forums.percona.com/u/cmcgrail)\
**Post date:** [September 15, 2014, 7:08pm UTC](https://forums.percona.com/t/log-slow-rate-limit-for-slow-logging/3740/1 "2014-09-15T19:08:49Z")

</div>

Attempts to test this feature are meeting with limited success.

The goal is always log truly slow queries, which we consider 1 second or more, and to also log a representative sample of all other queries, which on this test system would be 1 in 100. On production it would be 1/1000 or more.

To start I take a pretty much default 5.6 instance of Percona Server and enable slow logging (long\_query\_time is set to 1) and add this.

mysql\> set global log\_slow\_rate\_type =“query”;  
Query OK, 0 rows affected (0.00 sec)

mysql\> set global log\_slow\_rate\_limit = 100;  
Query OK, 0 rows affected (0.00 sec)

mysql\> set global slow\_query\_log\_use\_global\_control =“all”;  
Query OK, 0 rows affected (0.00 sec)

Here’s all the variables related to slow logging

show variables like ‘%slow%’;  
±-----------------------------------±-----------------------------------------------------------------------+  
| Variable\_name | Value |  
±-----------------------------------±-----------------------------------------------------------------------+  
| log\_slow\_admin\_statements | OFF |  
| log\_slow\_filter | |  
| log\_slow\_queries | ON |  
| log\_slow\_rate\_limit | 100 |  
| log\_slow\_rate\_type | query |  
| log\_slow\_slave\_statements | OFF |  
| log\_slow\_sp\_statements | ON |  
| log\_slow\_verbosity | microtime,query\_plan,innodb |  
| slow\_launch\_time | 2 |  
| slow\_query\_log | ON |  
| slow\_query\_log\_always\_write\_time | 1.000000 |  
| slow\_query\_log\_file | /datastore/mysql/devdb-slow.log |  
| slow\_query\_log\_timestamp\_always | ON |  
| slow\_query\_log\_timestamp\_precision | microsecond |  
| slow\_query\_log\_use\_global\_control | log\_slow\_filter,log\_slow\_rate\_limit,log\_slow\_verbosity,long\_query\_time |  
±-----------------------------------±-----------------------------------------------------------------------+  
15 rows in set (0.00 sec)

This was enabled on a test database that runs tens to hundreds of queries per second, so I would have expected a query to be logged every 1-10 seconds. However in the first 24 hours this ran, only four queries showed up in the slow log.

There must be something simple that I am missing.

The log entries do have the expected comments about the rate type and limit, which shows that the system is aware of the config.

# Log\_slow\_rate\_type: query Log\_slow\_rate\_limit: 100

---

<div class="post-metadata">

**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\
**Post date:** [September 16, 2014, 11:33am UTC](https://forums.percona.com/t/log-slow-rate-limit-for-slow-logging/3740/2 "2014-09-16T11:33:54Z")

</div>

Hi cmcgrail;

I think what’s getting you is the “log\_slow\_rate\_limit” option. When you set it to 100, that actually means that only 1 out of 100 “slow” queries will actually get logged. So if 199 slow queries come through, only 1 will get logged. Once the 200th one comes through, then the second will get logged.

If you want to get a sample of all queries, then I would set long\_query\_time to 0, which will give you a large sampling of queries that are run. Or if you want every query, you could always use the general log (just for limited use while testing in dev).

Also keep in mind that the global variables only take effect for new connections, so if you have an application with persistent connections, global settings you change may not even be in use until the app connects again. This also applies to your own connection when testing.

-Scott

---

<div class="post-metadata">

**Author:** ![cmcgrail](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/cmcgrail/32/15_2.png) [@cmcgrail](https://forums.percona.com/u/cmcgrail)\
**Post date:** [September 16, 2014, 5:13pm UTC](https://forums.percona.com/t/log-slow-rate-limit-for-slow-logging/3740/3 "2014-09-16T17:13:14Z")

</div>

Ah. I think I get it now.

The way the documentation is written allows an interpretation where log\_slow\_rate\_limit will force logging of queries to the slow log whether or not they were deemed slow by evaluation against the value of long\_query\_time.

The docs says stuff about log\_slow\_rate\_limit like: “Specifies that only a fraction of session/query should be logged. Logging is enabled for every nth session/query.”

It would be more clear if it said “every nth slow query”.

I set long\_query\_time to 0 and set log\_slow\_rate\_limit to 50 for a test where I was running queries in batches of 100, and the rate of logging seemed about what I wanted.

It would also be great if the docs gave a better idea of what would be logged in the case where log\_slow\_rate\_type were set to session. Do you only get sessions if those sessions issued a slow query? Do you get the part of the session that preceded the slow query? I’m not planning on using that feature, but if I wanted to I would still have questions.

Thanks for your help again.