Improve summary output for saved_queries to help find poorly performing database requests

bobbingwide commented 4 years ago

oik-bwtrace has an option to Trace 'shutdown' saved queries. When checked the MySQL queries are recorded in $wpdb->queries and the two reports are produced

One simply traces the raw data.
Then it's summarised in a block of output using [bw_sql] shortcodes, one per query.

To make it easier to analyse the overall performance of the SQL queries that are run we need to extract the data into a spreadsheet. It would be nice to have another report, sorted by longest execution time, and showing cumulative execution time. It should be formatted suitable for loading directly into a spreadsheet as a CSV file.

eg given this subset

[bw_sql 3 0.000169 get_option]SELECT option_value FROM wp_options WHERE option_name = 'health-check-current-theme' LIMIT 1[/bw_sql]
[bw_sql 4 0.002719 WP_User::get_data_by]SELECT * FROM wp_users WHERE user_login = 'wp' LIMIT 1[/bw_sql]
[bw_sql 5 0.000541 update_meta_cache]SELECT user_id&comma;  meta_key&comma;  meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC[/bw_sql]

the summary report would be

#,Time,Accum,Function,SQL
4,0.002719,0.002719,WP_User::get_data_by,SELECT * FROM....
5,0.000541,0.003260,update_meta_cache,SELECT user_id ...
3,0.000169,0.003429,get_option,SELECT option_value ...

Once loaded into the spreadsheet the data can be sorted and filtered.

Proposed solution

[x] Add new logic to display the raw results in CSV format
[x] Add logic to sort the table by query duration
[x] Add logic to reaccumulate the Accum. column
[x] Add logic to display the results again
[x] Add logic report the saved queries grouped by function
[x] Disable/ deprecate the logic to display the [bw_sql] shortcodes
[x] The index number ( column # ) should start from 0 to match the saved_queries array.
[x] The SQL query string should be formatted so that it uses as few cells as possible
[x] The timestamps should not be rounded

bobbingwide commented 4 years ago

I'm doing this to help you Mr L.

bobbingwide commented 4 years ago

I've now added logic to report the saved queries, grouped by function. Example:

C:\apache\htdocs\wordpress\wp-content\plugins\oik-bwtrace\includes\oik-actions.php(350:0)
 bw_trace_saved_queries_grouped_by_function(1) 
37 0 2020-11-20T15:20:10+00:00 2.875893 0.000800 
cf=shutdown 9917 354 964 10485760/12582912 256M Grouped by function 
Function | Count | Elapsed
-------- | ----- | -------
Total | 354 | 1.4749894142151
Yoast\WP\Lib\ORM::execute | 259 | 1.2082939147949
add_option | 1 | 0.11339902877808
wp_insert_post | 2 | 0.030858039855957
...

The table is formatted using Markdown for easy insertion into GitHub issues.

Function	Count	Elapsed
Total	354	1.4749894142151
Yoast\WP\Lib\ORM::execute	259	1.2082939147949
add_option	1	0.11339902877808
wp_insert_post	2	0.030858039855957

bobbingwide commented 3 years ago

This is now released in v3.2.1 on wordpress.org and oik-plugins. Closing

bobbingwide / oik-bwtrace

Improve summary output for saved_queries to help find poorly performing database requests #93

Proposed solution