Jcrespo has submitted this change and it was merged.
Change subject: Updated partitioning for s1 and s4
......................................................................
Updated partitioning for s1 and s4
Bug: T120513
Change-Id: I522f483cea08f80a65f776c9be62489840cec702
---
M dbtools/s1-pager.sql
M dbtools/s4-pager.sql
2 files changed, 107 insertions(+), 10 deletions(-)
Approvals:
Jcrespo: Verified; Looks good to me, approved
diff --git a/dbtools/s1-pager.sql b/dbtools/s1-pager.sql
index fcc3d42..125b127 100644
--- a/dbtools/s1-pager.sql
+++ b/dbtools/s1-pager.sql
@@ -1,7 +1,8 @@
-- enwiki
select 1 as n, count(rev_user) from enwiki.revision where
rev_user < 1
-union select 100000 as n, count(rev_user) from enwiki.revision where
rev_user >= 1 and rev_user < 100000
+union select 50000 as n, count(rev_user) from enwiki.revision where
rev_user >= 1 and rev_user < 50000
+union select 100000 as n, count(rev_user) from enwiki.revision where
rev_user >= 50000 and rev_user < 100000
union select 200000 as n, count(rev_user) from enwiki.revision where
rev_user >= 100000 and rev_user < 200000
union select 300000 as n, count(rev_user) from enwiki.revision where
rev_user >= 200000 and rev_user < 300000
union select 400000 as n, count(rev_user) from enwiki.revision where
rev_user >= 300000 and rev_user < 400000
@@ -22,34 +23,85 @@
union select 14000000 as n, count(rev_user) from enwiki.revision where
rev_user >= 12000000 and rev_user < 14000000
union select 16000000 as n, count(rev_user) from enwiki.revision where
rev_user >= 14000000 and rev_user < 16000000
union select 18000000 as n, count(rev_user) from enwiki.revision where
rev_user >= 16000000 and rev_user < 18000000
-union select 20000000 as n, count(rev_user) from enwiki.revision where
rev_user >= 18000000 and rev_user < 30000000;
+union select 20000000 as n, count(rev_user) from enwiki.revision where
rev_user >= 18000000 and rev_user < 20000000
+union select 22000000 as n, count(rev_user) from enwiki.revision where
rev_user >= 20000000 and rev_user < 22000000
+union select 24000000 as n, count(rev_user) from enwiki.revision where
rev_user >= 22000000 and rev_user < 24000000
+union select 99999999 as n, count(rev_user) from enwiki.revision where
rev_user >= 24000000;
+
+--
+-- Results on 2015-12-29:
+--
+-- +----------+-----------------+
+-- | n | count(rev_user) |
+-- +----------+-----------------+
+-- | 1 | 143012851 |
+-- | 50000 | 22262399 |
+-- | 100000 | 17246412 |
+-- | 200000 | 23860120 |
+-- | 300000 | 22837407 |
+-- | 400000 | 14815911 |
+-- | 500000 | 13818622 |
+-- | 750000 | 23865783 |
+-- | 1000000 | 17747080 |
+-- | 1500000 | 32002927 |
+-- | 2000000 | 26416371 |
+-- | 3000000 | 26939492 |
+-- | 4000000 | 29456398 |
+-- | 5000000 | 25721791 |
+-- | 6000000 | 19401515 |
+-- | 7000000 | 20810745 |
+-- | 8000000 | 26390816 |
+-- | 9000000 | 13651989 |
+-- | 10000000 | 12830219 |
+-- | 12000000 | 25810888 |
+-- | 14000000 | 24018280 |
+-- | 16000000 | 17205483 |
+-- | 18000000 | 15013314 |
+-- | 20000000 | 10100942 |
+-- | 22000000 | 9036396 |
+-- | 24000000 | 3941295 |
+-- | 99999999 | 5808754 |
+-- +----------+-----------------+
+-- 27 rows in set (3 hours 27 min 43.58 sec)
ALTER TABLE enwiki.logging
DROP PRIMARY KEY,
ADD PRIMARY KEY (log_id, log_user)
PARTITION BY RANGE (log_user) (
PARTITION p1 VALUES LESS THAN (1),
+ 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 p700000 VALUES LESS THAN (750000),
+ PARTITION p750000 VALUES LESS THAN (750000),
PARTITION p1000000 VALUES LESS THAN (1000000),
- PARTITION p1200000 VALUES LESS THAN (1500000),
+ PARTITION p1250000 VALUES LESS THAN (1250000),
+ PARTITION p1500000 VALUES LESS THAN (1500000),
+ PARTITION p1750000 VALUES LESS THAN (1750000),
PARTITION p2000000 VALUES LESS THAN (2000000),
+ PARTITION p2500000 VALUES LESS THAN (2500000),
PARTITION p3000000 VALUES LESS THAN (3000000),
+ PARTITION p3500000 VALUES LESS THAN (3500000),
PARTITION p4000000 VALUES LESS THAN (4000000),
+ PARTITION p4500000 VALUES LESS THAN (4500000),
PARTITION p5000000 VALUES LESS THAN (5000000),
PARTITION p6000000 VALUES LESS THAN (6000000),
PARTITION p7000000 VALUES LESS THAN (7000000),
+ PARTITION p7500000 VALUES LESS THAN (7500000),
PARTITION p8000000 VALUES LESS THAN (8000000),
PARTITION p9000000 VALUES LESS THAN (9000000),
PARTITION p10000000 VALUES LESS THAN (10000000),
+ PARTITION p11000000 VALUES LESS THAN (11000000),
PARTITION p12000000 VALUES LESS THAN (12000000),
+ PARTITION p13000000 VALUES LESS THAN (13000000),
PARTITION p14000000 VALUES LESS THAN (14000000),
PARTITION p16000000 VALUES LESS THAN (16000000),
PARTITION p18000000 VALUES LESS THAN (18000000),
+ PARTITION p22000000 VALUES LESS THAN (22000000),
+ PARTITION p24000000 VALUES LESS THAN (24000000),
+ PARTITION p28000000 VALUES LESS THAN (28000000),
PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE );
ALTER TABLE enwiki.revision
@@ -58,25 +110,38 @@
ADD PRIMARY KEY (rev_id, rev_user)
PARTITION BY RANGE (rev_user) (
PARTITION p1 VALUES LESS THAN (1),
+ 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 p700000 VALUES LESS THAN (750000),
+ PARTITION p750000 VALUES LESS THAN (750000),
PARTITION p1000000 VALUES LESS THAN (1000000),
- PARTITION p1200000 VALUES LESS THAN (1500000),
+ PARTITION p1250000 VALUES LESS THAN (1250000),
+ PARTITION p1500000 VALUES LESS THAN (1500000),
+ PARTITION p1750000 VALUES LESS THAN (1750000),
PARTITION p2000000 VALUES LESS THAN (2000000),
+ PARTITION p2500000 VALUES LESS THAN (2500000),
PARTITION p3000000 VALUES LESS THAN (3000000),
+ PARTITION p3500000 VALUES LESS THAN (3500000),
PARTITION p4000000 VALUES LESS THAN (4000000),
+ PARTITION p4500000 VALUES LESS THAN (4500000),
PARTITION p5000000 VALUES LESS THAN (5000000),
PARTITION p6000000 VALUES LESS THAN (6000000),
PARTITION p7000000 VALUES LESS THAN (7000000),
+ PARTITION p7500000 VALUES LESS THAN (7500000),
PARTITION p8000000 VALUES LESS THAN (8000000),
PARTITION p9000000 VALUES LESS THAN (9000000),
PARTITION p10000000 VALUES LESS THAN (10000000),
+ PARTITION p11000000 VALUES LESS THAN (11000000),
PARTITION p12000000 VALUES LESS THAN (12000000),
+ PARTITION p13000000 VALUES LESS THAN (13000000),
PARTITION p14000000 VALUES LESS THAN (14000000),
PARTITION p16000000 VALUES LESS THAN (16000000),
PARTITION p18000000 VALUES LESS THAN (18000000),
+ PARTITION p22000000 VALUES LESS THAN (22000000),
+ PARTITION p24000000 VALUES LESS THAN (24000000),
+ PARTITION p28000000 VALUES LESS THAN (28000000),
PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE );
+
diff --git a/dbtools/s4-pager.sql b/dbtools/s4-pager.sql
index 3f8487f..844b1b0 100644
--- a/dbtools/s4-pager.sql
+++ b/dbtools/s4-pager.sql
@@ -13,9 +13,34 @@
union select 500000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 400000 and rev_user < 500000
union select 1000000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 500000 and rev_user < 1000000
union select 2000000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 1000000 and rev_user < 2000000
-union select 2000000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 2000000 and rev_user < 3000000
-union select 2000000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 3000000 and rev_user < 4000000
-union select 2000000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 4000000 and rev_user < 5000000;
+union select 3000000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 2000000 and rev_user < 3000000
+union select 4000000 as n, count(rev_user) from commonswiki.revision where
rev_user >= 3000000 and rev_user < 4000000
+union select 9999999 as n, count(rev_user) from commonswiki.revision where
rev_user >= 4000000;
+
+--
+-- results on 2015-12-29:
+--
+-- +---------+-----------------+
+-- | n | count(rev_user) |
+-- +---------+-----------------+
+-- | 1 | 2312096 |
+-- | 10000 | 11038216 |
+-- | 20000 | 5254160 |
+-- | 30000 | 3460782 |
+-- | 40000 | 2351946 |
+-- | 50000 | 4844739 |
+-- | 100000 | 14958145 |
+-- | 200000 | 16239187 |
+-- | 300000 | 13011323 |
+-- | 400000 | 14644094 |
+-- | 500000 | 5853173 |
+-- | 1000000 | 19852423 |
+-- | 2000000 | 29449241 |
+-- | 3000000 | 8517747 |
+-- | 4000000 | 15687869 |
+-- | 5000000 | 2938461 |
+-- +---------+-----------------+
+-- 16 rows in set (10 min 23.22 sec)
ALTER TABLE commonswiki.logging
DROP PRIMARY KEY,
@@ -28,14 +53,17 @@
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 p2000000 VALUES LESS THAN (2000000),
PARTITION p3000000 VALUES LESS THAN (3000000),
PARTITION p4000000 VALUES LESS THAN (4000000),
+ PARTITION p5000000 VALUES LESS THAN (5000000),
PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE );
ALTER TABLE commonswiki.revision
@@ -50,12 +78,16 @@
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 p2000000 VALUES LESS THAN (2000000),
PARTITION p3000000 VALUES LESS THAN (3000000),
PARTITION p4000000 VALUES LESS THAN (4000000),
- PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE );
\ No newline at end of file
+ PARTITION p5000000 VALUES LESS THAN (5000000),
+ PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE );
+
--
To view, visit https://gerrit.wikimedia.org/r/266504
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I522f483cea08f80a65f776c9be62489840cec702
Gerrit-PatchSet: 1
Gerrit-Project: operations/software
Gerrit-Branch: master
Gerrit-Owner: Jcrespo <[email protected]>
Gerrit-Reviewer: Jcrespo <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
