Page MenuHomePhabricator
Create Task
Maniphest T326211

Install MariaDB 11 on db1176
Closed, ResolvedPublic
Actions

Description

db1176 is meant to be placed in s6 eqiad. But let's try mariadb 11 first on that host and move it to s1.

Details

ProjectBranchLines +/-Subject
operations/puppetproduction+1 -0db1176: Install MariaDB 11
operations/puppetproduction+1 -0db1134: Disable notifications
operations/puppetproduction+2 -6mariadb: Productionize db1176 into s1
Customize query in gerrit

Related Objects
Search...

Event Timeline

• Marostegui created this task.Jan 4 2023, 11:21 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJan 4 2023, 11:21 AM
• Marostegui triaged this task as Medium priority.Jan 4 2023, 11:21 AM
• Marostegui moved this task from Triage to In progress on the DBA board.
• Marostegui added a parent task: T326116: Package and test MariaDB 11.
• Marostegui updated the task description. (Show Details)Jan 4 2023, 11:29 AM
gerritbot added a comment.Jan 5 2023, 6:41 AM

Change 875458 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] db1176: Productionize db1176

https://gerrit.wikimedia.org/r/875458

gerritbot added a project: Patch-For-Review.Jan 5 2023, 6:41 AM
Stashbot added a comment.Jan 5 2023, 6:41 AM

Mentioned in SAL (#wikimedia-operations) [2023-01-05T06:41:54Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1134 to clone db1176 T326211', diff saved to https://phabricator.wikimedia.org/P42833 and previous config saved to /var/cache/conftool/dbconfig/20230105-064153-marostegui.json

gerritbot added a comment.Jan 5 2023, 6:44 AM

Change 875459 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] db1134: Disable notifications

https://gerrit.wikimedia.org/r/875459

gerritbot added a comment.Jan 5 2023, 6:45 AM

Change 875459 merged by Marostegui:

[operations/puppet@production] db1134: Disable notifications

https://gerrit.wikimedia.org/r/875459

gerritbot added a comment.Jan 5 2023, 7:08 AM

Change 875458 merged by Marostegui:

[operations/puppet@production] mariadb: Productionize db1176 into s1

https://gerrit.wikimedia.org/r/875458

Maintenance_bot removed a project: Patch-For-Review.Jan 5 2023, 7:30 AM
gerritbot added a comment.Jan 5 2023, 9:28 AM

Change 875808 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] db1176: Install MariaDB 11

https://gerrit.wikimedia.org/r/875808

gerritbot added a project: Patch-For-Review.Jan 5 2023, 9:28 AM

Change 875808 merged by Marostegui:

[operations/puppet@production] db1176: Install MariaDB 11

https://gerrit.wikimedia.org/r/875808

• Marostegui added a comment.Jan 5 2023, 9:34 AM

The first start looks good (errors are expected but nothing to worry about at the moment)

