james at host-it
Jan 13, 2009, 1:25 AM
Post #1 of 4
(743 views)
Permalink
|
|
DBMail Foreign keys and MySQL 5.1
|
|
Hey guys (& girls),
We recently decided to take the following paths of upgrade on our main db
server.
* Upgrade to debian etch adm64 (64bit) - from 32bit
* Upgrade to MySQL 5.1 - from MySQL 5.0
Unfortunately we had extreme performance issues when running under MySQL 5.1
with an increased amount of read waits using up threads and only turning off
thread_concurrency checks compensated this until the system grinds to a
halt. In any case, to calm the large influx of calls and support tickets we
were getting we performed emergency maintenance and dumped back to MySQL 5.0
(on 64bit) with exactly the same my.cnf options and we've since had no
issues and thread waits are negligible at the time of writing this.
At this time, our advice would be to avoid MySQL 5.1 from our p.o.v.
Now to my question for the list. We have found that our dbmail database is
missing a large amount of indexes and foreign keys. We have since added in
any missing indexes, however, due to the vast size of dbmail_messageblks and
dbmail_headervalue we cannot at this time permit increased downtime to add
the missing foreign keys.
Are we likely to have orphaned data by not having the 'delete on cascade'
foreign keys on these two tables? Or does dbmail-util compensate for this
and remove any orphaned data?
Just so you know our production dbmail service has approximately 6000 users.
The dbmail_messageblks table after a fresh mysqldump is '75G 2009-01-13
09:19 dbmail_messageblks.ibd'
Kind Regards
James Greig - AS33854 - AS45014
Nuco Technologies Ltd
james [at] host-it
www.nucotechnologies.com
Tel. 0870 165 1300
Nuco Technologies Ltd is a company registered in England and Wales with
company number 04470751
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail
|
