Best way to convert MySQL bayes DB to InnoDB?

[Jason Levine <sp...@list.masshole.us>]

Howdy -- I have a question I've been hunting for the answer to for a
while, but haven't found anything definitive.  I've been running
SpamAssassin for about two years now, with Sendmail as my MTA and
spamass-milter funneling all the mail into SpamAssassin, and with a MySQL
database for user prefs, bayes, and aut-owhitelisting.  As my setup has
slowed a bit, I went looking for ways to make my life happier, and noticed
a lot of lock contention on the database -- as it turns out, all the
tables are MyISAM tables, and that means that there are locks galore on
the table as things update.  This led me to the desire to move my tables
to InnoDB, to take care of the more robust row-level locking.  However, I
appear to have nearly 17 million records in my bayes_token table, and
another 750K in my bayes_seen table, and converting those to InnoDB might
take a LONG LONG TIME... so I'm looking for the best way to do this.

As I see it, I have three options:

1. Export and import the bayes records using sa-learn;
2. Alter the MySQL tables directly to InnoDB;
3. Create new InnoDB tables, and fill them via "INSERT INTO new_table
SELECT * FROM old_table".  (This is probably the exact same thing MySQL
does when it does an ALTER TABLE, though.)

Is there a plus side or minus side to any of these methods?

In a related vein, should I have expected my tables to be that big?  17M
records struck me as a whole bunch more than I would have thought, but
that might just be my naivete.

Thanks!
Jason

Re: Best way to convert MySQL bayes DB to InnoDB?

[Magnus Holmgren <ho...@lysator.liu.se>]

Jason Levine wrote:
> Howdy -- I have a question I've been hunting for the answer to for a
> while, but haven't found anything definitive.  I've been running
> SpamAssassin for about two years now, with Sendmail as my MTA and
> spamass-milter funneling all the mail into SpamAssassin, and with a MySQL
> database for user prefs, bayes, and aut-owhitelisting.  As my setup has
> slowed a bit, I went looking for ways to make my life happier, and noticed
> a lot of lock contention on the database -- as it turns out, all the
> tables are MyISAM tables, and that means that there are locks galore on
> the table as things update.  This led me to the desire to move my tables
> to InnoDB, to take care of the more robust row-level locking.  However, I
> appear to have nearly 17 million records in my bayes_token table, and
> another 750K in my bayes_seen table, and converting those to InnoDB might
> take a LONG LONG TIME... so I'm looking for the best way to do this.

Oh, 17 million records isn't that much ... I think. I can't se how your
other methods could be faster (but I don't have 10 years of work
experience with databases). However, you might want to drop the indices
before converting, and recreate them afterwards (I don't know how ALTER
TABLE works internally in this case but if it's as you suspect, it can
make a massive impact).

-- 
Magnus Holmgren