Page MenuHomePhabricator
Paste P17225

Backup progress db stats
ActivePublic

Authored by jcrespo on Sep 6 2021, 11:07 AM.
Tags
None
Referenced Files
F34634091: Backup progress db stats
Sep 6 2021, 11:07 AM
Subscribers
root@db1176.eqiad.wmnet[mediabackups]> START TRANSACTION; select count(*), status_name, backup_status_name FROM files JOIN wikis ON wikis.id = files.wiki JOIN backup_status ON backup_status.id = files.backup_status JOIN file_status ON files.status = file_status.id WHERE wiki=392 GROUP BY status, backup_status; select count(*), TIMESTAMPDIFF(MINUTE, '2021-09-03 07:30:00', max(backup_time)) as time, count(*)/ TIMESTAMPDIFF(SECOND, '2021-09-03 07:30:00', max(backup_time)) as speed from backups where wiki=392 and backup_time >= '2021-09-03 07:30:00'; select count(*), sum(size) from files where wiki=392 and backup_status=3; COMMIT;
Query OK, 0 rows affected (0.001 sec)
+----------+-------------+--------------------+
| count(*) | status_name | backup_status_name |
+----------+-------------+--------------------+
| 56824185 | public | pending |
| 18000 | public | processing |
| 19428001 | public | backedup |
| 3160 | public | error |
| 839 | public | duplicate |
| 5910460 | archived | pending |
| 6551674 | deleted | pending |
+----------+-------------+--------------------+
7 rows in set (2 min 32.894 sec)
+----------+------+---------+
| count(*) | time | speed |
+----------+------+---------+
| 13157923 | 4532 | 48.3804 |
+----------+------+---------+
1 row in set (9.376 sec)
+----------+----------------+
| count(*) | sum(size) |
+----------+----------------+
| 19428001 | 85480862272126 |
+----------+----------------+
1 row in set (23.009 sec)
Query OK, 0 rows affected (0.000 sec)

Event Timeline

root@db1176.eqiad.wmnet[mediabackups]> START TRANSACTION; select count(*), sum(size), status_name, backup_status_name FROM files JOIN wikis ON wikis.id = files.wiki JOIN backup_status ON backup_status.id = files.backup_status JOIN file_status ON files.status = file_status.id WHERE wiki=392 GROUP BY status, backup_status; select count(*)/ TIMESTAMPDIFF(SECOND, '2021-09-08 00:00:00', max(backup_time)) as speed from backups where wiki=392 and backup_time >= '2021-09-08 00:00:00'; COMMIT;
Query OK, 0 rows affected (0.001 sec)

+----------+-----------------+-------------+--------------------+
| count(*) | sum(size)       | status_name | backup_status_name |
+----------+-----------------+-------------+--------------------+
| 50113185 | 230629730589356 | public      | pending            |
|    19000 |    784034990247 | public      | processing         |
| 26135930 | 112599114854081 | public      | backedup           |
|     4569 |     11997408473 | public      | error              |
|     1501 |      3694299365 | public      | duplicate          |
|  5910460 |  11836525784142 | archived    | pending            |
|  6551674 |  12521825421712 | deleted     | pending            |
+----------+-----------------+-------------+--------------------+
7 rows in set (2 min 43.480 sec)

+---------+
| speed   |
+---------+
| 49.4979 |
+---------+
1 row in set (0.821 sec)

Query OK, 0 rows affected (0.001 sec)
root@db1176.eqiad.wmnet[mediabackups]> START TRANSACTION; select count(*), sum(size), status_name, backup_status_name FROM files JOIN wikis ON wikis.id = files.wiki JOIN backup_status ON backup_status.id = files.backup_status JOIN file_status ON files.status = file_status.id WHERE wiki=392 GROUP BY status, backup_status; select count(*) / 3600  as speed from backups where wiki=392 and backup_time >= (now() - INTERVAL 1 HOUR); COMMIT;
+----------+-----------------+-------------+--------------------+
| count(*) | sum(size)       | status_name | backup_status_name |
+----------+-----------------+-------------+--------------------+
| 22725185 | 120414907211896 | public      | pending            |
|    23000 |   1453583040725 | public      | processing         |
| 53509912 | 222117806785844 | public      | backedup           |
|    13346 |     36982140787 | public      | error              |
|     2742 |      5292962270 | public      | duplicate          |
|  5910460 |  11836525784142 | archived    | pending            |
|  6551674 |  12521825421712 | deleted     | pending            |
+----------+-----------------+-------------+--------------------+
7 rows in set (2 min 45.371 sec)

+---------+
| speed   |
+---------+
| 79.9892 |
+---------+
1 row in set (0.205 sec)

Query OK, 0 rows affected (0.001 sec)