Introduction
In the world of software development, performance is key. The ability to profile and optimize SQL queries can significantly improve the functionality and reliability of an application. This is where the Empress Recorder comes into play. Built into the Empress framework, this robust profiling tool is designed to capture all requests and background jobs, along with the SQL queries executed and corresponding stack traces.
Starting the Recorder
Imagine you’re dealing with a doctype that’s taking too much time to save, and you suspect that SQL queries might be the bottleneck. The Empress Recorder is the perfect tool to diagnose this issue. Here’s how to get started:
- Open the Recorder from the Awesomebar and click on “Start”.
- Perform the actions you want to profile (preferably in a separate tab).
- Once you’ve captured enough information, go back to the Recorder and click on “Stop”.
Now, you’ll see a list of all the requests that were made. You can sort them by various columns to identify problematic requests.
Analyzing Requests
By clicking on a row, you can open the request for more detailed information. The Recorder provides the following data:
- path - The requested path or job name, such as
/app. - cmd - The dotted path to the method, which will be empty for background jobs.
- time - The time at which the request was created.
- duration - The duration for completing the request.
- number of queries - The number of SQL queries executed for fulfilling the request.
- Time in queries - The time taken in SQL queries.
- Request headers - The HTTP headers received with the request (empty for background jobs).
- Form Dict - The form data received with the request (empty for background jobs).
- SQL queries - A table of all SQL queries that were executed.
To learn more about a particular query, click on the row to expand additional information. This includes the duration of the query, stack trace, and SQL’s EXPLAIN output for that query.
Note: The Recorder adds a substantial overhead for capturing the details, so the overall duration might not accurately represent real-world performance. The query time, however, is very close to real-world performance.
Exporting and Importing Recorder Captures
The Recorder allows you to export captures and import them on another site for further analysis. Here’s how:
- Go to the Recorder page. After recording, click on the Menu (three dots) > Export. This will download a JSON file containing the captured data.
- To view this data on another site, click on “Import” and select the JSON file.
Profiling with Bench
Bench’s execute command runs a dotted path to a method and supports profiling. Most commands you can run via the console with execute are supported, including db methods.
▶ bench --site [sitename] --profile execute erpnext.projects.doctype.task.task.set_tasks_as_overdue
▶ bench --site [sitename] execute frappe.db.get_database_size
6784
Using Empress Monitor
The Empress Monitor logs request and job metadata. To enable this feature, set "monitor": 1 in your site config. Collected data is buffered in the Redis cache and periodically moved to the monitor.json.log file in the logs directory with a scheduled job frappe.monitor.flush.
Monitoring Background Jobs
Empress uses RQ (Redis Queue) for asynchronously executing long tasks in the background. The framework provides inbuilt virtual doctypes for monitoring RQ:
- RQ Worker - Shows all background workers consuming the background jobs queue on your site. It also contains basic statistics about the worker like name, timing, successful and failed jobs count, and current status.
- RQ Job - Provides information about all background jobs. You can filter jobs by queue and status.
In conclusion, the Empress Recorder, along with the additional profiling and monitoring tools, forms a comprehensive suite for SQL profiling and performance optimization. By leveraging these tools, developers can more effectively diagnose and resolve performance bottlenecks, resulting in more efficient and reliable applications.