Hey All,
I'm testing the upgrade path for my mailserver from 2.2.11 to the 3.1.17
on a staging server and I'm a little confused. I run 2_2-3_0.mysql over
my 160GB production email database.
http://git.dbmail.eu/paul/dbmail/tree/sql/mysql/2_2-3_0.mysql
I'd like someone to confirm that 3_0_2-3_1_0.mysql at this time only
re-creates a view that's already newly created in 2_2-3_0.mysql, and as
such there is no need to run it.
I noticed 2_2-3_0.mysql is taking a rather long time on my 160GB
database, and I'm looking to into it to see if there is a way to speed
up the process.
My understanding of the script:
First the script drops, creates, calls and drops a stored procedure to
iterate all foreign keys and drops them
then it removes all indexes on the dbmail_sievescripts table.
Then 'DELETE FROM dbmail_referencesfield' is called. (inside a
transaction? why?) I'm assuming the author used DELETE FROM to make sure
all cascading tables would follow, but as all foreign keys have been
dropped by the stored procedure ran earlier, why not call 'TRUNCATE
TABLE dbmail_referencesfield'?
Some tables are dropped, (dbmail_ccfield ... dbmail_tofield)
dbmail_envelope is truncated. Some are dropped and (re?)created
(dbmail_mimeparts...dbmail_header)
The rest of the file is about column changes, re-adding foreign keys and
creating views for imap.
My comments on it:
One of the queries executed by the stored procedure is 'ALTER TABLE
dbmail_messageblks DROP FOREIGN KEY dbmail_messageblks_ibfk_1'. This
query copies all data in the dbmail_messageblks table, which is 140GB in
my case, into a temporary table. This means on upgrading a dbmail
install you should never have more than about 50% of the disk used on
your mysql database server. I couldn't find this information anywhere.
(I looked in wiki, mailinglist & UPGRADING text file) I think this
should be documented! Also this takes a while. My server is 6GB in
recreating dbmail_messageblk, which took about 2000 seconds. At this
pace 140G will take > 12 hours.
I noticed dbmail_messageblks data is never written into the
dbmail_mimeparts, table. My guess is that the current dbmail code can
still handle dbmail_messageblks, and conversion is not required.
Why is this foreign key removed? My guess is that it the consequence of
the column change for dbmail_physmessage.id, from a BIGINT(21) to
BIGINT(20) UNSIGNED. To me this seems an arbitrary change. What happens
if I keep dbmail_physmessage.id a BIGINT(21)? I'm sure you agree this
change in signedness does not warrant a day of mailserver downtime if it
can be avoided.
Greetings, Casper
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