Jan 05 09:30:02 db1176 mysqld[187808]: /opt/wmf-mariadb110/bin/mysqld: Deprecated program name. It will be removed in a future release, use '/opt/wmf-mariadb110/bin/mariadbd' instead
Jan 05 09:30:02 db1176 mysqld[187808]: 2023-01-05  9:30:02 0 [Note] /opt/wmf-mariadb110/bin/mysqld (server 11.0.0-MariaDB-log) starting as process 187808 ...
Jan 05 09:30:02 db1176 mysqld[187808]: 2023-01-05  9:30:02 0 [Warning] No argument was provided to --log-bin and neither --log-basename or --log-bin-index where used;  This may cause repliction to break when this server acts as a master and has its hostname changed! Please use '--log-basename=db1176' or '--log-bin=db1176-bin' to avoid this problem.
Jan 05 09:30:02 db1176 mysqld[187808]: 2023-01-05  9:30:02 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
Jan 05 09:30:02 db1176 mysqld[187808]: 2023-01-05  9:30:02 0 [Note] InnoDB: Number of transaction pools: 1
Jan 05 09:30:02 db1176 mysqld[187808]: 2023-01-05  9:30:02 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Jan 05 09:30:02 db1176 mysqld[187808]: 2023-01-05  9:30:02 0 [Note] InnoDB: Using Linux native AIO
Jan 05 09:30:02 db1176 mysqld[187808]: 2023-01-05  9:30:02 0 [Note] InnoDB: Initializing buffer pool, total size = 377.000GiB, chunk size = 5.891GiB
Jan 05 09:30:04 db1176 mysqld[187808]: 2023-01-05  9:30:04 0 [Note] InnoDB: Completed initialization of buffer pool
Jan 05 09:30:04 db1176 mysqld[187808]: 2023-01-05  9:30:04 0 [Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)
Jan 05 09:30:04 db1176 mysqld[187808]: 2023-01-05  9:30:04 0 [Note] InnoDB: Upgrading redo log: 2.000GiB; LSN=84798503018363
Jan 05 09:30:04 db1176 mysqld[187808]: 2023-01-05  9:30:04 0 [Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)
Jan 05 09:30:05 db1176 mysqld[187808]: 2023-01-05  9:30:05 0 [Note] InnoDB: Upgrading the change buffer
Jan 05 09:31:07 db1176 mysqld[187808]: 2023-01-05  9:31:07 0 [Note] InnoDB: Upgraded the change buffer: 0 tablespaces, 0 pages
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Reinitializing innodb_undo_tablespaces= 3 from 0
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Data file .//undo001 did not exist: new to be created
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Setting file .//undo001 size to 10.000MiB
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Database physically writes the file full: wait...
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Data file .//undo002 did not exist: new to be created
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Setting file .//undo002 size to 10.000MiB
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Database physically writes the file full: wait...
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Data file .//undo003 did not exist: new to be created
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Setting file .//undo003 size to 10.000MiB
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Database physically writes the file full: wait...
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: log sequence number 84798503018363; transaction id 211863277513
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] InnoDB: Loading buffer pool(s) from /srv/sqldata/ib_buffer_pool
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [ERROR] WSREP: plugin-wsrep-provider can't be enabled if wsrep_on==OFF or wsrep_provider is unset or set to 'none'
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [ERROR] mysqld: plugin-wsrep-provider can't be enabled if wsrep_on==OFF or wsrep_provider is unset or set to 'none'
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [ERROR] Plugin 'wsrep_provider' init function returned error.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Warning] 'innodb-change-buffering' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] Server socket created on IP: '0.0.0.0'.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] Server socket created on IP: '::'.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] Server socket created on IP: '0.0.0.0'.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] Server socket created on IP: '::'.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type varchar(, found type char(141).
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MariaDB server acts as a replica and has its hostname changed. Please use '--log-basename=#' or '--relay-log=db1176-relay-bin' to avoid this problem.
Jan 05 09:31:08 db1176 mysqld[187808]: 2023-01-05  9:31:08 0 [Note] /opt/wmf-mariadb110/bin/mysqld: ready for connections.
Jan 05 09:31:08 db1176 mysqld[187808]: Version: '11.0.0-MariaDB-log'  socket: '/run/mysqld/mysqld.sock'  port: 3306  MariaDB Server
Jan 05 09:31:08 db1176 systemd[1]: Started mariadb database server.
░░ Subject: A start job for unit mariadb.service has finished successfully
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A start job for unit mariadb.service has finished successfully.
░░
░░ The job identifier is 174540.
Jan 05 09:31:12 db1176 mysqld[187808]: 2023-01-05  9:31:12 6 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
Jan 05 09:31:12 db1176 mysqld[187808]: 2023-01-05  9:31:12 6 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
Jan 05 09:31:18 db1176 mysqld[187808]: 2023-01-05  9:31:18 5 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
Jan 05 09:31:18 db1176 mysqld[187808]: 2023-01-05  9:31:18 5 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
Jan 05 09:31:20 db1176 mysqld[187808]: 2023-01-05  9:31:20 13 [ERROR] Incorrect definition of table mysql.proc: expected column 'definer' at position 11 to have type varchar(, found type char(141).
Jan 05 09:31:21 db1176 mysqld[187808]: 2023-01-05  9:31:21 15 [Note] Event Scheduler: scheduler thread started with id 15
Jan 05 09:31:40 db1176 mysqld[187808]: 2023-01-05  9:31:40 29 [Note] Slave I/O thread: Start semi-sync replication to master 'repl@db1118.eqiad.wmnet:3306' in log 'db1118-bin.003096' at position 815797178
Jan 05 09:31:40 db1176 mysqld[187808]: 2023-01-05  9:31:40 29 [Note] Slave I/O thread: connected to master 'repl@db1118.eqiad.wmnet:3306',replication starts at GTID position '180355171-180355171-148310907,180359172-180359172-49702203,171970637-171970637-2116621969,180363268-180363268-1082287825,171970661-171970661-3655324752,171974720-171974720-2572451842,171970745-171970745-2419896119,171978774-171978774-5,0-171970637-5484646134,171970572-171970572-3158188397'
Jan 05 09:32:18 db1176 mysqld[187808]: 2023-01-05  9:32:18 0 [Note] InnoDB: Buffer pool(s) load completed at 230105  9:32:18
Jan 05 09:32:29 db1176 systemd[1]: Stopping mariadb database server...
░░ Subject: A stop job for unit mariadb.service has begun execution
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A stop job for unit mariadb.service has begun execution.
░░
░░ The job identifier is 174882.
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] /opt/wmf-mariadb110/bin/mysqld (initiated by: unknown): Normal shutdown
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 15
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] Event Scheduler: Stopped
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] Event Scheduler: Purging the queue. 4 events
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 30 [Note] Slave SQL thread exiting, replication stopped in log 'db1118-bin.003096' at position 823896081; GTID position '0-171970637-5484646134,171970572-171970572-3158205537,171970637-171970637-2116621969,171970661-171970661-3655324752,171970745-171970745-2419896119,171974720-171974720-2572451842,171978774-171978774-5,180355171-180355171-148310907,180359172-180359172-49702203,180363268-180363268-1082287825', master: db1118.eqiad.wmnet:3306
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 29 [Note] Slave I/O thread exiting, read up to log 'db1118-bin.003096', position 826756910; GTID position 180355171-180355171-148310907,180359172-180359172-49702203,171970637-171970637-2116621969,180363268-180363268-1082287825,171970661-171970661-3655324752,171974720-171974720-2572451842,171970745-171970745-2419896119,171978774-171978774-5,0-171970637-5484646134,171970572-171970572-3158212259, master db1118.eqiad.wmnet:3306
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] InnoDB: FTS optimize thread exiting.
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] InnoDB: Starting shutdown...
Jan 05 09:32:29 db1176 mysqld[187808]: 2023-01-05  9:32:29 0 [Note] InnoDB: Dumping buffer pool(s) to /srv/sqldata/ib_buffer_pool
Jan 05 09:32:30 db1176 mysqld[187808]: 2023-01-05  9:32:30 0 [Note] InnoDB: Buffer pool(s) dump completed at 230105  9:32:30
Jan 05 09:32:34 db1176 mysqld[187808]: 2023-01-05  9:32:34 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
Jan 05 09:32:34 db1176 mysqld[187808]: 2023-01-05  9:32:34 0 [Note] InnoDB: Shutdown completed; log sequence number 84798584845712; transaction id 211863312472
Jan 05 09:32:34 db1176 mysqld[187808]: 2023-01-05  9:32:34 0 [Note] /opt/wmf-mariadb110/bin/mysqld: Shutdown complete

