Troubleshooting

Follow this checklist if you find that the performance is slower than you expect.

Troubleshooting checklist
Step Description Results
1 A single query is slow

If a query isn’t performing as you expect, follow the Query best practices part of the Optimization and best practices guide.

If all queries are slow, continue to step 2.

2 All queries on a specific table are slow
  1. If all queries on a specific table aren’t performing as you expect, follow the Table design best practices part of the Optimization and best practices guide.
  2. Check for active delete predicates in the table. Consult the Deleting data guide for more information.

If the problem spans all tables, continue to step 3.

3 Check that all workers are up

Use SELECT show_cluster_nodes(); to list the active cluster workers.

If the worker list is incomplete, follow the cluster troubleshooting section below.

If all workers are up, continue to step 4.

4 Check that all workers are performing well
  1. Identify if a specific worker is slower than others by running the same query on different workers. (e.g. by connecting directly to the worker or through a service queue)
  2. If a specific worker is slower than others, investigate performance issues on the host using standard monitoring tools (e.g. top).
  3. Restart SQream DB workers on the problematic host.

If all workers are performing well, continue to step 5.

5 Check if the workload is balanced across all workers
  1. Run the same query several times and check that it appears across multiple workers (use SELECT show_server_status() to monitor)
  2. If some workers have a heavier workload, check the service queue usage. Refer to the Workload manager guide.

If the workload is balanced, continue to step 6.

6 Check if there are long running statements
  1. Identify any currently running statements (use SELECT show_server_status() to monitor)
  2. If there are more statements than available resources, some statements may be in an In queue mode.
  3. If there is a statement that has been running for too long and is blocking the queue, consider stopping it (use SELECT stop_statement(<statement id>)).

If the statement does not stop correctly, contact SQream support.

If there are no long running statements or this does not help, continue to step 7.

7 Check if there are active locks
  1. Use SELECT show_locks() to list any outstanding locks.
  2. If a statement is locking some objects, consider waiting for that statement to end or stop it.
  3. If after a statement is completed the locks don’t free up, refer to the Concurrency and locks guide.

If performance does not improve after the locks are released, continue to step 8.

8 Check free memory across hosts
  1. Check free memory across the hosts by running $ free -th from the terminal.
  2. If the machine has less than 5% free memory, consider lowering the limitQueryMemoryGB and spoolMemoryGB settings. Refer to the Configuration guide.
  3. If the machine has a lot of free memory, consider increasing the limitQueryMemoryGB and spoolMemoryGB settings.

If performance does not improve, contact SQream support for more help.

Troubleshooting common issues

Troubleshoot cluster setup and configuration

  1. Note any errors - Make a note of any error you see, or check the logs for errors you might have missed.
  2. If SQream DB can’t start, start SQream DB on a new storage cluster, with default settings. If it still can’t start, there could be a driver or hardware issue. Contact SQream support.
  3. Reproduce the issue with a standalone SQream DB - starting up a temporary, standalone SQream DB can isolate the issue to a configuration issue, network issue, or similar.
  4. Reproduce on a minimal example - Start a standalone SQream DB on a clean storage cluster and try to replicate the issue if possible.

Troubleshoot connectivity issues

  1. Verify the correct login credentials - username, password, and database name.
  2. Verify the host name and port
  3. Try connecting directly to a SQream DB worker, rather than via the load balancer
  4. Verify that the driver version you’re using is supported by the SQream DB version. Driver versions often get updated together with major SQream DB releases.
  5. Try connecting directly with the built in SQL client. If you can connect with the local SQL client, check network availability and firewall settings.

Troubleshoot query performance

  1. Use SHOW_NODE_INFO to examine which building blocks consume time in a statement. If the query has finished, but the results are not yet materialized in the client, it could point to a problem in the application’s data buffering or a network throughput issue..
  2. If a problem occurs through a 3rd party client, try reproducing it directly with the built in SQL client. If the performance is better in the local client, it could point to a problem in the application or network connection.
  3. Consult the Optimization and best practices guide to learn how to optimize queries and table structures.

Troubleshoot query behavior

  1. Consult the SQL statements and syntax reference to verify if a statement or syntax behaves correctly. SQream DB may have some differences in behavior when compared to other databases.
  2. If a problem occurs through a 3rd party client, try reproducing it directly with the built in SQL client. If the problem still occurs, file an issue with SQream support.

Examining logs

See the Collecting logs and metadata database section of the Gathering information for SQream support guide for information about collecting logs for support.

Start a temporary SQream DB for testing

Starting a SQream DB temporarily (not as part of a cluster, with default settings) can be helpful in identifying configuration issues.

Example:

$ sqreamd /home/rhendricks/raviga_database 0 5000 /home/sqream/.sqream/license.enc

Tip

  • Using nohup and & sends SQream DB to run in the background.

  • It is safe to stop SQream DB at any time using kill. No partial data or data corruption should occur when using this method to stop the process.

    $ kill -9 $SQREAM_PID