db1102 (sanitarium) filling up (WAS: Clean up old binlogs from db1102 (sanitarium multi-instance))
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	• Marostegui
	Dec 8 2017, 5:39 PM

Description

db1102 (multi-instance sanitarium that has: s2, s4,s6,s7) got a warning on disk space.
I cleaned up a bunch of binlogs.
We were storing up to 30 days of binlogs (for each shard) which is quite a lot in total.
Merged this: https://gerrit.wikimedia.org/r/#/c/396435/

The current status after that clean up:

root@db1102:/srv# df -hT /srv
Filesystem            Type  Size  Used Avail Use% Mounted on
/dev/mapper/tank-data xfs   3.6T  3.0T  611G  84% /srv

However, there are lots of old binlogs that can also be cleaned up as they are left overs from the transfers, and old binlogs form when we renamed them for each shard.
Example

root@db1102:/srv# ls -lhR | grep  "\-bin." | grep -v db1102 | egrep "M|G"
-rw-rw---- 1 mysql mysql 1001M Jun 27 10:29 db1060-bin.000373
-rw-rw---- 1 mysql mysql 1001M Jun 27 18:38 db1060-bin.000374
-rw-rw---- 1 mysql mysql 1001M Jun 28 03:03 db1060-bin.000375
-rw-rw---- 1 mysql mysql 1002M Jun 28 08:40 db1060-bin.000376
-rw-rw---- 1 mysql mysql 1001M Jun 28 14:31 db1060-bin.000377
-rw-rw---- 1 mysql mysql 1001M Jun 28 21:14 db1060-bin.000378
-rw-rw---- 1 mysql mysql 1001M Jun 29 04:23 db1060-bin.000379
-rw-rw---- 1 mysql mysql 1001M Jun 29 11:32 db1060-bin.000380
-rw-rw---- 1 mysql mysql 1001M Jun 29 18:37 db1060-bin.000381
-rw-rw---- 1 mysql mysql 1001M Jun 30 02:24 db1060-bin.000382
-rw-rw---- 1 mysql mysql 1001M Jun 30 09:39 db1060-bin.000383
-rw-rw---- 1 mysql mysql 1001M Jun 30 16:09 db1060-bin.000384
-rw-rw---- 1 mysql mysql 1001M Jun 30 23:30 db1060-bin.000385
-rw-rw---- 1 mysql mysql 1001M Jul  1 06:36 db1060-bin.000386
-rw-rw---- 1 mysql mysql 1001M Jul  1 13:05 db1060-bin.000387
-rw-rw---- 1 mysql mysql 1001M Jul  1 17:37 db1060-bin.000388
-rw-rw---- 1 mysql mysql 1018M Jul  1 22:54 db1060-bin.000389
-rw-rw---- 1 mysql mysql 1001M Jul  2 07:46 db1060-bin.000390
-rw-rw---- 1 mysql mysql 1005M Jul  2 15:04 db1060-bin.000391
-rw-rw---- 1 mysql mysql 1001M Jul  2 21:18 db1060-bin.000392
-rw-rw---- 1 mysql mysql 1001M Jul  3 05:20 db1060-bin.000393
-rw-rw---- 1 mysql mysql 1001M Jul  3 12:40 db1060-bin.000394
-rw-rw---- 1 mysql mysql 1001M Jul  3 20:46 db1060-bin.000395
-rw-rw---- 1 mysql mysql  893M Jul  4 06:06 db1060-bin.000396
-rw-rw---- 1 mysql mysql   17M Jul  4 06:16 db1060-bin.000397
-rw-rw---- 1 mysql mysql   18M Jul  4 06:16 db1060-relay-bin.000002
-rw-rw---- 1 mysql mysql 1001M Feb 28  2017 db1067-bin.003215
-rw-rw---- 1 mysql mysql 1001M Feb 28  2017 db1067-bin.003216
-rw-rw---- 1 mysql mysql 1001M Feb 28  2017 db1067-bin.003217
-rw-rw---- 1 mysql mysql 1001M Feb 28  2017 db1067-bin.003218
-rw-rw---- 1 mysql mysql 1004M Feb 28  2017 db1067-bin.003219
-rw-rw---- 1 mysql mysql 1007M Feb 28  2017 db1067-bin.003220
-rw-rw---- 1 mysql mysql 1012M Feb 28  2017 db1067-bin.003221
-rw-rw---- 1 mysql mysql 1001M Feb 28  2017 db1067-bin.003222
-rw-rw---- 1 mysql mysql 1001M Mar  1  2017 db1067-bin.003223
-rw-rw---- 1 mysql mysql 1001M Mar  1  2017 db1067-bin.003224
-rw-rw---- 1 mysql mysql 1001M Mar  1  2017 db1067-bin.003225
-rw-rw---- 1 mysql mysql 1001M Mar  1  2017 db1067-bin.003226
-rw-rw---- 1 mysql mysql 1001M Mar  1  2017 db1067-bin.003227
-rw-rw---- 1 mysql mysql 1001M Mar  1  2017 db1067-bin.003228
-rw-rw---- 1 mysql mysql  1.1G Mar  2  2017 db1067-bin.003229
-rw-rw---- 1 mysql mysql 1001M Mar  2  2017 db1067-bin.003230
-rw-rw---- 1 mysql mysql 1001M Mar  2  2017 db1067-bin.003231
-rw-rw---- 1 mysql mysql 1001M Mar  2  2017 db1067-bin.003232
-rw-rw---- 1 mysql mysql 1001M Mar  2  2017 db1067-bin.003233
-rw-rw---- 1 mysql mysql 1001M Mar  3  2017 db1067-bin.003234
-rw-rw---- 1 mysql mysql 1001M Mar  3  2017 db1067-bin.003235
-rw-rw---- 1 mysql mysql 1001M Mar  3  2017 db1067-bin.003236
-rw-rw---- 1 mysql mysql 1001M Mar  3  2017 db1067-bin.003237
-rw-rw---- 1 mysql mysql 1001M Mar  3  2017 db1067-bin.003238
-rw-rw---- 1 mysql mysql 1001M Mar  3  2017 db1067-bin.003239
-rw-rw---- 1 mysql mysql 1001M Mar  4  2017 db1067-bin.003240
-rw-rw---- 1 mysql mysql 1001M Mar  4  2017 db1067-bin.003241
-rw-rw---- 1 mysql mysql 1001M Mar  4  2017 db1067-bin.003242
-rw-rw---- 1 mysql mysql 1018M Mar  4  2017 db1067-bin.003243
-rw-rw---- 1 mysql mysql 1001M Mar  4  2017 db1067-bin.003244
-rw-rw---- 1 mysql mysql 1001M Mar  5  2017 db1067-bin.003245
-rw-rw---- 1 mysql mysql 1001M Mar  5  2017 db1067-bin.003246
-rw-rw---- 1 mysql mysql 1001M Mar  5  2017 db1067-bin.003247
-rw-rw---- 1 mysql mysql 1001M Mar  6  2017 db1067-bin.003248
-rw-rw---- 1 mysql mysql 1001M Mar  6  2017 db1067-bin.003249
-rw-rw---- 1 mysql mysql 1001M Mar  6  2017 db1067-bin.003250
-rw-rw---- 1 mysql mysql 1009M Mar  6  2017 db1067-bin.003251
-rw-rw---- 1 mysql mysql 1001M Mar  6  2017 db1067-bin.003252
-rw-rw---- 1 mysql mysql 1009M Mar  6  2017 db1067-bin.003253
-rw-rw---- 1 mysql mysql 1001M Mar  7  2017 db1067-bin.003254
-rw-rw---- 1 mysql mysql  801M Mar  7  2017 db1067-bin.003255
-rw-rw---- 1 mysql mysql   820 Mar  7  2017 db1067-bin.index
-rw-rw---- 1 mysql mysql   137 Mar  7  2017 db1067-bin.state
-rw-rw---- 1 mysql mysql  289M Mar  7  2017 db1067-relay-bin.000373
-rw-rw---- 1 mysql mysql  702M Mar  7  2017 db1067-relay-bin.000374
-rw-rw---- 1 mysql mysql    52 Mar  7  2017 db1067-relay-bin.index
-rw-rw---- 1 mysql mysql  1.1G Jul 21 19:09 s2-bin.000099
-rw-rw---- 1 mysql mysql  1.1G Jul 22 01:09 s2-bin.000100
-rw-rw---- 1 mysql mysql  1.1G Jul 22 07:02 s2-bin.000101
-rw-rw---- 1 mysql mysql  1.1G Jul 22 12:43 s2-bin.000102
-rw-rw---- 1 mysql mysql  1.1G Jul 22 18:04 s2-bin.000103
-rw-rw---- 1 mysql mysql  1.1G Jul 23 00:52 s2-bin.000104
-rw-rw---- 1 mysql mysql  1.1G Jul 23 07:12 s2-bin.000105
-rw-rw---- 1 mysql mysql  1.1G Jul 23 11:44 s2-bin.000106
-rw-rw---- 1 mysql mysql  1.1G Jul 23 16:57 s2-bin.000107
-rw-rw---- 1 mysql mysql  1.1G Jul 23 21:31 s2-bin.000108
-rw-rw---- 1 mysql mysql  1.1G Jul 24 01:28 s2-bin.000109
-rw-rw---- 1 mysql mysql  1.1G Jul 24 06:52 s2-bin.000110
-rw-rw---- 1 mysql mysql  1.1G Jul 24 12:26 s2-bin.000111
-rw-rw---- 1 mysql mysql  1.1G Jul 24 17:49 s2-bin.000112
-rw-rw---- 1 mysql mysql  1.1G Jul 25 00:00 s2-bin.000113
-rw-rw---- 1 mysql mysql  1.1G Jul 25 05:46 s2-bin.000114
-rw-rw---- 1 mysql mysql  1.1G Jul 25 11:14 s2-bin.000115
-rw-rw---- 1 mysql mysql  1.1G Jul 25 16:25 s2-bin.000116
-rw-rw---- 1 mysql mysql  1.1G Jul 25 21:38 s2-bin.000117
-rw-rw---- 1 mysql mysql  1.1G Jul 26 03:03 s2-bin.000118
-rw-rw---- 1 mysql mysql  1.1G Jul 26 09:27 s2-bin.000119
-rw-rw---- 1 mysql mysql  1.1G Jul 26 14:08 s2-bin.000120
-rw-rw---- 1 mysql mysql  1.1G Jul 26 18:59 s2-bin.000121
-rw-rw---- 1 mysql mysql  1.1G Jul 26 23:25 s2-bin.000122
-rw-rw---- 1 mysql mysql  1.1G Jul 27 03:50 s2-bin.000123
-rw-rw---- 1 mysql mysql  1.1G Jul 27 08:24 s2-bin.000124
-rw-rw---- 1 mysql mysql  1.1G Jul 27 11:40 s2-bin.000125
-rw-rw---- 1 mysql mysql  1.1G Jul 27 16:26 s2-bin.000126
-rw-rw---- 1 mysql mysql  1.1G Jul 27 21:14 s2-bin.000127
-rw-rw---- 1 mysql mysql  1.1G Jul 28 01:52 s2-bin.000128
-rw-rw---- 1 mysql mysql  1.1G Jul 28 06:36 s2-bin.000129
-rw-rw---- 1 mysql mysql  1.1G Jul 28 10:38 s2-bin.000130
-rw-rw---- 1 mysql mysql  1.1G Jul 28 14:53 s2-bin.000131
-rw-rw---- 1 mysql mysql  185M Jul 28 15:28 s2-bin.000132
-rw-rw---- 1 mysql mysql 1001M Jun 28 16:20 db1085-bin.000845
-rw-rw---- 1 mysql mysql 1001M Jun 29 03:48 db1085-bin.000846
-rw-rw---- 1 mysql mysql 1001M Jun 29 14:19 db1085-bin.000847
-rw-rw---- 1 mysql mysql 1001M Jun 29 23:55 db1085-bin.000848
-rw-rw---- 1 mysql mysql 1001M Jun 30 12:16 db1085-bin.000849
-rw-rw---- 1 mysql mysql 1018M Jun 30 17:36 db1085-bin.000850
-rw-rw---- 1 mysql mysql 1007M Jun 30 18:38 db1085-bin.000851
-rw-rw---- 1 mysql mysql 1019M Jun 30 19:02 db1085-bin.000852
-rw-rw---- 1 mysql mysql 1020M Jun 30 19:19 db1085-bin.000853
-rw-rw---- 1 mysql mysql 1022M Jun 30 19:50 db1085-bin.000854
-rw-rw---- 1 mysql mysql 1001M Jun 30 20:11 db1085-bin.000855
-rw-rw---- 1 mysql mysql 1023M Jun 30 21:04 db1085-bin.000856
-rw-rw---- 1 mysql mysql 1001M Jul  1 01:12 db1085-bin.000857
-rw-rw---- 1 mysql mysql 1001M Jul  1 12:50 db1085-bin.000858
-rw-rw---- 1 mysql mysql 1001M Jul  1 22:42 db1085-bin.000859
-rw-rw---- 1 mysql mysql 1001M Jul  2 11:13 db1085-bin.000860
-rw-rw---- 1 mysql mysql 1001M Jul  2 23:22 db1085-bin.000861
-rw-rw---- 1 mysql mysql 1001M Jul  3 10:59 db1085-bin.000862
-rw-rw---- 1 mysql mysql 1003M Jul  3 20:27 db1085-bin.000863
-rw-rw---- 1 mysql mysql 1001M Jul  4 10:35 db1085-bin.000864
-rw-rw---- 1 mysql mysql 1001M Jul  4 20:50 db1085-bin.000865
-rw-rw---- 1 mysql mysql  1.1G Jul  5 09:26 db1085-bin.000866
-rw-rw---- 1 mysql mysql  7.0M Jul  5 09:32 db1085-bin.000868
-rw-rw---- 1 mysql mysql  7.4M Jul  5 09:32 db1085-relay-bin.000002
-rw-rw---- 1 mysql mysql  1.1G Jul 21 08:21 s6-bin.000063
-rw-rw---- 1 mysql mysql  1.1G Jul 21 18:09 s6-bin.000064
-rw-rw---- 1 mysql mysql  1.1G Jul 22 06:36 s6-bin.000065
-rw-rw---- 1 mysql mysql  1.1G Jul 22 14:16 s6-bin.000066
-rw-rw---- 1 mysql mysql  1.1G Jul 22 23:27 s6-bin.000067
-rw-rw---- 1 mysql mysql  1.1G Jul 23 12:43 s6-bin.000068
-rw-rw---- 1 mysql mysql  1.1G Jul 24 02:06 s6-bin.000069
-rw-rw---- 1 mysql mysql  1.1G Jul 24 12:10 s6-bin.000070
-rw-rw---- 1 mysql mysql  1.1G Jul 24 21:35 s6-bin.000071
-rw-rw---- 1 mysql mysql  1.1G Jul 25 11:09 s6-bin.000072
-rw-rw---- 1 mysql mysql  1.1G Jul 25 21:37 s6-bin.000073
-rw-rw---- 1 mysql mysql  1.1G Jul 26 07:13 s6-bin.000074
-rw-rw---- 1 mysql mysql  1.1G Jul 26 16:23 s6-bin.000075
-rw-rw---- 1 mysql mysql  1.1G Jul 27 04:58 s6-bin.000076
-rw-rw---- 1 mysql mysql  1.1G Jul 27 13:42 s6-bin.000077
-rw-rw---- 1 mysql mysql  1.1G Jul 28 02:41 s6-bin.000078
-rw-rw---- 1 mysql mysql  948M Jul 28 15:28 s6-bin.000079
-rw-rw---- 1 mysql mysql 1001M Jun  8  2016 db1033-bin.002124
-rw-rw---- 1 mysql mysql 1001M Jun  9  2016 db1033-bin.002125
-rw-rw---- 1 mysql mysql 1001M Jun  9  2016 db1033-bin.002126
-rw-rw---- 1 mysql mysql 1001M Jun 10  2016 db1033-bin.002127
-rw-rw---- 1 mysql mysql 1001M Jun 10  2016 db1033-bin.002128
-rw-rw---- 1 mysql mysql 1001M Jun 10  2016 db1033-bin.002129
-rw-rw---- 1 mysql mysql 1001M Jun 11  2016 db1033-bin.002130
-rw-rw---- 1 mysql mysql 1001M Jun 12  2016 db1033-bin.002131
-rw-rw---- 1 mysql mysql 1001M Jun 12  2016 db1033-bin.002132
-rw-rw---- 1 mysql mysql 1001M Jun 12  2016 db1033-bin.002133
-rw-rw---- 1 mysql mysql 1001M Jun 13  2016 db1033-bin.002134
-rw-rw---- 1 mysql mysql 1001M Jun 13  2016 db1033-bin.002135
-rw-rw---- 1 mysql mysql 1001M Jun 13  2016 db1033-bin.002136
-rw-rw---- 1 mysql mysql 1001M Jun 14  2016 db1033-bin.002137
-rw-rw---- 1 mysql mysql 1001M Jun 14  2016 db1033-bin.002138
-rw-rw---- 1 mysql mysql 1001M Jun 15  2016 db1033-bin.002139
-rw-rw---- 1 mysql mysql  214M Jun 15  2016 db1033-bin.002140
-rw-rw---- 1 mysql mysql  480M Jun 15  2016 db1033-relay-bin.000190
-rw-rw---- 1 mysql mysql  177M Jun 15  2016 db1033-relay-bin.000191
-rw-rw---- 1 mysql mysql 1007M Jul  4 12:37 db1079-bin.001159
-rw-rw---- 1 mysql mysql 1001M Jul  4 20:12 db1079-bin.001160
-rw-rw---- 1 mysql mysql 1001M Jul  5 07:30 db1079-bin.001161
-rw-rw---- 1 mysql mysql 1001M Jul  5 16:20 db1079-bin.001162
-rw-rw---- 1 mysql mysql 1001M Jul  5 19:06 db1079-bin.001163
-rw-rw---- 1 mysql mysql 1002M Jul  5 20:34 db1079-bin.001164
-rw-rw---- 1 mysql mysql 1011M Jul  5 21:39 db1079-bin.001165
-rw-rw---- 1 mysql mysql 1002M Jul  5 22:28 db1079-bin.001166
-rw-rw---- 1 mysql mysql 1012M Jul  5 23:29 db1079-bin.001167
-rw-rw---- 1 mysql mysql 1003M Jul  6 00:44 db1079-bin.001168
-rw-rw---- 1 mysql mysql 1001M Jul  6 04:25 db1079-bin.001169
-rw-rw---- 1 mysql mysql 1012M Jul  6 09:41 db1079-bin.001170
-rw-rw---- 1 mysql mysql 1001M Jul  6 10:27 db1079-bin.001171
-rw-rw---- 1 mysql mysql 1005M Jul  6 11:37 db1079-bin.001172
-rw-rw---- 1 mysql mysql 1001M Jul  6 15:44 db1079-bin.001173
-rw-rw---- 1 mysql mysql 1001M Jul  7 03:16 db1079-bin.001174
-rw-rw---- 1 mysql mysql 1004M Jul  7 12:02 db1079-bin.001175
-rw-rw---- 1 mysql mysql 1002M Jul  7 15:14 db1079-bin.001176
-rw-rw---- 1 mysql mysql 1001M Jul  8 01:07 db1079-bin.001177
-rw-rw---- 1 mysql mysql 1001M Jul  8 09:29 db1079-bin.001178
-rw-rw---- 1 mysql mysql 1001M Jul  8 16:03 db1079-bin.001179
-rw-rw---- 1 mysql mysql 1005M Jul  9 01:31 db1079-bin.001180
-rw-rw---- 1 mysql mysql 1001M Jul  9 12:04 db1079-bin.001181
-rw-rw---- 1 mysql mysql 1001M Jul  9 16:54 db1079-bin.001182
-rw-rw---- 1 mysql mysql 1001M Jul 10 01:01 db1079-bin.001183
-rw-rw---- 1 mysql mysql 1001M Jul 10 09:53 db1079-bin.001184
-rw-rw---- 1 mysql mysql 1001M Jul 10 14:36 db1079-bin.001185
-rw-rw---- 1 mysql mysql 1001M Jul 10 15:46 db1079-bin.001186
-rw-rw---- 1 mysql mysql 1001M Jul 10 16:51 db1079-bin.001187
-rw-rw---- 1 mysql mysql 1001M Jul 10 18:07 db1079-bin.001188
-rw-rw---- 1 mysql mysql 1001M Jul 10 19:22 db1079-bin.001189
-rw-rw---- 1 mysql mysql 1009M Jul 10 21:46 db1079-bin.001190
-rw-rw---- 1 mysql mysql 1001M Jul 11 04:52 db1079-bin.001191
-rw-rw---- 1 mysql mysql  238M Jul 11 07:27 db1079-bin.001192
-rw-rw---- 1 mysql mysql   31M Jul 11 07:38 db1079-bin.001193
-rw-rw---- 1 mysql mysql   25M Jul 11 07:38 db1079-relay-bin.000002
-rw-rw---- 1 mysql mysql  1.1G Jul 11 14:21 s7-bin.000001
-rw-rw---- 1 mysql mysql  1.1G Jul 11 17:57 s7-bin.000002
-rw-rw---- 1 mysql mysql  1.1G Jul 11 23:32 s7-bin.000003
-rw-rw---- 1 mysql mysql  1.1G Jul 12 04:51 s7-bin.000004
-rw-rw---- 1 mysql mysql  1.1G Jul 12 11:23 s7-bin.000005
-rw-rw---- 1 mysql mysql  1.1G Jul 12 17:49 s7-bin.000006
-rw-rw---- 1 mysql mysql  1.1G Jul 12 20:07 s7-bin.000007
-rw-rw---- 1 mysql mysql  1.1G Jul 13 00:23 s7-bin.000008
-rw-rw---- 1 mysql mysql  1.1G Jul 13 04:32 s7-bin.000009
-rw-rw---- 1 mysql mysql  1.1G Jul 13 09:16 s7-bin.000010
-rw-rw---- 1 mysql mysql  1.1G Jul 13 15:19 s7-bin.000011
-rw-rw---- 1 mysql mysql  1.1G Jul 13 21:19 s7-bin.000012
-rw-rw---- 1 mysql mysql  1.1G Jul 13 23:45 s7-bin.000013
-rw-rw---- 1 mysql mysql  1.1G Jul 14 09:17 s7-bin.000014
-rw-rw---- 1 mysql mysql  1.1G Jul 14 15:50 s7-bin.000015
-rw-rw---- 1 mysql mysql  1.1G Jul 15 02:05 s7-bin.000016
-rw-rw---- 1 mysql mysql  1.1G Jul 15 09:27 s7-bin.000017
-rw-rw---- 1 mysql mysql  1.1G Jul 15 15:34 s7-bin.000018
-rw-rw---- 1 mysql mysql  1.1G Jul 15 22:57 s7-bin.000019
-rw-rw---- 1 mysql mysql  1.1G Jul 16 08:07 s7-bin.000020
-rw-rw---- 1 mysql mysql  1.1G Jul 16 17:15 s7-bin.000021
-rw-rw---- 1 mysql mysql  1.1G Jul 16 22:10 s7-bin.000022
-rw-rw---- 1 mysql mysql  1.1G Jul 17 06:42 s7-bin.000023
-rw-rw---- 1 mysql mysql  1.1G Jul 17 15:22 s7-bin.000024
-rw-rw---- 1 mysql mysql  1.1G Jul 17 18:29 s7-bin.000025
-rw-rw---- 1 mysql mysql  1.1G Jul 18 03:41 s7-bin.000026
-rw-rw---- 1 mysql mysql  1.1G Jul 18 15:37 s7-bin.000027
-rw-rw---- 1 mysql mysql  1.1G Jul 19 02:30 s7-bin.000028
-rw-rw---- 1 mysql mysql  1.1G Jul 19 11:02 s7-bin.000029
-rw-rw---- 1 mysql mysql  1.1G Jul 19 18:00 s7-bin.000030
-rw-rw---- 1 mysql mysql  1.1G Jul 20 00:01 s7-bin.000031
-rw-rw---- 1 mysql mysql  1.1G Jul 20 07:05 s7-bin.000032
-rw-rw---- 1 mysql mysql  1.1G Jul 20 13:16 s7-bin.000033
-rw-rw---- 1 mysql mysql  1.1G Jul 20 21:01 s7-bin.000034
-rw-rw---- 1 mysql mysql  1.1G Jul 21 04:59 s7-bin.000035
-rw-rw---- 1 mysql mysql  1.1G Jul 21 13:30 s7-bin.000036
-rw-rw---- 1 mysql mysql  1.1G Jul 21 20:17 s7-bin.000037
-rw-rw---- 1 mysql mysql  1.1G Jul 22 05:29 s7-bin.000038
-rw-rw---- 1 mysql mysql  1.1G Jul 22 14:55 s7-bin.000039
-rw-rw---- 1 mysql mysql  1.1G Jul 22 23:45 s7-bin.000040
-rw-rw---- 1 mysql mysql  1.1G Jul 23 08:46 s7-bin.000041
-rw-rw---- 1 mysql mysql  1.1G Jul 23 17:29 s7-bin.000042
-rw-rw---- 1 mysql mysql  1.1G Jul 24 02:50 s7-bin.000043
-rw-rw---- 1 mysql mysql  1.1G Jul 24 09:49 s7-bin.000044
-rw-rw---- 1 mysql mysql  1.1G Jul 24 13:50 s7-bin.000045
-rw-rw---- 1 mysql mysql  1.1G Jul 24 17:50 s7-bin.000046
-rw-rw---- 1 mysql mysql  1.1G Jul 24 22:24 s7-bin.000047
-rw-rw---- 1 mysql mysql  1.1G Jul 25 04:53 s7-bin.000048
-rw-rw---- 1 mysql mysql  1.1G Jul 25 15:45 s7-bin.000049
-rw-rw---- 1 mysql mysql  1.1G Jul 26 00:55 s7-bin.000050
-rw-rw---- 1 mysql mysql  1.1G Jul 26 10:59 s7-bin.000051
-rw-rw---- 1 mysql mysql  1.1G Jul 26 17:36 s7-bin.000052
-rw-rw---- 1 mysql mysql  1.1G Jul 27 02:56 s7-bin.000053
-rw-rw---- 1 mysql mysql  1.1G Jul 27 09:59 s7-bin.000054
-rw-rw---- 1 mysql mysql  1.1G Jul 27 16:55 s7-bin.000055
-rw-rw---- 1 mysql mysql  1.1G Jul 28 02:07 s7-bin.000056
-rw-rw---- 1 mysql mysql  1.1G Jul 28 13:52 s7-bin.000057
-rw-rw---- 1 mysql mysql  195M Jul 28 15:28 s7-bin.000058


