Springle has submitted this change and it was merged. Change subject: s6 pager slave partitioning ......................................................................
s6 pager slave partitioning Change-Id: I64f7ffa2a985346f5e425299d7d58ce15de48887 --- A dbtools/s6-pager.sql 1 file changed, 155 insertions(+), 0 deletions(-) Approvals: Springle: Verified; Looks good to me, approved diff --git a/dbtools/s6-pager.sql b/dbtools/s6-pager.sql new file mode 100644 index 0000000..3534cc3 --- /dev/null +++ b/dbtools/s6-pager.sql @@ -0,0 +1,155 @@ +-- frwiki + + select 1 as n, count(rev_user) from frwiki.revision where rev_user < 1 +union select 10000 as n, count(rev_user) from frwiki.revision where rev_user >= 1 and rev_user < 10000 +union select 20000 as n, count(rev_user) from frwiki.revision where rev_user >= 10000 and rev_user < 20000 +union select 30000 as n, count(rev_user) from frwiki.revision where rev_user >= 20000 and rev_user < 30000 +union select 40000 as n, count(rev_user) from frwiki.revision where rev_user >= 30000 and rev_user < 40000 +union select 50000 as n, count(rev_user) from frwiki.revision where rev_user >= 40000 and rev_user < 50000 +union select 100000 as n, count(rev_user) from frwiki.revision where rev_user >= 50000 and rev_user < 100000 +union select 200000 as n, count(rev_user) from frwiki.revision where rev_user >= 100000 and rev_user < 200000 +union select 300000 as n, count(rev_user) from frwiki.revision where rev_user >= 200000 and rev_user < 300000 +union select 400000 as n, count(rev_user) from frwiki.revision where rev_user >= 300000 and rev_user < 400000 +union select 500000 as n, count(rev_user) from frwiki.revision where rev_user >= 400000 and rev_user < 500000 +union select 1000000 as n, count(rev_user) from frwiki.revision where rev_user >= 500000 and rev_user < 1000000 +union select 2000000 as n, count(rev_user) from frwiki.revision where rev_user >= 1000000 and rev_user < 2000000; + +ALTER TABLE frwiki.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 p40000 VALUES LESS THAN (40000), + 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 p400000 VALUES LESS THAN (400000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +ALTER TABLE frwiki.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 p30000 VALUES LESS THAN (30000), + PARTITION p40000 VALUES LESS THAN (40000), + 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 p400000 VALUES LESS THAN (400000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +-- ruwiki + + select 1 as n, count(rev_user) from ruwiki.revision where rev_user < 1 +union select 10000 as n, count(rev_user) from ruwiki.revision where rev_user >= 1 and rev_user < 10000 +union select 20000 as n, count(rev_user) from ruwiki.revision where rev_user >= 10000 and rev_user < 20000 +union select 30000 as n, count(rev_user) from ruwiki.revision where rev_user >= 20000 and rev_user < 30000 +union select 40000 as n, count(rev_user) from ruwiki.revision where rev_user >= 30000 and rev_user < 40000 +union select 50000 as n, count(rev_user) from ruwiki.revision where rev_user >= 40000 and rev_user < 50000 +union select 100000 as n, count(rev_user) from ruwiki.revision where rev_user >= 50000 and rev_user < 100000 +union select 200000 as n, count(rev_user) from ruwiki.revision where rev_user >= 100000 and rev_user < 200000 +union select 300000 as n, count(rev_user) from ruwiki.revision where rev_user >= 200000 and rev_user < 300000 +union select 400000 as n, count(rev_user) from ruwiki.revision where rev_user >= 300000 and rev_user < 400000 +union select 500000 as n, count(rev_user) from ruwiki.revision where rev_user >= 400000 and rev_user < 500000 +union select 1000000 as n, count(rev_user) from ruwiki.revision where rev_user >= 500000 and rev_user < 1000000 +union select 2000000 as n, count(rev_user) from ruwiki.revision where rev_user >= 1000000 and rev_user < 2000000; + +ALTER TABLE ruwiki.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 p40000 VALUES LESS THAN (40000), + 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 p400000 VALUES LESS THAN (400000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +ALTER TABLE ruwiki.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 p30000 VALUES LESS THAN (30000), + PARTITION p40000 VALUES LESS THAN (40000), + 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 p400000 VALUES LESS THAN (400000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +-- jawiki + + select 1 as n, count(rev_user) from jawiki.revision where rev_user < 1 +union select 10000 as n, count(rev_user) from jawiki.revision where rev_user >= 1 and rev_user < 10000 +union select 20000 as n, count(rev_user) from jawiki.revision where rev_user >= 10000 and rev_user < 20000 +union select 30000 as n, count(rev_user) from jawiki.revision where rev_user >= 20000 and rev_user < 30000 +union select 40000 as n, count(rev_user) from jawiki.revision where rev_user >= 30000 and rev_user < 40000 +union select 50000 as n, count(rev_user) from jawiki.revision where rev_user >= 40000 and rev_user < 50000 +union select 100000 as n, count(rev_user) from jawiki.revision where rev_user >= 50000 and rev_user < 100000 +union select 200000 as n, count(rev_user) from jawiki.revision where rev_user >= 100000 and rev_user < 200000 +union select 300000 as n, count(rev_user) from jawiki.revision where rev_user >= 200000 and rev_user < 300000 +union select 400000 as n, count(rev_user) from jawiki.revision where rev_user >= 300000 and rev_user < 400000 +union select 500000 as n, count(rev_user) from jawiki.revision where rev_user >= 400000 and rev_user < 500000 +union select 1000000 as n, count(rev_user) from jawiki.revision where rev_user >= 500000 and rev_user < 1000000; + +ALTER TABLE jawiki.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 p40000 VALUES LESS THAN (40000), + 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 p400000 VALUES LESS THAN (400000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + +ALTER TABLE jawiki.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 p30000 VALUES LESS THAN (30000), + PARTITION p40000 VALUES LESS THAN (40000), + 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 p400000 VALUES LESS THAN (400000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); \ No newline at end of file -- To view, visit https://gerrit.wikimedia.org/r/223732 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I64f7ffa2a985346f5e425299d7d58ce15de48887 Gerrit-PatchSet: 1 Gerrit-Project: operations/software Gerrit-Branch: master Gerrit-Owner: Springle <sprin...@wikimedia.org> Gerrit-Reviewer: Jcrespo <jcre...@wikimedia.org> Gerrit-Reviewer: Springle <sprin...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
