Page MenuHomePhabricator

testreduce_vd database in m5 still in use?
Closed, ResolvedPublic

Description

I was wondering if the testreduce_vd database that lives in m5 is still in use.
From what I can see it hasn't received any new data since April 2019
If it is not in use, can it be dropped?

root@db1133:/srv/sqldata/testreduce_vd# ls -lhSrh *.ibd
-rw-rw---- 1 mysql mysql  96K May 23  2018 perfstats.ibd
-rw-rw---- 1 mysql mysql  96K Apr  5  2019 commits.ibd
-rw-rw---- 1 mysql mysql  17M Apr  6  2019 pages.ibd
-rw-rw---- 1 mysql mysql 132M Apr  6  2019 stats.ibd
-rw-rw---- 1 mysql mysql 196M Apr  6  2019 results.ibd

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 17 2020, 10:10 AM
Marostegui triaged this task as Medium priority.Feb 17 2020, 10:11 AM
Marostegui moved this task from Triage to Blocked external/Not db team on the DBA board.

We will use it again once we start running regular visual diff runs between Parsoid output and core PHP parser output (sometime in the next few months). If we change anything in the infrastructure and how / where we run it, will let you know.

Excellent! Thank you!
As a follow up, maybe not super related, but I also saw:

root@db1133:/srv/sqldata/testreduce_0715# ls -lShr
total 520G
-rw-rw---- 1 mysql mysql   54 May 23  2018 db.opt
-rw-rw---- 1 mysql mysql 1.1K May 23  2018 commits.frm
-rw-rw---- 1 mysql mysql 1.2K May 23  2018 perfstats.frm
-rw-rw---- 1 mysql mysql 1.6K May 23  2018 results.frm
-rw-rw---- 1 mysql mysql 1.7K May 23  2018 stats.frm
-rw-rw---- 1 mysql mysql 2.9K May 23  2018 pages.frm
-rw-rw---- 1 mysql mysql 128K Feb  7 23:10 commits.ibd
-rw-rw---- 1 mysql mysql  64M Feb  8 10:14 pages.ibd
-rw-rw---- 1 mysql mysql  12G Feb  8 10:07 stats.ibd
-rw-rw---- 1 mysql mysql  85G Feb  8 10:07 perfstats.ibd
-rw-rw---- 1 mysql mysql 424G Feb  8 10:07 results.ibd

That results table is quite big already, what's the future of it? What's the usage it has? Can it be purged? Maybe it has lots of fragmentation (happens the table has lots of INSERT+DELETE)?

root@db2135.codfw.wmnet[testreduce_0715]> select count(*) from results;
+----------+
| count(*) |
+----------+
| 66946261 |
+----------+

Do I have permissions to delete rows from this database? But ,perfstats, stats, results are all keyed on commit hashes in the commits table. The commit table has one commit hash per test run and has a timestamp for ordering.. So, all but the most recent 50 (arbitrarily picked) test runs can be purged from all tables.

That said, maybe we can use this opportunity to simply reset test pages to start with a totally new set of test pages. At that time, we can do a complete reset of the database. But, it will take us a few weeks to get around to that.

Do I have permissions to delete rows from this database? But ,perfstats, stats, results are all keyed on commit hashes in the commits table. The commit table has one commit hash per test run and has a timestamp for ordering.. So, all but the most recent 50 (arbitrarily picked) test runs can be purged from all tables.

Not sure which user you use, but testreduce user does have grants for DELETE

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `testreduce`.* TO 'testreduce'@'10.64.48.43'      |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `testreduce_vd`.* TO 'testreduce'@'10.64.48.43'   |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `testreduce_0715`.* TO 'testreduce'@'10.64.48.43' |

Even if you deleted rows, I would still need to optimize the table to be able to reclaim the disk space, so we'd need to coordinate for that too :-)

That said, maybe we can use this opportunity to simply reset test pages to start with a totally new set of test pages. At that time, we can do a complete reset of the database. But, it will take us a few weeks to get around to that.

We are not in a rush here, but something to keep in mind and to work on eventually, to avoid the table growing a lot more.
Thank you!

`

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON testreduce.* TO 'testreduce'@'10.64.48.43'

Oh, I forgot the original testreduce database is still around! If so, you could truncate it completely (it has test run data from before 2015) and we could initialize that database with a fresh set of test pages and once ready, switch over to using that database instead of 'testreduce_0715'. After a few new test runs with this db and we are happy with it, we could simply drop the testreduce_0715 database altogether.

`

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON testreduce.* TO 'testreduce'@'10.64.48.43'

Oh, I forgot the original testreduce database is still around! If so, you could truncate it completely (it has test run data from before 2015) and we could initialize that database with a fresh set of test pages and once ready, switch over to using that database instead of 'testreduce_0715'. After a few new test runs with this db and we are happy with it, we could simply drop the testreduce_0715 database altogether.

Actually it is not present anymore:

root@db1133:~# mysql -A testreduce
ERROR 1049 (42000): Unknown database 'testreduce'
root@db1133:~#

I like your idea of initializing it, and removing testreduce_0715. Do you need me to create the database itself? And tables?
Thank you!

@ssastry any follow up on this task?

@ssastry any follow up on this task?

Sorry .. got distracted by everything else. So, yes, can you create the database and tables (same schema as testreduce_0715). https://github.com/wikimedia/mediawiki-services-parsoid-testreduce/blob/master/server/sql/create_everything.mysql in case you need it.

ssastry moved this task from Needs Triage to Testing on the Parsoid board.