root@db1102:/srv# ls -lhR | grep  "\-bin." | grep -v db1102 | egrep "M|G" | wc -l
260

I think those can be simply removed from each shard directory.

Related Objects
Search...

Status	Assigned	Task
Resolved	jcrespo	T140788 Labs databases rearchitecture (tracking)
Resolved	jcrespo	T153058 LabsDB infrastructure pending work
Resolved	jcrespo	T182450 db1102 (sanitarium) filling up (WAS: Clean up old binlogs from db1102 (sanitarium multi-instance))

Event Timeline

• Marostegui renamed this task from Clean up old binlogs from db1102 to Clean up old binlogs from db1102 (sanitarium multi-instance).Dec 8 2017, 5:39 PM

• Marostegui triaged this task as Medium priority.

• Marostegui created this task.

• Marostegui moved this task from Triage to In progress on the DBA board.

I think this is a generalized problem, and we should detect it globally, get a clean up list (but on a separate ticket with lower precedence).

We could also change the procedure to remove all binlogs on RESET SLAVE ALL;

jcrespo claimed this task.Dec 11 2017, 8:52 AM

$ du -hs sqldata*
649G    sqldata.s2
895G    sqldata.s4
539G    sqldata.s6
739G    sqldata.s7
$ df -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/sda1               37G  9.2G   26G  27% /

