Maniphest T204930

dbstore2002 tables compression status check
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Marostegui
	Sep 20 2018, 9:45 AM

Description

dbstore2002 is suffering from some disk space issues (T204593#4593924).
While we are now compressing s2 after recloning it (T204593#4597992) it might be worth checking the other sections as some of them might not be compressed.
An example is s4 commonswiki.externallinks table which is 300G which sounds like a lot to be compressed. That table also got a column recently dropped and it is now 190GB on the master.

This is an example of s4:

root@dbstore2002.codfw.wmnet[commonswiki]> SELECT table_schema, table_name FROM information_Schema.tables WHERE engine='INNODB' and row_format != 'COMPRESSED' and table_schema='commonswiki' ORDER BY DATA_LENGTH ASC;
+--------------+-----------------------+
| table_schema | table_name            |
+--------------+-----------------------+
| commonswiki  | revision_actor_temp   |
| commonswiki  | content_models        |
| commonswiki  | change_tag_def        |
| commonswiki  | slot_roles            |
| commonswiki  | actor                 |
| commonswiki  | protected_titles      |
| commonswiki  | ip_changes            |
| commonswiki  | image_comment_temp    |
| commonswiki  | revision_comment_temp |
| commonswiki  | comment               |
| commonswiki  | change_tag            |
| commonswiki  | watchlist             |
| commonswiki  | wbc_entity_usage      |
| commonswiki  | page                  |
| commonswiki  | linter                |
| commonswiki  | slots                 |
| commonswiki  | pagelinks             |
| commonswiki  | content               |
| commonswiki  | templatelinks         |
+--------------+-----------------------+
19 rows in set (0.05 sec)

Worth checking and compressing tables on s1, s3 and s4 on dbstore2002.

Related Objects

Mentioned In: T204593: dbstore2002 s2 crashed
Mentioned Here: T204593: dbstore2002 s2 crashed

Event Timeline

Marostegui created this task.Sep 20 2018, 9:45 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 20 2018, 9:45 AM

• Banyek claimed this task.Sep 20 2018, 9:46 AM

Marostegui triaged this task as Medium priority.Sep 20 2018, 9:46 AM

• Banyek added a project: User-Banyek.Sep 20 2018, 1:51 PM

• Banyek moved this task from Backlog to In progress on the User-Banyek board.Sep 20 2018, 1:56 PM

• Banyek moved this task from Triage to Pending comment on the DBA board.Sep 21 2018, 2:26 PM

Marostegui mentioned this in T204593: dbstore2002 s2 crashed.Sep 24 2018, 3:05 PM

there are still tables to compress from s2@dbstore2002, as it was mentioned in T204593.

+--------------+------------------+
| table_schema | table_name       |
+--------------+------------------+
| nlwiki       | __wmf_checksums  |
| nlwiki       | revision         |
| svwiki       | externallinks    |
| itwiki       | content          |
| itwiki       | slots            |
| ptwiki       | pagelinks        |
| nlwiki       | text             |
| svwiki       | templatelinks    |
| trwiki       | flaggedtemplates |
| itwiki       | text             |
| zhwiki       | pagelinks        |
| itwiki       | revision         |
| itwiki       | pagelinks        |
| plwiki       | pagelinks        |
| enwiktionary | templatelinks    |
| plwiki       | flaggedimages    |
| plwiki       | flaggedtemplates |
+--------------+------------------+

compressing s2 is resumed

• Banyek moved this task from Pending comment to In progress on the DBA board.Sep 26 2018, 8:48 AM

In T204593 I was mentioned that I reenabled the cache on the host even the BBU is broken - I disabled it because of the hosts SPOF-ness

The compression is stopped for the weekend, and the replication got restarted

resuming s2 compression

Mentioned in SAL (#wikimedia-operations) [2018-10-01T12:19:36Z] <banyek> stopping replication on s2@dbstore20002: the tables being compressed (T204930)

• Banyek moved this task from In progress to On hold on the User-Banyek board.Oct 2 2018, 8:05 AM

• Banyek moved this task from On hold to In progress on the User-Banyek board.Oct 4 2018, 1:12 PM

Mentioned in SAL (#wikimedia-operations) [2018-10-04T13:14:05Z] <banyek> muting alerts on dbstore2002 and resuming compression of s2 database tables (T204930)

Mentioned in SAL (#wikimedia-operations) [2018-10-04T13:14:26Z] <banyek> muting alerts on s2replication @dbstore2002 and resuming compression of s2 database tables (T204930)

the compression of the s2 tables finally finished, I'll check the others

• Banyek moved this task from In progress to On hold on the User-Banyek board.Oct 15 2018, 7:50 AM

• Banyek moved this task from On hold to In progress on the User-Banyek board.Oct 19 2018, 9:36 AM

Compressing s4 tables with the follwing command:

mysql -BN -S /run/mysqld/mysqld.s4.sock -e "SELECT table_schema, table_name FROM information_Schema.tables WHERE engine='INNODB' and row_format <> 'COMPRESSED'ORDER BY DATA_LENGTH ASC"  | while read db table; do echo "compressing $db.$table" ;mysql -BN -S /run/mysqld/mysqld.s4.sock -e "ALTER TABLE $db.$table ROW_FORMAT=COMPRESSED, FORCE;"; sleep 10; done

Mentioned in SAL (#wikimedia-operations) [2018-10-19T11:46:37Z] <banyek> starting compression of s4 tables @dbstore2002 (T204930)

In T204930#4680094, @Banyek wrote:

Compressing s4 tables with the follwing command:

mysql -BN -S /run/mysqld/mysqld.s4.sock -e "SELECT table_schema, table_name FROM information_Schema.tables WHERE engine='INNODB' and row_format <> 'COMPRESSED'ORDER BY DATA_LENGTH ASC"  | while read db table; do echo "compressing $db.$table" ;mysql -BN -S /run/mysqld/mysqld.s4.sock -e "ALTER TABLE $db.$table ROW_FORMAT=COMPRESSED, FORCE;"; sleep 10; done

It is safer for that kind of operations to avoid replicating those to the binary log. Please make sure to use set session sql_log_bin=0 before any alter, unless you have a good reason to get them replicated (ie: a schema change on the master that you really want to do with replication)

Mentioned in SAL (#wikimedia-operations) [2018-10-19T14:16:08Z] <banyek> disconnecting s4 replication on dbstore2002 (T204930)

The modified command is:

mysql -BN -S /run/mysqld/mysqld.s4.sock -e "SELECT table_schema, table_name FROM information_Schema.tables WHERE engine='INNODB' and row_format <> 'COMPRESSED'ORDER BY DATA_LENGTH ASC"  | while read db table; do echo "compressing $db.$table" ;mysql -BN -S /run/mysqld/mysqld.s4.sock -e "SET SESSION SQL_LOG_BIN=0; ALTER TABLE $db.$table ROW_FORMAT=COMPRESSED, FORCE;"; sleep 10; done

The compression of s4 finished, resuming the replication, and the compression will be continued with s1 after the next backup window.

Mentioned in SAL (#wikimedia-operations) [2018-10-21T20:42:26Z] <banyek> resuming replication on s4@dbstore2002 (T204930)

• Banyek moved this task from In progress to On hold on the User-Banyek board.Oct 21 2018, 8:43 PM

Here are the compressable tables from s1:

MariaDB [(none)]> SELECT table_schema, table_name, data_length/1024/1024/1024 as size FROM information_
Schema.tables WHERE engine='INNODB' and row_format != 'COMPRESSED' ORDER BY DATA_LENGTH ASC;
+--------------+-----------------------+-----------------+
| table_schema | table_name            | size            |
+--------------+-----------------------+-----------------+
| enwiki       | ipblocks_restrictions |  0.000015258789 |
| ops          | __wmf_checksums       |  0.000015258789 |
| ops          | event_log             |  0.000015258789 |
| enwiki       | change_tag_def        |  0.000015258789 |
| mysql        | innodb_table_stats    |  0.000015258789 |
| enwiki       | actor                 |  0.000015258789 |
| heartbeat    | heartbeat             |  0.000015258789 |
| enwiki       | revision_actor_temp   |  0.000015258789 |
| enwiki       | content_models        |  0.000015258789 |
| mysql        | gtid_slave_pos        |  0.000015258789 |
| enwiki       | slot_roles            |  0.000015258789 |
| sys          | sys_config            |  0.000015258789 |
| mysql        | innodb_index_stats    |  0.000366210938 |
| enwiki       | image_comment_temp    |  0.004898071289 |
| enwiki       | protected_titles      |  0.007369995117 |
| enwiki       | oldimage              |  0.196014404297 |
| enwiki       | user_newtalk          |  0.351455688477 |
| enwiki       | revision_comment_temp |  0.422851562500 |
| enwiki       | ores_classification   |  0.572280883789 |
| enwiki       | comment               |  0.950195312500 |
| enwiki       | tag_summary           |  1.688476562500 |
| enwiki       | change_tag            |  5.058547973633 |
| enwiki       | ip_changes            |  8.304687500000 |
| enwiki       | slots                 | 35.211914062500 |
| enwiki       | templatelinks         | 36.854431152344 |
| enwiki       | content               | 69.850585937500 |
| enwiki       | pagelinks             | 94.991668701172 |
+--------------+-----------------------+-----------------+
27 rows in set (0.04 sec)

MariaDB [(none)]> SELECT sum(data_length)/1024/1024/1024 as size FROM information_Schema.tables WHERE e
ngine='INNODB' and row_format != 'COMPRESSED' ORDER BY DATA_LENGTH ASC;
+------------------+
| size             |
+------------------+
| 254.465927124023 |
+------------------+
1 row in set (0.05 sec)

I continue compressing s1 on dbstore2002.

Mentioned in SAL (#wikimedia-operations) [2018-10-24T21:26:15Z] <banyek> pausing replication on dbstore2002 (T204930)

Mentioned in SAL (#wikimedia-operations) [2018-10-24T21:33:27Z] <banyek> compressing tables in s1@dbstore2002 (T204930)

The command which used for compressing tables is

mysql -BN -S /run/mysqld/mysqld.s1.sock -e "SELECT table_schema, table_name FROM information_Schema.tables WHERE engine='INNODB' and row_format <> 'COMPRESSED' ORDER BY DATA_LENGTH ASC"  | while read db table; do echo "compressing $db.$table" ;mysql -BN -S
 /run/mysqld/mysqld.s1.sock -e "SET SESSION SQL_LOG_BIN=0; ALTER TABLE $db.$table ROW_FORMAT=COMPRESSED, FORCE;"; sleep 10; done

s1 compression on dbstore2002 finished now I resume replication.
iirc there's only s3 left, but because of the lots of tables the

SELECT table_schema, table_name FROM information_Schema.tables WHERE engine='INNODB' and row_format <> 'COMPRESSED' ORDER BY DATA_LENGTH ASC"

query takes ages.

Mentioned in SAL (#wikimedia-operations) [2018-10-27T12:29:13Z] <banyek> resuming replication on s1@dbstore2002 as table compression is finished (T204930)

All the sections are compressed, except s3 - but due the number of the tables we have there the compression would take more effort than what we would win with it: I think this task is considered resolved, as we have plenty of free space:

/dev/mapper/tank-data  6.6T  4.1T  2.5T  63% /srv

dbstore2002 tables compression status checkClosed, ResolvedPublicActions

Description

Related Objects

Event Timeline

dbstore2002 tables compression status check
Closed, ResolvedPublic
Actions