Page MenuHomePhabricator

Test schema migration script
Closed, ResolvedPublic

Description

Before attempting the schema conversion on a live system, want to run the populateContentTables script against the test databases on db1111 and db1112.

The tricky bit is to figure out how to best run a script against a "non-standard" db. 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).

Then running the script, the first things we are interested in are

  • does it run smoothly and
  • how long does it take
  • how long does it take to re-run it on an already converted db

If needed, we can reset the test by simply truncating the relevant tables (slots, content, content_models, and slot_roles).

Of course, we'll want to know whether the conversion was actually successful. That's not really easy to tell, but as a fist sanity check, I'd assert that

  • the slots table and the content table have the same number of rows (this should be the case right after conversion).
  • that number is the number of rows in the revision table + the number of rows in the archive table.
  • slot_roles contains one row (for "main")
  • content_models contains entries not just for wikitext, but also for JS and CSS pages.
  • the content row for *.js and *.css pages in NS_MEDIAWIKI and NS_USER have the correct content model.

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusAssignedTask
Resolvedtstarling
ResolvedAnomie

Event Timeline

daniel triaged this task as Normal priority.Jun 1 2018, 4:16 PM
daniel created this task.
Anomie added a comment.EditedJun 4 2018, 7:56 PM

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:

LS.php
<?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.

Anomie added a comment.Jun 4 2018, 8:02 PM
// 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.

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.

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.

All sounds good to me

Clarification, Tomorrow 7th, not 6th.

jcrespo claimed this task.Jun 6 2018, 3:23 PM
daniel added a comment.Jun 6 2018, 3:52 PM

@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.

jcrespo reassigned this task from jcrespo to Addshore.Jun 7 2018, 5:11 PM
Restricted Application added a project: User-Addshore. · View Herald TranscriptJun 7 2018, 5:11 PM
Addshore reassigned this task from Addshore to Anomie.Jun 7 2018, 5:12 PM

@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!

Anomie closed this task as Resolved.Jun 18 2018, 7:47 PM

Commons run with batch-size 4000:

  • Average transaction time for the revision table was 1.3709 seconds, max was 3.07866 seconds.
  • The initial run took 31 hours 36.5 minutes to process 17896490 rows. At that rate, enwiki would take about 87.2 hours.
  • For some reason the Commons run had a much lower MRSS than the eowiki run with the same code. That makes me a bit skeptical about using it as a measure of memory usage for --reuse-content.
  • Second run took 722 seconds to process 0 rows.
  • Third run with --reuse-content seemed to be using too much memory and took too long. Let's not do it.
  • Sanity checks passed.

I think we can call this resolved now. The live runs should probably use a somewhat lower batch size (2000?) to keep the time per transaction down, and shouldn't use --reuse-content.

Vvjjkkii renamed this task from Test schema migration script to 3tbaaaaaaa.Jul 1 2018, 1:06 AM
Vvjjkkii reopened this task as Open.
Vvjjkkii removed Anomie as the assignee of this task.
Vvjjkkii raised the priority of this task from Normal to High.
Vvjjkkii updated the task description. (Show Details)
Vvjjkkii removed subscribers: Aklapper, gerritbot.
Marostegui renamed this task from 3tbaaaaaaa to Test schema migration script.Jul 2 2018, 5:02 AM
Marostegui closed this task as Resolved.
Marostegui assigned this task to Anomie.
Marostegui lowered the priority of this task from High to Normal.
Marostegui updated the task description. (Show Details)