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
