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
