Page MenuHomePhabricator

Deploy access to performance_schema/sys for the administrative mediawiki account (mediawiki deployers)
Closed, ResolvedPublic

Description

Performance schema statistics were not deployed to public prometheus/grafana because of privacy reasons (it could contain sensitive information AND could reveal user information for wikis with low load, we were told). The alternative was going to be to deploy and aggregate that to tendril or to a private prometheus instance, but that is not that easy/quick.

For short term, we realized all deployers did not have access to that (only to wiki dbs/those with root access). Please check performance schema/sys utility metrics on each database, see if they are helpful, and if there is something exposed that shouldn't be to deployers. It is available on the production, but not pooled with active use db2083. If everythings is correct, we will provide access to all mediawiki servers to all deployers.

Log in to db2083 (sql wikidatawiki -h db2083, use sys), and query some starts/query digest/index stats.

I have added some people to this ticket that may be interested on this- but I require no actionable from you (except test it/break it), also feel free to unubscribe if this is not interesting for you.

For a quick documentation, check my mediawiki query optimization slides at: https://www.slideshare.net/jynus/query-optimization-from-0-to-10-and-up-to-57/158 / https://github.com/jynus/query-optimization

For Security-Team, the request is:

we want your ok to expose low level internal mw database performance metrics to mw deployers for debugging purposes, through the existing mediawiki admin database account. Think of logs, but real time and database layer. Metrics will be accessed, for now, through the SQL interface only. There is a lot of metrics involved (literally billions of them) but it includes file, thread, user, transaction, query and query digest, and indexes individual metrics and counters (aggregations). Currently that access is reserved for mysql root users only.

For DBA s, the actual changes to be done are:

grants
set sql_log_bin=0;
GRANT SELECT, EXECUTE ON `sys`.* TO <mw_admin_account>;
GRANT SELECT ON `performance_schema`.* TO <mw_admin_account>;

on all mw core dbs.

  • Security sign off
  • Deploy change
    • s1
    • s2
    • s3
    • s4
    • s5
    • s6
    • s7
    • s8
    • x1
    • x2
    • es4
    • es5
  • Once deployed, send an announcement email

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Marostegui renamed this task from lacaaaaaaa to Deploy access to performance_schema/sys for the administrative mediawiki account (mediawiki deployers).Jul 1 2018, 6:59 PM
Marostegui lowered the priority of this task from High to Medium.
Marostegui updated the task description. (Show Details)
jcrespo added subscribers: Reedy, Bawolff, JBennett.

@Security-team Do you see any blocker or reason not to enable this on all hosts? This was already available for roots only, and now it will be extended to the admin account (deployers with mediawiki database access). It contains performance information similar to that of tendril, but more detailed. Please have a look at it on db2083 yourselves if on doubt.

If no blocker, I will enable this on all mediawiki db hosts. If you are unsure, I will wait for your feedback.

Ladsgroup added a subscriber: Ladsgroup.

Having something like this would be extremely useful for me. I have been looking for something like this for a while now.

I think this should be tagged with the security team. The Security tag is very general.

@jcrespo @Marostegui @Ladsgroup -

Sorry for the ridiculous delay here. As @Ladsgroup noted above, adding Security-Team to a task should now get said task within our team's weekly triage/review process. For this task, given its age, is it still relevant? Has the work already been completed? If it's still being blocked on a risk analysis from the Security-Team, could we formalize the request a bit more so we can better understand the scope and get a review scheduled? Thanks.

For this task, given its age, is it still relevant?

This is still very relevant. Let me know what process is needed, other than a deeper description of the task, as the new requests formats didn't exist back when this query was first created.

For starters, does anyone on your team have access to performance_schema/sys on production databases, or on a test host?

@jcrespo -

Just confirming this is the specific ask of the Security-Team:

Please check performance schema/sys utility metrics on each database, see if they are helpful, and if there is something exposed that shouldn't be to deployers ... If everythings is correct, we will provide access to all mediawiki servers to all deployers.

I'm not certain that members of the Security-Team have easy access to do this, so we might require some assistance and perhaps some further definition of what, specifically, we should review. Thanks.

We don't need Security-Team feedback in terms of usefulness, that part of the ticket was for general deployers to comment if they found it helpful as to enable it on all hosts.

For Security-Team, the request is:

we want your ok to expose low level internal mw database performance metrics to mw deployers for debugging purposes, through the existing mediawiki admin database account. Think of logs, but real time and database layer. Metrics will be accessed, for now, through the SQL interface only. There is a lot of metrics involved (literally billions of them) but it includes file, thread, user, transaction, query and query digest, and indexes individual metrics and counters (aggregations). Currently that access is reserved for mysql root users only.

It is my fault for mixing the "user feedback" with the "security ok/heads up".

I 100% understand you may lack lots of context here, that is why I am asking what would be the requirement for oking this ? For example,

  • Access to an example database (e.g. "Log in to db2083 (sql wikidatawiki -h db2083, use sys), and query some starts/query digest/index stats.") for playing around?
  • Documentation of every metric exposed?
  • Delegate who makes the call on what is the appropriate level of access on any other team (DBAs or Releng) because this is out of your scope/area of expertise?

What we don't want is to proceed with this without at least you being aware.

chasemp moved this task from Back Orders to Waiting on the Security-Team board.
chasemp added a subscriber: chasemp.

@jcrespo thanks for explaining, I'll get this on our agenda for this week. If there is some big unforseen concern it may need another week to germinate as @JBennett is mostly occupied till then.

Just to be clear, not precisely a #1 priority thing, we didn't make much noise about this for a reason... but this got unearthed lately after a couple of performance-related requests from mw maintainers, as it could help with as a self-service model rather than pinging DBAs everytime. :-D

Feel free to ask for anything- access, info, etc.

Security never came back with comments, and we gave them a full year to do so, even insisting by several methods/people. At this point, from my point of view, if @Marostegui is cool with it, I would give you full access to p_s/sys- as long as fine-grained access statistics are not exposed, specially of small wikis, which is why this cannot be fully public in the first place.

I think it is ok to give access to @Ladsgroup (NDA signed, full access to our MW databases...) but as you said, let's not expose that data.
Thanks Jaime! (If you have time, can you document the procedure of giving access to individuals so we have written it somewhere?).

@Marostegui Allow me to deploy for now only the access he needs (wikidata) to understand what that would be (and use him to check he can access properly), and I will document here what's an appropiate deployment procedure for all dbs.

@Marostegui Allow me to deploy for now only the access he needs (wikidata) to understand what that would be (and use him to check he can access properly), and I will document here what's an appropiate deployment procedure for all dbs.

+1 thank you

Security-Team dropped the ball here on feedback. This is a bit of one-off, and I'll feed it back into our queue so it gets attention, but I support @jaime and @Marostegui in their practicality. Thanks gents.

Mentioned in SAL (#wikimedia-operations) [2020-09-17T15:02:22Z] <jynus> deploying extended grants for admin account on sys/p_s at s8@codfw T195578

To unblock @Ladsgroup, and so he can serve as "beta tester", I have added to the following hosts (s8 mw-pooled instances on codfw):

list of instances
db2079
db2080
db2081
db2082
db2083
db2084
db2085:3318
db2086:3318
db2091

The following grants:

grants
set sql_log_bin=0;
GRANT SELECT, EXECUTE ON `sys`.* TO <mw_admin_account>;
GRANT SELECT ON `performance_schema`.* TO <mw_admin_account>;

So only reads can be done on the profiling engine.

Once he confirms it works ok (there could be some issues regarding needing additional privileges -SUPER or PROCESSLIST- for some functions, see https://fromdual.com/mysql-performance-schema-hints#grant-ps-schema-access ), we can add this to the grants on puppet and thinking about deploying it cluster-wide.

Complete usage manual is at: https://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-index.html

To unblock @Ladsgroup, and so he can serve as "beta tester", I have added to the following hosts (s8 mw-pooled instances on codfw):

list of instances
db2079
db2080
db2081
db2082
db2083
db2084
db2085:3318
db2086:3318
db2091

The following grants:

grants
set sql_log_bin=0;
GRANT SELECT, EXECUTE ON `sys`.* TO <mw_admin_account>;
GRANT SELECT ON `performance_schema`.* TO <mw_admin_account>;

So only reads can be done on the profiling engine.

Once he confirms it works ok (there could be some issues regarding needing additional privileges -SUPER or PROCESSLIST- for some functions, see https://fromdual.com/mysql-performance-schema-hints#grant-ps-schema-access ), we can add this to the grants on puppet and thinking about deploying it cluster-wide.

Complete usage manual is at: https://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-index.html

Thank you for handling this.

Thank you for the right, it was extremely useful to me, I wrote a detailed report on the load of s8 thanks to this right on T246415#6474175 onwards

I did a small introduction to Ladsgroup on how to use sys/performance_schema, feel free to request an introduction too (but preferably in a group).

@JFishback_WMF what's the relative priority of this in your opinion? Do you see a chance of looking into it anytime soon?

Hey @LSobanski - I haven't reviewed this task in any detail yet. I can add this to our current sprint and take a look in the next couple of weeks. Does that work?

That would be great. There is no real rush, I've just been reviewing blocked tasks that have been silent for a while.

That would be great. There is no real rush, I've just been reviewing blocked tasks that have been silent for a while.

As part of our current sprint, we have started working on this. After following some steps detailed further up in this thread, I was able to log into db2083 and started exploring the tables under sys (ssh mwmaint2002.codfw.wmnet, sql wikidatawiki --host db2083, use sys;). That being said, I think I would need some guidance to understand which tables needs review.

we want your ok to expose low level internal mw database performance metrics to mw deployers for debugging purposes, through the existing mediawiki admin database account. Think of logs, but real time and database layer. Metrics will be accessed, for now, through the SQL interface only. There is a lot of metrics involved (literally billions of them) but it includes file, thread, user, transaction, query and query digest, and indexes individual metrics and counters (aggregations). Currently that access is reserved for mysql root users only.

@jcrespo previously suggested to toy with "some starts/query digest/index stats" to understand and evaluate the metrics exposed from a privacy standpoint. Is there any documentation available on wikitech or elsewhere that I could make use of, instead of going head first and looking into every table :) ?

LSobanski moved this task from Blocked to Pending comment on the DBA board.
LSobanski added a subscriber: JFishback_WMF.

@sguebo_WMF thanks a lot for starting to take a look into this.
sys schema has lots of view (this is the documentation about them: https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html) to be honest, I am not fully sure which ones could leak private information.

If this is helpful in trying to identify what can be potentially private, this is an output of one row per table, in case something rings a bell and you want to explore the table further:

host_summary
+--------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| host         | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users |
+--------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| 10.192.0.113 |   34848092 | 3.36 h            | 347.46 us             |     1092621 |   211236 | 43.48 s         |                   0 |           1064561 |            1 |
+--------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
host_summary_by_file_io
+------------+------------+------------+
| host       | ios        | io_latency |
+------------+------------+------------+
| background | 8828173834 | 3.99 d     |
+------------+------------+------------+
host_summary_by_file_io_type
+--------------+--------------------------------------+--------+---------------+-------------+
| host         | event_name                           | total  | total_latency | max_latency |
+--------------+--------------------------------------+--------+---------------+-------------+
| 10.192.0.113 | wait/io/file/innodb/innodb_data_file | 211002 | 43.47 s       | 43.55 ms    |
+--------------+--------------------------------------+--------+---------------+-------------+
host_summary_by_stages
host_summary_by_statement_latency
+------------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host       | total      | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| background | 2382240462 | 1.11 w        | 4.85 h      | 1.29 d       |         0 |    1570489550 |             0 |       4169 |
+------------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
host_summary_by_statement_type
+--------------+-----------+----------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host         | statement | total    | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+--------------+-----------+----------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| 10.192.0.113 | select    | 30924098 | 3.31 h        | 17.73 s     | 31.20 m      |  60393898 |     614363763 |             0 |    1037491 |
+--------------+-----------+----------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
innodb_buffer_stats_by_schema
+---------------+-----------+-----------+---------+--------------+-----------+-------------+
| object_schema | allocated | data      | pages   | pages_hashed | pages_old | rows_cached |
+---------------+-----------+-----------+---------+--------------+-----------+-------------+
| wikidatawiki  | 57.18 GiB | 88.27 GiB | 7495291 |      7495291 |   7495291 |    21118151 |
+---------------+-----------+-----------+---------+--------------+-----------+-------------+
innodb_buffer_stats_by_table
+---------------+----------------+-----------+-----------+---------+--------------+-----------+-------------+
| object_schema | object_name    | allocated | data      | pages   | pages_hashed | pages_old | rows_cached |
+---------------+----------------+-----------+-----------+---------+--------------+-----------+-------------+
| wikidatawiki  | wbt_item_terms | 20.59 GiB | 28.82 GiB | 2698613 |      2698613 |   2698613 |   356000675 |
+---------------+----------------+-----------+-----------+---------+--------------+-----------+-------------+
innodb_lock_waits
io_by_thread_by_latency
+-----------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
| user      | total      | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-----------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
| slave_sql | 4779213158 | 3.32 d        | 825.68 ns   | 73.44 us    | 1.17 s      |        28 |           NULL |
+-----------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
io_global_by_file_by_bytes
+-------------------+------------+------------+-----------+-------------+---------------+-----------+----------+-----------+
| file              | count_read | total_read | avg_read  | count_write | total_written | avg_write | total    | write_pct |
+-------------------+------------+------------+-----------+-------------+---------------+-----------+----------+-----------+
| @@datadir/ibdata1 |       1168 | 20.27 MiB  | 17.77 KiB |    74644501 | 6.17 TiB      | 88.79 KiB | 6.17 TiB |    100.00 |
+-------------------+------------+------------+-----------+-------------+---------------+-----------+----------+-----------+
io_global_by_file_by_latency
+-----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                  | total      | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+-----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| @@datadir/ib_logfile1 | 1224247647 | 1.10 d        |          2 | 613.90 us    |   612124894 | 2.82 h        |  612122751 | 23.66 h      |
+-----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
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 | 1616618962 | 1.20 d        | 0 ps        | 64.24 us    | 147.40 ms   |  450473236 | 3.36 TiB   | 8.00 KiB |   471838816 | 9.13 TiB      | 20.78 KiB   | 12.49 TiB       |
+-------------------------+------------+---------------+-------------+-------------+-------------+------------+------------+----------+-------------+---------------+-------------+-----------------+
io_global_by_wait_by_latency
+------------------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
| event_name             | total      | total_latency | avg_latency | max_latency | read_latency | write_latency | misc_latency | count_read | total_read | avg_read  | count_write | total_written | avg_written |
+------------------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
| innodb/innodb_log_file | 2447910584 | 2.21 d        | 77.86 us    | 394.45 ms   | 1.96 ms      | 5.64 h        | 1.97 d       |          6 | 66.50 KiB  | 11.08 KiB |  1223957608 | 4.37 TiB      | 3.83 KiB    |
+------------------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
latest_file_io
metrics
+-----------------+----------------+---------------+---------+
| Variable_name   | Variable_value | Type          | Enabled |
+-----------------+----------------+---------------+---------+
| aborted_clients | 37868          | Global Status | YES     |
+-----------------+----------------+---------------+---------+
processlist
+--------+---------+----------+-------+---------+-------+----------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
| thd_id | conn_id | user     | db    | command | state | time     | current_statement | statement_latency | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement | last_statement_latency | last_wait | last_wait_latency | source |
+--------+---------+----------+-------+---------+-------+----------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
|      1 |    NULL | sql/main | mysql | NULL    | NULL  | 13366316 | NULL              | NULL              | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
+--------+---------+----------+-------+---------+-------+----------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
ps_check_lost_instrumentation
+----------------------------------+----------------+
| variable_name                    | variable_value |
+----------------------------------+----------------+
| PERFORMANCE_SCHEMA_ACCOUNTS_LOST | 58300          |
+----------------------------------+----------------+
schema_auto_increment_columns
+--------------+---------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
| table_schema | table_name    | column_name | data_type | column_type      | is_signed | is_unsigned | max_value  | auto_increment | auto_increment_ratio |
+--------------+---------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
| wikidatawiki | recentchanges | rc_id       | int       | int(10) unsigned |         0 |           1 | 4294967295 |     1540954690 |               0.3588 |
+--------------+---------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
schema_index_statistics
+--------------+----------------+------------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name     | index_name             | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+----------------+------------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| wikidatawiki | wbt_item_terms | wbt_item_terms_item_id |  588914841420 | 16.66 w        |             0 | 0 ps           |            0 | 0 ps           |      4385266 | 0 ps           |
+--------------+----------------+------------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
schema_object_overview
+-----------+-------------+-------+
| db        | object_type | count |
+-----------+-------------+-------+
| heartbeat | BASE TABLE  |     1 |
+-----------+-------------+-------+
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                                                 |
+--------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+----------------------------------------------------------------+
| wikidatawiki | config     | cf_name_value        | cf_name,cf_value        |                          1 | PRIMARY             | cf_name                |                         0 |              1 | ALTER TABLE `wikidatawiki`.`config` DROP INDEX `cf_name_value` |
+--------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+----------------------------------------------------------------+
schema_table_statistics
+--------------+----------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
| table_schema | table_name     | total_latency | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read    | io_read_latency | io_write_requests | io_write   | io_write_latency | io_misc_requests | io_misc_latency |
+--------------+----------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
| wikidatawiki | wbt_item_terms | 16.70 w       | 588947836010 | 16.66 w       |     188037185 | 6.07 h         |            0 | 0 ps           |     15925176 | 13.87 m        |         60678271 | 462.94 GiB | 3.24 h          |          26569613 | 202.71 GiB | 6.21 m           |         40251464 | 21.97 m         |
+--------------+----------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
schema_table_statistics_with_buffer
+--------------+----------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+-------------------------+--------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| table_schema | table_name     | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read    | io_read_latency | io_write_requests | io_write   | io_write_latency | io_misc_requests | io_misc_latency | innodb_buffer_allocated | innodb_buffer_data | innodb_buffer_free | innodb_buffer_pages | innodb_buffer_pages_hashed | innodb_buffer_pages_old | innodb_buffer_rows_cached |
+--------------+----------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+-------------------------+--------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| wikidatawiki | wbt_item_terms | 588947868489 | 16.66 w       |     188037187 | 6.07 h         |            0 | 0 ps           |     15925176 | 13.87 m        |         60678274 | 462.94 GiB | 3.24 h          |          26569613 | 202.71 GiB | 6.21 m           |         40251464 | 21.97 m         | 20.61 GiB               | 28.85 GiB          | -8846661828 bytes  |             2701522 |                    2701522 |                 2701522 |                 356387265 |
+--------------+----------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+-------------------------+--------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
schema_tables_with_full_table_scans
+---------------+---------------------+-------------------+---------+
| object_schema | object_name         | rows_full_scanned | latency |
+---------------+---------------------+-------------------+---------+
| wikidatawiki  | wb_changes_dispatch |       42476230348 | 8.15 h  |
+---------------+---------------------+-------------------+---------+
schema_unused_indexes
+---------------+--------------+------------+
| object_schema | object_name  | index_name |
+---------------+--------------+------------+
| wikidatawiki  | abuse_filter | af_user    |
+---------------+--------------+------------+
session
+------------+-----------+----------------------+--------------------+---------+-------+------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+-------------------------------------------------------------------+------------------------+-----------+-------------------+--------+
| thd_id     | conn_id   | user                 | db                 | command | state | time | current_statement | statement_latency | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement                                                    | last_statement_latency | last_wait | last_wait_latency | source |
+------------+-----------+----------------------+--------------------+---------+-------+------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+-------------------------------------------------------------------+------------------------+-----------+-------------------+--------+
| 4736178362 | 614709641 | watchdog@10.64.0.122 | information_schema | Sleep   | NULL  | 2769 | NULL              | NULL              | 121.00 us    |             0 |         0 |             0 |          1 |               0 | YES       | SELECT `USER`, `TOTAL_CONNECTI ... UERIES` FROM `USER_STATISTICS` | 263.79 us              | NULL      | NULL              | NULL   |
+------------+-----------+----------------------+--------------------+---------+-------+------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+-------------------------------------------------------------------+------------------------+-----------+-------------------+--------+
statement_analysis
+-------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+------------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| 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           |
+-------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+------------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| SELECT `wbtl_id` , `wbtl_type_ ... ` = ? AND `wbxl_language` = ?  | wikidatawiki |           | 2581232585 |         0 |          0 | 14.01 w       | 628.43 ms   | 3.28 ms     | 2.45 d       | 1390280777 |             1 |  382287347978 |               148 |             0 |                 0 |          0 |               0 |           0 |                 0 | e6710becca6f731f082ec61199fa2f7d | 2021-06-14 11:32:22 | 2021-09-07 05:58:09 |
+-------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+------------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
statements_with_errors_or_warnings
+-------------------------------------------------------------------+--------------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| query                                                             | db           | exec_count | errors | error_pct | warnings | warning_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+--------------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| SELECT `page_id` , `page_len`  ...  AND `page_title` = ? LIMIT ?  | wikidatawiki | 1378186682 |   2654 |    0.0002 |        0 |      0.0000 | 2021-04-05 13:04:50 | 2021-09-07 05:58:10 | 8d06129ff0b06526429e6fa04681c977 |
+-------------------------------------------------------------------+--------------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
statements_with_full_table_scans
+-------------------------------------------------------------------+--------------------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db                 | exec_count | total_latency | no_index_used_count | no_good_index_used_count | no_index_used_pct | rows_sent | rows_examined | rows_sent_avg | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+--------------------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| SELECT `TRIGGER_CATALOG` , `TR ... HERE ( `TRIGGER_SCHEMA` = ? )  | information_schema |        168 | 975.48 ms     |                 168 |                        0 |               100 |         0 |             0 |             0 |                 0 | 2021-04-06 00:01:14 | 2021-04-29 00:02:22 | ce45a8c224e5ead251ef363c5820285c |
+-------------------------------------------------------------------+--------------------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
statements_with_runtimes_in_95th_percentile
+-------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db           | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| CREATE UNIQUE INDEX `page_name ... e_namespace` , `page_title` )  | wikidatawiki |           |          1 |         0 |          0 | 4.13 h        | 4.13 h      | 4.13 h      |         0 |             0 |             0 |                 0 | 2021-06-15 09:02:50 | 2021-06-15 09:02:50 | 4cedbd9c825e94867ad9896d37837c09 |
+-------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
statements_with_sorting
+-------------------------------------------------------------------+--------------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| query                                                             | db           | exec_count | total_latency | sort_merge_passes | avg_sort_merges | sorts_using_scans | sort_using_range | rows_sorted | avg_rows_sorted | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+--------------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| SELECT TIMESTAMPDIFF ( MICROSE ...  ? ORDER BY `ts` DESC LIMIT ?  | wikidatawiki | 4189252743 | 1.34 w        |                 0 |               0 |        4191660145 |                0 |  4191664303 |               1 | 2021-05-05 19:19:33 | 2021-09-07 05:58:16 | 752ffd44d554202978a146a0b714dd21 |
+-------------------------------------------------------------------+--------------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
statements_with_temp_tables
+-------------------------------------------------------------------+--------------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db           | exec_count | total_latency | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+--------------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM ( SELECT `rev_id ...  `rev_id` DESC LIMIT ?, ... )  | wikidatawiki |    3131785 | 3.94 h        |           3131764 |         3131747 |                        1 |                    100 | 2021-08-04 19:10:22 | 2021-09-07 05:58:15 | b70092435539822140e993e99673656b |
+-------------------------------------------------------------------+--------------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
sys_config
+------------------------------+-------+---------------------+--------+
| variable                     | value | set_time            | set_by |
+------------------------------+-------+---------------------+--------+
| diagnostics.allow_i_s_tables | OFF   | 2016-05-04 14:28:58 | NULL   |
+------------------------------+-------+---------------------+--------+
user_summary
+------------+------------+-------------------+-----------------------+-------------+------------+-----------------+---------------------+-------------------+--------------+
| user       | statements | statement_latency | statement_avg_latency | table_scans | file_ios   | file_io_latency | current_connections | total_connections | unique_hosts |
+------------+------------+-------------------+-----------------------+-------------+------------+-----------------+---------------------+-------------------+--------------+
| background | 2382290774 | 1.11 w            | 281.43 us             |        4170 | 8828246472 | 3.99 d          |                 217 |           4632669 |            0 |
+------------+------------+-------------------+-----------------------+-------------+------------+-----------------+---------------------+-------------------+--------------+
user_summary_by_file_io
+------------+------------+------------+
| user       | ios        | io_latency |
+------------+------------+------------+
| background | 8828246647 | 3.99 d     |
+------------+------------+------------+
user_summary_by_file_io_type
+------------+-------------------------------------+------------+---------+-------------+
| user       | event_name                          | total      | latency | max_latency |
+------------+-------------------------------------+------------+---------+-------------+
| background | wait/io/file/innodb/innodb_log_file | 2447927847 | 2.21 d  | 394.45 ms   |
+------------+-------------------------------------+------------+---------+-------------+
user_summary_by_stages
user_summary_by_statement_latency
+----------+-------------+---------------+-------------+--------------+-------------+---------------+---------------+------------+
| user     | total       | total_latency | max_latency | lock_latency | rows_sent   | rows_examined | rows_affected | full_scans |
+----------+-------------+---------------+-------------+--------------+-------------+---------------+---------------+------------+
| wikiuser | 59418889073 | 19.69 w       | 1.55 m      | 3.94 w       | 91317797617 | 1858826706972 |             0 | 4742873705 |
+----------+-------------+---------------+-------------+--------------+-------------+---------------+---------------+------------+
user_summary_by_statement_type
+------------+-----------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user       | statement | total      | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-----------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| background | insert    | 1251338437 | 3.37 d        | 661.09 ms   | 15.30 h      |         0 |             0 |             0 |          0 |
+------------+-----------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
version
+-------------+---------------------+
| sys_version | mysql_version       |
+-------------+---------------------+
| 1.5.0       | 10.4.18-MariaDB-log |
+-------------+---------------------+
wait_classes_global_by_avg_latency
+--------------+------------+---------------+-------------+-------------+-------------+
| event_class  | total      | total_latency | min_latency | avg_latency | max_latency |
+--------------+------------+---------------+-------------+-------------+-------------+
| wait/io/file | 9225895181 | 4.74 d        | 0 ps        | 44.35 us    | 1.61 s      |
+--------------+------------+---------------+-------------+-------------+-------------+
wait_classes_global_by_latency
+---------------+---------------+---------------+-------------+-------------+-------------+
| event_class   | total         | total_latency | min_latency | avg_latency | max_latency |
+---------------+---------------+---------------+-------------+-------------+-------------+
| wait/io/table | 1884235798773 | 2.62 w        | 44.33 ns    | 842.54 ns   | 1.64 s      |
+---------------+---------------+---------------+-------------+-------------+-------------+
waits_by_host_by_latency
+--------------+---------------------------+-----------+---------------+-------------+-------------+
| host         | event                     | total     | total_latency | avg_latency | max_latency |
+--------------+---------------------------+-----------+---------------+-------------+-------------+
| 10.192.0.113 | wait/io/table/sql/handler | 666100643 | 1.41 h        | 7.60 us     | 41.32 ms    |
+--------------+---------------------------+-----------+---------------+-------------+-------------+
waits_by_user_by_latency
+--------+---------------------------+---------+---------------+-------------+-------------+
| user   | event                     | total   | total_latency | avg_latency | max_latency |
+--------+---------------------------+---------+---------------+-------------+-------------+
| nagios | wait/io/table/sql/handler | 5209343 | 13.58 s       | 2.61 us     | 157.55 ms   |
+--------+---------------------------+---------+---------------+-------------+-------------+
waits_global_by_latency
+---------------------------+---------------+---------------+-------------+-------------+
| events                    | total         | total_latency | avg_latency | max_latency |
+---------------------------+---------------+---------------+-------------+-------------+
| wait/io/table/sql/handler | 1884236080404 | 2.62 w        | 10.63 us    | 1.64 s      |
+---------------------------+---------------+---------------+-------------+-------------+
x$host_summary
+--------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| host         | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users |
+--------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| 10.192.0.113 |   34848150 | 12108452250087000 |        347463272.8018 |     1092628 |   211239 |  43485425474920 |                   0 |           1064568 |            1 |
+--------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
x$host_summary_by_file_io
+------------+------------+--------------------+
| host       | ios        | io_latency         |
+------------+------------+--------------------+
| background | 8828247777 | 345130486810709886 |
+------------+------------+--------------------+
x$host_summary_by_file_io_type
+--------------+--------------------------------------+--------+----------------+-------------+
| host         | event_name                           | total  | total_latency  | max_latency |
+--------------+--------------------------------------+--------+----------------+-------------+
| 10.192.0.113 | wait/io/file/innodb/innodb_data_file | 211005 | 43476171645764 | 43550566488 |
+--------------+--------------------------------------+--------+----------------+-------------+
x$host_summary_by_stages
x$host_summary_by_statement_latency
+------------+------------+--------------------+-------------------+--------------------+-----------+---------------+---------------+------------+
| host       | total      | total_latency      | max_latency       | lock_latency       | rows_sent | rows_examined | rows_affected | full_scans |
+------------+------------+--------------------+-------------------+--------------------+-----------+---------------+---------------+------------+
| background | 2382292038 | 670451868331290000 | 17468509954824000 | 111577417783000000 |         0 |    1570520557 |             0 |       4170 |
+------------+------------+--------------------+-------------------+--------------------+-----------+---------------+---------------+------------+
x$host_summary_by_statement_type
+--------------+-----------+----------+-------------------+----------------+------------------+-----------+---------------+---------------+------------+
| host         | statement | total    | total_latency     | max_latency    | lock_latency     | rows_sent | rows_examined | rows_affected | full_scans |
+--------------+-----------+----------+-------------------+----------------+------------------+-----------+---------------+---------------+------------+
| 10.192.0.113 | select    | 30924134 | 11926176216421000 | 17728208218000 | 1871844436000000 |  60393931 |     614363805 |             0 |    1037498 |
+--------------+-----------+----------+-------------------+----------------+------------------+-----------+---------------+---------------+------------+
x$innodb_buffer_stats_by_schema
+---------------+-------------+-------------+---------+--------------+-----------+-------------+
| object_schema | allocated   | data        | pages   | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-------------+---------+--------------+-----------+-------------+
| wikidatawiki  | 61391495168 | 94717556400 | 7494076 |      7494076 |   7494076 |    20033674 |
+---------------+-------------+-------------+---------+--------------+-----------+-------------+
x$innodb_buffer_stats_by_table
+---------------+----------------+-------------+-------------+---------+--------------+-----------+-------------+
| object_schema | object_name    | allocated   | data        | pages   | pages_hashed | pages_old | rows_cached |
+---------------+----------------+-------------+-------------+---------+--------------+-----------+-------------+
| wikidatawiki  | wbt_item_terms | 22141165568 | 30991536759 | 2702779 |      2702779 |   2702779 |   356582485 |
+---------------+----------------+-------------+-------------+---------+--------------+-----------+-------------+
x$innodb_lock_waits
x$io_by_thread_by_latency
+-----------+------------+--------------------+-------------+---------------+---------------+-----------+----------------+
| user      | total      | total_latency      | min_latency | avg_latency   | max_latency   | thread_id | processlist_id |
+-----------+------------+--------------------+-------------+---------------+---------------+-----------+----------------+
| slave_sql | 4779263824 | 286506242758100794 |      825682 | 73445306.0000 | 1165830609372 |        28 |           NULL |
+-----------+------------+--------------------+-------------+---------------+---------------+-----------+----------------+
x$io_global_by_file_by_bytes
+----------------------+------------+------------+------------+-------------+---------------+------------+---------------+-----------+
| file                 | count_read | total_read | avg_read   | count_write | total_written | avg_write  | total         | write_pct |
+----------------------+------------+------------+------------+-------------+---------------+------------+---------------+-----------+
| /srv/sqldata/ibdata1 |       1168 |   21250048 | 18193.5342 |    74645890 | 6787099328512 | 90923.9521 | 6787120578560 |    100.00 |
+----------------------+------------+------------+------------+-------------+---------------+------------+---------------+-----------+
x$io_global_by_file_by_latency
+--------------------------+------------+-------------------+------------+--------------+-------------+-------------------+------------+-------------------+
| file                     | total      | total_latency     | count_read | read_latency | count_write | write_latency     | count_misc | misc_latency      |
+--------------------------+------------+-------------------+------------+--------------+-------------+-------------------+------------+-------------------+
| /srv/sqldata/ib_logfile1 | 1224298777 | 95339685428925080 |          2 |    613899286 |   612150459 | 10153273329871100 |  612148316 | 85186411485154694 |
+--------------------------+------------+-------------------+------------+--------------+-------------+-------------------+------------+-------------------+
x$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 | 1616650855 | 103934141967023940 |           0 |    64289654 | 147395255722 |  450489038 | 3690419462144 | 8192.0294 |   471844072 | 10042340892672 |  21283.1770 |  13732760354816 |
+-------------------------+------------+--------------------+-------------+-------------+--------------+------------+---------------+-----------+-------------+----------------+-------------+-----------------+
x$io_global_by_wait_by_latency
+------------------------+------------+--------------------+-------------+--------------+--------------+-------------------+--------------------+------------+------------+------------+-------------+---------------+-------------+
| event_name             | total      | total_latency      | avg_latency | max_latency  | read_latency | write_latency     | misc_latency       | count_read | total_read | avg_read   | count_write | total_written | avg_written |
+------------------------+------------+--------------------+-------------+--------------+--------------+-------------------+--------------------+------------+------------+------------+-------------+---------------+-------------+
| innodb/innodb_log_file | 2447961858 | 190624111610588370 |    77870364 | 394448349438 |   1960452780 | 20312281285578282 | 170311828364557308 |          6 |      68096 | 11349.3333 |  1223983245 | 4806358520832 |   3926.8173 |
+------------------------+------------+--------------------+-------------+--------------+--------------+-------------------+--------------------+------------+------------+------------+-------------+---------------+-------------+
x$latest_file_io
x$processlist
+--------+---------+----------+-------+---------+-------+----------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
| thd_id | conn_id | user     | db    | command | state | time     | current_statement | statement_latency | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement | last_statement_latency | last_wait | last_wait_latency | source |
+--------+---------+----------+-------+---------+-------+----------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
|      1 |    NULL | sql/main | mysql | NULL    | NULL  | 13366675 | NULL              |              NULL |         NULL |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           |                   NULL | NULL      | NULL              | NULL   |
+--------+---------+----------+-------+---------+-------+----------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
x$ps_digest_95th_percentile_by_avg_us
+--------+------------+
| avg_us | percentile |
+--------+------------+
|  34323 |     0.9501 |
+--------+------------+
x$ps_digest_avg_latency_distribution
+-----+--------+
| cnt | avg_us |
+-----+--------+
|  16 |     10 |
+-----+--------+
x$ps_schema_table_statistics_io
+--------------+------------+------------+--------------------------+----------------+-------------+---------------------------+-----------------+------------+----------------+
| table_schema | table_name | count_read | sum_number_of_bytes_read | sum_timer_read | count_write | sum_number_of_bytes_write | sum_timer_write | count_misc | sum_timer_misc |
+--------------+------------+------------+--------------------------+----------------+-------------+---------------------------+-----------------+------------+----------------+
| charsets     | Index      |          1 |                    23500 |      305690528 |           0 |                         0 |               0 |          2 |       44574816 |
+--------------+------------+------------+--------------------------+----------------+-------------+---------------------------+-----------------+------------+----------------+
x$schema_flattened_keys
+--------------+------------+------------+------------+----------------+---------------+
| table_schema | table_name | index_name | non_unique | subpart_exists | index_columns |
+--------------+------------+------------+------------+----------------+---------------+
| heartbeat    | heartbeat  | PRIMARY    |          0 |              0 | server_id     |
+--------------+------------+------------+------------+----------------+---------------+
x$schema_index_statistics
+--------------+----------------+------------------------+---------------+----------------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name     | index_name             | rows_selected | select_latency       | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+----------------+------------------------+---------------+----------------------+---------------+----------------+--------------+----------------+--------------+----------------+
| wikidatawiki | wbt_item_terms | wbt_item_terms_item_id |  588946405484 | 10076703940876603888 |             0 |              0 |            0 |              0 |      4385285 |              0 |
+--------------+----------------+------------------------+---------------+----------------------+---------------+----------------+--------------+----------------+--------------+----------------+
x$schema_table_statistics
+--------------+----------------+----------------------+--------------+----------------------+---------------+-------------------+--------------+----------------+--------------+-----------------+------------------+--------------+-------------------+-------------------+--------------+------------------+------------------+------------------+
| table_schema | table_name     | total_latency        | rows_fetched | fetch_latency        | rows_inserted | insert_latency    | rows_updated | update_latency | rows_deleted | delete_latency  | io_read_requests | io_read      | io_read_latency   | io_write_requests | io_write     | io_write_latency | io_misc_requests | io_misc_latency  |
+--------------+----------------+----------------------+--------------+----------------------+---------------+-------------------+--------------+----------------+--------------+-----------------+------------------+--------------+-------------------+-------------------+--------------+------------------+------------------+------------------+
| wikidatawiki | wbt_item_terms | 10101487365624185960 | 588979311419 | 10078812769026572142 |     188038751 | 21842370465785298 |            0 |              0 |     15925210 | 832226131828520 |         60681622 | 497104939065 | 11676818609986816 |          26569820 | 217659965440 |  372601858848076 |         40251901 | 1318471917827350 |
+--------------+----------------+----------------------+--------------+----------------------+---------------+-------------------+--------------+----------------+--------------+-----------------+------------------+--------------+-------------------+-------------------+--------------+------------------+------------------+------------------+
x$schema_table_statistics_with_buffer
+--------------+----------------+--------------+----------------------+---------------+-------------------+--------------+----------------+--------------+-----------------+------------------+--------------+-------------------+-------------------+--------------+------------------+------------------+------------------+-------------------------+--------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| table_schema | table_name     | rows_fetched | fetch_latency        | rows_inserted | insert_latency    | rows_updated | update_latency | rows_deleted | delete_latency  | io_read_requests | io_read      | io_read_latency   | io_write_requests | io_write     | io_write_latency | io_misc_requests | io_misc_latency  | innodb_buffer_allocated | innodb_buffer_data | innodb_buffer_free | innodb_buffer_pages | innodb_buffer_pages_hashed | innodb_buffer_pages_old | innodb_buffer_rows_cached |
+--------------+----------------+--------------+----------------------+---------------+-------------------+--------------+----------------+--------------+-----------------+------------------+--------------+-------------------+-------------------+--------------+------------------+------------------+------------------+-------------------------+--------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| wikidatawiki | wbt_item_terms | 588979338632 | 10078812930563136760 |     188038754 | 21842370637965592 |            0 |              0 |     15925210 | 832226131828520 |         60681626 | 497104971833 | 11676819361861368 |          26569820 | 217659965440 |  372601858848076 |         40251901 | 1318471917827350 |             22111100928 |        30950957581 |        -8839856653 |             2699109 |                    2699109 |                 2699109 |                 356137881 |
+--------------+----------------+--------------+----------------------+---------------+-------------------+--------------+----------------+--------------+-----------------+------------------+--------------+-------------------+-------------------+--------------+------------------+------------------+------------------+-------------------------+--------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
x$schema_tables_with_full_table_scans
+---------------+---------------------+-------------------+-------------------+
| object_schema | object_name         | rows_full_scanned | latency           |
+---------------+---------------------+-------------------+-------------------+
| wikidatawiki  | wb_changes_dispatch |       42477749989 | 29326749949732664 |
+---------------+---------------------+-------------------+-------------------+
x$session
+------------+-----------+----------------------+--------------------+---------+-------+------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+-----------+-------------------+--------+
| thd_id     | conn_id   | user                 | db                 | command | state | time | current_statement | statement_latency | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement                                                                                                                                                                                                                                                                                                                                                                                                                                        | last_statement_latency | last_wait | last_wait_latency | source |
+------------+-----------+----------------------+--------------------+---------+-------+------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+-----------+-------------------+--------+
| 4736178362 | 614709641 | watchdog@10.64.0.122 | information_schema | Sleep   | NULL  | 3135 | NULL              |              NULL |    121000000 |             0 |         0 |             0 |          1 |               0 | YES       | SELECT `USER`, `TOTAL_CONNECTIONS`, `CONCURRENT_CONNECTIONS`, `CONNECTED_TIME`, `BUSY_TIME`, `CPU_TIME`, `BYTES_RECEIVED`, `BYTES_SENT`, `BINLOG_BYTES_WRITTEN`, `ROWS_READ`, `ROWS_SENT`, `ROWS_DELETED`, `ROWS_INSERTED`, `ROWS_UPDATED`, `SELECT_COMMANDS`, `UPDATE_COMMANDS`, `OTHER_COMMANDS`, `COMMIT_TRANSACTIONS`, `ROLLBACK_TRANSACTIONS`, `DENIED_CONNECTIONS`, `LOST_CONNECTIONS`, `ACCESS_DENIED`, `EMPTY_QUERIES` FROM `USER_STATISTICS` |              263789000 | NULL      | NULL              | NULL   |
+------------+-----------+----------------------+--------------------+---------+-------+------+-------------------+-------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+-----------+-------------------+--------+
x$statement_analysis
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------------+--------------+-------------+--------------------+------------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| 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           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------------+--------------+-------------+--------------------+------------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| SELECT `wbtl_id` , `wbtl_type_id` , `wbxl_language` , `wbx_text` , `wbit_item_id` FROM `wbt_term_in_lang` JOIN `wbt_text_in_lang` ON ( ( `wbtl_text_in_lang_id` = `wbxl_id` ) ) JOIN `wbt_text` ON ( ( `wbxl_text_id` = `wbx_id` ) ) JOIN `wbt_item_terms` ON ( ( `wbit_term_in_lang_id` = `wbtl_id` ) ) WHERE `wbit_item_id` = ? AND `wbtl_type_id` = ? AND `wbxl_language` = ?  | wikidatawiki |           | 2581390369 |         0 |          0 | 8473664548645985000 | 628425984000 |  3282597000 | 212074572782000000 | 1390362225 |             1 |  382310359162 |               148 |             0 |                 0 |          0 |               0 |           0 |                 0 | e6710becca6f731f082ec61199fa2f7d | 2021-06-14 11:32:22 | 2021-09-07 06:04:15 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+---------------------+--------------+-------------+--------------------+------------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
x$statements_with_errors_or_warnings
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| query                                                                                                                                                                                              | db           | exec_count | errors | error_pct | warnings | warning_pct | first_seen          | last_seen           | digest                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| SELECT `page_id` , `page_len` , `page_is_redirect` , `page_latest` , `page_restrictions` , `page_content_model` , `page_lang` FROM `page` WHERE `page_namespace` = ? AND `page_title` = ? LIMIT ?  | wikidatawiki | 1378249222 |   2654 |    0.0002 |        0 |      0.0000 | 2021-04-05 13:04:50 | 2021-09-07 06:04:16 | 8d06129ff0b06526429e6fa04681c977 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
x$statements_with_full_table_scans
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------------------+---------------------+--------------------------+-------------------+------------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                                                                                                                                         | db           | exec_count | total_latency      | no_index_used_count | no_good_index_used_count | no_index_used_pct | rows_sent  | rows_examined | rows_sent_avg | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------------------+---------------------+--------------------------+-------------------+------------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| SELECT TIMESTAMPDIFF ( MICROSECOND , `ts` , UTC_TIMESTAMP (?) ) AS `us_ago` FROM `heartbeat` . `heartbeat` WHERE `shard` = ? AND `datacenter` = ? ORDER BY `ts` DESC LIMIT ?  | wikidatawiki | 4189487330 | 812907635242744000 |          4191895501 |                        0 |               100 | 4190171875 |    8482649402 |             1 |                 2 | 2021-05-05 19:19:33 | 2021-09-07 06:04:16 | 752ffd44d554202978a146a0b714dd21 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------------------+---------------------+--------------------------+-------------------+------------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
x$statements_with_runtimes_in_95th_percentile
+------------------------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+-------------------+-------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                                              | db           | full_scan | exec_count | err_count | warn_count | total_latency     | max_latency       | avg_latency       | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | first_seen          | last_seen           | digest                           |
+------------------------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+-------------------+-------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| CREATE UNIQUE INDEX `page_name_title` ON PAGE ( `page_namespace` , `page_title` )  | wikidatawiki |           |          1 |         0 |          0 | 14855162304328000 | 14855162304328000 | 14855162304328000 |         0 |             0 |             0 |                 0 | 2021-06-15 09:02:50 | 2021-06-15 09:02:50 | 4cedbd9c825e94867ad9896d37837c09 |
+------------------------------------------------------------------------------------+--------------+-----------+------------+-----------+------------+-------------------+-------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
x$statements_with_sorting
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| query                                                                                                                                                                         | db           | exec_count | total_latency      | sort_merge_passes | avg_sort_merges | sorts_using_scans | sort_using_range | rows_sorted | avg_rows_sorted | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| SELECT TIMESTAMPDIFF ( MICROSECOND , `ts` , UTC_TIMESTAMP (?) ) AS `us_ago` FROM `heartbeat` . `heartbeat` WHERE `shard` = ? AND `datacenter` = ? ORDER BY `ts` DESC LIMIT ?  | wikidatawiki | 4189491406 | 812908372739530000 |                 0 |               0 |        4191898880 |                0 |  4191903041 |               1 | 2021-05-05 19:19:33 | 2021-09-07 06:04:22 | 752ffd44d554202978a146a0b714dd21 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+--------------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
x$statements_with_temp_tables
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+-------------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | db           | exec_count | total_latency     | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen          | last_seen           | digest                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+-------------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM ( SELECT `rev_id` , `rev_page` , `rev_timestamp` , `rev_minor_edit` , `rev_deleted` , `rev_len` , `rev_parent_id` , `rev_sha1` , `comment_rev_comment` . `comment_text` AS `rev_comment_text` , `comment_rev_comment` . `comment_data` AS `rev_comment_data` , `comment_rev_comment` . `comment_id` AS `rev_comment_cid` , `actor_rev_user` . `actor_user` AS `rev_user` , `actor_rev_user` . `actor_name` AS `rev_user_text` , `temp_rev_user` . `revactor_actor` AS `rev_actor` FROM `revision` JOIN `revision_comment_temp` `temp_rev_comment` ON ( ( `temp_rev_comment` . `revcomment_rev` = `rev_id` ) ) JOIN `comment` `comment_rev_comment` ON ( ( `comment_rev_comment` . `comment_id` = `temp_rev_comment` . `revcomment_comment_id` ) ) JOIN `revision_actor_temp` `temp_rev_user` ON ( ( `temp_rev_user` . `revactor_rev` = `rev_id` ) ) JOIN `actor` `actor_rev_user` ON ( ( `actor_rev_user` . `actor_id` = `temp_rev_user` . `revactor_actor` ) ) WHERE `rev_page` = ? ORDER BY `rev_timestamp` DESC , `rev_id` DESC LIMIT ?, ... )  | wikidatawiki |    3132142 | 14181847022689000 |           3132121 |         3132104 |                        1 |                    100 | 2021-08-04 19:10:22 | 2021-09-07 06:04:19 | b70092435539822140e993e99673656b |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+------------+-------------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
x$user_summary
+------------+------------+--------------------+-----------------------+-------------+------------+--------------------+---------------------+-------------------+--------------+
| user       | statements | statement_latency  | statement_avg_latency | table_scans | file_ios   | file_io_latency    | current_connections | total_connections | unique_hosts |
+------------+------------+--------------------+-----------------------+-------------+------------+--------------------+---------------------+-------------------+--------------+
| background | 2382345005 | 670469063528592000 |        281432396.2824 |        4170 | 8828320833 | 345153868112613560 |                 219 |           4632895 |            0 |
+------------+------------+--------------------+-----------------------+-------------+------------+--------------------+---------------------+-------------------+--------------+
x$user_summary_by_file_io
+------------+------------+--------------------+
| user       | ios        | io_latency         |
+------------+------------+--------------------+
| background | 8828320994 | 345153877245473296 |
+------------+------------+--------------------+
x$user_summary_by_file_io_type
+------------+-------------------------------------+------------+--------------------+--------------+
| user       | event_name                          | total      | latency            | max_latency  |
+------------+-------------------------------------+------------+--------------------+--------------+
| background | wait/io/file/innodb/innodb_log_file | 2447983545 | 190631717588664352 | 394448349438 |
+------------+-------------------------------------+------------+--------------------+--------------+
x$user_summary_by_stages
x$user_summary_by_statement_latency
+----------+-------------+----------------------+----------------+---------------------+-------------+---------------+---------------+------------+
| user     | total       | total_latency        | max_latency    | lock_latency        | rows_sent   | rows_examined | rows_affected | full_scans |
+----------+-------------+----------------------+----------------+---------------------+-------------+---------------+---------------+------------+
| wikiuser | 59422192975 | 11909536870766784384 | 93218201458000 | 2385663020671000000 | 91322217657 | 1858924153789 |             0 | 4743139217 |
+----------+-------------+----------------------+----------------+---------------------+-------------+---------------+---------------+------------+
x$user_summary_by_statement_type
+------------+-----------+------------+--------------------+--------------+-------------------+-----------+---------------+---------------+------------+
| user       | statement | total      | total_latency      | max_latency  | lock_latency      | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-----------+------------+--------------------+--------------+-------------------+-----------+---------------+---------------+------------+
| background | insert    | 1251366101 | 291343617180337000 | 661093086000 | 55082762278000000 |         0 |             0 |             0 |          0 |
+------------+-----------+------------+--------------------+--------------+-------------------+-----------+---------------+---------------+------------+
x$wait_classes_global_by_avg_latency
+--------------+------------+--------------------+-------------+---------------+---------------+
| event_class  | total      | total_latency      | min_latency | avg_latency   | max_latency   |
+--------------+------------+--------------------+-------------+---------------+---------------+
| wait/io/file | 9225985961 | 409309640481671358 |           0 | 44364867.0410 | 1609620821094 |
+--------------+------------+--------------------+-------------+---------------+---------------+
x$wait_classes_global_by_latency
+---------------+---------------+---------------------+-------------+-------------+---------------+
| event_class   | total         | total_latency       | min_latency | avg_latency | max_latency   |
+---------------+---------------+---------------------+-------------+-------------+---------------+
| wait/io/table | 1884333560087 | 1588447596111041936 |       44330 | 842975.8031 | 1640202289440 |
+---------------+---------------+---------------------+-------------+-------------+---------------+
x$waits_by_host_by_latency
+--------------+---------------------------+-----------+------------------+-------------+-------------+
| host         | event                     | total     | total_latency    | avg_latency | max_latency |
+--------------+---------------------------+-----------+------------------+-------------+-------------+
| 10.192.0.113 | wait/io/table/sql/handler | 666101304 | 5063751130041032 |     7601880 | 41315661530 |
+--------------+---------------------------+-----------+------------------+-------------+-------------+
x$waits_by_user_by_latency
+--------+---------------------------+---------+----------------+-------------+--------------+
| user   | event                     | total   | total_latency  | avg_latency | max_latency  |
+--------+---------------------------+---------+----------------+-------------+--------------+
| nagios | wait/io/table/sql/handler | 5209385 | 13578467868966 |     2606318 | 157548807988 |
+--------+---------------------------+---------+----------------+-------------+--------------+
x$waits_global_by_latency
+---------------------------+---------------+---------------------+-------------+---------------+
| events                    | total         | total_latency       | avg_latency | max_latency   |
+---------------------------+---------------+---------------------+-------------+---------------+
| wait/io/table/sql/handler | 1884333953747 | 1588451839446362732 |    10632336 | 1640202289440 |
+---------------------------+---------------+---------------------+-------------+---------------+

I believe if any, we should inspect the following ones further (although everything is redacted):

  • statement_analysis
  • statements_with_errors_or_warnings
  • statements_with_sorting
  • statements_with_temp_tables
  • x$statement_analysis
  • x$statements_with_errors_or_warnings
  • x$statements_with_full_table_scans
  • x$statements_with_sorting
  • x$statements_with_temp_tables

We also need to keep in mind that everyone who would potentially be accessing these tables will have a NDA.

@sguebo_WMF thanks a lot for starting to take a look into this.
sys schema has lots of view (this is the documentation about them: https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html) to be honest, I am not fully sure which ones could leak private information.

If this is helpful in trying to identify what can be potentially private, this is an output of one row per table, in case something rings a bell and you want to explore the table further:

Thanks for pasting the output of sys’ tables, @Marostegui. That’s really helpful. As I am wrapping up my analysis, I’d like to ask a quick question just to make sure that I understand things correctly. The host_summary table seems to contain IP addresses in the 10.192.** range. Judging by the range, my understanding is that these IPs are from Wikimedia load balancer and not from the end user. Is my understanding accurate?

Yes, those are our application servers private IP ips on our internal (private) range. They are ok to be shown.

Hey @Marostegui,

Thank you for your answer. I have now wrapped the privacy review and would like to share the conclusion. The analysis focused on the sys database of db2083 server, which as of writing, contains 88 tables, encompassing performance statistics.

Of the 88 tables reviewed, only a few revealed some data that could be concerning.

  • host_summary seems to contain IP addresses in the 10.192.** range. However, those are not end-user’s PII. Instead, they are IPs from Wikimedia’s private application servers. As such they pose no privacy risk for end users.
  • The statement_analysis table could have been concerning if its SQL query column could be associated with individual user data such as their IP address, or user ID. But this is not the case. Similarly to query or statements_with_errors_or_warnings tables, the columns of statement_analysis table contain data which is either aggregated or insufficient to pinpoint single users.
  • The first_seen and last_seen columns present in certain tables such as x$statements_with_errors_or_warnings could have posed some problems. As per Mysql’s documentation, both fields give some indication about the time at which the SQL statement in query was run. However, the risk is sufficiently mitigated by the fact that the query statement does not indicate the page_id that was accessed using the query.

Taking in consideration the above, as well as all the other tables of sys that were reviewed, it appears that many mitigations are already in place when server statistics are gathered: aggregation, redaction of SQL parameters, omission of user ID and other fingerprint. Also, sys being restricted by access control to a selective list of NDA’ed and trusted users adds another layer of precaution privacy-wise. In the unlikely event that a deployer decides to use the data of sys to harm users, the information of that database would be of low impact on the end-user’s privacy.

In light of the above, the privacy risk of this change was categorized as LOW. That being said, please allow me to clarify that it is not the Security-Team place to ‘ok’ or ‘approve’ such changes. Per our risk management policy, we assess risks, explore mitigations, and provide some risk rating to the relevant stakeholder. In this instance, since it was rated as LOW, it means that the privacy risk can be automatically accepted by the Foundation’s team behind this change (SRE I guess?). Kindly let me know if you have further questions or concerns.

I think for now we should deploy this only for wikiadmin user.

I have deployed this on x2.

It is a bit of a mess to deploy this due to the messy grants we have for 'wikiadmin' already, so probably going to deploy this to 10.64 and 10.192 for now.

Sent to ops@ as all the deployers (who are typically those with access to wikiadmin user) are on that list