Commit message:

MDEV-33144 Implement the Percona variable slow_query_log_always_write_time

This task is inspired by the Percona implementation of
slow_query_log_always_write_time.

This task implements the variable log_slow_always_query_time (name matching
other MariaDB variables using the slow query log). The default value for
the variable is 0, which makes MariaDB compatible with older installations.

For queries with execution time longer than log_slow_always_query_time
the variables log_slow_rate_limit and log_slow_min_examined_row_limit
will not ignored and the query will be written to the slow query log
if there is no other limitations (like log_slow_filter etc).

Other things:

• long_query_time internal variable renamed to log_slow_query_time.
• More descriptive information for "log_slow_query_time".

This code can be found in bb-11.5-monty

to test this feature separately please use bb-11.5-MDEV-33144-slow-query-log-always-write-time

In my opinion the feature as of 48ae09f8 (currently the top commit in bb-11.5-MDEV-33144-slow-query-log-always-write-time) can be pushed into 11.5 and released with 11.5.1 RC.

Notes

• MDEV-33872 was filed for improvement of the MTR test. I don't consider it an obstacle for releasing the feature in RC, but I recommend fixing issues described here, either using the suggested patch or some other way;
• The commit comment needs to be fixed:
  • the default value of the variable is 31536000, not 0;
  • "will not ignored" => "will be ignored"
  • also some typos, but those aren't semantically important
• It would be better to put the commit comment or a similar text right into the description of this JIRA task (rather than only in a comment), especially since the resulting variable name is different from one in the task summary, it can be confusing for those who search for it later;
• It is worth mentioning in documentation that a query still needs to be longer than both log_slow_query_time and log_slow_always_query_time for the latter to have effect. It makes sense, but may be not immediately obvious.

A test improvement was pushed into bb-11.5-monty and cherry-picked into bb-11.5-MDEV-33144-slow-query-log-always-write-time on top of the commit described in the previous comment. However, it cannot yet be used "as is", as it has made the test fail, apparently deterministically, with view-protocol.

A fix for main.log_slow_always_query_time test was pushed into bb-11.5-monty and cherry-picked into the feature tree.

I've cherry-picked it into 11.7 for preview. But I expect the test to be extremely fragile, as timing dependent tests are, it'll likely need to be rewritten before this goes into the main branch.

branch for testing: bb-11.7-MDEV-33144-slow-query-log-always-write-time

My opinion remains that the feature (now as of commit 22347482) can be pushed into the main branch and released with 11.7.1.

I didn't do full re-testing of the feature, but the variable still works, I don't see any essential changes comparing to the status of my comment added in the scope of 11.5 preview testing, except that the notes from it have been addressed since then.

For the fragility of the test, I see one place where it is clearly non-deterministic, the last sleep-based query ("this-should-not-be-logged" one):

SET log_slow_query_time=0.5;

SET log_slow_rate_limit=999;

SET log_slow_always_query_time=1.5;

SET log_slow_min_examined_row_limit= 100;

--source include/log_slow_start.inc

SELECT sleep(2) as 'this-should-be-logged';

SELECT sleep(1) as 'this-should-not-be-logged'

So, if the server is slow and the query actually takes longer than 1.5 seconds, it will be logged.
Without re-writing the test completely (and I don't know how it can be done deterministically without debug which we probably don't want to use unless necessary), I think the probability of the failure could be essentially reduced by changing the sleep times and variable values a little bit, e.g. like so:

SET log_slow_query_time=0.5;

SET log_slow_rate_limit=999;

SET log_slow_always_query_time=1.9;

SET log_slow_min_examined_row_limit= 100;

--source include/log_slow_start.inc

SELECT sleep(2) as 'this-should-be-logged';

SELECT sleep(0.6) as 'this-should-not-be-logged'

that is, increase log_slow_always_query_time and decrease the second sleep.
There should be no danger in having the sleep time close to the limit as long as it is exceeding the limit – I don't think sleep ever takes less than the parameter value.

We can however wait to see it fail. I ran the test (the current version in the branch) several hundred times in shm, on disk, with embedded, with ps protocol, with the heavy disk/cpu usage in parallel, and didn't get a failure so far, so it doesn't seem that the probability is extremely high.