Logging Queries

SingleStore DB supports logging all or specific queries in a general query log by configuring engine variables. Query log is disabled by default. When enabled, SingleStore DB logs queries into the query.log file, which typically resides in the /var/lib/memsql/<nodeID>/tracelogs directory. To learn how to set up query logging, refer to the following sections.

Configuration Parameters for Query Logging

general_log: Controls whether database queries are logged. This variable is disabled by default and can be set to one of the following values:

  • OFF (default): No queries are logged.

  • PARTIAL: Logs only a subset of the queries running through the system. This mode is designed for minimal overhead: if multiple queries are trying to write to the query log simultaneously, only one will succeed and the other writes will be abandoned.

  • ON: Every query running through the system is logged. This mode has a significantly negative impact on performance as query execution now depends on writes to the trace log.

To log user queries, you can set the general_log variable on all aggregator nodes. For more information on how to set engine variables, see the engine variables overview.

  • general_log_file: Specifies the file in which connections and queries are logged. By default, this variable is set to /var/lib/memsql/tracelogs/query.log. For query logging to take place, general_log must be set to ON or PARTIAL, depending on your requirements.

  • minimal_disk_space: Defines the minimal disk space in MB, which, when reached on the disk where the data directories (such as logs, snapshots, blobs, plancache, auditlogs, and tracelogs) are mounted, SingleStore will halt new write queries. As query log files can potentially grow in size until the disk limit is reached, it is important to monitor this configuration.

  • tracelogsdir: The path to the log destination directory, which contains the query.log file among others. By default, this variable points to the /var/lib/memsql/<nodeID>/tracelogs directory.

Note: If a query includes Remote Procedure Call (RPC) data, the RPC data is logged as a JSON string rather than query text.

Rotating Query Log to Manage its Size

Query logs can generate large files over time and may require a log rotation policy to be implemented. The query.log file can be rotated by moving the query.log file and then sending SIGHUP to the memsqld process. This will trigger the server to reopen query.log and continue writing. As query.log is a text file, you may want to compress the file after rotation to free up disk space.

For an illustration of the query.log file rotation, refer to Log Rotation.