123143 - add option to syncshadowdb to use mysqlhotcopy instead of mysqldumpx

Status: RESOLVED WONTFIX

(Bugzilla :: Bugzilla-General, defect)

Description

[Myk Melez [:myk] [@mykmelez]]

"syncshadowdb -syncall" (i.e. the shadow database rebuild) has been regularly
failing on b.m.o. lately.  One of the reasons has been a lack of disk space for
storing the temporary dump file.  Even when syncshadowdb succeeds, however, it
takes at least 45-50 minutes to finish (during which time b.m.o. is unavailable)
without compression of the dump file and 1 hour 20-25 minutes to finish with
compression.

mysqlhotcopy is a utility distributed with MySQL that copies raw database files
from one location to another.  This obviates the need for a dump file and is
also much faster (should take no more than a few minutes).  syncshadowdb should
use mysqlhotcopy instead of a dump file to rebuild the shadow database.

Comment 1

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v1: uses mysqlhotcopy and hacks mysqlhotcopy to purge shadowlog

This patch makes syncshadowdb use mysqlhotcopy to rebuild the database and
includes a hacked version of mysqlhotcopy that purges the shadowlog once the
database has been rebuilt.  It would be nice to be able to avoid having to hack
mysqlhotcopy; if we can't we should consider building the functionality in
mysqlhotcopy directly into syncshadowdb.

Note that you have to run "syncshadowdb -syncall" as the mysql user in order to
get this to work, since the rebuilt shadow_bugs database ends up owned by the
user account under which mysqlhotcopy runs, and if that user is not mysql then
mysqld will not have access to the database.

Comment 2

[Myk Melez [:myk] [@mykmelez]]

Comment on attachment 67584 [details] [diff] [review]
patch v1: uses mysqlhotcopy and hacks mysqlhotcopy to purge shadowlog

Needs-working this patch.  I'm going to 1. remove the hack to mysqlhotcopy 
by forcing Bugzilla to be shut down when mysqlhotcopy is used, 2. make use
of mysqlhotcopy an optional method of rebuilding rather than replacing the 
current mechanism entirely, and 3. add an switch for whether or not to
delete the shadow database files before copying over them for installations
that don't have the same space problems as b.m.o.

Comment 3

[Bradley Baetz (:bbaetz)]

How long does mysqlhotcopy take? Can you run it as a test on a backup? The
reason I ask is that w/o a versioning database, I think this just grabs read
locks on all the tables. Looking at the code for v 3.23.41 seems to back me up
on that. OTOH, it does do a plain copy, which could be faster. You'll still need
to do a dump for backups though, won't you?

Comment 4

[Myk Melez [:myk] [@mykmelez]]

Yes, we'll still do a dump for backups, but this already happens as a separate
process.

You are correct that mysqlhotcopy just grabs read locks on the tables in the
bugs database.  When I make mysqlhotcopy an optional rebuild method I am going
to force Bugzilla to be shut down if this method is selected, since this is the
only way I can effectively ensure that no updates get applied after the copy is
over but before I can purge the shadowlog.

Comment 5

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v2: implements as optional feature

This version of the patch implements mysqlhotcopy as an option available via a
switch on the command line: "-hotcopy".  An additional switch "-dropold"
specifies whether or not to drop the old shadow tables before recreating them
(necessary with mysqldump but optional with mysqlhotcopy, and it's useful to
keep them around if possible in case the rebuild fails and you have to go back
to the old tables).

Comment 6

[Myk Melez [:myk] [@mykmelez]]

Upgrading severity and setting milestone.  I know Bugzilla 2.16 is frozen, but
syncshadowdb problems have been serious and debilitating for b.m.o., and it
would be really good to get these fixes in before 2.16 ships.

Even if this doesn't make the milestone, however, it would be very helpful for a
few people besides me to review it and test it before I put it up on b.m.o. 
Please take a moment to take a look.

Comment 7

[Myk Melez [:myk] [@mykmelez]]

Adding appropriate keywords and clarifying summary.

Comment 8

[Gervase Markham [:gerv]]

