Ilja: To truly benefit from transaction control you really need to advantage, not just by grouping DML statements, but also by use of the "rollback" statement in the case of an individual statement's failure.
Eric's first try is a quick and dirty method which manages to bundle together the three insert statements which are used for inserting data into the database, which will definitely help performance and is definitely an improvement. Best use concepts, however, dictate that transaction control really belongs to the database level, not the application level. His is, however, the "easiest" solution given the current code layout. I believe the correct solution, if it doesn't break one of the IMAP or POP3 RFC's is to call only one database function to completely manage a message insert when one is desired rather than depending upon an assorted number of these. Then transaction enable the function to respond correctly upon inserts (batch them together) and or failures (rollback everything) if and when they happen. The problem is that there are just too many "base" functions which are being used by non-database programs (pop3? and imap?, others?). Here are a few and what they currently do: db_insert_message insert a message entry only into the messages table db_update_message only updates unique_id and rfcsize db_update_message_multiple same as above but for a group of messages db_insert_message_block inserts a message block into messageblks db_insert_message_block_multiple same as above but for a group of blocks imap_append_message insert a complete message into messages and messageblks db_rollback_insert rolls back, the hard way, a complete message insert db_delete_messageblk deletes a block based upon it's messageblk_idnr db_delete_message deletes a complete message - as current rollback_insert The above should be restricted, if possible, as: low-level-db-only: _db_insert_message, _db_insert_message_block, _db_delete_message, _db_delete_message_blk transaction-control-functions (if base calls are not acceptable): _db_commit, _db_rollback, _db_rollback_insert *for non-transactional dbases _db_commit and _db_rollback could be no-ops and db_rollback_insert could be used when necessary. interface-calls: db_insert_message (i.e. imap_append_message), db_delete_message *to handle a complete message. *message pieces should never be inserted or deleted by the apps. .......... Gary .......... -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Ilja Booij Sent: Monday, November 17, 2003 4:42 AM To: [email protected] Subject: Re: [Dbmail] 2.0 and transactions Hi, On Nov 14, 2003, at 11:16 PM, Eric Soroos wrote: > > Now that I have 2.0 built, I was poking around a bit and noticed that > there are no transactions used. This will tend to hurt performance (at > least for postgresql) for inserts and that sort of thing since the > database can batch writes to the database during a transaction. Transactions are not used because MySQL ISAM tables do not support them. > > With a really basic addition of begin/commit around the calls > db_insert_message and db_insert_block in pipe.c, I saw an improvement > of about 30% in insert speed over the non-transactioned version. (run > over ~1000 messages, 6 min vs ~9 min. ) That's quite a significant speedup. It would probably be a good idea to put these functions (db_begin_transaction(), commit_transaction(), db_rollback_transaction()), in db.c, like you did. I did some test with mysql, showing no difference when using these transaction statements. It probably is safe to put the statements in there. I'm only wondering where to put the calls to these functions. In db.c (dbsearch.c & dbmsgbuf.c & authsql.c) itself, or in the functions making use of the functions in those files (like you did, by putting them in pipe.c)? any ideas? cheers, Ilja _______________________________________________ Dbmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
