Table of contents
Preamble
SYS schema is a collection of objects that helps DBAs and developers interpret data collected by the Performance Schema. in my article on Performance Schema I have been able to build a bunch of queries to extract useful information but apparently it is even easier with SYS schema that act as a facilitator for Performance Schema views and tables. SYS schema objects helps you investigate and dig in typical tuning use cases. SYS schema is made of:
- Views that summarize Performance Schema data into more understandable by formatting columns (picosesonds and bytes converted for examples) and ordered form.
- Stored procedures that perform Performance Schema configuration and generate diagnostic reports.
Again here the MariaDB official documentation is almost non existant so access the MySQL one…
My test server has 64MB with 12 cores (24 threads) and is running with Red Hat Enterprise Linux release 8.7 (Ootpa). My MariaDB Community edition database is 10.11.7 and I’m using sysbench 1.0.20 to generate some workload to have some figures to report in my blog post. You might also see some select from a 10.6.16 MariaDB Enterprise running on a 16 cores (32 threads), Red Hat Enterprise Linux release 8.4 (Ootpa) with 2TB of memory Linux server.
SYS schema configuration
sys.sys_config
MariaDB [(none)]> select * from sys.sys_config; +--------------------------------------+-------+---------------------+--------+ | variable | value | set_time | set_by | +--------------------------------------+-------+---------------------+--------+ | statement_truncate_len | 64 | 2024-02-01 14:47:11 | NULL | | statement_performance_analyzer.limit | 100 | 2024-02-01 14:47:11 | NULL | | statement_performance_analyzer.view | NULL | 2024-02-01 14:47:11 | NULL | | diagnostics.allow_i_s_tables | OFF | 2024-02-01 14:47:11 | NULL | | diagnostics.include_raw | OFF | 2024-02-01 14:47:11 | NULL | | ps_thread_trx_info.max_length | 65535 | 2024-02-01 14:47:11 | NULL | +--------------------------------------+-------+---------------------+--------+ 6 rows in set (0.001 sec) |
Table without the X$ suffix are more human readable but be very cautious when selecting them. Most of them are already sorted. Just to give an example the sys.io_global_by_file_by_bytes, if you try to order by total you end up with something misleading: the table is already sorted and it is written explicitly in the official documentation:
MariaDB [(none)]> select * from sys.io_global_by_file_by_bytes order by total desc limit 5; +------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+ | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | +------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+ | @@datadir/mysql/transaction_registry.ibd | 6 | 96.00 KiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 96.00 KiB | 0.00 | | @@datadir/appusers/Users.ibd | 0 | 0 bytes | 0 bytes | 6 | 96.00 KiB | 16.00 KiB | 96.00 KiB | 100.00 | | @@datadir/appusers/UsersHist.ibd | 0 | 0 bytes | 0 bytes | 6 | 96.00 KiB | 16.00 KiB | 96.00 KiB | 100.00 | | @@datadir/exampledb/t1.ibd | 59706 | 932.91 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 932.91 MiB | 0.00 | | @@datadir/appprod/Info.ibd | 3 | 48.00 KiB | 16.00 KiB | 5765 | 90.08 MiB | 16.00 KiB | 90.12 MiB | 99.95 | +------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+ 5 rows in set (0.014 sec) |
So simply do:
MariaDB [(none)]> select * from sys.io_global_by_file_by_bytes limit 5; +-------------------------------------+------------+------------+-----------+-------------+---------------+------------+------------+-----------+ | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | +-------------------------------------+------------+------------+-----------+-------------+---------------+------------+------------+-----------+ | @@datadir/ibdata1 | 187984 | 2.87 GiB | 16.01 KiB | 2057088 | 3.76 TiB | 1.91 MiB | 3.76 TiB | 99.93 | | @@datadir/ib_logfile0 | 6 | 66.50 KiB | 11.08 KiB | 1929587 | 602.20 GiB | 327.25 KiB | 602.20 GiB | 100.00 | | @@datadir/appprod/table01.ibd | 34 | 544.00 KiB | 16.00 KiB | 39379652 | 600.89 GiB | 16.00 KiB | 600.89 GiB | 100.00 | | @@datadir/appprod/table02.ibd | 31 | 496.00 KiB | 16.00 KiB | 38457802 | 586.82 GiB | 16.00 KiB | 586.82 GiB | 100.00 | | @@datadir/appprod/table03.ibd | 25 | 400.00 KiB | 16.00 KiB | 28265140 | 431.29 GiB | 16.00 KiB | 431.29 GiB | 100.00 | +-------------------------------------+------------+------------+-----------+-------------+---------------+------------+------------+-----------+ 5 rows in set (0.006 sec) |
This understood, the views without the X$ are particularly interesting as the boring unit conversion is already done. The order by on the most interesting column(s) is also done for you. Tables with the X$ are, like in the Performance Schema, in picoseconds (trillionths of a second, abbreviated as ps) BUT are also ordered. The only things you would have to do in unit conversion if required…
You have also a set of procedures to configure the Performance Schema. If I try to do what I have done manually by updating performance_schema.setup_consumers and performance_schema.setup_instruments tables:
MariaDB [(none)]> call sys.ps_setup_enable_consumer('current'); +---------------------+ | summary | +---------------------+ | Enabled 4 consumers | +---------------------+ 1 row in set (0.001 sec) Query OK, 4 rows affected (0.002 sec) MariaDB [(none)]> call sys.ps_setup_enable_instrument('transaction'); +------------------------+ | summary | +------------------------+ | Enabled 10 instruments | +------------------------+ 1 row in set (0.003 sec) Query OK, 10 rows affected (0.003 sec) |
Remark:
I see a first glitch here where the transaction keyword has activated a bit more than what I expected…
MariaDB [(none)]> select * from performance_schema.setup_instruments where name like '%transaction%'; +--------------------------------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +--------------------------------------------------------------------------------+---------+-------+ | stage/sql/Waiting for prior transaction to commit | YES | YES | | stage/sql/Waiting for prior transaction to start commit | YES | YES | | transaction | YES | YES | | memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES | NO | | memory/performance_schema/events_transactions_summary_by_host_by_event_name | YES | NO | | memory/performance_schema/events_transactions_summary_by_thread_by_event_name | YES | NO | | memory/performance_schema/events_transactions_history | YES | NO | | memory/performance_schema/events_transactions_summary_by_user_by_event_name | YES | NO | | memory/performance_schema/events_transactions_history_long | YES | NO | | memory/sql/THD::transactions::mem_root | YES | NO | +--------------------------------------------------------------------------------+---------+-------+ 10 rows in set (0.002 sec) |
Instances global waits
select * from sys.waits_global_by_latency limit 10; +--------------------------------------+------------+---------------+-------------+-------------+ | events | total | total_latency | avg_latency | max_latency | +--------------------------------------+------------+---------------+-------------+-------------+ | wait/io/table/sql/handler | 9123621625 | 1.54 d | 14.54 us | 24.33 s | | wait/io/file/innodb/innodb_data_file | 310047827 | 19.28 h | 223.90 us | 575.44 ms | | wait/io/file/innodb/innodb_log_file | 3896078 | 1.13 h | 1.04 ms | 214.84 ms | | wait/io/file/aria/MAD | 1419118 | 22.57 m | 954.34 us | 92.43 ms | | wait/io/file/aria/MAI | 6394060 | 22.07 m | 207.07 us | 81.15 ms | | wait/io/file/sql/binlog | 22880244 | 18.26 m | 47.89 us | 611.02 ms | | wait/lock/table/sql/handler | 3372386 | 5.83 s | 1.73 us | 2.13 ms | | wait/io/file/sql/FRM | 2319 | 813.97 ms | 351.00 us | 11.20 ms | | wait/io/file/sql/binlog_index | 4970 | 811.43 ms | 163.27 us | 51.73 ms | | wait/io/file/sql/file_parser | 1966 | 646.10 ms | 328.63 us | 6.87 ms | +--------------------------------------+------------+---------------+-------------+-------------+ 10 rows in set (0.010 sec) |
You can then use those two tables to group event by class (stripping of first three components) to have a more synthetic view:
- wait_classes_global_by_latency
- wait_classes_global_by_avg_latency
The stage and transaction tables of the SYS schema are not really interesting, I guess it means we should not concentrate too much on this while investigating performance issue…
The main statement view to look first is sys.statement_analysis. Versus Performance Schema you directly have all the required information in one table. This table cannot really be displayed in text mode:
select * from sys.statement_analysis; |
Then you have a set of tables to dig into more details:
- statements_with_errors_or_warnings
- statements_with_full_table_scans
- statements_with_runtimes_in_95th_percentile
- statements_with_sorting
- statements_with_temp_tables
- user_summary_by_statement_latency
- user_summary_by_statement_type
select * from sys.user_summary_by_statement_type where user='sbuser'; +--------+-----------+----------+---------------+-------------+--------------+------------+---------------+---------------+------------+ | user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +--------+-----------+----------+---------------+-------------+--------------+------------+---------------+---------------+------------+ | sbuser | select | 63234934 | 3.45 h | 893.47 ms | 29.61 m | 1404718891 | 3206914510 | 0 | 0 | | sbuser | update | 9033562 | 1.18 h | 7.62 s | 5.41 m | 0 | 9033562 | 9033562 | 0 | | sbuser | delete | 4516781 | 16.57 m | 7.63 s | 2.04 m | 0 | 4516781 | 4516781 | 0 | | sbuser | commit | 4516781 | 16.00 m | 293.98 ms | 0 ps | 0 | 0 | 0 | 0 | | sbuser | insert | 4516781 | 15.46 m | 9.23 s | 3.24 m | 0 | 0 | 4516781 | 0 | | sbuser | begin | 4516781 | 2.80 m | 22.54 ms | 0 ps | 0 | 0 | 0 | 0 | | sbuser | Quit | 72 | 640.03 us | 25.28 us | 0 ps | 0 | 0 | 0 | 0 | +--------+-----------+----------+---------------+-------------+--------------+------------+---------------+---------------+------------+ 7 rows in set (0.009 sec) |
You have also a procedure that display an histogram:
MariaDB [(none)]> call sys.ps_statement_avg_latency_histogram()\G *************************** 1. row *************************** Performance Schema Statement Digest Average Latency Histogram: . = 1 unit * = 2 units # = 3 units (0 - 5961ms) 418 | ########################################################################################################################################### (5961 - 11921ms) 0 | (11921 - 17882ms) 0 | (17882 - 23842ms) 0 | (23842 - 29803ms) 0 | (29803 - 35763ms) 0 | (35763 - 41724ms) 0 | (41724 - 47685ms) 0 | (47685 - 53645ms) 0 | (53645 - 59606ms) 0 | (59606 - 65566ms) 0 | (65566 - 71527ms) 0 | (71527 - 77487ms) 0 | (77487 - 83448ms) 0 | (83448 - 89408ms) 0 | (89408 - 95369ms) 0 | Total Statements: 419; Buckets: 16; Bucket Size: 5961 ms; 1 row in set (0.048 sec) Query OK, 0 rows affected (0.048 sec) |
But I have difficulties to find it useful but it could give you a very high level on how your statement time are spread on your instance…
For the SYS Schema IOPS tables the list is quite complete:
- io_by_thread_by_latency
- io_global_by_file_by_bytes
- io_global_by_file_by_latency
- io_global_by_wait_by_bytes
- io_global_by_wait_by_latency
- latest_file_io
- user_summary_by_file_io
- user_summary_by_file_io_type
Example:
select * from sys.io_global_by_wait_by_bytes; +-------------------------+---------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+ | event_name | total | total_latency | min_latency | avg_latency | max_latency | count_read | total_read | avg_read | count_write | total_written | avg_written | total_requested | +-------------------------+---------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+ | innodb/innodb_data_file | 2585842 | 10.11 m | 0 ps | 234.52 us | 1.13 s | 272551 | 4.16 GiB | 16.01 KiB | 2045958 | 61.25 GiB | 31.39 KiB | 65.41 GiB | | sql/binlog | 4260878 | 2.03 m | 0 ps | 28.55 us | 294.64 ms | 1 | 412 bytes | 412 bytes | 4260845 | 3.49 GiB | 880 bytes | 3.49 GiB | | aria/MAD | 199964 | 42.26 s | 0 ps | 211.31 us | 327.30 ms | 77723 | 44.22 MiB | 597 bytes | 2940 | 14.35 MiB | 5.00 KiB | 58.57 MiB | | aria/MAI | 171401 | 59.24 s | 0 ps | 345.60 us | 154.41 ms | 13149 | 4.28 MiB | 342 bytes | 112353 | 5.15 MiB | 48 bytes | 9.43 MiB | | sql/slow_log | 7189 | 357.51 ms | 1.44 us | 49.73 us | 13.81 ms | 0 | 0 bytes | 0 bytes | 7186 | 8.85 MiB | 1.26 KiB | 8.85 MiB | | sql/FRM | 914 | 2.13 s | 0 ps | 2.33 ms | 51.84 ms | 312 | 493.76 KiB | 1.58 KiB | 0 | 0 bytes | 0 bytes | 493.76 KiB | | mysys/charset | 3 | 32.98 ms | 14.27 us | 10.99 ms | 32.90 ms | 1 | 22.96 KiB | 22.96 KiB | 0 | 0 bytes | 0 bytes | 22.96 KiB | | aria/translog | 31 | 50.42 ms | 0 ps | 1.63 ms | 45.31 ms | 2 | 8.04 KiB | 4.02 KiB | 0 | 0 bytes | 0 bytes | 8.04 KiB | | sql/binlog_index | 50 | 39.18 ms | 708.95 ns | 783.63 us | 14.42 ms | 8 | 860 bytes | 108 bytes | 5 | 344 bytes | 69 bytes | 1.18 KiB | | sql/dbopt | 30 | 40.51 ms | 1.44 us | 1.35 ms | 10.46 ms | 5 | 331 bytes | 66 bytes | 0 | 0 bytes | 0 bytes | 331 bytes | | aria/control | 14 | 15.45 ms | 2.43 us | 1.10 ms | 12.16 ms | 1 | 52 bytes | 52 bytes | 0 | 0 bytes | 0 bytes | 52 bytes | | sql/binlog_state | 6 | 2.84 ms | 1.48 us | 473.21 us | 2.82 ms | 1 | 13 bytes | 13 bytes | 0 | 0 bytes | 0 bytes | 13 bytes | | sql/global_ddl_log | 4 | 2.24 ms | 0 ps | 560.78 us | 1.98 ms | 0 | 0 bytes | 0 bytes | 1 | 9 bytes | 9 bytes | 9 bytes | | sql/pid | 3 | 387.78 us | 33.01 us | 129.26 us | 202.67 us | 0 | 0 bytes | 0 bytes | 1 | 7 bytes | 7 bytes | 7 bytes | | sql/misc | 2 | 51.85 us | 0 ps | 25.92 us | 27.13 us | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes | 0 bytes | | sql/file_parser | 101 | 595.52 us | 0 ps | 5.90 us | 12.16 us | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes | 0 bytes | | sql/casetest | 5 | 27.62 ms | 0 ps | 5.52 ms | 9.87 ms | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes | 0 bytes | +-------------------------+---------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+ 17 rows in set (0.009 sec) |
SYS schema schema view
The SYS Schema has also a set of schema views to display various information about schema:
- schema_auto_increment_columns
- schema_index_statistics
- schema_object_overview
- schema_redundant_indexes
- schema_table_lock_waits
- schema_table_statistics
- schema_table_statistics_with_buffer
- schema_tables_with_full_table_scans
- schema_unused_indexes
Few examples with my favorites ones. Tables with full table scan (FTS):
select object_schema, object_name, rows_full_scanned, latency from sys.schema_tables_with_full_table_scans; +---------------+---------------+-------------------+-----------+ | object_schema | object_name | rows_full_scanned | latency | +---------------+---------------+-------------------+-----------+ | F6.URaadjsXjc | F6..HZ3dRSqJY | 551900284 | 2.48 m | | F6L.0LQ.0RnFg | F6LLsA6u425nc | 72029 | 4.95 h | | F6.URaadjsXjc | F6CcgCJv6Pu.A | 20246 | 7.72 s | | F61HINDmwYilY | F6fT32AQAd96A | 62 | 1.20 ms | | F6L.0LQ.0RnFg | F6asAp6GKz8E. | 55 | 4.07 h | | F6yj0zX2CYPLU | F63wcSs8REDPc | 18 | 267.16 us | | F6L.0LQ.0RnFg | F6XsqTZeaXSd. | 14 | 3.51 s | | F61HINDmwYilY | F6zK9lJsiSqTc | 10 | 216.54 us | | F6L.0LQ.0RnFg | F6zK9lJsiSqTc | 8 | 100.78 us | | F6.URaadjsXjc | F6zK9lJsiSqTc | 2 | 102.47 us | +---------------+---------------+-------------------+-----------+ 10 rows in set (0.007 sec) |
Redundant indexes:
select * from sys.schema_redundant_indexes; +--------------+------------+----------------------+-----------------------------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+-------------------------------------------------------------------+ | table_schema | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | subpart_exists | sql_drop_index | +--------------+------------+----------------------+-----------------------------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+-------------------------------------------------------------------+ | F6.URaadjsjc | F61HINDmY | F61HINDmwYilY | column1,column2,column3 | 1 | uk_column1 | column1 | 0 | 0 | ALTER TABLE `F6.URaadjsjc`.`F61HINDmY` DROP INDEX `F61HINDmwYilY` | +--------------+------------+----------------------+-----------------------------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+-------------------------------------------------------------------+ 1 row in set (0.064 sec) |
Unused indexes:
select * from sys.schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | sbtest | sbtest1 | k_1 | | sbtest | sbtest10 | k_10 | | sbtest | sbtest11 | k_11 | | sbtest | sbtest12 | k_12 | | sbtest | sbtest13 | k_13 | | sbtest | sbtest14 | k_14 | | sbtest | sbtest15 | k_15 | | sbtest | sbtest16 | k_16 | | sbtest | sbtest17 | k_17 | | sbtest | sbtest18 | k_18 | | sbtest | sbtest19 | k_19 | | sbtest | sbtest2 | k_2 | | sbtest | sbtest20 | k_20 | | sbtest | sbtest21 | k_21 | | sbtest | sbtest22 | k_22 | | sbtest | sbtest23 | k_23 | | sbtest | sbtest24 | k_24 | | sbtest | sbtest25 | k_25 | | sbtest | sbtest26 | k_26 | | sbtest | sbtest27 | k_27 | | sbtest | sbtest28 | k_28 | | sbtest | sbtest29 | k_29 | | sbtest | sbtest3 | k_3 | | sbtest | sbtest30 | k_30 | | sbtest | sbtest4 | k_4 | | sbtest | sbtest5 | k_5 | | sbtest | sbtest6 | k_6 | | sbtest | sbtest7 | k_7 | | sbtest | sbtest8 | k_8 | | sbtest | sbtest9 | k_9 | +---------------+-------------+------------+ 30 rows in set (0.003 sec) |
SYS schema side procedures
sys.diagnostics()
I was expecting here an Oracle AWR like report, I was probably too much enthusiastic… I have tried the exact same example as the official documentation i.e. a
MariaDB [(none)]> tee diag.out; Logging to file 'diag.out' MariaDB [(none)]> CALL sys.diagnostics(120, 30, 'current'); . . . MariaDB [(none)]> notee; Outfile disabled. |
If you want an HTML output you can use below command but some output will not be correctly formatted, like the performance schema statement digest average latency histogram just to give an example:
mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=mariadb -H -e "CALL sys.diagnostics(120, 30, 'current');" > diag.html |
I have tried to have a look to the text version of the report and it is so much information that I have not been able to extract really useful information from it… In my opinion report should be more concise and should more focus only on conclusion…
sys.statement_performance_analyzer()
Procedure to create different reports of the statement running on your instance. The example I have tried is based on sys.statement_analysis view and display top ten queries sorted by latency.
Create the initial snapshot:
MariaDB [(none)]> call sys.statement_performance_analyzer('snapshot', NULL, NULL); +-------------------+ | summary | +-------------------+ | Disabled 1 thread | +-------------------+ 1 row in set (0.006 sec) +------------------+ | summary | +------------------+ | Enabled 1 thread | +------------------+ 1 row in set (0.027 sec) Query OK, 424 rows affected (0.027 sec) |
Limit the number of return rows to 10:
MariaDB [(none)]> SET @sys.statement_performance_analyzer.limit = 10; Query OK, 0 rows affected (0.000 sec) |
Perform analysis based on the snapshot:
MariaDB [(none)]> call sys.statement_performance_analyzer('overall', NULL, 'analysis'); +-------------------+ | summary | +-------------------+ | Disabled 1 thread | +-------------------+ 1 row in set (0.006 sec) +-----------------------------------------+ | Next Output | +-----------------------------------------+ | Top 10 Queries Ordered by Total Latency | +-----------------------------------------+ 1 row in set (0.012 sec) +-------------------------------------------------------------------+--------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+ | query | db | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | rows_affected | rows_affected_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest | first_seen | last_seen | +-------------------------------------------------------------------+--------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+ | COMMIT | sbtest | | 4731491 | 0 | 0 | 16.31 m | 293.98 ms | 206.88 us | 0 ps | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 06dbaf4a605324ce11a8d3efe7b6d5c5 | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | | SELECT * FROM ( SELECT `digest ... um_sort_scan` AS `SortScan` , | NULL | * | 7518 | 0 | 0 | 6.11 m | 518.78 ms | 48.77 ms | 2.84 s | 25182 | 3 | 10136372 | 1348 | 0 | 0 | 15036 | 15036 | 120290 | 0 | 35980e15de849c78ba0a4dd8f740750d | 2024-02-21 10:28:58 | 2024-02-26 16:07:34 | | CALL `sys` . `diagnostics` (...) | NULL | | 4 | 1 | 0 | 5.49 m | 1.59 m | 1.37 m | 764.00 us | 0 | 0 | 0 | 0 | 19191 | 4798 | 0 | 0 | 0 | 0 | 77320d80863a157cbc09311520f6725b | 2024-02-23 12:24:25 | 2024-02-26 14:38:46 | | SELECT `c` FROM `sbtest26` WHERE ID = ? | sbtest | | 1584700 | 0 | 0 | 3.63 m | 690.71 ms | 137.40 us | 42.53 s | 1584219 | 1 | 1584239 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | f60b1bdfa9784247ee6a450d08467288 | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | | SELECT `c` FROM `sbtest5` WHERE ID = ? | sbtest | | 1578160 | 0 | 0 | 3.62 m | 625.49 ms | 137.73 us | 42.47 s | 1577620 | 1 | 1577628 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 79eed6517d8811275de35e5243c0f3e9 | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | | SELECT `c` FROM `sbtest7` WHERE ID = ? | sbtest | | 1584955 | 0 | 0 | 3.58 m | 77.94 ms | 135.45 us | 42.41 s | 1584398 | 1 | 1584405 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 2bc5623033dc6aad3ece551d198aec31 | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | | SELECT `c` FROM `sbtest6` WHERE ID = ? | sbtest | | 1579565 | 0 | 0 | 3.57 m | 667.87 ms | 135.74 us | 42.44 s | 1579081 | 1 | 1579088 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | a3427e3fae6b2059fc77ac9c34e31641 | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | | SELECT `c` FROM `sbtest9` WHERE ID = ? | sbtest | | 1577985 | 0 | 0 | 3.57 m | 745.93 ms | 135.75 us | 42.51 s | 1577517 | 1 | 1577531 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 77ab8b72ca9ee25c5f8827efa23fe78a | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | | SELECT `c` FROM `sbtest3` WHERE ID = ? | sbtest | | 1578778 | 0 | 0 | 3.57 m | 92.12 ms | 135.66 us | 42.22 s | 1578238 | 1 | 1578251 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | d6a8c384b7019a4584573ba0cd2bd5ac | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | | SELECT `c` FROM `sbtest11` WHERE ID = ? | sbtest | | 1579362 | 0 | 0 | 3.57 m | 333.50 ms | 135.60 us | 42.35 s | 1578935 | 1 | 1578951 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 6ad443aa77dca0f74ceaef48842ac716 | 2024-02-22 18:00:28 | 2024-02-26 16:07:50 | +-------------------------------------------------------------------+--------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+ 10 rows in set (0.030 sec) +------------------+ | summary | +------------------+ | Enabled 1 thread | +------------------+ 1 row in set (0.030 sec) Query OK, 425 rows affected (0.031 sec) |
sys.format_time() and sys.format_bytes()
Interesting funtions that you could use in your queries on Performance Schema are sys.format_time() (sys.format_pico_time() starting with MariaDB 11) and sys.format_bytes():
MariaDB [(none)]> select sys.format_time(2024), sys.format_time(20241568751654); +-----------------------+---------------------------------+ | sys.format_time(2024) | sys.format_time(20241568751654) | +-----------------------+---------------------------------+ | 2.02 ns | 20.24 s | +-----------------------+---------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> select sys.format_bytes(1000000000),sys.format_bytes(1073741874); +------------------------------+------------------------------+ | sys.format_bytes(1000000000) | sys.format_bytes(1073741874) | +------------------------------+------------------------------+ | 953.67 MiB | 1.00 GiB | +------------------------------+------------------------------+ 1 row in set (0.001 sec) |
References
