Jcrespo has submitted this change and it was merged. Change subject: Update s5 & s6 partitioning according to current row distribution ......................................................................
Update s5 & s6 partitioning according to current row distribution Bug: T120513 Change-Id: If11836ab198216c5e21987e7f83fc7a4f3930f89 --- M dbtools/s5-pager.sql M dbtools/s6-pager.sql 2 files changed, 203 insertions(+), 8 deletions(-) Approvals: Jcrespo: Verified; Looks good to me, approved diff --git a/dbtools/s5-pager.sql b/dbtools/s5-pager.sql index 9e3671d..c9d5e9f 100644 --- a/dbtools/s5-pager.sql +++ b/dbtools/s5-pager.sql @@ -8,20 +8,50 @@ 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; +union select 2000000 as n, count(rev_user) from dewiki.revision where rev_user >= 1000000 and rev_user < 2000000 +union select 3000000 as n, count(rev_user) from dewiki.revision where rev_user >= 2000000; + +-- results on 2016-03-07: +-- +---------+-----------------+ +-- | n | count(rev_user) | +-- +---------+-----------------+ +-- | 1 | 23175897 | +-- | 10000 | 6689692 | +-- | 20000 | 3446708 | +-- | 30000 | 3900499 | +-- | 100000 | 18133533 | +-- | 200000 | 14848243 | +-- | 300000 | 14203410 | +-- | 1000000 | 40046991 | +-- | 2000000 | 13909989 | +-- | 3000000 | 1204622 | +-- +---------+-----------------+ +-- 10 rows in set (12 min 19.38 sec) 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 p5000 VALUES LESS THAN (5000), PARTITION p10000 VALUES LESS THAN (10000), PARTITION p20000 VALUES LESS THAN (20000), - PARTITION p50000 VALUES LESS THAN (30000), + PARTITION p30000 VALUES LESS THAN (30000), + PARTITION p50000 VALUES LESS THAN (50000), + PARTITION p70000 VALUES LESS THAN (70000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p150000 VALUES LESS THAN (150000), 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 p600000 VALUES LESS THAN (600000), + PARTITION p700000 VALUES LESS THAN (700000), + PARTITION p800000 VALUES LESS THAN (800000), + PARTITION p900000 VALUES LESS THAN (900000), PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION p2000000 VALUES LESS THAN (2000000), + PARTITION p3000000 VALUES LESS THAN (3000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); ALTER TABLE dewiki.revision @@ -30,13 +60,25 @@ ADD PRIMARY KEY (rev_id, rev_user) PARTITION BY RANGE (rev_user) ( PARTITION p1 VALUES LESS THAN (1), + PARTITION p5000 VALUES LESS THAN (5000), PARTITION p10000 VALUES LESS THAN (10000), PARTITION p20000 VALUES LESS THAN (20000), + PARTITION p30000 VALUES LESS THAN (30000), PARTITION p50000 VALUES LESS THAN (50000), + PARTITION p70000 VALUES LESS THAN (70000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p150000 VALUES LESS THAN (150000), 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 p600000 VALUES LESS THAN (600000), + PARTITION p700000 VALUES LESS THAN (700000), + PARTITION p800000 VALUES LESS THAN (800000), + PARTITION p900000 VALUES LESS THAN (900000), PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION p2000000 VALUES LESS THAN (2000000), + PARTITION p3000000 VALUES LESS THAN (3000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); -- wikidatawiki @@ -49,32 +91,80 @@ 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; +union select 2000000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 1000000 and rev_user < 2000000 +union select 3000000 as n, count(rev_user) from wikidatawiki.revision where rev_user >= 2000000; + +-- results on 2016-03-07: +-- +---------+-----------------+ +-- | n | count(rev_user) | +-- +---------+-----------------+ +-- | 1 | 1467305 | +-- | 10000 | 28725758 | +-- | 20000 | 21309827 | +-- | 30000 | 27248967 | +-- | 100000 | 40315544 | +-- | 200000 | 71837026 | +-- | 300000 | 26518572 | +-- | 1000000 | 54299316 | +-- | 2000000 | 33821553 | +-- | 3000000 | 2507345 | +-- +---------+-----------------+ +-- 10 rows in set (22 min 54.92 sec) 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 p5000 VALUES LESS THAN (5000), PARTITION p10000 VALUES LESS THAN (10000), + PARTITION p15000 VALUES LESS THAN (15000), PARTITION p20000 VALUES LESS THAN (20000), + PARTITION p25000 VALUES LESS THAN (25000), PARTITION p30000 VALUES LESS THAN (30000), + PARTITION p50000 VALUES LESS THAN (50000), + PARTITION p70000 VALUES LESS THAN (70000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p125000 VALUES LESS THAN (125000), + PARTITION p150000 VALUES LESS THAN (150000), + PARTITION p175000 VALUES LESS THAN (175000), PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p250000 VALUES LESS THAN (250000), PARTITION p300000 VALUES LESS THAN (300000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p700000 VALUES LESS THAN (700000), PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION p1500000 VALUES LESS THAN (1500000), + PARTITION p2000000 VALUES LESS THAN (2000000), + PARTITION p3000000 VALUES LESS THAN (3000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); ALTER TABLE wikidatawiki.revision DROP PRIMARY KEY, +-- DROP INDEX rev_page_id, ADD PRIMARY KEY (rev_id, rev_user) PARTITION BY RANGE (rev_user) ( PARTITION p1 VALUES LESS THAN (1), + PARTITION p5000 VALUES LESS THAN (5000), PARTITION p10000 VALUES LESS THAN (10000), + PARTITION p15000 VALUES LESS THAN (15000), PARTITION p20000 VALUES LESS THAN (20000), + PARTITION p25000 VALUES LESS THAN (25000), PARTITION p30000 VALUES LESS THAN (30000), + PARTITION p50000 VALUES LESS THAN (50000), + PARTITION p70000 VALUES LESS THAN (70000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p125000 VALUES LESS THAN (125000), + PARTITION p150000 VALUES LESS THAN (150000), + PARTITION p175000 VALUES LESS THAN (175000), PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p250000 VALUES LESS THAN (250000), PARTITION p300000 VALUES LESS THAN (300000), + PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p700000 VALUES LESS THAN (700000), PARTITION p1000000 VALUES LESS THAN (1000000), - PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); \ No newline at end of file + PARTITION p1500000 VALUES LESS THAN (1500000), + PARTITION p2000000 VALUES LESS THAN (2000000), + PARTITION p3000000 VALUES LESS THAN (3000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + diff --git a/dbtools/s6-pager.sql b/dbtools/s6-pager.sql index 3534cc3..2ca33e9 100644 --- a/dbtools/s6-pager.sql +++ b/dbtools/s6-pager.sql @@ -12,7 +12,29 @@ 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; +union select 2000000 as n, count(rev_user) from frwiki.revision where rev_user >= 1000000 and rev_user < 2000000 +union select 3000000 as n, count(rev_user) from frwiki.revision where rev_user >= 2000000; + +-- results on 2016-03-07: +-- +---------+-----------------+ +-- | n | count(rev_user) | +-- +---------+-----------------+ +-- | 1 | 15638394 | +-- | 10000 | 6914711 | +-- | 20000 | 3372557 | +-- | 30000 | 4133218 | +-- | 40000 | 3820140 | +-- | 50000 | 3235927 | +-- | 100000 | 8347306 | +-- | 200000 | 16201008 | +-- | 300000 | 10825620 | +-- | 400000 | 9228063 | +-- | 500000 | 5028945 | +-- | 1000000 | 15686998 | +-- | 2000000 | 13874767 | +-- | 3000000 | 1572649 | +-- +---------+-----------------+ +-- 14 rows in set (5 min 52.62 sec) ALTER TABLE frwiki.logging DROP PRIMARY KEY, @@ -24,12 +46,23 @@ PARTITION p30000 VALUES LESS THAN (30000), PARTITION p40000 VALUES LESS THAN (40000), PARTITION p50000 VALUES LESS THAN (50000), + PARTITION p75000 VALUES LESS THAN (75000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p125000 VALUES LESS THAN (125000), + PARTITION p150000 VALUES LESS THAN (150000), + PARTITION p175000 VALUES LESS THAN (175000), PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p250000 VALUES LESS THAN (250000), PARTITION p300000 VALUES LESS THAN (300000), PARTITION p400000 VALUES LESS THAN (400000), PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p600000 VALUES LESS THAN (600000), + PARTITION p700000 VALUES LESS THAN (700000), + PARTITION p800000 VALUES LESS THAN (800000), + PARTITION p900000 VALUES LESS THAN (900000), PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION p1500000 VALUES LESS THAN (1500000), + PARTITION p2000000 VALUES LESS THAN (2000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); ALTER TABLE frwiki.revision @@ -43,12 +76,23 @@ PARTITION p30000 VALUES LESS THAN (30000), PARTITION p40000 VALUES LESS THAN (40000), PARTITION p50000 VALUES LESS THAN (50000), + PARTITION p75000 VALUES LESS THAN (75000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p125000 VALUES LESS THAN (125000), + PARTITION p150000 VALUES LESS THAN (150000), + PARTITION p175000 VALUES LESS THAN (175000), PARTITION p200000 VALUES LESS THAN (200000), + PARTITION p250000 VALUES LESS THAN (250000), PARTITION p300000 VALUES LESS THAN (300000), PARTITION p400000 VALUES LESS THAN (400000), PARTITION p500000 VALUES LESS THAN (500000), + PARTITION p600000 VALUES LESS THAN (600000), + PARTITION p700000 VALUES LESS THAN (700000), + PARTITION p800000 VALUES LESS THAN (800000), + PARTITION p900000 VALUES LESS THAN (900000), PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION p1500000 VALUES LESS THAN (1500000), + PARTITION p2000000 VALUES LESS THAN (2000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); -- ruwiki @@ -65,24 +109,51 @@ 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; +union select 2000000 as n, count(rev_user) from ruwiki.revision where rev_user >= 1000000 and rev_user < 2000000 +union select 3000000 as n, count(rev_user) from ruwiki.revision where rev_user >= 2000000; + +-- +---------+-----------------+ +-- | n | count(rev_user) | +-- +---------+-----------------+ +-- | 1 | 12135172 | +-- | 10000 | 5248819 | +-- | 20000 | 2733849 | +-- | 30000 | 2094022 | +-- | 40000 | 2200810 | +-- | 50000 | 1985636 | +-- | 100000 | 5583611 | +-- | 200000 | 8486887 | +-- | 300000 | 4889031 | +-- | 400000 | 4751174 | +-- | 500000 | 3384289 | +-- | 1000000 | 12957853 | +-- | 2000000 | 4237809 | +-- | 3000000 | 0 | +-- +---------+-----------------+ +-- 14 rows in set (3 min 35.96 sec) 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 p5000 VALUES LESS THAN (5000), 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 p75000 VALUES LESS THAN (75000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p150000 VALUES LESS THAN (150000), 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 p600000 VALUES LESS THAN (600000), + PARTITION p750000 VALUES LESS THAN (750000), PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION p2000000 VALUES LESS THAN (2000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); ALTER TABLE ruwiki.revision @@ -91,18 +162,25 @@ ADD PRIMARY KEY (rev_id, rev_user) PARTITION BY RANGE (rev_user) ( PARTITION p1 VALUES LESS THAN (1), + PARTITION p5000 VALUES LESS THAN (5000), 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 p75000 VALUES LESS THAN (75000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p150000 VALUES LESS THAN (150000), 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 p600000 VALUES LESS THAN (600000), + PARTITION p750000 VALUES LESS THAN (750000), PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION p2000000 VALUES LESS THAN (2000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); + -- jawiki @@ -117,7 +195,28 @@ 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; +union select 1000000 as n, count(rev_user) from jawiki.revision where rev_user >= 500000 and rev_user < 1000000 +union select 2000000 as n, count(rev_user) from jawiki.revision where rev_user >= 1000000; + +-- results on 2016-03-07: +-- +---------+-----------------+ +-- | n | count(rev_user) | +-- +---------+-----------------+ +-- | 1 | 18757874 | +-- | 10000 | 2444970 | +-- | 20000 | 2238066 | +-- | 30000 | 1604146 | +-- | 40000 | 1682537 | +-- | 50000 | 1195892 | +-- | 100000 | 4615865 | +-- | 200000 | 7725097 | +-- | 300000 | 4367828 | +-- | 400000 | 3440772 | +-- | 500000 | 2256746 | +-- | 1000000 | 5891921 | +-- | 2000000 | 274100 | +-- +---------+-----------------+ +-- 13 rows in set (3 min 27.18 sec) ALTER TABLE jawiki.logging DROP PRIMARY KEY, @@ -130,10 +229,13 @@ PARTITION p40000 VALUES LESS THAN (40000), PARTITION p50000 VALUES LESS THAN (50000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p150000 VALUES LESS THAN (150000), 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 p750000 VALUES LESS THAN (750000), + PARTITION p1000000 VALUES LESS THAN (1000000), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); ALTER TABLE jawiki.revision @@ -148,8 +250,11 @@ PARTITION p40000 VALUES LESS THAN (40000), PARTITION p50000 VALUES LESS THAN (50000), PARTITION p100000 VALUES LESS THAN (100000), + PARTITION p150000 VALUES LESS THAN (150000), 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 + PARTITION p750000 VALUES LESS THAN (750000), + PARTITION p1000000 VALUES LESS THAN (1000000), + PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ); -- To view, visit https://gerrit.wikimedia.org/r/275454 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: If11836ab198216c5e21987e7f83fc7a4f3930f89 Gerrit-PatchSet: 3 Gerrit-Project: operations/software Gerrit-Branch: master Gerrit-Owner: Jcrespo <[email protected]> Gerrit-Reviewer: Jcrespo <[email protected]> Gerrit-Reviewer: Springle <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
