How can I check the cpu and memory usage per query on SQL queries in Virtuoso?

Virtuoso Universal Server
,
1

I need these statistcs to do a benchmark. Is anyone knows How can I check the cpu and memory usage per query on SQL queries in Virtuoso?

2

The Virtuoso Query Logging feature can provide details of query cpu, memory, thread and other metric usage as detailed in the documentation link.

3

Hey, Hugh. Thanks for your fast answer.

Before I create this topic, I’ve already tried from this approach. I’ve found in the DB folders, the view:
DB.DBA.SYS_QUERY_LOG

I’ve almost configured the .ini file with the virtuoso.qrl file and created this file in the database folder
the line of .ini file have been this way:

[Parameters]
QueryLog = virtuoso.qrl

But, when I run this query:

SELECT *
  FROM DB.DBA.SYS_QUERY_LOG
 WHERE qrl_file = 'virtuoso.qrl'
   AND qrl_start_dt = cast ('2023-11-19' as datetime)
   AND qrl_end_dt = cast ('2023-11-19' as datetime);

I have the result with zero rows result. That is: the virtuoso.qrl is not been fullfiled.

I don’t know what I’m doing wrong… ;(

Thanks for your help!

*Ps: I perfomed some queries before running the query of the sys_query_log, but it kept producing no rows.

4

Att:

I’ve made succesfuly the query in DB.DBA.SYS_QUERY_LOG view.

But only without the qrl_start_dt and qrl_start_dt parameters. The names or the parameters seems not to be like described in documentation. It can be outdated.

The query I runned was like this:

SELECT *
  FROM DB.DBA.SYS_QUERY_LOG
 WHERE qrl_file = 'virtuoso.qrl';

Now, I have some doubts about some parameters like. Is these values useful for measure the cpu usage for a query compilation?

  • ql_user_cpu– Cumulative user CPU in milliseconds for this server process.
  • ql_sys_cpu– Cumulative system CPU in milliseconds for this server process.

Or should I have to use only these other parameters:

  • ql_client_ip

    • Requesting client IP as dotted decimal.

  • ql_text

    • Source text of the query

  • ql_c_clocks

    • CPU clocks of real time used for query compilation. This will be 0 if the query is separately prepared of if the query compilation comes from a cache of recently compiled queries. This is likely if the query is parametrized and executed multiple times.

  • ql_c_msec

    • Real time used for query compilation in milliseconds.

  • ql_c_memory

      • Count of bytes allocated for compiling the query. This is the peak size of the memory pool for query compilation.
        (I was wondering if there’s some running query memory. But it seems to be only this memory used to compile que query.)

I need to perform a benchmark with some queries and I need: CPU Usage, Memory Usage and Time Spent Per Query.

Thank you for your time, Hughes.

5

The ql_start_dt column exists but not the ql_end_dt which we shall have to check, why this is missing from the view.

The ql_c_memory parameter only provides the memory used for compiling the query and not execution. In fact in speaking to development they indicated the query logging params only provide metric for query compilation and not execution, and thus are not completely suitable for your request.

1 Like
6

I think not only the compilation times. Like these two statistics. Fix me, if I’m wrong! :

  • ql_rt_msec– real time elapsed in milliseconds between the start and the logging of the query
  • ql_rt_clocks– Clock cycles of real time spent running the query, not including time between consecutive fetches from a client if the query was a cursor fetched in multiple chunks by a client. This is the number of clocks during which there was at least one thread running on behalf of the query. The average CPU% of the query is given by:

100 * ql_thread_clocks / ql_rt_clocks

7

The ql_rt_clocks is a cumulative counter for execution in CPU ticks, it starts when client start executing a query and samples are recorded when log sample is taken, and excludes compile time.

1 Like