Several of my clients use DBMail with mail loads ranging from small (a few users) to massive (100s of users). We use MySQL 4.0.x (x >= 13) in all of these places and haven't noticed any corruption but that says nothing about what you've experienced as the two loads are very, very different to say the least.
Thanks for sharing your experiences! At a bare minimum, I'll know that 3.23.49 is a bad version of MySQL. Regards, Chris On Sun, 2004-02-15 at 22:47, John Hansen wrote: > No, aspseek doesn't use transactions as mysql 3.23 in the standard > distro doesn't have them. > The version I wrote for postgres does however. > > I'm not sure if the mysql mailing lists was consulted, but it was a > known problem on the aspseek mailing lists. > > ... John > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > On Behalf Of Chris Nolan > Sent: Sunday, February 15, 2004 9:56 PM > To: dbmail-dev@dbmail.org > Subject: RE: [Dbmail-dev] some speed tests > > > Hmm...does ASPSeek utilise transactions at all? > > The fact that it happened with both points to some glaring bug inside > MySQL's upper layers or a hardware problem, but since PostgreSQL was > fine (I'm assuming on the same hardware), that's a totally discounted > possibility. > > Did anyone on the MySQL mailing list give you any joy? > > Regards, > > Chris > > On Sun, 2004-02-15 at 21:40, John Hansen wrote: > > We tried both innodb and myisam with the same results, except of > > course performance suffered when using the innodb type. However both > > types resulted in corrupted tables after a few days starting from > > scratch. > > > > Following that corruption happened daily. > > > > I abandoned mysql early last year as I rewrote the application to use > > postgresql and haven't had any problems since. > > > > Other reasons I abandoned mysql was that to my dismay I discovered > > that statements such as select .. From table for update; is allowed. > > This should definately throw an error since for update is not > > supported and thus would lead to inconsistent data. > > > > I have not attempted to use mysql 4. > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > On Behalf Of Chris Nolan > > Sent: Sunday, February 15, 2004 9:31 PM > > To: dbmail-dev@dbmail.org > > Subject: RE: [Dbmail-dev] some speed tests > > > > > > Now you've peaked my curiosity! > > > > Do you have any additional details regarding table types used and > > whether or not the same thing occurs in the 4.0.x series? > > > > Regards, > > > > Chris > > > > On Sun, 2004-02-15 at 21:14, John Hansen wrote: > > > Download and install aspseek-1.2.10 from http://www.aspseek.org Run > > > this against mysql 3.23.49 > > > > > > Run it for a few days, indexing about 1million urls, > > > Then keep reindexing them and watch your database corrupt... > > > > > > Regards, > > > > > > John > > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] > > > On Behalf Of Chris Nolan > > > Sent: Sunday, February 15, 2004 6:37 PM > > > To: dbmail-dev@dbmail.org > > > Subject: RE: [Dbmail-dev] some speed tests > > > > > > > > > Table corruptions? > > > > > > I'd be very interested to hear the story around your grief, if for > > > nothing else, for reference purposes. :-) > > > > > > Regards, > > > > > > Chris > > > > > > On Sun, 2004-02-15 at 17:19, John Hansen wrote: > > > > Personally, I recent the statement; 'Considering MySQL has proven > > > > itself time and time again in terms of reliability, licencing > > > > flexibility and performance, such statements are baseless > > > > regardless > > > > > > of what arguments you make for which features'; based on > > > > experience, > > > > > > mysql proved that it was not reliable, tho performance was top of > > the > > > > line. > > > > > > > > In my opinion, if you're concerned with reliability, mysql should > > > > not > > > > be an option. I had daily table corruptions, and thus one of my > main > > > > > > tasks was to run mysql repair jobs regularly, just to keep > > everything > > > > running. > > > > > > > > Granted, postgresql has it's own problems, mainly with performance > > > > and > > > > > > > lack of a proper master / slave replication model, but that's a > > > > small > > > > price to pay for reliability. > > > > > > > > Just my $0.02 worth. > > > > > > > > Regards, > > > > > > > > John Hansen > > > > > > > > -----Original Message----- > > > > From: [EMAIL PROTECTED] > > > > [mailto:[EMAIL PROTECTED] > > > > On Behalf Of Chris Nolan > > > > Sent: Sunday, February 15, 2004 4:42 PM > > > > To: dbmail-dev@dbmail.org > > > > Subject: Re: [Dbmail-dev] some speed tests > > > > > > > > > > > > Dear Aaron, > > > > > > > > My comments are also inline. :-) > > > > > > > > On Sun, 2004-02-15 at 12:27, Aaron Stone wrote: > > > > > Comments inline... > > > > > > > > > > Chris Nolan <[EMAIL PROTECTED]> said: > > > > > > > > > > > Forgive the bluntness of the statement, but why is anyone even > > > > > > worrying about transactions as they relate to MySQL??? > > > > > > > > > > Because we are currently structured to have a single set of > > > > > mid-level database operations that are translated into specific > > > > > low-level database function calls. The upside is that there > > > > > isn't any > > database > > > > > > > > specific handling outside of these mid-level functions. The > > > > > downside > > > > > > > > is that we need to make sure that function calls for certain > > > features > > > > > are consistent with analogous features in each database and that > > > they > > > > > carry with them enough information to make the appropriate > > > > > low-level > > > > > > > > call. A good example is the last inserted id number. In MySQL, > > > > > you only need the database connection identifier to get this. In > > > > > > PostgreSQL, yo need both the database connection and the table > > > > > identifier. For DBMail to have a mid-level function that worked > > for > > > > > both, we'd have to make sure that it took both arguments and > > > > > used > > > them > > > > > > > > > as needed for whichever database's low-level calls were being > > > > > used. (Those were off the top of my head, so they may be > > > > > incorrect, but > > > they > > > > > > > > > do illustrate my point.) > > > > > > > > I've looked through the DBMail source code on a few occassions and > > > > > even released a dodgy tool to convert Cyrus mailboxes to DBMail a > > > > while ago. The fact that I can use the calls present in the DB > > modules > > > > > > > and avoid having to write queries by hand for each different DB is > > > > an > > > > excellent feature of the DBMail API. I have nothing negative to > say > > > > here at all. > > > > > > > > > > > > > > > COMMIT in MySQL is passed to the table handler. In the case of > > > > > > MyISAM tables, the handler disregards the statement. For > InnoDB > > > > > > and BDB > > > > tables, > > > > > > COMMIT acts as it does in PostgreSQL. > > > > > > > > > > So then we have THREE different configurations to consider, and > > > > > need to be sure to design the mid-level interface appropriately. > > > > > > > > My statement was meant to say that the MyISAM table handler will > > > > just > > > > disregard BEGIN and COMMIT statements, just as it parses but > ignores > > > > > > CHECK and FOREIGN KEY constraints in table creation, thus > > illustrating > > > > > > > that you wouldn't need to add a mysql-with-transactions directory > > > > to the source tree. > > > > > > > > > > > > > > > So various people in this thread implying that MySQL isn't > > > > > > really > > > > > > a database need to do some more reading. > > > > > > > > > > If you're referring to my suggestion that the transaction > > > > > functions are a noop for MySQL, then you're reading too > deeply... > > > > > I just > > > didn't > > > > > realize that InnoDB would handle transactions entirely normally. > > > > > > > > My apologies, I was not referring to you at all! Yourself and your > > > > > collegues have provided the world with a very, very funky mail > > > > repository! > > > > > > > > I was referring to someone who said in the body of their message > > > > posted to another branch of this thread "the supposed database > > mysql". > > > > > > > Considering MySQL has proven itself time and time again in terms > > > > of reliability, licencing flexibility and performance, such > > > > statements are baseless regardless of what arguments you make for > > > > which > > features. > > > > > > > For example, Visual FoxPro's backend is technically a database but > > > > > provides nothing in the way of a privilege system (you need write > > > > access to record locking, so filesystem-level controls are no good > > > > > either). > > > > > > > > > > > > > > > In summary, just encapsulate everything in transaction blocks > > > > > > and the underlaying database will act appropriately. > > > > > > > > > > Right. That's what we're talking about. > > > > > > > > > > Is the entire point of your comment that we can safely use > > > > > > > > > > BEGIN; > > > > > > > > > > .. whatever dbmail does... > > > > > > > > > > COMMIT; > > > > > > > > > > regardless of whether or not we know if the host database > > > > > actually > > > > > > > supports these keywords? You could have just said that. > > > > > > > > I could have, but then I would have risked putting an end to the > > > > thread. > > > > :-) > > > > > > > > Please accept my apology - I never meant to offend yourself or > > > > anyone > > > > else who contributes to DBMail. It seems my reading of the post I > > > > mentioned above caused me to be a bit heavier than I should have > > been. > > > > > > > > But yes, you have perfectly summarised the point of my comment. > > > > > > > > > > > Regards, > > > > > > > > > > > > Chris > > > > > > > > > > > > Aaron Stone wrote: > > > > > > > > > > > > >I don't even know where to begin in terms of designing the > > > > > > >delivery > > > > > > > > > > >chain around transactions. Could we do it as simply as adding > > > > > > >functions... > > > > > > > > > > > > > > void db_begin_transaction(void); > > > > > > > void db_flush_transaction(void); (or db_commit_...?) > > > > > > > > > > > > > >and then calling these functions before and after each major > > > > > > >section of database code? For the delivery chain, we could do > > > > > > > >it inside of insert_message(). For dbmail-smtp, this > basically > > > > > > >means > > > > > > > > > >that the execution of the whole program is within one > > > transaction. > > > > > > >For dbmail-lmtpd, it means that each message is delivered > > > > > > >within > > > a > > > > > > >transaction but the miscellaneous queries before the main > > > > > > >message > > > > > > > > > >delivery chain are not transacted. For MySQL, these functions > > > would > > > > > > > > > > >be noops. > > > > > > > > > > > > > >Thing that might work? > > > > > > > > > > > > > >Aaron > > > > > > > > > > > > > > > > > > > > >Thomas Mueller <[EMAIL PROTECTED]> said: > > > > > > > > > > > > > > > > > > > > > > > > > > > >>Hi Aaron, > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >>>Do you have any way of narrowing this down to specific > > > > > > >>>queries that are taking the longest and/or are being > executed > > > > > > > > >>>the most? > > > > > > > > > >>>That would identify which low-level database functions are > > > being > > > > > > >>>called, then we can just trace our way up the call chain to > > > > > > >>>see > > > > > > > > > >>>who's misbehaving or acting on a flawed design. Also, if > > > > > > >>>you could run similar tests against the latest 1.2, it > > > > > > >>>would help > > > to > > > > > > >>>give a frame of reference, particularly for my delivery > > > > > > >>>chain design. > > > > > > >>> > > > > > > >>> > > > > > > >>That's simple: the main design flaw (actually that's no > > > > > > >>design > > > > > > > > >>flaw I think, that's because the so called database MySQL > > > couldn't > > > > > > > > > > >>do transactions in the past) is that dbmail doesn't use > > > > > > >>transaction. > > > > > > >> > > > > > > >>Because of that AutoCommit is used and whenever dbmail does > > > > > > >>anySqlQuery Postgres does 'BEGIN; anySqlQuery; COMMIT;' - > and > > > that > > > > > > > > > > >>is terribly slow. To ensure the Durability in ACID the > > > > > > >>database has to fflush() every transaction to stable > storage! > > > > > > >>That's why there is only one solution: we have to use > > > > > > >>transaction. > > > > > > >> > > > > > > >>With transactions we could remove the integrity checks of > > > > > > >>dbmail-maintenance too, because the database guarantees > > > integrity. > > > > > > >> > > > > > > >>Anyway, I did a trace of all SQL queries when a mail is > > > > > > >>copied > > > > > > > > >>using IMAP. I got 35 SELECT, 4 INSERT, 5 UPDATE (44 db > > > operations > > > > > > >>to insert one mail?). > > > > > > >> > > > > > > >>When searching for the sequential scan I found something > > > > > > >>quite > > > > > > > > >>interesting in the docs: the planer decides for every scan > > > > > > >>if > > > > > > >>a seqScan is cheaper that an index scan, and does a seqScan > > > > > > >>even > > > if > > > > > > >>an index > > > > > > >>exists: > > > > > > >> > > > > > > >>dbmail=> explain SELECT mailbox_idnr FROM mailboxes WHERE > > > > owner_idnr=2; > > > > > > >> QUERY PLAN > > > > > > >>--------------------------------------------------------- > > > > > > >> Seq Scan on mailboxes (cost=0.00..1.06 rows=3 width=8) > > > > > > >> Filter: (owner_idnr = 2) > > > > > > >>(2 rows) > > > > > > >> > > > > > > >>The table has an index on owner_idnr. > > > > > > >> > > > > > > >>So I should repeat this test with a database with several > > > > > > >>hundred to thousand user, several dozen mailboxes for each > > > > > > >>user and several dozen mails in each mailbox to find out if > > > > > > >>all > > required > > > > > > >>indizes are there. Did anyone write a script to create such > > > > > > >>a > > > > > > >>database? > > > > > > >> > > > > > > >>But I found a strange query: > > > > > > >>SELECT mailbox_idnr FROM mailboxes WHERE mailbox_idnr = '4' > > > > > > >>AND owner_idnr = '2' mailbox_idnr is the primary key so that > > > > > > > >>could > > > be > > > > > > >>optimized to: SELECT 4 > > > > > > >>;-) > > > > > > >> > > > > > > >> > > > > > > >>I don't have a 1.2 installation, I'm sorry. > > > > > > >> > > > > > > >> > > > > > > >>-- > > > > > > >>MfG Thomas Mueller - http://www.tmueller.com for pgp key > > > > (95702B3B) > > > > > > >>_______________________________________________ > > > > > > >>Dbmail-dev mailing list > > > > > > >>Dbmail-dev@dbmail.org > > > > > > >>http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > > > Dbmail-dev mailing list > > > > > > Dbmail-dev@dbmail.org > > > > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > Dbmail-dev mailing list > > > > Dbmail-dev@dbmail.org > > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > > _______________________________________________ > > > > Dbmail-dev mailing list > > > > Dbmail-dev@dbmail.org > > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > > > > _______________________________________________ > > > Dbmail-dev mailing list > > > Dbmail-dev@dbmail.org > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > _______________________________________________ > > > Dbmail-dev mailing list > > > Dbmail-dev@dbmail.org > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > > _______________________________________________ > > Dbmail-dev mailing list > > Dbmail-dev@dbmail.org > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > _______________________________________________ > > Dbmail-dev mailing list > > Dbmail-dev@dbmail.org > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
