SingleStore Cloud Performance Troubleshooting Notebook

Clock Icon

3 min read

Pencil Icon

Mar 25, 2024

SingleStore is the most comprehensive data platform of choice for developers building immersive applications that require real-time analytics. To fully optimize applications developers, administrators and users alike need to understand existing system performance — and how to tune their queries.

SingleStore Cloud Performance Troubleshooting Notebook

SingleStore Notebooks are web-based Jupyter notebooks that enable developers to create, explore, visualize and collaborate on data analysis and workflows using SQL or Python code within the SingleStore environment. They support scheduled runs, can be shared with other users and run in separate containers for security.

Furthermore, SingleStore Notebooks enhance Jupyter Notebook capabilities, allowing direct querying of SingleStore's distributed SQL database — facilitating data exploration and analysis without needing to set up connection strings. It supports SQL/Python interoperability, collaborative workflows and interactive data visualizations using libraries like Matplotlib and Plotly.

objectiveObjective

Database performance monitoring is an ongoing process involving continuous recording, analysis, and action to address issues as they arise. This notebook facilitates routine checks and records the results, providing suggestions for common performance improvement scenarios.

By utilizing the scheduling feature, we can automate running this notebook at intervals to monitor performance trends over time.

common-performance-improvement-scenariosCommon performance improvement scenarios

  • Plan warnings
  • Shard and sort key summary
  • Pipeline lag
  • Queries with repartitioning or broadcasting operations

steps-to-run-the-notebookSteps to run the notebook:

Import the notebook file into notebooks section on cloud portal.

Select workspace and database from the dropdown.

From the Run menu, click Run All

When prompted, provide a management API key.

Note: The management API key stage area path will be printed to the console.

reportsReports

Upon successful completion, reports are generated for each scenario previously listed in HTML format. These files are archived and uploaded to the stage area path provided.

You can download and extract these files. Open the index.html.

The index page shows a table with links to each scenario, with report pages showing information in these sections:

  • Background
  • Actions
  • Query result

  • References

steps-to-customize-adding-new-scenariosSteps to customize adding new scenarios

Currently, query information and HTML templates are exposed via S3 public buckets.

If you wish to add/modify, you may copy over these files, modify and expose them on your own public URLs.

If that’s the case, be sure to update  the URLs in Python:

QUERY_DATA_URL="https://b1.s3.amazonaws.com/DB_PERFORMANCE_TROUBLESHOOT_Q
UERIES.csv"
TEMPLATE_URL_BASE = 'https://b1.s3.amazonaws.com/templates/'

Let's say you have exposed them on different AWS S3 buckets, 'b2'

QUERY_DATA_URL="https://b2.s3.amazonaws.com/DB_PERFORMANCE_TROUBLESHOOT_Q
UERIES.csv"
TEMPLATE_URL_BASE = 'https://b2.s3.amazonaws.com/templates/'

steps-to-enable-logsSteps to enable logs

In the notebook, click on a cell with Python and modify it:

#set_logging_enabled(False)
set_logging_enabled(True)

steps-to-schedule-the-notebookSteps to schedule the notebook

Only shared notebooks can be scheduled. Click on the schedule button on the top right.

Choose the options, and click Create Job.

After the job is created, you will see the following screen:

secretsSecrets

SingleStore recently added a new feature, Secrets, to handle sensitive data and share it across  organizations. This can be used for API keys, passwords and other protected information.

conclusionConclusion

In conclusion, SingleStore Notebooks provide a flexible, powerful solution for scheduled database performance checks. Automating these checks on a schedule allows users to proactively identify and address potential performance issues, ensuring optimal database system functioning. The versatility and ease of use of SingleStore Notebooks make it an invaluable tool for database administrators and analysts looking to maintain and enhance database performance.

Interested in testing the full power of Notebooks? Try SingleStore free today.


Share