I will create a separate task for other hosts.

root@db1102:~# df -hT /srv
Filesystem            Type  Size  Used Avail Use% Mounted on
/dev/mapper/tank-data xfs   3.6T  2.8T  856G  77% /srv

Yes, sorry, wrong grep.

jcrespo mentioned this in T182563: Identify and delete non-useful files on database hosts taking space unnecesarily.Dec 11 2017, 9:37 AM

db1102 is back into almost 90% usage.
I will start defragmenting all the tables there across all the shards. But maybe we need to consider altering them again and going from KEY_BLOCK_SIZE=8 to 4.

Also the following tables are not compressed on s4:

mysql:root@localhost [information_schema]> select TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT from TABLES where ROW_FORMAT not like 'Compressed' and TABLE_SCHEMA like '%wik%';
+--------------+-----------------------+------------+
| TABLE_SCHEMA | TABLE_NAME            | ROW_FORMAT |
+--------------+-----------------------+------------+
| commonswiki  | bv2017_edits          | Compact    |
| commonswiki  | comment               | Compact    |
| commonswiki  | content               | Compact    |
| commonswiki  | content_models        | Compact    |
| commonswiki  | filearchive           | Compact    |
| commonswiki  | image_comment_temp    | Compact    |
| commonswiki  | ip_changes            | Compact    |
| commonswiki  | ipblocks              | Compact    |
| commonswiki  | linter                | Compact    |
| commonswiki  | logging               | Compact    |
| commonswiki  | oldimage              | Compact    |
| commonswiki  | pagelinks             | Compact    |
| commonswiki  | protected_titles      | Compact    |
| commonswiki  | recentchanges         | Compact    |
| commonswiki  | revision_comment_temp | Compact    |
| commonswiki  | searchindex           | Dynamic    |
| commonswiki  | slot_roles            | Compact    |
| commonswiki  | slots                 | Compact    |
| commonswiki  | templatelinks         | Compact    |
+--------------+-----------------------+------------+
19 rows in set (0.01 sec)

