In T196172#4249120, @daniel wrote:

The tricky bit is to figure out how to best run a script against a "non-standard" db.

That turned out to be not too bad after all. Create a local file with content like:

<?php

require $IP . '/LocalSettings.php';

// Change DB login info
$wgDBuser = 'test_wikiuser';
$wgDBpassword = file_get_contents( __DIR__ . '/pw' );
$wgDBadminuser = 'test_wikiadmin';
$wgDBadminpassword = file_get_contents( __DIR__ . '/pw2' );
$wgLBFactoryConf['serverTemplate']['user'] = $wgDBuser;
$wgLBFactoryConf['serverTemplate']['password'] = $wgDBpassword;

// Point all the things to the test DBs
$wgLBFactoryConf['sectionsByDB'] = [];
$wgLBFactoryConf['sectionLoads'] = [
	'DEFAULT' => [
		'db1111' => 0,
		'db1112' => 100,
	],
];
$wgLBFactoryConf['hostsByName'] = [
	'db1111' => 'db1111',
	'db1112' => 'db1112',
];
$wgLBFactoryConf['groupLoadsBySection'] = [];
$wgLBFactoryConf['templateOverridesBySection'] = [
        'DEFAULT' => [
                'lagDetectionMethod' => 'pt-heartbeat',
                'lagDetectionOptions' => [ 'conds' => [
                    'shard' => 'test-s4', // Check this!
                    'datacenter' => 'eqiad'
                ] ],
                'useGTIDs' => true,
        ],
];

// Probably better to just break CentralAuth, ExternalStore, and so on.
$wgLBFactoryConf['externalLoads'] = [];
$wgLBFactoryConf['templateOverridesByCluster'] = [];

Then also create files 'pw' and 'pw2' containing the passwords. The slightly tricky part is that they have to be readable by www-data rather than your user, so the easiest way I could think of to make it happen turned out to be using mwrepl:

$ chmod o+w .; mwrepl enwiki; chmod o-w .
hphpd> umask( 0027 ); 
umask( 0027 );
hphpd> file_put_contents( "/home/.../pw", "..." );
file_put_contents( "/home/.../pw", "..." );
hphpd> file_put_contents( "/home/.../pw2", "..." );
file_put_contents( "/home/.../pw2", "..." );

After that, anything run via mwscript can use --conf=`pwd`/LS.php and it should be pointing to the testing DBs instead of the real ones.

In T196172#4255062, @Anomie wrote:

// Probably better to just break CentralAuth, ExternalStore, and so on.

I'll probably have to fix the ExternalStore bit to still read from prod ExternalStore for the migration script to function, though.

In T196172#4249120, @daniel wrote:

Also, before running the schema migration (stage 2), we'll have to create the tables (stage 0) and clean up the archive table (stage 1).

The "clean up the archive table" bit may be annoying, since some of those cleanups write to ExternalStore and we probably don't want the data re-written to production when the test DB is migrated.

I suppose I could just create a new ExternalStore cluster on db1111/db1112 for the test cleanups to write to, unless its easy to get the test DBs updated with fresh copies of the wikis.

Per our little MCR related sync up call, pinging DBA, this is confirmation that we would like the hosts wiped of current data and a new data set from the current master data loaded onto the machines (along with the same accounts that currently give us access).

The data will be offline for a day or so but we are okay with that (it fits within our timeframe)

Is tomorrow ok to wipe it and reimage them? cc @jcrespo

The plan is to reimage those servers into stretch tomorrow (thursday 6th June) and reload its data from production. I can also deploy grants for performance_schema/sys T195578 there so you have more data for profiling.

During reimage/upgrade/load, access in read-write will not be available.

The plan is to reimage those servers into stretch tomorrow (thursday 7th June) and reload its data from production. I can also deploy grants for performance_schema/sys T195578 there so you have more data for profiling.

During reimage/upgrade/load, access in read-write will not be available.

In T196172#4261268, @Marostegui wrote:

