On Wed, 2003-10-15 at 13:37, Chris Mason wrote:
> Hello everyone,
>
> On mysql transactional tables, a commit is implied on each insert
> operation unless you turn off autocommit.
>
> Most operations that involves multiple writes to the database can be
> made faster by grouping them inside a transaction, since it will mean
> fewer commits overall.
Ok, here's a patch for dbmail 1.2 cvs that implements the transaction
begin/commit for mysql during pipe.c:insert_messages, and the imap
copy/append message commands. This will only improve performance for
innodb/bdb table types, it should change nothing for the
non-transactional tables.
It also changes the mysql quota funcs to skip computing the sum of all
message sizes when the user has no quota. I'm not sure why there are
two different funcs that do the same quota calculations, but I think
I've got them both right.
It does not touch postgres, so applying this for postgres won't even
compile. Use at your own risk, I haven't tried huge attachments with it
yet.
-chris
diff -u -r1.41 db.h
--- db.h 2003/08/27 09:38:56 1.41
+++ db.h 2003/10/16 13:48:55
@@ -119,6 +119,8 @@
int db_get_nofity_address(u64_t userid, char **notify_address);
int db_get_reply_body(u64_t userid, char **body);
+int db_start_transaction(void);
+int db_stop_transaction(void);
u64_t db_get_mailboxid(u64_t useridnr, const char *mailbox);
u64_t db_get_useridnr(u64_t messageidnr);
u64_t db_get_message_mailboxid(u64_t messageidnr);
diff -u -r1.95 pipe.c
--- pipe.c 2003/03/17 16:04:08 1.95
+++ pipe.c 2003/10/16 13:48:56
@@ -192,6 +192,8 @@
/* get the first target address */
tmp=list_getstart(users);
+ if (db_start_transaction())
+ trace(TRACE_STOP, "insert_messages(): error starting transaction");
while (tmp!=NULL)
{
/* loops all mailusers and adds them to the list */
@@ -528,6 +530,9 @@
*((u64_t *)tmp->data));
}
}
+
+ if (db_stop_transaction())
+ trace(TRACE_STOP, "insert_messages(): error ending transaction");
trace (TRACE_DEBUG,"insert_messages(): Freeing memory blocks");
diff -u -r1.75 dbmysql.c
--- mysql/dbmysql.c 2003/10/13 11:42:29 1.75
+++ mysql/dbmysql.c 2003/10/16 13:48:58
@@ -123,7 +123,31 @@
return 0;
}
+int db_start_transaction(void)
+{
+ snprintf(query, DEF_QUERYSIZE, "BEGIN");
+
+ if (db_query(query) == -1)
+ {
+ /* query failed */
+ trace (TRACE_ERROR, "transaction begin failed");
+ return -1;
+ }
+ return 0;
+}
+int db_stop_transaction(void)
+{
+ snprintf(query, DEF_QUERYSIZE, "COMMIT");
+
+ if (db_query(query) == -1)
+ {
+ /* query failed */
+ trace (TRACE_ERROR, "transaction commit failed");
+ return -1;
+ }
+ return 0;
+}
/*
* returns the number of bytes used by user userid or
* (u64_t)(-1) on dbase failure
@@ -1299,12 +1323,17 @@
u64_t currmail_size = 0, maxmail_size = 0, j, n;
*useridnr = db_get_useridnr (message_idnr);
-
+ maxmail_size = auth_getmaxmailsize(*useridnr);
+ if (maxmail_size == 0)
+ {
+ trace (TRACE_INFO,"db_check_sizelimit(): mailboxsize of useridnr %llu is zero, skipping check\n", *useridnr);
+ return 0;
+ }
+
/* checking current size */
snprintf (query, DEF_QUERYSIZE,"SELECT mailbox_idnr FROM mailboxes WHERE owner_idnr = %llu",
*useridnr);
-
if (db_query(query) != 0)
{
trace (TRACE_ERROR,"db_check_sizelimit(): could not execute query [%s]\n",
@@ -1350,17 +1379,10 @@
mysql_free_result(res);
- /* current mailsize from INBOX is now known, now check the maxsize for this user */
- maxmail_size = auth_getmaxmailsize(*useridnr);
-
-
trace (TRACE_DEBUG, "db_check_sizelimit(): comparing currsize + blocksize [%llu], "
"maxsize [%llu]\n",
currmail_size, maxmail_size);
-
- /* currmail already represents the current size of messages from this user */
-
if (((currmail_size) > maxmail_size) && (maxmail_size != 0))
{
trace (TRACE_INFO,"db_check_sizelimit(): mailboxsize of useridnr %llu exceed with %llu bytes\n",
@@ -2332,7 +2354,7 @@
/*
- * db_imap_append_msg()
+ * __db_imap_append_msg()
*
* inserts a message
*
@@ -2343,7 +2365,7 @@
* 2 mail quotum exceeded
*
*/
-int db_imap_append_msg(const char *msgdata, u64_t datalen, u64_t mboxid, u64_t uid)
+static int __db_imap_append_msg(const char *msgdata, u64_t datalen, u64_t mboxid, u64_t uid)
{
char timestr[30];
time_t td;
@@ -2512,6 +2534,29 @@
return 0;
}
+/*
+ * db_imap_append_msg()
+ *
+ * inserts a message
+ *
+ * returns:
+ * -1 serious dbase/memory error
+ * 0 ok
+ * 1 invalid msg
+ * 2 mail quotum exceeded
+ *
+ */
+int db_imap_append_msg(const char *msgdata, u64_t datalen, u64_t mboxid, u64_t uid)
+{
+ int ret;
+ if (db_start_transaction())
+ return -1;
+ ret = __db_imap_append_msg(msgdata, datalen, mboxid, uid);
+ if (db_stop_transaction() && ret == 0)
+ ret = -1;
+ return ret;
+}
+
/*
* db_insert_message_complete()
@@ -3212,7 +3257,7 @@
* copies a msg to a specified mailbox
* returns 0 on success, -1 on failure, -2 on quotum exceeded/quotum would exceed
*/
-int db_copymsg(u64_t msgid, u64_t destmboxid)
+static int __db_copymsg(u64_t msgid, u64_t destmboxid)
{
u64_t newid,tmpid, curr_quotum, userid, maxmail, msgsize;
time_t td;
@@ -3227,13 +3272,6 @@
return -1;
}
- curr_quotum = db_get_quotum_used(userid);
- if (curr_quotum == -1 || curr_quotum == -2)
- {
- trace(TRACE_ERROR, "db_copymsg(): error fetching used quotum for user [%llu]", userid);
- return -1;
- }
-
maxmail = auth_getmaxmailsize(userid);
if (maxmail == -1)
{
@@ -3243,6 +3281,13 @@
if (maxmail > 0)
{
+ curr_quotum = db_get_quotum_used(userid);
+ if (curr_quotum == -1 || curr_quotum == -2)
+ {
+ trace(TRACE_ERROR, "db_copymsg(): error fetching used quotum for user [%llu]", userid);
+ return -1;
+ }
+
if (curr_quotum >= maxmail)
{
trace(TRACE_INFO, "db_copymsg(): quotum already exceeded\n");
@@ -3407,6 +3452,22 @@
return 0; /* success */
}
+/*
+ * db_copymsg()
+ *
+ * copies a msg to a specified mailbox
+ * returns 0 on success, -1 on failure, -2 on quotum exceeded/quotum would exceed
+ */
+int db_copymsg(u64_t msgid, u64_t destmboxid)
+{
+ int ret;
+ if (db_start_transaction())
+ return -1;
+ ret = __db_copymsg(msgid, destmboxid);
+ if (db_stop_transaction() && ret == 0)
+ ret = -1;
+ return ret;
+}
/*
* db_getmailboxname()