Comment on attachment 67896 [details] [diff] [review]
patch v2: implements as optional feature

As far as I can see, this patch looks extremely sane. I'm pretty sure it still
does the same thing in the normal case.

However, I haven't tested it and, given its nature, therefore can't really give
it r= yet.

Myk - could you outline a testing and verification procedure that leaves the
relevant DB intact?

Gerv

Comment 9

[Dave Miller [:justdave]]

Comment on attachment 67896 [details] [diff] [review]
patch v2: implements as optional feature

I'm not sure if anyone outside of b.m.o has the ability to test this without a
lot of setup work.  I would say as long as you can guarantee it doesn't break
the old way (since you're only adding it as an option) to just go ahead and go
for it.  My visual inspection of the code tells me this is the case.

You can consider this an r= justdave on those grounds.

Comment 10

[Gervase Markham [:gerv]]

Comment on attachment 67896 [details] [diff] [review]
patch v2: implements as optional feature

r=gerv if Myk confirms Dave's statement. Otherwise, I'd like to test it, if
instructions can be provided.

Gerv

Comment 11

[Myk Melez [:myk] [@mykmelez]]

To test this patch, run the following commands:

./syncshadowdb -syncall -shutdown
mysqldump shadow_bugs > mysqldump.sql
./syncshadowdb -syncall -hotcopy
mysqldump shadow_bugs > hotcopy1.sql
./syncshadowdb -syncall -hotcopy -dropold
mysqldump shadow_bugs > hotcopy2.sql
diff mysqldump.sql hotcopy1.sql
diff mysqldump.sql hotcopy2.sql

If the two diff commands show no relevant difference between the files, then the
patch theoretically works.  On the other hand, mysqld core dumped shortly after
this version of syncshadowdb finished running on b.m.o. last week, so there may
be a problem in it somewhere.

Comment 12

[Gervase Markham [:gerv]]

I'm having serious trouble turning the shadowdb on. Can someone else do this
briefly in their params and confirm it does actually work?

Gerv

Comment 13

[Gervase Markham [:gerv]]

I got it turned on by naming it test_shadow, as the Bugzilla Guide said I could
bypass permissions that way. Then I had Perl problems. Having solved those, I
found the following in mysqlhotcopy:

"WARNING: THIS IS VERY MUCH A FIRST-CUT ALPHA. Comments/patches welcome."

(This is version 1.11)

After that, is still had permission problems <sigh>.

Oh, check the wretched patch in! :-)

Gerv

Comment 14

[Dave Miller [:justdave]]

Comment on attachment 67896 [details] [diff] [review]
patch v2: implements as optional feature

revoking my second-review on the grounds of allowing someone more qualified to
review it, since I'm not very familiar with the shadowdb operation

Comment 15

[James Cox]

i'll be happy to give this second review. the usage of mysqlhotcopy here is
good, and will make the update process ten times faster.

nice work myk!

Comment 16

[James Cox]

Comment on attachment 67896 [details] [diff] [review]
patch v2: implements as optional feature

making second review official. :)

Comment 17

[Myk Melez [:myk] [@mykmelez]]

Can someone besides me do testing on a big install with lots of traffic?  mysqld
ate memory and dumped core after I used this on b.m.o., then several days later
database corruption occurred (which hadn't happened in the previous nine months
I've been babysitting b.m.o.).  I'm not going to check this in until more
testing has been done on it.

Comment 18

[Dave Miller [:justdave]]

I don't know as anyone will have time to adaquately test this before 2.16 ships.
 Bumping to 2.18, feel free to change it back if you decide it's safe.

Comment 19

[Dave Miller [:justdave]]

Comment on attachment 67896 [details] [diff] [review]
patch v2: implements as optional feature

-second-review
+needs-work

on behalf of Myk's prior comments.

Comment 20

[Bradley Baetz (:bbaetz)]

syncshadowdb is gone.

Comment 21

[Dave Miller [:justdave]]

clearing target on INVALID/WORKSFORME/WONTFIX/DUPLICATE so they'll show up as
untriaged if they get reopened.