You have fixed a short term problem but forgot about long term
consequences.
Email system is a delete/insert/update/select heavy system, if you have
lots of users. Going with innodb you get concurrent reads + writes. I
can do most of the dbmail-util cleanup functions without having to lock
tables. A user can be downloading/updating delete flags of 50K pop3
messages via dbmail-pop3d and I don't have to worry about it locking up
the system and slowing inserts other updates (pop/imap users) to a
turtle pace. Can't say the same for myisam.
Xing
Simon wrote:
S> ) TYPE=MERGE UNION(dbmail_messageblks_part1, dbmail_messageblks_part2)
S> INSERT_METHOD=LAST;
It's rather interesting decision. I wonder is it real to build
something like an array of merged dbmail_messageblks_partX tables?
In previous maillists I have asked about big dbmail_messageblks table
(now it's around 50Gb) and there was no decision to make OPTIMIZE
TABLE dbmail_messageblks because of it's locking for a long time.
As I understand we can make 10 or X number of dbmail_messageblks
tables and we shall be able to make OPTIMIZE of each table separately.
What do you think about it?
Here is some more thinking on this:
Because we used INSERT_METHOD=LAST, the data is always inserted into
the last _partX table. It states in the mysql manual "MERGE TABLES:
Differences in table options such as AVG_ROW_LENGTH, MAX_ROWS, or
PACK_KEYS do not matter"... so i can just continue to add 4GB (or what
ever) _partX's as and when needed. This way, if a table becomes
corrupted, i only have to remove the offending 4GB table and repair,
and then re-insert into the MERGE - shure, some old IMAP data is temp
unavailable, but new mail is fine and the old mail will re-appear once
the repaired table is put back into the MERGE.
You can then OPTIMISE each partition thus:
FLUSH TABLES, then copy the _partX file, then optimize it then stop
the DB, put back in-place optimized _part1, then restart. This means
that delivery to _part2 (or what ever your last part is) continues
unabated. You need to do the optimize and restore IN-BETWEEN
dbmail-maintenance runs though. e.g. the deletes need to not occur
from the messageblks table.
Any comments, bad or good are very welcome and needed. :)
(Thanks again to Mark for help with this)
Simon
_______________________________________________
Dbmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