Is tomorrow ok to wipe it and reimage them? cc @jcrespo

In T196172#4261272, @jcrespo wrote:

The plan is to reimage those servers into stretch tomorrow (thursday 6th June) and reload its data from production. I can also deploy grants for performance_schema/sys T195578 there so you have more data for profiling.

During reimage/upgrade/load, access in read-write will not be available.

All sounds good to me

Clarification, Tomorrow 7th, not 6th.

@jcrespo, @Marostegui Excellent, thank you!

Change 437966 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Prepare reimage of db1111 and db1112

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

Change 437966 merged by Jcrespo:
[operations/puppet@production] mariadb: Prepare reimage of db1111 and db1112

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

Script wmf-auto-reimage was launched by jynus on neodymium.eqiad.wmnet for hosts:

['db1111.eqiad.wmnet', 'db1112.eqiad.wmnet']

The log can be found in /var/log/wmf-auto-reimage/201806071204_jynus_13938.log.

Completed auto-reimage of hosts:

['db1112.eqiad.wmnet', 'db1111.eqiad.wmnet']

and were ALL successful.

db1111 and db1112 have been set up again with replication (db1111 is the master and db1112 is the replica)
Jaime have reimaged them to 10.1 (which is what we are mostly running in core now).
The most recent data from production has been loaded too (commonswiki and eowiki as it had before). I have checked grants for terbium and they are available as they used to.

Let me know if something else is required.

@Marostegui: When I tried running some queries through MediaWiki, it tells me "Database is read-only: The database has been automatically locked while the replica database servers catch up to the master." Is there a significant level of lag I should expect between the two?

Nothing related to mediawiki, just a misconfiguration after all the reimages:

root@PRODUCTION test-s4 master[(none)]> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

root@PRODUCTION test-s4 master[(none)]> set global read_only = OFF;
Query OK, 0 rows affected (0.00 sec)

Should be solved now!

Ha, thanks. Although it turns out my hacky configuration also needed some extra hacks, it was somehow or other picking up other DBs from 'groupLoadsBySection'.

And I needed to change the shard in lagDetectionOptions.

Doing a test run on the eowiki copy now. Rough guess is that it'll take around 4 hours to run, if the time taken for the first few thousand rows is indicative.

For eowiki, with the default batch size (500):

• Each transaction took around 1-2 seconds during the initial run.
• The initial run took 13855 seconds to insert 6306218 (there was a bug that made it skip the first row, BTW).
• The second run (after fixing the bug) took 47 seconds to process 2 rows.
• The third run, with --reuse-content, took 326 seconds to load the existing rows then 48 seconds to run through and find nothing to do. Maximum resident set size reported by /usr/bin/time -v was 777008 K.
• The fourth run, without --reuse-content, took 45 seconds and reported MRSS of 109048 K.
• Sanity checks passed.

The next test will be to truncate the tables and re-run eowiki with a different batch size to see what difference that makes to the run time.

Increasing the batch size to 1000 increased the time-per-transaction to about 2-3 seconds, and decreased the total time to 12447 seconds (a savings of only about 23 minutes). Time for the subsequent runs was down to about 32 seconds. MRSS stayed the same (BTW, an initial run with --reuse-content had an MRSS of 687472 K).

If the time scales linearly, the 840894447 rows on enwiki would save 52 hours (off of 21 days) with the larger batch size. Once I run things against the commonswiki data set that might give a better indication.

I think one more thing to try: batching the slot row inserts within each transaction instead of doing them individually. It'd be nice to batch the content row inserts too, but doing that sanely seems rather complicated since we need the IDs back from each row inserted.

Third run with the batched slot table inserts (and batch size 1000) took 9875 seconds, which is such an improvement I'm reconsidering batching the content table inserts too even if it will make the code more complicated.

Yeah, that was worth it. 2322 seconds.

I'm going to run commonswiki now, with a larger batch size. Rough estimate is that it'll take a day or two for it to run.

@Anomie Did you just shave off 80% of the runtime? Wow!