Springle has uploaded a new change for review. https://gerrit.wikimedia.org/r/215567
Change subject: s5 pager slave partitioning ...................................................................... s5 pager slave partitioning Change-Id: If8243abe23fb175866f524bf39adf547fc67df3e --- A dbtools/s5-pager.sql 1 file changed, 80 insertions(+), 0 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/operations/software refs/changes/67/215567/1 diff --git a/dbtools/s5-pager.sql b/dbtools/s5-pager.sql new file mode 100644 index 0000000..9e3671d --- /dev/null +++ b/dbtools/s5-pager.sql @@ -0,0 +1,80 @@ +-- dewiki + + select 1 as n, count(rev_user) from dewiki.revision where rev_user < 1 +union select 10000 as n, count(rev_user) from dewiki.revision where rev_user >= 1 and rev_user < 10000 +union select 20000 as n, count(rev_user) from dewiki.revision where rev_user >= 10000 and rev_user < 20000 +union select 30000 as n, count(rev_user) from dewiki.revision where rev_user >= 20000 and rev_user < 30000 +union select 100000 as n, count(rev_user) from dewiki.revision where rev_user >= 30000 and rev_user < 100000 +union select 200000 as n, count(rev_user) from dewiki.revision where rev_user >= 100000 and rev_user < 200000 +union select 300000 as n, count(rev_user) from dewiki.revision where rev_user >= 200000 and rev_user < 300000 +union select 1000000 as n, count(rev_user) from dewiki.revision where rev_user >= 300000 and rev_user < 1000000 +union select 2000000 as n, count(rev_user) from dewiki.revision where rev_user >= 1000000 and rev_user < 2000000; + +ALTER TABLE dewiki.logging + DROP PRIMARY KEY, + ADD PRIMARY KEY (log_id, log_user) + PARTITION BY RANGE (log_user) ( + PARTITION p1 VALUES LESS THAN (1), + PARTITION p10000 VALUES LESS THAN (10000), + PARTITION p20000 VALUES LESS THAN (20000), + PARTITION p50000 VALUES LESS THAN (30000), + PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p300000 VALUES LESS THAN (300000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +ALTER TABLE dewiki.revision + DROP PRIMARY KEY, + DROP INDEX rev_id, + ADD PRIMARY KEY (rev_id, rev_user) + PARTITION BY RANGE (rev_user) ( + PARTITION p1 VALUES LESS THAN (1), + PARTITION p10000 VALUES LESS THAN (10000), + PARTITION p20000 VALUES LESS THAN (20000), + PARTITION p50000 VALUES LESS THAN (50000), + PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p300000 VALUES LESS THAN (300000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +-- wikidatawiki + + select 1 as n, count(rev_user) from wikidatawiki.revision where rev_user < 1 +union select 10000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 1 and rev_user < 10000 +union select 20000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 10000 and rev_user < 20000 +union select 30000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 20000 and rev_user < 30000 +union select 100000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 30000 and rev_user < 100000 +union select 200000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 100000 and rev_user < 200000 +union select 300000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 200000 and rev_user < 300000 +union select 1000000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 300000 and rev_user < 1000000 +union select 2000000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 1000000 and rev_user < 2000000; + +ALTER TABLE wikidatawiki.logging + DROP PRIMARY KEY, + ADD PRIMARY KEY (log_id, log_user) + PARTITION BY RANGE (log_user) ( + PARTITION p1 VALUES LESS THAN (1), + PARTITION p10000 VALUES LESS THAN (10000), + PARTITION p20000 VALUES LESS THAN (20000), + PARTITION p30000 VALUES LESS THAN (30000), + PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p300000 VALUES LESS THAN (300000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +ALTER TABLE wikidatawiki.revision + DROP PRIMARY KEY, + ADD PRIMARY KEY (rev_id, rev_user) + PARTITION BY RANGE (rev_user) ( + PARTITION p1 VALUES LESS THAN (1), + PARTITION p10000 VALUES LESS THAN (10000), + PARTITION p20000 VALUES LESS THAN (20000), + PARTITION p30000 VALUES LESS THAN (30000), + PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p300000 VALUES LESS THAN (300000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); \ No newline at end of file -- To view, visit https://gerrit.wikimedia.org/r/215567 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: If8243abe23fb175866f524bf39adf547fc67df3e Gerrit-PatchSet: 1 Gerrit-Project: operations/software Gerrit-Branch: master Gerrit-Owner: Springle <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