A restart after mysql_upgrade:

Jan 05 09:32:55 db1176 mysqld[189693]: /opt/wmf-mariadb110/bin/mysqld: Deprecated program name. It will be removed in a future release, use '/opt/wmf-mariadb110/bin/mariadbd' instead
Jan 05 09:32:55 db1176 mysqld[189693]: 2023-01-05  9:32:55 0 [Note] /opt/wmf-mariadb110/bin/mysqld (server 11.0.0-MariaDB-log) starting as process 189693 ...
Jan 05 09:32:55 db1176 mysqld[189693]: 2023-01-05  9:32:55 0 [Warning] No argument was provided to --log-bin and neither --log-basename or --log-bin-index where used;  This may cause repliction to break when this server acts as a master and has its hostname changed! Please use '--log-basename=db1176' or '--log-bin=db1176-bin' to avoid this problem.
Jan 05 09:32:55 db1176 mysqld[189693]: 2023-01-05  9:32:55 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
Jan 05 09:32:55 db1176 mysqld[189693]: 2023-01-05  9:32:55 0 [Note] InnoDB: Number of transaction pools: 1
Jan 05 09:32:55 db1176 mysqld[189693]: 2023-01-05  9:32:55 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Jan 05 09:32:55 db1176 mysqld[189693]: 2023-01-05  9:32:55 0 [Note] InnoDB: Using Linux native AIO
Jan 05 09:32:55 db1176 mysqld[189693]: 2023-01-05  9:32:55 0 [Note] InnoDB: Initializing buffer pool, total size = 377.000GiB, chunk size = 5.891GiB
 ^[[AJan 05 09:32:57 db1176 mysqld[189693]: 2023-01-05  9:32:57 0 [Note] InnoDB: Completed initialization of buffer pool
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: Resetting space id's in the doublewrite buffer
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: Opened 3 undo tablespaces
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: log sequence number 84798584845712; transaction id 211863312473
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] InnoDB: Loading buffer pool(s) from /srv/sqldata/ib_buffer_pool
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [ERROR] WSREP: plugin-wsrep-provider can't be enabled if wsrep_on==OFF or wsrep_provider is unset or set to 'none'
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [ERROR] mysqld: plugin-wsrep-provider can't be enabled if wsrep_on==OFF or wsrep_provider is unset or set to 'none'
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [ERROR] Plugin 'wsrep_provider' init function returned error.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Warning] 'innodb-change-buffering' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] Server socket created on IP: '0.0.0.0'.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] Server socket created on IP: '::'.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] Server socket created on IP: '0.0.0.0'.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] Server socket created on IP: '::'.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 3 [Note] Event Scheduler: scheduler thread started with id 3
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MariaDB server acts as a replica and has its hostname changed. Please use '--log-basename=#' or '--relay-log=db1176-relay-bin' to avoid this problem.
Jan 05 09:32:58 db1176 mysqld[189693]: 2023-01-05  9:32:58 0 [Note] /opt/wmf-mariadb110/bin/mysqld: ready for connections.
Jan 05 09:32:58 db1176 mysqld[189693]: Version: '11.0.0-MariaDB-log'  socket: '/run/mysqld/mysqld.sock'  port: 3306  MariaDB Server
Jan 05 09:32:58 db1176 systemd[1]: Started mariadb database server.
░░ Subject: A start job for unit mariadb.service has finished successfully
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A start job for unit mariadb.service has finished successfully.
░░
░░ The job identifier is 174884.
Jan 05 09:33:06 db1176 mysqld[189693]: 2023-01-05  9:33:06 13 [Note] Slave I/O thread: Start semi-sync replication to master 'repl@db1118.eqiad.wmnet:3306' in log 'db1118-bin.003096' at position 823896081
Jan 05 09:33:06 db1176 mysqld[189693]: 2023-01-05  9:33:06 13 [Note] Slave I/O thread: connected to master 'repl@db1118.eqiad.wmnet:3306',replication starts at GTID position '180355171-180355171-148310907,180359172-180359172-49702203,171970637-171970637-2116621969,180363268-180363268-1082287825,171970661-171970661-3655324752,171974720-171974720-2572451842,171970745-171970745-2419896119,171978774-171978774-5,0-171970637-5484646134,171970572-171970572-3158205537'
• Marostegui added a comment.Jan 5 2023, 9:35 AM
root@db1176:~/11.0# mysql -e "start slave"
mysql: Deprecated program name. It will be removed in a future release, use '/opt/wmf-mariadb110/bin/mariadb' instead
root@db1176:~/11.0# mariadb -e "stop slave; start slave;"
root@db1176:~/11.0#
• Marostegui added a comment.Jan 5 2023, 9:51 AM

