Hi all -
I was wondering whether anyone had played using MERGE tables for dbmail?
I've currently got a MyISAM table that's about 171G for messageblks - and
was looking at converting it back to Innodb -- but the size of course is
horendous, so will take hours. I've also been wanting to setup a replicated
copy for a while, but again size has been an issue (don't want 12+ hours
downtime).
So then I started thinking about MERGE tables:
The messageblks table is the prime candidate for this - as it is effectively
an insert-only table until you run the maintenance scripts, so by
potentially creating multiple tables...
E.g. MyISAM: messageblks_2004_08
MyISAM: messageblks_2004_07
MyISAM: messageblks_latest
MERGE: messageblks
... the messageblks_latest table would be the only one changing. So any
copying done of messageblks_2004_XX would be fine as the disk copy would not
be changing; which would be great for me as I could copy say 170G of old
mail over ot the slave server, and then just have a short bit of downtime to
clone the messageblks_latest table over to the slave and then start the
replication up again.
It should be able to copy a live archived table (e.g. messageblks_2004_07)
to a temporary name and then do things like OPTIMIZE TABLE and then rename
it back to the correct name, with only minimal down time (just time to flush
and lock tables and complete the table shuffle), etc.
Things like table recovery, etc would probably be better too - as only the
messageblks_latest would have changed.
---
Does anyone have any experience with this in a dbmail installation? Or
comments based on experiences elsewhere?
There no doubt are potential issues with MERGE which I'm unaware of (e.g.
more file descriptor usage, etc); but perhaps this is a good technique I can
use to get the database copied over to another server and then just copy the
messageblks_latest contents back into a central messageblk table...
Anyone's thoughts, comments appreciated.
Also - does anyone know if this sort of thing could be done with Innodb?
--
Regards,
Mark Mackay