So I will try to compress those too (or check why they failed) at least the big ones like logging, pagelinks etc

• Marostegui renamed this task from Clean up old binlogs from db1102 (sanitarium multi-instance) to db1102 (sanitarium) filling up (WAS: Clean up old binlogs from db1102 (sanitarium multi-instance)).Jan 21 2018, 12:01 PM

Mentioned in SAL (#wikimedia-operations) [2018-01-21T12:03:14Z] <marostegui> Defragment s2 on db1102 - T182450

I just realised that for some reason s6 is not fully compressed:

mysql:root@localhost [information_schema]> select TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT from TABLES where ROW_FORMAT not like 'Compressed' and TABLE_SCHEMA like '%wik%';
+--------------+--------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME               | ROW_FORMAT |
+--------------+--------------------------+------------+
| frwiki       | abuse_filter             | Compact    |
| frwiki       | abuse_filter_action      | Compact    |
| frwiki       | abuse_filter_history     | Compact    |
| frwiki       | abuse_filter_log         | Compact    |
| frwiki       | archive_save             | Compact    |
| frwiki       | babel                    | Compact    |
| frwiki       | betafeatures_user_counts | Compact    |
| frwiki       | bv2011_edits             | Compact    |
| frwiki       | bv2013_edits             | Compact    |
| frwiki       | bv2015_edits             | Compact    |
| frwiki       | bv2017_edits             | Compact    |
| frwiki       | category                 | Compact    |
| frwiki       | categorylinks            | Compact    |
| frwiki       | change_tag               | Compact    |
| frwiki       | click_tracking_events    | Compact    |
| frwiki       | comment                  | Compact    |
| frwiki       | content                  | Compact    |
| frwiki       | content_models           | Compact    |
| frwiki       | externallinks            | Compact    |
| frwiki       | filearchive              | Compact    |
| frwiki       | flaggedimages            | Compact    |
| frwiki       | flaggedpage_config       | Compact    |
| frwiki       | flaggedpage_pending      | Compact    |
| frwiki       | flaggedpages             | Compact    |
| frwiki       | flaggedrevs              | Compact    |
| frwiki       | flaggedrevs_promote      | Compact    |
| frwiki       | flaggedrevs_statistics   | Compact    |
| frwiki       | flaggedrevs_tracking     | Compact    |
| frwiki       | flaggedtemplates         | Compact    |
| frwiki       | geo_tags                 | Compact    |
| frwiki       | global_block_whitelist   | Compact    |
| frwiki       | image                    | Compact    |
| frwiki       | image_comment_temp       | Compact    |
| frwiki       | imagelinks               | Compact    |
| frwiki       | interwiki                | Dynamic    |
| frwiki       | ip_changes               | Compact    |
| frwiki       | ipblocks                 | Compact    |
| frwiki       | iwlinks                  | Compact    |
| frwiki       | l10n_cache               | Compact    |
| frwiki       | langlinks                | Compact    |
| frwiki       | linter                   | Compact    |
| frwiki       | localisation             | Compact    |
| frwiki       | logging                  | Compact    |
| frwiki       | logging_pre_1_10         | Compact    |
| frwiki       | math                     | Compact    |
| frwiki       | mathoid                  | Compact    |
| frwiki       | module_deps              | Compact    |
| frwiki       | msg_resource_links       | Compact    |
| frwiki       | oldimage                 | Compact    |
| frwiki       | ores_classification      | Compact    |
| frwiki       | ores_model               | Compact    |
| frwiki       | page                     | Compact    |
| frwiki       | page_props               | Compact    |
| frwiki       | page_restrictions        | Compact    |
| frwiki       | pagelinks                | Compact    |
| frwiki       | pif_edits                | Compact    |
| frwiki       | povwatch_log             | Compact    |
| frwiki       | povwatch_subscribers     | Compact    |
| frwiki       | protected_titles         | Compact    |
| frwiki       | recentchanges            | Compact    |
| frwiki       | redirect                 | Compact    |
| frwiki       | revision                 | Compact    |
| frwiki       | revision_comment_temp    | Compact    |
| frwiki       | searchindex              | Dynamic    |
| frwiki       | site_identifiers         | Compact    |
| frwiki       | site_stats               | Compact    |
| frwiki       | sites                    | Compact    |
| frwiki       | slot_roles               | Compact    |
| frwiki       | slots                    | Compact    |
| frwiki       | tag_summary              | Compact    |
| frwiki       | templatelinks            | Compact    |
| frwiki       | transcode                | Compact    |
| frwiki       | updatelog                | Compact    |
| frwiki       | user                     | Compact    |
| frwiki       | user_former_groups       | Compact    |
| frwiki       | user_groups              | Compact    |
| frwiki       | user_properties          | Compact    |
| frwiki       | valid_tag                | Compact    |
| frwiki       | wbc_entity_usage         | Compact    |
| jawiki       | abuse_filter             | Compact    |
| jawiki       | abuse_filter_action      | Compact    |
| jawiki       | abuse_filter_history     | Compact    |
| jawiki       | abuse_filter_log         | Compact    |
| jawiki       | archive                  | Compact    |
| jawiki       | babel                    | Compact    |
| jawiki       | betafeatures_user_counts | Compact    |
| jawiki       | bv2011_edits             | Compact    |
| jawiki       | bv2013_edits             | Compact    |
| jawiki       | bv2015_edits             | Compact    |
| jawiki       | bv2017_edits             | Compact    |
| jawiki       | category                 | Compact    |
| jawiki       | categorylinks            | Compact    |
| jawiki       | change_tag               | Compact    |
| jawiki       | click_tracking_events    | Compact    |
| jawiki       | comment                  | Compact    |
| jawiki       | content                  | Compact    |
| jawiki       | content_models           | Compact    |
| jawiki       | externallinks            | Compact    |
| jawiki       | filearchive              | Compact    |
| jawiki       | geo_tags                 | Compact    |
| jawiki       | global_block_whitelist   | Compact    |
| jawiki       | image                    | Compact    |
| jawiki       | image_comment_temp       | Compact    |
| jawiki       | imagelinks               | Compact    |
| jawiki       | interwiki                | Dynamic    |
| jawiki       | ip_changes               | Compact    |
| jawiki       | ipblocks                 | Compact    |
| jawiki       | iwlinks                  | Compact    |
| jawiki       | l10n_cache               | Compact    |
| jawiki       | langlinks                | Compact    |
| jawiki       | linter                   | Compact    |
| jawiki       | localisation             | Compact    |
| jawiki       | logging                  | Compact    |
| jawiki       | logging_pre_1_10         | Compact    |
| jawiki       | math                     | Compact    |
| jawiki       | mathoid                  | Compact    |
| jawiki       | module_deps              | Compact    |
| jawiki       | msg_resource_links       | Compact    |
| jawiki       | oldimage                 | Compact    |
| jawiki       | page                     | Compact    |
| jawiki       | page_props               | Compact    |
| jawiki       | page_restrictions        | Compact    |
| jawiki       | pagelinks                | Compact    |
| jawiki       | pif_edits                | Compact    |
| jawiki       | povwatch_log             | Compact    |
| jawiki       | povwatch_subscribers     | Compact    |
| jawiki       | protected_titles         | Compact    |
| jawiki       | recentchanges            | Compact    |
| jawiki       | redirect                 | Compact    |
| jawiki       | revision                 | Compact    |
| jawiki       | revision_comment_temp    | Compact    |
| jawiki       | searchindex              | Dynamic    |
| jawiki       | site_identifiers         | Compact    |
| jawiki       | site_stats               | Compact    |
| jawiki       | sites                    | Compact    |
| jawiki       | slot_roles               | Compact    |
| jawiki       | slots                    | Compact    |
| jawiki       | tag_summary              | Compact    |
| jawiki       | templatelinks            | Compact    |
| jawiki       | transcode                | Compact    |
| jawiki       | updatelog                | Compact    |
| jawiki       | user                     | Compact    |
| jawiki       | user_former_groups       | Compact    |
| jawiki       | user_groups              | Compact    |
| jawiki       | user_properties          | Compact    |
| jawiki       | valid_tag                | Compact    |
| jawiki       | wbc_entity_usage         | Compact    |
| jawiki       | wikilove_log             | Compact    |
| ruwiki       | abuse_filter             | Compact    |
| ruwiki       | abuse_filter_action      | Compact    |
| ruwiki       | abuse_filter_history     | Compact    |
| ruwiki       | abuse_filter_log         | Compact    |
| ruwiki       | archive                  | Compact    |
| ruwiki       | babel                    | Compact    |
| ruwiki       | betafeatures_user_counts | Compact    |
| ruwiki       | bv2011_edits             | Compact    |
| ruwiki       | bv2013_edits             | Compact    |
| ruwiki       | bv2015_edits             | Compact    |
| ruwiki       | bv2017_edits             | Compact    |
| ruwiki       | category                 | Compact    |
| ruwiki       | categorylinks            | Compact    |
| ruwiki       | change_tag               | Compact    |
| ruwiki       | click_tracking_events    | Compact    |
| ruwiki       | comment                  | Compact    |
| ruwiki       | content                  | Compact    |
| ruwiki       | content_models           | Compact    |
| ruwiki       | ep_articles              | Compact    |
| ruwiki       | ep_cas                   | Compact    |
| ruwiki       | ep_courses               | Compact    |
| ruwiki       | ep_events                | Compact    |
| ruwiki       | ep_instructors           | Compact    |
| ruwiki       | ep_oas                   | Compact    |
| ruwiki       | ep_orgs                  | Compact    |
| ruwiki       | ep_revisions             | Compact    |
| ruwiki       | ep_students              | Compact    |
| ruwiki       | ep_users_per_course      | Compact    |
| ruwiki       | externallinks            | Compact    |
| ruwiki       | filearchive              | Compact    |
| ruwiki       | flaggedimages            | Compact    |
| ruwiki       | flaggedpage_config       | Compact    |
| ruwiki       | flaggedpage_pending      | Compact    |
| ruwiki       | flaggedpages             | Compact    |
| ruwiki       | flaggedrevs              | Compact    |
| ruwiki       | flaggedrevs_promote      | Compact    |
| ruwiki       | flaggedrevs_statistics   | Compact    |
| ruwiki       | flaggedrevs_stats        | Fixed      |
| ruwiki       | flaggedrevs_stats2       | Fixed      |
| ruwiki       | flaggedrevs_tracking     | Compact    |
| ruwiki       | flaggedtemplates         | Compact    |
| ruwiki       | geo_tags                 | Compact    |
| ruwiki       | global_block_whitelist   | Compact    |
| ruwiki       | image                    | Compact    |
| ruwiki       | image_comment_temp       | Compact    |
| ruwiki       | imagelinks               | Compact    |
| ruwiki       | interwiki                | Dynamic    |
| ruwiki       | ip_changes               | Compact    |
| ruwiki       | ipblocks                 | Compact    |
| ruwiki       | iwlinks                  | Compact    |
| ruwiki       | l10n_cache               | Compact    |
| ruwiki       | langlinks                | Compact    |
| ruwiki       | linter                   | Compact    |
| ruwiki       | logging                  | Compact    |
| ruwiki       | logging_pre_1_10         | Compact    |
| ruwiki       | math                     | Compact    |
| ruwiki       | mathoid                  | Compact    |
| ruwiki       | module_deps              | Compact    |
| ruwiki       | msg_resource_links       | Compact    |
| ruwiki       | oldimage                 | Compact    |
| ruwiki       | ores_classification      | Compact    |
| ruwiki       | ores_model               | Compact    |
| ruwiki       | page                     | Compact    |
| ruwiki       | page_props               | Compact    |
| ruwiki       | page_restrictions        | Compact    |
| ruwiki       | pagelinks                | Compact    |
| ruwiki       | pif_edits                | Compact    |
| ruwiki       | povwatch_log             | Compact    |
| ruwiki       | povwatch_subscribers     | Compact    |
| ruwiki       | protected_titles         | Compact    |
| ruwiki       | recentchanges            | Compact    |
| ruwiki       | redirect                 | Compact    |
| ruwiki       | revision                 | Compact    |
| ruwiki       | revision_comment_temp    | Compact    |
| ruwiki       | searchindex              | Dynamic    |
| ruwiki       | site_identifiers         | Compact    |
| ruwiki       | site_stats               | Compact    |
| ruwiki       | sites                    | Compact    |
| ruwiki       | slot_roles               | Compact    |
| ruwiki       | slots                    | Compact    |
| ruwiki       | tag_summary              | Compact    |
| ruwiki       | templatelinks            | Compact    |
| ruwiki       | transcode                | Compact    |
| ruwiki       | updatelog                | Compact    |
| ruwiki       | user                     | Compact    |
| ruwiki       | user_former_groups       | Compact    |
| ruwiki       | user_groups              | Compact    |
| ruwiki       | user_properties          | Compact    |
| ruwiki       | valid_tag                | Compact    |
| ruwiki       | wbc_entity_usage         | Compact    |
+--------------+--------------------------+------------+
238 rows in set (0.01 sec)

So I will start with that after s2 has finished defragmenting

Mentioned in SAL (#wikimedia-operations) [2018-01-21T17:21:25Z] <marostegui> Compress frwiki and jawiki on db1102 - T182450

Mentioned in SAL (#wikimedia-operations) [2018-01-22T06:18:41Z] <marostegui> Compress ruwiki on db1102 - T182450

We should be good for quite lots of months now

root@db1102:~# df -hT /srv
Filesystem            Type  Size  Used Avail Use% Mounted on
/dev/mapper/tank-data xfs   3.6T  2.6T  1.1T  70% /srv

I have also realised that some wikis in s7 are not compressed. So we can save more space there. I am not going to compress them now, because we can use them as a safety net for when this host starts to fill up again, we have some room compressing those wikis before we have to order a new server.
If I compress all them now, once we get the alert again, will have no much room left :-)

db1102 (sanitarium) filling up (WAS: Clean up old binlogs from db1102 (sanitarium multi-instance))Closed, ResolvedPublicActions

Description

Related ObjectsSearch...

Event Timeline

db1102 (sanitarium) filling up (WAS: Clean up old binlogs from db1102 (sanitarium multi-instance))
Closed, ResolvedPublic
Actions

Related Objects
Search...