Some first tests with traditional slow queries, it shows that MariaDB 11 performs a lot better:

P42842 (An Untitled Masterwork)
1Query:
2
3SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` STRAIGHT_JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) JOIN `categorylinks` ON ((rc_cur_id = cl_from)) WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_timestamp >= '20221229093755') AND cl_to = 'Articles_with_VIAF_identifiers' ORDER BY rc_timestamp DESC LIMIT 50 ;
4
5Tables:
6
7root@db1176.eqiad.wmnet[enwiki]> show create table categorylinks\G show create table recentchanges\G show create table actor\G show create table page\G show create table flaggedpages\G show create table ores_model\G show create table comment\G show create table change_tag\G show create table change_tag_def\G
8*************************** 1. row ***************************
9 Table: categorylinks
10Create Table: CREATE TABLE `categorylinks` (
11 `cl_from` int(8) unsigned NOT NULL DEFAULT 0,
12 `cl_to` varbinary(255) NOT NULL DEFAULT '',
13 `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
14 `cl_timestamp` timestamp /* mariadb-5.3 */ NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
15 `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
16 `cl_collation` varbinary(32) NOT NULL DEFAULT '',
17 `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
18 PRIMARY KEY (`cl_from`,`cl_to`),
19 KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
20 KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
21 KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
22) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
231 row in set (0.001 sec)
24
25*************************** 1. row ***************************
26 Table: recentchanges
27Create Table: CREATE TABLE `recentchanges` (
28 `rc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
29 `rc_timestamp` binary(14) NOT NULL,
30 `rc_actor` bigint(20) unsigned NOT NULL,
31 `rc_namespace` int(11) NOT NULL DEFAULT 0,
32 `rc_title` varbinary(255) NOT NULL DEFAULT '',
33 `rc_comment_id` bigint(20) unsigned NOT NULL,
34 `rc_minor` tinyint(3) unsigned NOT NULL DEFAULT 0,
35 `rc_bot` tinyint(3) unsigned NOT NULL DEFAULT 0,
36 `rc_new` tinyint(3) unsigned NOT NULL DEFAULT 0,
37 `rc_cur_id` int(10) unsigned NOT NULL DEFAULT 0,
38 `rc_this_oldid` int(10) unsigned NOT NULL DEFAULT 0,
39 `rc_last_oldid` int(10) unsigned NOT NULL DEFAULT 0,
40 `rc_type` tinyint(3) unsigned NOT NULL DEFAULT 0,
41 `rc_source` varbinary(16) NOT NULL DEFAULT '',
42 `rc_patrolled` tinyint(3) unsigned NOT NULL DEFAULT 0,
43 `rc_ip` varbinary(40) NOT NULL DEFAULT '',
44 `rc_old_len` int(10) DEFAULT NULL,
45 `rc_new_len` int(10) DEFAULT NULL,
46 `rc_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
47 `rc_logid` int(10) unsigned NOT NULL DEFAULT 0,
48 `rc_log_type` varbinary(255) DEFAULT NULL,
49 `rc_log_action` varbinary(255) DEFAULT NULL,
50 `rc_params` blob DEFAULT NULL,
51 PRIMARY KEY (`rc_id`),
52 KEY `rc_timestamp` (`rc_timestamp`),
53 KEY `rc_cur_id` (`rc_cur_id`),
54 KEY `rc_ip` (`rc_ip`),
55 KEY `rc_name_type_patrolled_timestamp` (`rc_namespace`,`rc_type`,`rc_patrolled`,`rc_timestamp`),
56 KEY `rc_ns_actor` (`rc_namespace`,`rc_actor`),
57 KEY `rc_actor` (`rc_actor`,`rc_timestamp`),
58 KEY `rc_namespace_title_timestamp` (`rc_namespace`,`rc_title`,`rc_timestamp`),
59 KEY `rc_this_oldid` (`rc_this_oldid`),
60 KEY `rc_new_name_timestamp` (`rc_new`,`rc_namespace`,`rc_timestamp`)
61) ENGINE=InnoDB AUTO_INCREMENT=1587532939 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
621 row in set (0.001 sec)
63
64*************************** 1. row ***************************
65 Table: actor
66Create Table: CREATE TABLE `actor` (
67 `actor_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
68 `actor_user` int(10) unsigned DEFAULT NULL,
69 `actor_name` varbinary(255) NOT NULL,
70 PRIMARY KEY (`actor_id`),
71 UNIQUE KEY `actor_name` (`actor_name`),
72 UNIQUE KEY `actor_user` (`actor_user`)
73) ENGINE=InnoDB AUTO_INCREMENT=222987468 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
741 row in set (0.001 sec)
75
76*************************** 1. row ***************************
77 Table: page
78Create Table: CREATE TABLE `page` (
79 `page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
80 `page_namespace` int(11) NOT NULL DEFAULT 0,
81 `page_title` varbinary(255) NOT NULL DEFAULT '',
82 `page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT 0,
83 `page_is_new` tinyint(1) unsigned NOT NULL DEFAULT 0,
84 `page_random` double unsigned NOT NULL DEFAULT 0,
85 `page_touched` binary(14) NOT NULL,
86 `page_links_updated` varbinary(14) DEFAULT NULL,
87 `page_latest` int(8) unsigned NOT NULL DEFAULT 0,
88 `page_len` int(8) unsigned NOT NULL DEFAULT 0,
89 `page_content_model` varbinary(32) DEFAULT NULL,
90 `page_lang` varbinary(35) DEFAULT NULL,
91 PRIMARY KEY (`page_id`),
92 UNIQUE KEY `page_name_title` (`page_namespace`,`page_title`),
93 KEY `page_random` (`page_random`),
94 KEY `page_len` (`page_len`),
95 KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
96) ENGINE=InnoDB AUTO_INCREMENT=72670555 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
971 row in set (0.001 sec)
98
99*************************** 1. row ***************************
100 Table: flaggedpages
101Create Table: CREATE TABLE `flaggedpages` (
102 `fp_page_id` int(10) unsigned NOT NULL,
103 `fp_reviewed` tinyint(1) NOT NULL DEFAULT 0,
104 `fp_pending_since` binary(14) DEFAULT NULL,
105 `fp_stable` int(10) unsigned NOT NULL,
106 `fp_quality` tinyint(1) DEFAULT NULL,
107 PRIMARY KEY (`fp_page_id`),
108 KEY `fp_reviewed_page` (`fp_reviewed`,`fp_page_id`),
109 KEY `fp_quality_page` (`fp_quality`,`fp_page_id`),
110 KEY `fp_pending_since` (`fp_pending_since`)
111) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1121 row in set (0.001 sec)
113
114*************************** 1. row ***************************
115 Table: ores_model
116Create Table: CREATE TABLE `ores_model` (
117 `oresm_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
118 `oresm_name` varbinary(32) NOT NULL,
119 `oresm_version` varbinary(32) NOT NULL,
120 `oresm_is_current` tinyint(1) NOT NULL,
121 PRIMARY KEY (`oresm_id`),
122 UNIQUE KEY `oresm_version` (`oresm_name`,`oresm_version`),
123 KEY `ores_model_status` (`oresm_name`,`oresm_is_current`)
124) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1251 row in set (0.001 sec)
126
127*************************** 1. row ***************************
128 Table: comment
129Create Table: CREATE TABLE `comment` (
130 `comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
131 `comment_hash` int(11) NOT NULL,
132 `comment_text` blob NOT NULL,
133 `comment_data` blob DEFAULT NULL,
134 PRIMARY KEY (`comment_id`),
135 KEY `comment_hash` (`comment_hash`)
136) ENGINE=InnoDB AUTO_INCREMENT=414186490 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1371 row in set (0.001 sec)
138
139*************************** 1. row ***************************
140 Table: change_tag
141Create Table: CREATE TABLE `change_tag` (
142 `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
143 `ct_rc_id` int(10) unsigned DEFAULT NULL,
144 `ct_log_id` int(10) unsigned DEFAULT NULL,
145 `ct_rev_id` int(10) unsigned DEFAULT NULL,
146 `ct_params` blob DEFAULT NULL,
147 `ct_tag_id` int(10) unsigned NOT NULL,
148 PRIMARY KEY (`ct_id`),
149 UNIQUE KEY `ct_rc_tag_id` (`ct_rc_id`,`ct_tag_id`),
150 UNIQUE KEY `ct_log_tag_id` (`ct_log_id`,`ct_tag_id`),
151 UNIQUE KEY `ct_rev_tag_id` (`ct_rev_id`,`ct_tag_id`),
152 KEY `ct_tag_id_id` (`ct_tag_id`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
153) ENGINE=InnoDB AUTO_INCREMENT=294706137 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1541 row in set (0.001 sec)
155
156*************************** 1. row ***************************
157 Table: change_tag_def
158Create Table: CREATE TABLE `change_tag_def` (
159 `ctd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
160 `ctd_name` varbinary(255) NOT NULL,
161 `ctd_user_defined` tinyint(1) NOT NULL,
162 `ctd_count` bigint(20) unsigned NOT NULL DEFAULT 0,
163 PRIMARY KEY (`ctd_id`),
164 UNIQUE KEY `ctd_name` (`ctd_name`),
165 KEY `ctd_count` (`ctd_count`),
166 KEY `ctd_user_defined` (`ctd_user_defined`)
167) ENGINE=InnoDB AUTO_INCREMENT=640 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
168
169
170
171
172
173
174Query plans and query times:
175
17610.4 (db1134)
177Query time: 50 rows in set (24.554 sec)
178
179+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
180| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
181+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
182| 1 | PRIMARY | categorylinks | ref | PRIMARY,cl_timestamp,cl_sortkey | cl_timestamp | 257 | const | 1605914 | Using where; Using index; Using temporary; Using filesort |
183| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_cur_id,rc_actor | rc_cur_id | 4 | enwiki.categorylinks.cl_from | 1 | Using where |
184| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
185| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
186| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
187| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
188| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
189| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
190| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
191| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
192+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
19310 rows in set (0.002 sec)
194
195
19610.6 (db1132)
197Query time: 50 rows in set (23.573 sec)
198+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
199| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
200+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
201| 1 | PRIMARY | categorylinks | ref | PRIMARY,cl_timestamp,cl_sortkey | cl_timestamp | 257 | const | 1639616 | Using where; Using index; Using temporary; Using filesort |
202| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_cur_id,rc_actor | rc_cur_id | 4 | enwiki.categorylinks.cl_from | 3 | Using where |
203| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
204| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
205| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
206| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
207| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
208| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
209| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
210| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
211+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
21210 rows in set (0.003 sec)
213
214
21511.0 (db1176)
216Query time:
217+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
218| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
219+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
220| 1 | PRIMARY | categorylinks | ref | PRIMARY,cl_timestamp,cl_sortkey | cl_timestamp | 257 | const | 1822624 | Using where; Using index; Using temporary; Using filesort |
221| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_cur_id,rc_actor | rc_cur_id | 4 | enwiki.categorylinks.cl_from | 1 | Using where |
222| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
223| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
224| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
225| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
226| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
227| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
228| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
229| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
230+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
23110 rows in set (0.002 sec)
232
233Query time: 50 rows in set (10.772 sec)

• Marostegui closed this task as Resolved.Jan 9 2023, 9:01 PM

This is done, let's keep tracking of the tests on the main task T326116

Maintenance_bot moved this task from In progress to Done on the DBA board.Jan 9 2023, 9:15 PM
Content licensed under Creative Commons Attribution-ShareAlike 3.0 (CC-BY-SA) unless otherwise noted; code licensed under GNU General Public License (GPL) or other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL