959801 - Create method to obtain the last 12 months of data from scheduler and status DBs

Status: RESOLVED WONTFIX

(Data & BI Services Team :: DB: MySQL, task)

Description

[Armen [:armenzg]]

It would be great to have a script that would allow us to grab a dump of the scheduler and status DBs containing the last 12 months of data.

I could also be happy with having a method to obtain the whole dump if it is too complicated.

On another note, do we have staging versions of these databases?

Comment 1

[Armen [:armenzg]]

Also, what is the correct way to set it up locally on my machine? I'm running Linux.

Thanks!

Comment 2

[Sheeri Cabral [:sheeri]]

Note that sentry_log keeps only the latest 2 hours of data.

Other than that, what data do you want? Just exports of the buildbot and buildbot_schedulers databases? I can give those to you in a .sql.gz file, where you can decompress and load in the data to a MySQL instance (you just have to give it a database, e.g. mysql my_bouncer_db < foo.sql)

And where's a good place to share it? We can get that shared automatically, you just tell us how often you want the export done - daily, weekly, etc. Also, how long to keep the exports on the machine (if it's something IT manages) so that we don't fill up disk space.

Comment 3

[Armen [:armenzg]]

(In reply to Sheeri Cabral [:sheeri] from comment #2)
> Note that sentry_log keeps only the latest 2 hours of data.
> 
I don't know what sentry_log is.

> Other than that, what data do you want? Just exports of the buildbot and
> buildbot_schedulers databases? I can give those to you in a .sql.gz file,
> where you can decompress and load in the data to a MySQL instance (you just
> have to give it a database, e.g. mysql my_bouncer_db < foo.sql)
> 
Yes, that's right. I don't need data further than a year. I've been told that the data is close to 100GB IIUC.

> And where's a good place to share it? We can get that shared automatically,
> you just tell us how often you want the export done - daily, weekly, etc.
> Also, how long to keep the exports on the machine (if it's something IT
> manages) so that we don't fill up disk space.

I don't know where to place it. I'm fine if it gets overwritten with the latest dump.
Weekly would be great.

Many thanks!

Comment 4

[Sheeri Cabral [:sheeri]]

Attachment: buildbot schema

sentry_log is one of the tables in buildbot. 

I'm looking for exactly what you need . . . do you know exactly the tables you want? Most of the tables are MyISAM so there's not a clear link among all of them...Some, but not all, the tables have build_id or builder_id.

The "builds" table has "starttime" and "endtime" - does it matter which we use for "a year old"?

I'm attaching the schemas here for you so you can let me know exactly what data you need and don't need for your purposes, so I can get the proper exports.

Comment 5

[Sheeri Cabral [:sheeri]]

Attachment: buildbot schedulers schema

Comment 6

[Armen [:armenzg]]

It will take me a while to figure out the relationships.
In any case I could work on it from the DB dump from bug 965960.

Thanks sheeri for your help!