Mentioned in SAL (#wikimedia-operations) [2017-11-10T10:24:24Z] <marostegui> Deploy schema change on db2089 - T179106

Going to test+optimize on db2089 which has wb_terms compressed.

-rw-rw---- 1 mysql mysql 529G Nov 10 10:25 /srv/sqldata.s5/wikidatawiki/wb_terms.ibd

In T179106#3750118, @Marostegui wrote:

I will definitely try to optimize the table, as it is getting out of hand

Now that we use Elastic for prefix search, we could drop all rows with term_type = "alias" from this table. Needs a little bit of code to make this optional, but should save a lot of space. If you think that would help, ask @hoo and @Lydia_Pintscher about it.

In T179106#3750329, @daniel wrote:

In T179106#3750118, @Marostegui wrote:

I will definitely try to optimize the table, as it is getting out of hand

Now that we use Elastic for prefix search, we could drop all rows with term_type = "alias" from this table. Needs a little bit of code to make this optional, but should save a lot of space. If you think that would help, ask @hoo and @Lydia_Pintscher about it.

The % of rows with that type isn't huge

root@db2086.codfw.wmnet[wikidatawiki]> select count(*) from wb_terms where term_type="alias";
+----------+
| count(*) |
+----------+
| 16570605 |
+----------+
1 row in set (11 min 24.28 sec)


root@db2086.codfw.wmnet[wikidatawiki]> select count(*) from wb_terms;
+------------+
| count(*)   |
+------------+
| 1268234185 |
+------------+
1 row in set (6 min 4.49 sec)

So that makes it around 1.3%, not huge, but it would still be good to get them cleaned if they are not necessary.

@hoo @Lydia_Pintscher you want me to create a ticket about it or you'd do it?

Interesting. I would have expected it to be a lot more than that.

Mentioned in SAL (#wikimedia-operations) [2017-11-13T06:18:21Z] <marostegui> Deploy alter table db2086 - T179106

In T179106#3750305, @Marostegui wrote:

Going to test+optimize on db2089 which has wb_terms compressed.

-rw-rw---- 1 mysql mysql 529G Nov 10 10:25 /srv/sqldata.s5/wikidatawiki/wb_terms.ibd

Not a big win after the optimization on a compressed table:

-rw-rw---- 1 mysql mysql 496G Nov 13 06:37 /srv/sqldata.s5/wikidatawiki/wb_terms.ibd

The index has been dropped from all codfw.
I am optimizing a non-compressed slave to see if the gain is worth the time. As we have seen that probably for a compressed one is not.

root@neodymium:/home/marostegui/git/software/dbtools# cat s5.hosts | grep codfw | while read host port; do echo $host:$port; mysql --skip-ssl wikidatawiki -h$host -P$port -e "show create table wb_terms\G" | grep wb_terms_language; done
dbstore2001.codfw.wmnet:3315
db2089.codfw.wmnet:3315
db2086.codfw.wmnet:3315
db2085.codfw.wmnet:3315
db2084.codfw.wmnet:3315
db2083.codfw.wmnet:3306
db2082.codfw.wmnet:3306
db2081.codfw.wmnet:3306
db2080.codfw.wmnet:3306
db2079.codfw.wmnet:3306
db2075.codfw.wmnet:3306
db2038.codfw.wmnet:3306
db2045.codfw.wmnet:3306
db2052.codfw.wmnet:3306
db2059.codfw.wmnet:3306
db2066.codfw.wmnet:3306
db2023.codfw.wmnet:3306

This is the non-compressed status:

root@db2052:~# ls -lh /srv/sqldata/wikidatawiki/wb_terms.ibd
-rw-rw---- 1 mysql mysql 718G Nov 13 07:32 /srv/sqldata/wikidatawiki/wb_terms.ibd

Mentioned in SAL (#wikimedia-operations) [2017-11-13T07:33:35Z] <marostegui> Optimize wb_terms table on db2052 - T179106

Mentioned in SAL (#wikimedia-operations) [2017-11-13T07:38:53Z] <marostegui> Deploy alter table to db1104 - T179106

In T179106#3753731, @Marostegui wrote:

This is the non-compressed status:

root@db2052:~# ls -lh /srv/sqldata/wikidatawiki/wb_terms.ibd
-rw-rw---- 1 mysql mysql 718G Nov 13 07:32 /srv/sqldata/wikidatawiki/wb_terms.ibd

This one was worth the optimize

-rw-rw---- 1 mysql mysql 489G Nov 14 06:34 wb_terms.ibd

Change 392582 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1087

https://gerrit.wikimedia.org/r/392582

Change 392582 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1087

https://gerrit.wikimedia.org/r/392582

Mentioned in SAL (#wikimedia-operations) [2017-11-21T06:47:15Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1087 - T179106 (duration: 00m 48s)

Mentioned in SAL (#wikimedia-operations) [2017-11-21T06:47:22Z] <marostegui> Remove index wb_terms_language from db1087 - https://phabricator.wikimedia.org/T179106

Mentioned in SAL (#wikimedia-operations) [2017-11-21T06:50:07Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T179106 (duration: 00m 48s)