Slow Query Detection

Everyone is concerned about the performance of their database and specifically queries on their database. No matter how fast your hardware is or how fast your database is, query optimization still offers very significant performance benefits. Identifying which queries require optimization can be a challenge. NuoDB has a solution – actually two helpful solutions – both new in NuoDB Starlings 1.1. First, if you want to know which of your finished queries took an egregious amount of time, then check out NuoDB’s SYSTEM.QUERYSTATS table. By default, NuoDB will store the ten slowest queries that took more than ten seconds to execute. This is, of course, configurable. Second, if you want to know the performance status of your currently running queries, NuoDB provides a SYSTEM.CONNECTIONS table.

SYSTEM.QUERYSTATS

Let’s look at SYSTEM.PROPERTIES for configuring which queries will be logged to the QUERYSTATS table.

SQL> select * from SYSTEM.PROPERTIES;
    PROPERTY    VALUE
--------------- ------
MAX_QUERY_COUNT   10
MIN_QUERY_TIME    10

MAX_QUERY_COUNT in SYSTEM.PROPERTIES tells NuoDB the maximum number of queries to log. MIN_QUERY_TIME is the minimum execution time for a query to make it eligible for logging. MIN_QUERY_TIME may be a decimal value, so it can be expressed in subsecond intervals. To change one of these values dynamically, use the UPDATE command in your client session. SYSTEM.PROPERTIES is a persistent table, available to the entire chorus and from one client session to the next.

To find your slowest queries, run a simple select from SYSTEM.QUERYSTATS.

SQL> select * from SYSTEM.QUERYSTATS;
              SQLSTRING                COUNT  RUNTIME  USER  SCHEMA  NUMPARAM    PARAMS    NODEID  NROWS  UNIQUEID         TIMESTAMP
-------------------------------------- ------ -------- ----- ------- --------- ----------- ------- ------ --------- --------------------------
select * from t where x=?;               1    13010424 CLOUD  USER       1     0/string/2     1      1        4     2013-04-23 12:29:53.512637
select s from t2 join t3 on t2.s=t3.s;   1    14013823 CLOUD  USER       0                    2      1        2     2013-04-23 12:52:54.830913

NB: In the table above, you will need to scroll right to see the entire contents.

This will give you the SQL string for the query, the number of times the query has executed, the execution time for the query in microseconds (exaggerated here for sample purposes, fyi!), information about the user, schema and any parameters as well as other information. TIMESTAMP is the time at which the query started. This table is one of several “pseudo” tables implemented in NuoDB. This is a transient table, created on the fly, every time you query from it. Its contents are always relative to the moment in time at which the query was executed.

Notice NODEID in SYSTEM.QUERYSTATS above is different for the two queries listed. This is generally not a concern for most NuoDB users, but it does tell us that the queries are running on separate NuoDB Transaction Engines. If NODEID was the same for all the slow queries, then it might indicate a hardware performance problem wherever that node is hosted, rather than a query that requires optimization. The user can do “select * from SYSTEM.NODES” (another pseudo table) to see information about each node in the chorus. The NODEID column in SYSTEM.QUERYSTATS corresponds to the NODEID column in SYSTEM.NODES, so this is where you could find out the IP address and port number for the TE on which a query is running.

Remember that SYSTEM.QUERYSTATS is a transient table, and when a NuoDB Transaction Engine shuts down, the slow queries that were executed against that Transaction Engine will no longer be reported in the QUERYSTATS table.

SYSTEM.CONNECTIONS

NuoDB also provides a mechanism for realtime query analysis. SYSTEM.CONNECTIONS is another NuoDB pseudo table, a transient table created on the fly whenever you query it. This will report all currently running queries on your database. When the queries above from QUERYSTATS were still executing, they would have been reported in the CONNECTIONS table as follows:

SQL> select * from SYSTEM.CONNECTIONS;
              SQLSTRING                COUNT  RUNTIME  USER  SCHEMA  NUMPARAM    PARAMS    NODEID  CONNID  OPEN  HANDLE  NODEID  EXECID
-------------------------------------- ------ -------- ----- ------- --------- ----------- ------- ------  ----  ------  ------  --------------------
select * from t where x=?;               1    13010424 CLOUD  USER       1     0/string/2     1      2      1      3        1    55340232229718589441
select s from t2 join t3 on t2.s=t3.s;   1    14013823 CLOUD  USER       0                    2      3      1      1        2    18446744086594453505

NB: In the table above, you will need to scroll right to see the entire contents.

This gives similar information as in the SYSTEM.QUERYSTATS table. It includes CONNID, OPEN, and HANDLE which in combination uniquely identify each SQL statement and may be used as parameters to the KILL STATEMENT command. EXECID is an identifier created from CONNID, OPEN, and HANDLE, which also provides a unique way to identify your SQL statement and may be copied and pasted as an argument to the KILL STATEMENT command.

All the pseudo tables may be accessed by one or more columns, just like any other table. A helpful, simple query for analyzing currently running queries is:

SQL> select SQLSTRING,RUNTIME from SYSTEM.CONNECTIONS;
              SQLSTRING                RUNTIME  
-------------------------------------- --------
select * from t where x=?;             13010424
select s from t2 join t3 on t2.s=t3.s; 14013823

More “Pseudo” Tables Coming…

Having queries run in a distributed environment like ours makes the creation of these pseudo tables a bit of a challenge. One Transaction Engine receives the query from the client, and then has to send messages to all the other nodes in the chorus, to get information about queries running there. It waits for the responses from every node in the chorus and then displays the results to the client. Being able to query every node in a NuoDB chorus, wait for the responses and report the results in a pseudo table is a powerful tool and allows us to gather all kinds of useful information from the NuoDB chorus. Stay tuned for more useful NuoDB pseudo tables!

Martin Suer
Anonymous's picture
great feature, great post

great feature, great post

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.