[
https://issues.apache.org/jira/browse/CASSANDRA-13001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17942170#comment-17942170
]
Stefan Miklosovic edited comment on CASSANDRA-13001 at 4/9/25 11:55 AM:
------------------------------------------------------------------------
This was dormant for 7 years so I took it.
The idea is same as we already have for system_views.system_logs table.
I have implemented logback appender of slow queries to virtual table. By basing
it on logback appenders, we can log, in theory, slow queries everywhere. E.g.
we can log to a separate file,whatever we have an appender for.
Virtual table with slow queries is
1) limited in its size (configurable) - if full, as new log entries come, the
oldest will be removed
2) it is possible to truncate it to wipe it from memory
3) it is possible to remove all slow logs for particular keyspace
4) we can query this table as any other, allow filtering is implicitly enabled
so we do not need to specify it, this will make all custom tooling around
parsing it obsolete (I believe so).
This approach will enable us to do queries like
{code:java}
cassandra@cqlsh> select * from system_views.slow_queries where keyspace_name =
'system_schema';
keyspace_name | table_name | timestamp | avg_ms | max_ms
| min_ms | query |
times_reported
---------------+------------+---------------------------------+--------+--------+--------+-------------------------------------------------------+----------------
system_schema | columns | 2025-04-08 17:32:55.747000+0200 | 16 | 31
| 6 | SELECT * FROM system_schema.columns ALLOW FILTERING |
3
system_schema | columns | 2025-04-08 17:33:06.842000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.columns ALLOW FILTERING |
1
system_schema | functions | 2025-04-08 17:32:55.747000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.functions ALLOW FILTERING |
1
system_schema | tables | 2025-04-08 17:32:55.747000+0200 | 8 | 12
| 4 | SELECT * FROM system_schema.tables ALLOW FILTERING |
3
system_schema | tables | 2025-04-08 17:33:06.842000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.tables ALLOW FILTERING |
1
system_schema | triggers | 2025-04-08 17:32:55.747000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.triggers ALLOW FILTERING |
1
{code}
If we want to see all slow queries which have violated some threshold, we can
do (independently from slow_query_log_timeout configuration in yaml)
{code:java}
cassandra@cqlsh> select * from system_views.slow_queries where keyspace_name =
'system_schema' and avg_ms > 10;
keyspace_name | table_name | timestamp | avg_ms | max_ms
| min_ms | query | times_reported
---------------+------------+---------------------------------+--------+--------+--------+-----------------------------------------------------+----------------
system_schema | columns | 2025-04-08 17:32:55.747000+0200 | 16 | 31
| 6 | SELECT * FROM system_schema.columns ALLOW FILTERING | 3
{code}
{code:java}
delete from system_views.slow_queries where keyspace_name = 'system_schema';
{code}
{code:java}
truncate system_views.slow_queries;
{code}
In logback.xml we would do:
{code}
<!-- Uncomment below configuration and corresponding appender-ref in
slow_queries logger to activate
logging into system_views.slow_queries virtual table. -->
<appender name="SLOW_QUERIES_TO_VTABLE"
class="org.apache.cassandra.utils.logging.SlowQueriesAppender"/>
<!-- Log slow queries to system_views.slow_queries virtual table -->
<logger name="slow_queries" additivity="false" level="DEBUG">
<appender-ref ref="SLOW_QUERIES_TO_VTABLE"/>
</logger>
{code}
By default, everything behaves as it was, a user has to explicitly opt-in into
this.
PR
[https://github.com/apache/cassandra/pull/4067/files]
was (Author: smiklosovic):
This was dormant for 7 years so I took it.
I have implemented logback appender of slow queries to virtual table. By basing
it on logback appenders, we can log, in theory, slow queries everywhere. E.g.
we can log to a separate file,whatever we have an appender for.
Virtual table with slow queries is
1) limited in its size (configurable) - if full, as new log entries come, the
oldest will be removed
2) it is possible to truncate it to wipe it from memory
3) it is possible to remove all slow logs for particular keyspace
4) we can query this table as any other, allow filtering is implicitly enabled
so we do not need to specify it, this will make all custom tooling around
parsing it obsolete (I believe so).
This approach will enable us to do queries like
{code:java}
cassandra@cqlsh> select * from system_views.slow_queries where keyspace_name =
'system_schema';
keyspace_name | table_name | timestamp | avg_ms | max_ms
| min_ms | query |
times_reported
---------------+------------+---------------------------------+--------+--------+--------+-------------------------------------------------------+----------------
system_schema | columns | 2025-04-08 17:32:55.747000+0200 | 16 | 31
| 6 | SELECT * FROM system_schema.columns ALLOW FILTERING |
3
system_schema | columns | 2025-04-08 17:33:06.842000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.columns ALLOW FILTERING |
1
system_schema | functions | 2025-04-08 17:32:55.747000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.functions ALLOW FILTERING |
1
system_schema | tables | 2025-04-08 17:32:55.747000+0200 | 8 | 12
| 4 | SELECT * FROM system_schema.tables ALLOW FILTERING |
3
system_schema | tables | 2025-04-08 17:33:06.842000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.tables ALLOW FILTERING |
1
system_schema | triggers | 2025-04-08 17:32:55.747000+0200 | 3 | 3
| 3 | SELECT * FROM system_schema.triggers ALLOW FILTERING |
1
{code}
If we want to see all slow queries which have violated some threshold, we can
do (independently from slow_query_log_timeout configuration in yaml)
{code:java}
cassandra@cqlsh> select * from system_views.slow_queries where keyspace_name =
'system_schema' and avg_ms > 10;
keyspace_name | table_name | timestamp | avg_ms | max_ms
| min_ms | query | times_reported
---------------+------------+---------------------------------+--------+--------+--------+-----------------------------------------------------+----------------
system_schema | columns | 2025-04-08 17:32:55.747000+0200 | 16 | 31
| 6 | SELECT * FROM system_schema.columns ALLOW FILTERING | 3
{code}
{code:java}
delete from system_views.slow_queries where keyspace_name = 'system_schema';
{code}
{code:java}
truncate system_views.slow_queries;
{code}
In logback.xml we would do:
{code}
<!-- Uncomment below configuration and corresponding appender-ref in
slow_queries logger to activate
logging into system_views.slow_queries virtual table. -->
<appender name="SLOW_QUERIES_TO_VTABLE"
class="org.apache.cassandra.utils.logging.SlowQueriesAppender"/>
<!-- Log slow queries to system_views.slow_queries virtual table -->
<logger name="slow_queries" additivity="false" level="DEBUG">
<appender-ref ref="SLOW_QUERIES_TO_VTABLE"/>
</logger>
{code}
By default, everything behaves as it was, a user has to explicitly opt-in into
this.
PR
[https://github.com/apache/cassandra/pull/4067/files]
> slow query logging to virtual table via logback appender
> --------------------------------------------------------
>
> Key: CASSANDRA-13001
> URL: https://issues.apache.org/jira/browse/CASSANDRA-13001
> Project: Apache Cassandra
> Issue Type: New Feature
> Components: Legacy/Observability
> Reporter: Jon Haddad
> Assignee: Stefan Miklosovic
> Priority: Normal
> Fix For: 5.x
>
> Attachments:
> 0001-Add-basic-pluggable-logging-to-debug.log-and-table.patch,
> 0001-Add-multiple-logging-methods-for-slow-queries-CASSAN.patch
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Currently CASSANDRA-12403 logs slow queries as DEBUG to a file. It would be
> better to have this as an interface which we can log to alternative
> locations, such as to a table on the cluster or to a remote location (statsd,
> graphite, etc).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@cassandra.apache.org
For additional commands, e-mail: commits-h...@cassandra.apache.org