Mentioned in SAL (#wikimedia-operations) [2020-03-24T06:16:57Z] <marostegui> Create empty database testreduce on m5 master T245408

@ssastry any follow up on this task?

Sorry .. got distracted by everything else. So, yes, can you create the database and tables (same schema as testreduce_0715). https://github.com/wikimedia/mediawiki-services-parsoid-testreduce/blob/master/server/sql/create_everything.mysql in case you need it.

No worries!
So I have created this:

root@db1133.eqiad.wmnet[testreduce]> show tables;
+----------------------+
| Tables_in_testreduce |
+----------------------+
| commits              |
| pages                |
| perfstats            |
| results              |
| stats                |
+----------------------+
5 rows in set (0.00 sec)
root@cumin1001:~# for i in ` mysql.py -hdb1133 -BN -A testreduce -e "show tables"`; do echo $i; mysql.py -hdb1133 -A testreduce -e "select count(*) from $i";done
commits
+----------+
| count(*) |
+----------+
|        0 |
+----------+
pages
+----------+
| count(*) |
+----------+
|        0 |
+----------+
perfstats
+----------+
| count(*) |
+----------+
|        0 |
+----------+
results
+----------+
| count(*) |
+----------+
|        0 |
+----------+
stats
+----------+
| count(*) |
+----------+
|        0 |
+----------+

The grants are still the same:

GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, INSERT, SELECT, UPDATE ON `testreduce`.* TO 'testreduce'@'10.64.32.180';
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, INSERT, SELECT, UPDATE ON `testreduce`.* TO 'testreduce'@'10.64.48.43';
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, INSERT, SELECT, UPDATE ON `testreduce`.* TO 'testreduce'@'10.64.48.94';

From what I can see the @ssastry user has the following grants:

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON `testreduce_0715`.* TO 'ssastry'@'10.64.32.180';
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON `testreduce_vd`.* TO 'ssastry'@'10.64.32.180';
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON `testreduce_0715`.* TO 'ssastry'@'10.64.48.43';
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON `testreduce_vd`.* TO 'ssastry'@'10.64.48.43';
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON `testreduce_0715`.* TO 'ssastry'@'10.64.48.94';
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON `testreduce_vd`.* TO 'ssastry'@'10.64.48.94';

The testreduce database isn't included on those, do you want me to include it too?
Thank you!

...
The testreduce database isn't included on those, do you want me to include it too?

Thanks for initializing this db! Yes, please!

It may take a week or two to get my act together to initialize this db with an update set of test titles. After I switch over, we can then get rid of the testreduce_0715 db. Probably 2-3 weeks from now before I get there since I'll work on this on low priority.

...
The testreduce database isn't included on those, do you want me to include it too?

Thanks for initializing this db! Yes, please!

Done!

| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `testreduce`.* TO 'ssastry'@'10.64.32.180'                         |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `testreduce`.* TO 'ssastry'@'10.64.48.43'                         |

It may take a week or two to get my act together to initialize this db with an update set of test titles. After I switch over, we can then get rid of the testreduce_0715 db. Probably 2-3 weeks from now before I get there since I'll work on this on low priority.

No problem, just let me know when you want me to remove testreduce_0715.
Thank you for working on this!

I don't see to have access to the new testreduce db.

ssastry@scandium:~/new_testdb$ mysql testreduce
ERROR 1044 (42000): Access denied for user 'ssastry'@'10.64.48.94' to database 'testreduce'

ssastry@scandium:~/new_testdb$ mysql testreduce_0715
Reading table information for completion of table and column names
...
...

Can you take a look? I've got new set of test titles ready to initialize this new db.

I have included that IP so it should be fixed now:

| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `testreduce`.* TO 'ssastry'@'10.64.48.94'                         |

Thanks for working on this.

@ssastry were you able to test the GRANTs?

Hey yes. it worked and we are now working with the new test database.

In a week's time, you can drop the old tesreduce_0715 database and resolve this "repurposed" ticket :)

Cool! So can I drop testreduce_vd and testreduce_0715 databases?
Nothing seems to be writing:

root@db1133:/srv/sqldata/testreduce_vd# ls -lhSrh *.ibd
-rw-rw---- 1 mysql mysql  96K May 23  2018 perfstats.ibd
-rw-rw---- 1 mysql mysql  96K Apr  5  2019 commits.ibd
-rw-rw---- 1 mysql mysql  17M Apr  6  2019 pages.ibd
-rw-rw---- 1 mysql mysql 132M Apr  6  2019 stats.ibd
-rw-rw---- 1 mysql mysql 196M Apr  6  2019 results.ibd

And last write to testreduce_0715 was May 10th

root@db1133:/srv/sqldata/testreduce_0715# ls -lShr
total 548G
-rw-rw---- 1 mysql mysql   54 May 23  2018 db.opt
-rw-rw---- 1 mysql mysql 1.1K May 23  2018 commits.frm
-rw-rw---- 1 mysql mysql 1.2K May 23  2018 perfstats.frm
-rw-rw---- 1 mysql mysql 1.6K May 23  2018 results.frm
-rw-rw---- 1 mysql mysql 1.7K May 23  2018 stats.frm
-rw-rw---- 1 mysql mysql 2.9K May 23  2018 pages.frm
-rw-rw---- 1 mysql mysql 128K May  9 15:15 commits.ibd
-rw-rw---- 1 mysql mysql  64M May 10 07:27 pages.ibd
-rw-rw---- 1 mysql mysql  13G May 10 06:50 stats.ibd
-rw-rw---- 1 mysql mysql  88G May 10 06:50 perfstats.ibd
-rw-rw---- 1 mysql mysql 449G May 10 06:50 results.ibd
Marostegui moved this task from Blocked external/Not db team to In progress on the DBA board.

In a week's time, you can drop the old tesreduce_0715 database and resolve this "repurposed" ticket :)

Haha thanks! I will drop it on Monday (don't want to drop things on a Friday!)

Please do not drop the testreduce_vd database! :) Only testreduce_0715. We are yet to make a decision where and how we will run our visual diff test runs ( T252483 )

Cool, thank you!

Mentioned in SAL (#wikimedia-operations) [2020-06-01T04:54:35Z] <marostegui> Drop testreduce_0715 from m5 master T245408

Marostegui closed this task as Resolved.Jun 1 2020, 4:55 AM

testreduce_0715 has been dropped:

root@db1133.eqiad.wmnet[(none)]> drop database if exists testreduce_0715;
Query OK, 5 rows affected (1 min 0.27 sec)