Details
-
New Feature
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
Description
In a case when slow query contains huge TEXT/BLOB values it becomes impractical to have such queries in the log. Think of a case when a slow INSERT or UPDATE contains TEXT values longer than 1MB.
In such case it would be good to have an option to truncate the query to some reasonably long size which would still reveal the nature of the query, but will not flood the log with too much information.
This will indeed create a problem for mysqldumpslow tool and parsing the log in general, for which it would be best if only the values are truncated, but that means parsing and processing the query before logging it into slow log which in my opinion is too much extra effort for such corner cases.
Alternatively, instead of introducing another option, this could be an extension of log_slow_verbosity
If such option is introduced should be disabled by default or zero meaning no truncation.
Attachments
Activity
If the DBA has deliberately set an option to truncate long statements in the log messages, then surely that's their choice and problem.
That said, perhaps the need for this feature could be somewhat mitigated by adding more options to mariadb-dumpslow.
For example, options to:
- specify a time range
- abstract or truncate strings with at least x number of characters (separate from the -a option)
- include the query timestamps in the output
- order results by timestamp
Truncating doesn't seem to be very useful. For example, consider
UPDATE tbl_name SET blob_col='1 Mb blob' WHERE ...slow condition... |
SET goes before WHERE, we'll be always truncating the most important part.
replacing a blob value with something small (like REPEAT('x', 1024*1024*1024) — that looks better. But not exactly clear how to do that, we don't have a facility for rewriting queries before logging, it needs to be implemented first.
Also, it's quite possible for the query to be huge not because of one string literal. It could be a 1Mb long IN list in the where clause, we've seen that before. In this case there's nothing to rewrite.
An easiest and simplest fix would be to skip them. Like SET @@slow_log_query_size_limit=512*1024*1024
What is the actual problem here, mariadb-dumpslow being slow? Perhaps we should fix the tool instead?
Truncating the query does not seem to be a good idea at least to me, given that you want to know the exact query which was slow. Truncating the values of some data types like BLOB values could be an option, the question still is if such a huge BLOB can be the reason why the query ended up in the slow query log. In that case such data should not just be truncated but should at least get some information about the size added, that it is still clear why the query was logged at all