614675 - Move prod -> stage DB sync cron job to the nighttime

Status: RESOLVED FIXED

(mozilla.org Graveyard :: Server Operations, task)

Description

[Stephen Donner [:stephend] Not actively reading bugmail]

It's during business hours when I'm filing this, so hopefully someone can take a look :-)

http://input.stage.mozilla.com/en-US/ is down frequently today, contains stale data on its homepage, and queries often individual Opinions often time out trying to load.

See the screencast for examples:

http://screencast.com/t/XumSQbr5

Comment 1

[Jeremy Orem [:oremj]]

Is this still happening?

Comment 2

[Stephen Donner [:stephend] Not actively reading bugmail]

(In reply to comment #1)
> Is this still happening?

While it's not timing out, I still see "1 day ago" on the dashboard/homepage -- Michael/Dave, any ideas?

Comment 3

[Dave Dash [:davedash, :dd] (assign all bugs to mbrandt)]

When does the cron run?

Comment 4

[Jeremy Orem [:oremj]]

Which cron populates this?

Comment 5

[Fred Wenzel [:wenzel]]

That's what we'd like to know. We assume it is a cron job on one of the database machines. It is not part of the application's crons on the app node.

Comment 6

[Michael Kurze [:michaelk]]

I am not sure if sites data is okay on stage. It might quite possibly not be okay due to bug 612610. 

I just can’t seem to even connect to stage to verify this.

Comment 7

[Michael Kurze [:michaelk]]

Okay, was able to browse staging now. Sites data is definitely not up to date, and (from the logs on the metrics machine) bug 612610 is to blame for that. I hope I can attend that tonight. This should not have anything to do with the timeout problems though.

Comment 8

[Jeremy Orem [:oremj]]

Dave, did you set up the DB sync for input? I'm not sure what is going on here.

Comment 9

[Stephen Donner [:stephend] Not actively reading bugmail]

Downgrading this from critical; this, in fact, probably isn't a bug.  In our last Input meeting, we talked through it, and, iirc, here's what's happening:

* because we have a dump from prod -> staging, the timestamp reflects when that data was submitted on prod, not staging
* since we don't yet have any automated tests that submit to *staging*, it appears the data is stale

Comment 10

[Dave Miller [:justdave]]

I have no idea how the site itself is set up.  This needs debugging from the app side if it's still an issue.  I concur with comment 9 about the reason for stale data.  That's populated from production daily.

Comment 11

[Fred Wenzel [:wenzel]]

To summarize: We would like to know what cron job is run to copy the DB from prod to stage at about 9am Pacific every day. Once we've identified the cron job, we will add it to the code repository for reference, and change its running time to the nighttime as opposed to the morning.

Comment 12

[Corey Shields [:cshields]]

This has been a fun one to trace down..

The dump happens on tm-backup02 at 0700 every day

From there, a cron runs every 3 hours at 17 after the hour (so the next chance being 9:17 every morning) to copy the dump tarball to dracula.

From there, a cron job run every hour at :25 pushes the dump to tm-stage01-master01 (so this occurs at 9:25).

On tm-stage01-master01 there is a cron job that runs every 30 mins (/etc/cron.d/import-databases which fires /root/bin/import-input-db) that eventually imports the data.

Does that help at all? :) Now, most of these scripts handle a multitude of databases in each step (not just input), so moving any times may have a ripple effect.

Comment 13

[Dave Dash [:davedash, :dd] (assign all bugs to mbrandt)]

What the hell?

Here's what we want:

The last cron hat puts the data into the staging database - can that be done once a week?  Like on Monday at midnight?

Comment 14

[Corey Shields [:cshields]]

Ok, this is done per comment 13:

in tm-stage01-master01:/etc/cron.d/import-databases I've changed the following

30 * * * * root /root/bin/import-input-db

to

0 0 * * 2 root /root/bin/import-input-db