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 > > > >
