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 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.
Objective
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 scenarios
- Plan warnings
- Shard and sort key summary
- Pipeline lag
- Queries with repartitioning or broadcasting operations
Steps 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.
Reports
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 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_QUERIES.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_QUERIES.csv"TEMPLATE_URL_BASE = 'https://b2.s3.amazonaws.com/templates/'
#set_logging_enabled(False)set_logging_enabled(True)
Steps 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:
Secrets
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.
Conclusion
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.
