Re: [Dbmail-dev] webmail direct from database.
My two cents. I would also concentrate on general optimization within IMAP rather than another API. Reason being there this is no need to reinvent the wheel when there are still much to be done to improve IMAP speed especially when development resources are not exactly plentiful. I think there are only 2-3 active developers and they are already more stuff to do than man hours in my view: overworked. Instead of stored procedures, using prepared statements for both mysql/postgres I think will have a noticeable improvement. In my own web app, I have a query abstraction layer which automatically generate the proper prepared statement, only if another one has not been previous created/cached, and execute bound parameters. This method would help dbmail as it dbmail does not "kill" the mysql connection after each IMAP connection thus prepared statement caching will help plenty as the number of "repeating" queries dbmail issues is several magnitudes more than most sql based apps. With prepared statements, at least with mysql, binary protocol is used which is a bit faster since no encoding/decoding is necessary and the second time the prepared statement is run, there is no need for mysql server to parse the sql string. Dynamically generated prepared statements is much more cross-db portable than stored procedures. Xing Casper Langemeijer wrote: Another option that comes to mind is a hybrid approach... I don't know the guts of IMAP, but presumably it's plenty fast for a lot of operations. The key then would be to provide a database API for the operations it wasn't fast at. I suspect that could be substantially less than 154 queries. hm... and what interface should we use? I feel a non-standard IMAP extension coming up... It saves us the problem authenticating and the such. But what for? To have a custom webmail client that is faster than what we have now? The speed enhancements to be made in the mailclients itself far outweigh the improvement that can be made using non-standard extensions. webclients... I tried alot of them... roundcube, squirrelmail, dbwebmail. I use IlohaMail currently, because it's fast. It's *way* faster than the dbwebmail client. (I have >6000 mails in one mailbox, dbwebmail has serious O^n issues.) The best thing: All my users can use their own taste in client. The featurefreaks will use squirrelmail, designfreaks will use roundcube, speedfreaks use IlohaMail. They all use IMAP, and I'm very cool with that. Standards make it a no-brainer. I think that only if you want to build a mass mailhosting company you'll benefit from a direct connection to the db. Yes I like my users to be able to change their own passwords, manage mailfilter rules, create aliases and stuff. The interface for that is not there (except for managesieve), but hey, the application for that is not there yet too :-) Grtz, Casper ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
[Dbmail-dev] Gmime compile...
I'm trying to upgrade to 2.1.7 with gmime 2.1.19 or 2.2.3 but getting the following error in dbmail make install. dbmail-mailbox.c: In function `_merge_search': dbmail-mailbox.c:1363: error: `G_TRAVERSE_LEAVES' undeclared (first use in this function) dbmail-mailbox.c:1363: error: (Each undeclared identifier is reported only once dbmail-mailbox.c:1363: error: for each function it appears in.) make[1]: *** [libdbmail_la-dbmail-mailbox.lo] Error 1 make[1]: Leaving directory `/root/dbmail-2.1.7' make: *** [install-recursive] Error 1 This happens to both gmime 2.1.19 and gmime 2.2.3. I'm totally lost and not sure what would cause this. Any help is appreciated. Xing
[Dbmail-dev] dbmail 2.0.10 IMAPD note
Dev-monsters, Not reproducible on demand so I really can't call/submit it as a bug but it's serious enough to post to the dev list in case others are having this problem. Approximately 2 days after upgrading from 2.0.9/8 (forgot which one) to 2.0.10, my mail server for whatever reason had over 3 thousand dbmail-imapd proccesses (the dbmail.conf specified 20 max) causing the system to run out of memory, swap. Systems is Centos 4.2 with Mysql 4.1.18. This was 2 days ago and haven't seen the IMAPD issue come up yet. However, today, 48 hours later, dbmail-pop3d has 4 zombies. I'm the only user of IMAP access on the server though several boxes have 30K+ messages. Don't want to sound the life-boat alarm but just a warning bell to watch this if others experience similar issues after upgrade. Xing
Re: [Dbmail-dev] New libSieve API docs posted!
This has been the best dev news I have heard since. Give me 10 core cpu and 2000 threads and a 256MB celeron with an properly written aysnc based webserver and it will smack the 10 core cpu like non other. Abdullah Ibn Hamad wrote: --- Paul J Stevens <[EMAIL PROTECTED]> wrote: Aaron Stone wrote: Now, a quick question: Paul, do you intend to make the 2.1 series multithreaded? See, flex is still stuck in single-thread land... so we should hold off on multithreading until flex gets fixed. Or I have to rewrite libSieve's lexer and parser in something else (ugh). Dont worry Aaron. Dbmail's daemons will be using multi-plexed IO using select(), not multi-threading. And I'm not at all sure that will happen before 2.2.0. Do you have any plans to have multi-threading since we have more OSes like FreeBSD supports that now, and the upcoming cpus multi with core from Intel and AMD? Regards, -A __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] suggestion to dev branch name...
Logo eh..Good idea. Hard to visualize "db". Almost every vendor and schema group represent db graphically as a cyclinder. Why a cylinder I wonder. It's almost an industry standard now. I'm mind painting a picture of a simple 3d cylinder with a slot on top and an envelope dropping into the slot. All with nice primary color and vector based. =P Unfortunately, I'm not a graphic artist. Sigh. If I have time, I could download the 30 day Adobe Illustrator trial and see if I can put the idea to play. With tag line: "Why dbmail? Cause select * is so much better than fread()." =) Xing Aaron Stone wrote: On Thu, Aug 11, 2005, Paul J Stevens <[EMAIL PROTECTED]> said: Paul J Stevens wrote: Aaron Stone wrote: I agree 110% -- Ilja, do you have a moment to make the website changes? I updated the download page. Oh, cool, I didn't know you were in the secret website club ;-) now all we need is a logo... This strange idea of a "logo" you speak of, it sounds very interesting. Aaron ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
[Dbmail-dev] suggestion to dev branch name...
It appears this will be a problem that's not going away. New dbmail users tend to install 2.1 first because they didn't know it's a highly evolving dev branch. The distinction is not very clear and could deter future adoption if new users encounter what is basically growing pains of the 2.1 tree. Proposed changes: 1) Download page: prepend (STABLE) to 2.0 series dl list header. prepend (UNSTABLE) to 2.1 series dl list header. 2) Homepage/News, do the same as 1. Insert STABLE or UNSTABLE to article header depending on which package is referenced. Xing
Re: [Dbmail-dev] dbmail web interface
I have not used either james or jsmtpd. =) More rant than advice. From reading james faq and docs, it reeks of bloatware. Besides any product with "Enterprise" in their name is anything but. Quick, think of an elegant and scalable product that contains the dubious title of "Enterprise". JAMES = "Apache Java Enterprise Mail Server". If any java api is to developed, I would look more closely at jsmptd: http://www.jsmtpd.org/site/ It's already got some nice, TSL over 25 + dead simple filtering (spam/rbl/virus) as plugins, and not even out of 0.4 beta. Albeit only smtp gateway + filters + local mbox store for now but that's all you really need. Just extend the storage api and you are good to go. Simple, clean, customizable by any single digit developer team. Dbmail Pop3/Imap/Storage is good. I personally would love to replace postfix mta. Postfix to me, is better than Qmail/Sendmail, but still sucks. Why would the sql SASL interface be so different from the sql alias maps? They both do "verification" and both support sql yet widly different syntax and modules for basically the same issues. Of course, cyrus sasl support other authentication modules, etc, but that's where bloatware comes in: supporting everything under the sun. Xing Kevin Baker wrote: [snip] 3. I thought since Brandon was writing the Java Library to DBMail he might also be aware of and maybe looking to integrate with Apache James, Java SMTP and POP3 server. Oh, that's a pretty neat idea. What do they use for a data store right now? So James is an open Java based mail server... all RDBMS backend through jdbc so pretty much any db server. Unfortunately, NO IMAP Server. Some dev has been done on IMAP, but it hasn't had any progress for years. This is what first lead me to find DBMail. Mostly because I saw all the advantages of RDBMS backend but James had no IMAP. At one point I thought of integration between the two rather than using Postfix. This would loose the advantages of pure-java but could still use a common backend and Java for mailet functionality in place of sieve and other. This would also give me the advantage of a pure java api if there was a java api to dbmail. James Features RDBMS mailboxes/spool (stable) RDBMS - Users (stable) LDAP Support - Users (experimental) SMTP server (stable) Mailet Engine (stable) POP3 server (stable) 100% Pure Java http://james.apache.org/ Kevin ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] Huge memory requirements for dbmail with large imap folders?
300MB of memory is not the norm for IMAP access to 1500 items. I have 16K+ messages in my inbox accessed over the slowest interface of them all, SquirrelMail + Dbmail 2.0.4 IMAP (inndob). Access time to inbox is 5 seconds with less than 8MB ram usage from the dbmail-imap process. First, use innodb for medium to large sized setup if using mysql and dbmail. Second, make sure the innodb buffer is large enough to hold the indicies. Same goes for myisam key buffer. Xing Nathan Neulinger wrote: I should point out that other than this issue, it's working great, and is VERY fast. -- Nathan On Sat, Apr 16, 2005 at 01:11:56PM -0500, Nathan Neulinger wrote: I've just starting testing out dbmail to hold my mail archive for easy access via imap. Unfortunately, it looks to me like it does not perform reasonably for large folders - in particular, I've seen memory utilization (both total and rss) climb to over 300MB on folders with 1500 messages in them. It's not clear if this is a leak or a design issue. Should I expect dbmail-imapd to use that much memory for large folders? Running 2.1.0 on a relatively current mdk 10 cooker. -- Nathan Nathan Neulinger EMail: [EMAIL PROTECTED] University of Missouri - Rolla Phone: (573) 341-6679 UMR Information Technology Fax: (573) 341-4216 ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev Nathan Neulinger EMail: [EMAIL PROTECTED] University of Missouri - Rolla Phone: (573) 341-6679 UMR Information Technology Fax: (573) 341-4216 ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] CVS compile error..
Doh. This is the second time I have checked out the wrong CVS tree. HEAD vs Branch. Need to remember the difference next time. Xing Paul J Stevens wrote: [EMAIL PROTECTED] wrote: I got the following after a "make" attempt with "configure --with-mysql" with the latest cvs checkout. Am I missing some dev package? CVS-head requires both libglib2-dev as well as libgmime2.0-dev.
[Dbmail-dev] CVS compile error..
I got the following after a "make" attempt with "configure --with-mysql" with the latest cvs checkout. Am I missing some dev package? [EMAIL PROTECTED] dbmail]# make make all-recursive make[1]: Entering directory `/opt/dbmail' Making all in mysql make[2]: Entering directory `/opt/dbmail/mysql' /bin/sh ../libtool --mode=compile gcc -DHAVE_CONFIG_H -I. -I. -I.. -I.. -I/usr/include/mysql -fomit-frame-pointer -g -O2 -W -Wall -Wpointer-arith -Wstrict-prototypes -c dbmysql.c mkdir .libs gcc -DHAVE_CONFIG_H -I. -I. -I.. -I.. -I/usr/include/mysql -fomit-frame-pointer -g -O2 -W -Wall -Wpointer-arith -Wstrict-prototypes -Wp,-MD,.deps/dbmysql.pp -c dbmysql.c -fPIC -DPIC -o .libs/dbmysql.o In file included from ../debug.h:27, from ../db.h:36, from dbmysql.c:32: ../dbmail.h:37:18: glib.h: No such file or directory make[2]: *** [dbmysql.lo] Error 1 make[2]: Leaving directory `/opt/dbmail/mysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/opt/dbmail' make: *** [all-recursive-am] Error 2 Xing
[Dbmail-dev] Logging question...
Is it possible to have have dbmail programs (smtp, pop3, imap) log to a
different syslog handler other than default mail?
I'm asking cause my email server sends out over 100K emails every 24
hours and with all the incoming logging, sorting our dbmail entries can
be a real pain. The problem is currently compounded with me pushing out
100K of backlogged out mail.
Right now I'm trying to enable TRACE=5 for smtp to trace a confirmed
SIG11 problem with dbmail-smtp. Fortunately the problem is specific to
an email/user so it should be pretty easy to find. If dbmail already can
specific an alternate log file within dbmail.conf, it would make
logging/debugging dbmail stuff a lot easier without going through ("tail
-n", "cat") + "grep" that I have to do now to filter all the non-dbmail
junk.
Xing
[Dbmail-dev] Suggestion regarding release process
We have a third confirmation on the authentication bug which is pretty serious now that we know it's not an isolated incident with Thunderbird. This got me thinking a bit how dbmail could improve on the pre-release testing. Speaking for myself, I don't like to test cvs since there are just too many changes in there to track to manage but would install the latest release with real version number asap. Perhaps dbmail could make it easier for admins to test pre-release stuff by release RCXX packages often? one every week starting on feature freeze date for that version even for 2.0.X minor stuff? This way, non-devel users like myself could stress test while still having the piece of mind of knowing exactly which bleed-version I'm running and roll-forward or backward if errors are encountered, before the general public get hold of it. Right now for minor versions we have CVS, which I don't think have enough people testing on, and immediate release. New model for minor point releases: CVS --> Feature Freeze --> 1-2 Week of RC testing (RC every 4 days or whenever a large bug is found/fix, which ever is first) --> Release. In this way, RC testing can begin right when feature freeze is announced which. Xing
Re: [Dbmail-dev] IMAP access control
Sounds good. I just looked at the php.net keywords and noticed: TEXT "string" - match messages with text "string" Is that synonymous to BODY? Xing Aaron Stone wrote: So let's make two text fields: IMAP_SEARCH_ALLOW and IMAP_SEARCH_DENY, then use these keywords: http://us2.php.net/imap_search By default, allow all and deny none. If Deny is *, then we only allow what's in the allowed list. If Allow is * then we only deny the denied list. In your case, IMAP_SEARCH_DENY=BODY and you're done. Aaron ""[EMAIL PROTECTED]"" <[EMAIL PROTECTED]> said: The search queries would be more intensive as it has to process each record retrieved vs a just retrieving all the records. The reason I have suggested the search block option is because in it's current state, 2.0.1 + mysql, the BODY search is already useless, timing out each time, so instead of having a feature that nets only negative gain to my server load with zero return, it's heck a lot safer to stop serving searches altogether. Xing Is there really a gain for this feature? The IMAP client has to make a search, preferably on the server. If the server lacks these capabilities will the client back off and download all messages and do the search itself. We actually do not gain anything, the database must in these cases serve the content of all the messages down to the client. This is as expensive as doing a linear search in the database. Magnus ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] IMAP access control
The search queries would be more intensive as it has to process each record retrieved vs a just retrieving all the records. The reason I have suggested the search block option is because in it's current state, 2.0.1 + mysql, the BODY search is already useless, timing out each time, so instead of having a feature that nets only negative gain to my server load with zero return, it's heck a lot safer to stop serving searches altogether. Xing Is there really a gain for this feature? The IMAP client has to make a search, preferably on the server. If the server lacks these capabilities will the client back off and download all messages and do the search itself. We actually do not gain anything, the database must in these cases serve the content of all the messages down to the client. This is as expensive as doing a linear search in the database. Magnus
[Dbmail-dev] IMAP access control
Just want to add my take on this subject. It's related to the IMAP access control but more in a global way and not on a per-user basis. It would be real nice if there was a way to disable IMAP search capability within dbmail using a config line in dbmail.conf. The reason is that no matter how much optimization goes into IMAP search, it's just way too slow and overload the backend too much for the feature it offers. Even with fulltext search enabled, the performance issue will not go away as IMAP folders can easily contain 10s of thousands of entries. Most clients I believe already perform sender/subject/from/header searches internally since they have to cache them regardless of settings. Not sure if Thunderbird would use internal mech for body searches if IMAP offline features are checked for all the folders. For a large install of dbmail, IMAP search is just too costly for the benefit it offers. Xing -- Feature Suggestion: http://www.dbmail.org/mantis/bug_view_advanced_page.php?bug_id=006&nbn=3#bugnotes Summary Access control for IMAP Description In my setup I want only certain people to be allowed to use the IMAP service. I suggest we set up a flag for this in the database. Actually, this should be added to pop3 and optionally webmail aswell. Since an ISP might want to disable a users access to his mailbox if he does not pay. But they might not want to delete the user or disable incoming mails. ilja 09-Jul-04 14:11 This can be done by adding the following fields to the users table: can_imap (obvious) can_pop (obvious) can_sieve (is allowed to have sieve scripts (for later, when Sieve is implemented) etc? aaron 15-Oct-04 18:25 With this response, let's pop over to the mailing list for a little while. But, I'm thinking, columns strikes me as a bad idea. It means a database upgrade for every new feature that we might want to control. Instead, we can add a permissions table that grants or removes privileges to the various daemons. With client_idnr in the mix, we can do things like say, "Client X pays for POP3, Client Y pays for IMAP and Client Z pays for both." We should consider how/if this will interact with the user account and client account suspensions feature that has been so often requested.
Re: [Dbmail-dev] MySQL 4.1 and dbmail 1.3
I'm quickly getting there at 150GB. I don't trust MySQL to not corrupt itself when a table gets more than 1-3M rows in it. I have customers I manage a production db (non-dbmail) of 30 million rows (largest table has 19 mil rows) spanning 21 gigs. Extensive use of storing compressed data vs raw (at 50% ratio as a whole) make it small relatively. This is a high transaction databse with signficant load 24/7, not a low volume, huge research type dbs which is not designed to fly when interfaced with the world. Any db vendor nowadays can support TB size but what it comes down to is a blend of size, speed, and management. Only had one time where a mysql table got corrupted due to the table size. It was almost 2 years ago when I reached the max row size of a myisam table and mysql barfed. Mysql has since fixed this bug and disallow the transaction if the row size limit is reached. You need to modify avg_row_size to increase the default table row limit. "Show table status" should let you know when the boundry is getting close to be crossed. If you want to prevent table locks on Mysql, Innodb is availabble. The problem that some do not point out is that a transaction safe table require 50%+ more hd space (same applies if you move myisam table to pgsql). Which means the scan operations will be slower. For me, I move the high transaction, low storage tables to Innodb and large size, low trans tables to the slim Myisam format. Don't dog Mysql cause if you google PgSQL it has just as much problems. =) who use MySQL and the damn thing corrupts itself when tables get to have more than a few million rows. MySQL is just not a stable database when the number of records grows. It also has problems with lock contention. PostgreSQL is much better in both regards. I have PostgreSQL installations with easily over 1B rows and using 500GB of space without it breaking a sweat. Biggest Pg database I know of is several terabytes in size ala the Human Genome Project. :) I'd love to talk and share ideas for managing large installs, Accounts added, deleted, etc. via dbmail-user that are created via triggers. This is going to disappear at some point in favor of natively updating the database itself, but I haven't done that yet. optimal db configurations, FreeBSD 5.X (post 5.3 is best, IMHO. ie RELENG_5), PostgreSQL 8.0, AMD64, Perdition replication slony /backups, pg_dump database maintenance, pg_autovacuum etc. Drop me a private email (mark a-t orcon.net.nz) or to the list. Eh, I figure someone else may find it useful.
Re: [Dbmail-dev] performance of speedup patches
Speaking soley about 2.0CVS-Current There could be a minor glitch with the icfetch_speedup pertaining to MySQL. I noticed this after about ten hours. Traffic light, dept. int. mail server, 128 accounts. The error seems to occur when the messageblocks on a multipart physmessage_id contains a binary (actually a screenshot with comments) plus text, plus header. Messages with just binary, no prob. Well. I am suspicious a litle too about the fetch count change (nervous Nelly) so scrutiny is real close and might be seeing things. ...I will do more extensive work, but for sure rebuilding back to STABLE with the imapcommands.c.orig (4xfetch) makes problem go away. May I suggest, Mikhail, that I work in whatever else (patches) you have there and do so some testing for you, if that would help. I did not run across your db_header_speedup patch but did patch for dbmysql.c.patch and the icfetch. dbmysql.c is a keeper - straight forward. 12-hour performance is trending in the right direction. Bravo. Mikhail you rock! Nice work. Could you please send me the db_header_speedup patch (or link) and if you have any testing tools, criteria or measurement standard you would like to have used, I am supposed to get a piece of iron pushed back to me here tomorrow from another project, a week early. I can swap in a BSD drive and have the thing for a WEEK. of DbMail testing. If not now, the offer stands whenever I can. LIST NOTE: Did I miss it or is the issue resolved with 2.0CVS-Current NOT resetting 'status to 001' on 'deleted_flag=1'? best... Mike Mikhail Ramendik wrote: Matthew T. O'Connor wrote: dbmail 2.0+dbmysql: 128 2.0+dbmysql+icfetch_speedup: 184 2.0+dbmysql+icfetch_speedup+db_header_speedup: 193 I'm still very skeptical of making changes to is_fetch in the 2.0.x branch. I'm all for putting these in 2.1. Anyone else have any thoughts on the invasiveness of these patches and adding them to 2.0? Well, I tried to make the invasiveness as little as possible; and there is a check that falls back to the old behavior in case of anything unexpected. So I feel this code could be for 2.0.x, but of course let's wait for independent review. For 2.1 I'm planning a bigger change, although I'm not yet sure it will work out in the current code framework. I also have an idea for a SEARCH speedup in 2.1. Yours, Mikhail Ramendik ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] performance of speedup patches
Make sure you have mysql query cachining disabled for the test. Was it? Xing Mikhail Ramendik wrote: Hello, I have retested the performance impact of my icfetch_speedup and db_header_speedup patches. (My previous tests were irrelevant because logging caused the main overhead). The dbmysql patch was applied; it's simple and obvious so who needs to test it anyway... Here are the results, on the same machine and the same settings, with no other tasks running and no swapping, average between 10 secs somewhere in the beginning of the operation and 10 secs somewhere at the end, in messages per second: dbmail 2.0+dbmysql: 128 2.0+dbmysql+icfetch_speedup: 184 2.0+dbmysql+icfetch_speedup+db_header_speedup: 193 So, both speedups work but the real big impact is in icfetch_speedup. Unfortunately it's the more complicated as well, and so requires some testing and/or independent review before getting included into the 2.0.x branch. But it's still small enough to be included, I think. Yours, Mikhail Ramendik ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] Two bottlenecks in db_getmailbox
Thanks to both Mikhail and Paul grabbing the obvious bottlenecks by the
horn and taking action.
I have applied Mikhail's Patch and the result is nothing short of amazing.
Before the patch on my IMAP 21,000 item inbox, check new email took
dbmail-imapd literally infinity (Thunderbird was never able to check
new email because it chokes waitig for dbmail-imapd to complete)
grabbing 99% cpu along with it. With the patch, dbmail-imapd chews up
less 10% cpu on the same transaction and it takes only 1 second to complete.
Have yet to apply Paul's sql based patch and can't wait to pair both up.
Thanks guys. I can now finally use IMAP to check my inbox. =)
Xing
Mikhail Ramendik wrote:
Paul J Stevens wrote:
I have this implemented already, and the loop now is through in no time.
Moreover, my version is probably faster than yours - because no extra
COUNT is needed, and because my db_get_result won't perform a single
seek in all this loop! It checks if the row we want is the next row from
the one already fetched, and if so, just fetches it!
SHow me the code. against 2.0 or cvs I don't really care. This change
is so small and localized it doesn't
much matter. I too have fixed this, but I'm curious how you did it.
I've attached my own patch.
Attaching mine. (regular patch, not dpatch).
Our patches are actually compatible, they're in fact against different
files. Whether yours is needed after mine is up to you - I'm not sure
either way. On a second thought, probably it is, because mine only gives
an improvement for mysql.
Mine might fix things in other places which I don't even know about. It
basically improves speed every time one reads from several fields on the
same row, AND when one reads one row and then the next.
I think that any writing of mime-parsers today is a waste of developer
time. There are many lying ready.
Which is why I started using gmime.
Agreed.
But, what has a mime-parser got to do with the number of queries here?
Joining all the queries, except the one for messageblks, into one or two
big queries for ALL messages can be implemented with no changes in the
mime parser, right? And even that will give us a 3 or 4 times better
performance.
When messages are retrieved by _ic_fetch, they are parsed by dbmail's
mime-parser in many cases, like for
retrieving a list of the message-headers.
Didnt realize that. (I got lost in the code before this point).
Yours, Mikhail Ramendik
--- mysql/dbmysql.c.orig2004-06-03 16:41:55.0 +0400
+++ mysql/dbmysql.c 2004-10-23 16:10:31.0 +0400
@@ -1,6 +1,8 @@
/*
Copyright (C) 1999-2004 IC & S [EMAIL PROTECTED]
+ Modified 2004 by Mikhail Ramendik [EMAIL PROTECTED] (MR)
+
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either
@@ -49,6 +51,11 @@
static MYSQL_RES *stored_res = NULL; /**< MySQL result set backup */
static MYSQL_ROW last_row; /**< MySQL result row */
+/* Additions by MR */
+
+static int res_changed = 1; /* result set changed */
+static unsigned last_row_number = 0; /* the number of the row in last_row */
+
/** database parameters */
db_param_t _db_params;
@@ -134,6 +141,7 @@
trace(TRACE_WARNING, "%s,%s: Trying to free result set "
"that is already NULL!", __FILE__, __func__);
res = NULL;
+res_changed = 1; /*MR*/
}
@@ -154,9 +162,28 @@
return NULL;
}
- mysql_data_seek(res, row);
- last_row = mysql_fetch_row(res);
+/*MR*/
+
+if (res_changed)
+
+{
+ mysql_data_seek(res, row);
+ last_row = mysql_fetch_row(res);
+} else {
+if (row == last_row_number+1)
+last_row = mysql_fetch_row(res);
+else if (row != last_row_number) {
+mysql_data_seek(res, row);
+ last_row = mysql_fetch_row(res);
+};
+/* otherwise last_row is already loaded and does not
need to change! MR*/
+};
+res_changed = 0;
+last_row_number = row;
+
+/*MR changes end here*/
+
if (last_row == NULL) {
trace(TRACE_WARNING, "%s,%s: row is NULL\n",
__FILE__, __func__);
@@ -238,6 +265,7 @@
}
res = mysql_store_result(&conn);
+res_changed = 1; /*MR*/
return 0;
}
@@ -304,12 +332,14 @@
{
stored_res = res;
res = msgbuf_res;
+res_changed = 1; /*MR*/
}
void db_store_msgbuf_result()
{
msgbuf_res = res;
Re: [Dbmail-dev] Two bottlenecks in db_getmailbox
I had exact same slowness problem after upgrading to 2.0 via imap last
night. I sent a report to the users list before I saw this thread.
If the following new query is to be used to optimize the imap:
select
count(message_idnr),
count(message_idnr) - sum(seen_flag),
sum(recent_flag)
from %smessages
where mailbox_idnr = '%llu' and status < '%d';
dbmail should have an index, at least for mysql that index both column
(mailbox_idnr and status). I was looking at some of the debug queries
and after add a (mailbox_idnr, status, uniqueid) combo index, mysql was
able to trim the records need to file sort from 21000 to 17000 from of
the queries which use all of these 3 fields. 4000/21000 = 19% decrease
in the records mysql has to manually sort through.
Xing
Paul J Stevens wrote:
Mikhail Ramendik wrote:
Hello,
I have imported a large (about 50,000 messages) folder into dbmail - and
got really SLOW performance. Opening the folder with a IMAP client
(Evolution, on the same local machine) takes ages! The promised 250
messages/second are just not there.
Painfully true.
I have analyzed the logs and looked at the code. And I can clearly see
where the bottlenecks are. (This is why I am writing to the developers
list.)
However, I'm not an expert in database programming. So while finding the
problems was easy, I'd prefer it if someone else could fix them :) I
have some ideas how to do this; but this would be a "last resort".
Please do share your ideas. Anything that will boost perfomance will
be seriously considered.
So, when the folder gets opened, first the system spends some minutes
with dbmail-imapd hogging the CPU (a Celeron 2400). And here are the log
entriesbetween which this happens:
Oct 23 11:42:28 localhost dbmail/imap4d[2762]: dbmysql.c,db_query:
executing query [SELECT message_idnr, seen_flag, recent_flag FROM
dbmail_messages WHERE mailbox_idnr = '9' AND status < '2' AND unique_id
!= '' ORDER BY message_idnr ASC]
Oct 23 11:44:19 localhost dbmail/imap4d[2762]: dbmysql.c,db_query:
executing query [SELECT MAX(message_idnr) FROM dbmail_
messages WHERE unique_id != '']
I have found the corresponding place in the code and can see the CPU hog
there:
(db.c line 2534)
Mmm, for me this is around 2340 more like.
/* alloc mem */
mb->seq_list = (u64_t *) my_malloc(sizeof(u64_t) * mb->exists);
if (!mb->seq_list) {
/* out of mem */
db_free_result();
return -1;
}
for (i = 0; i < db_num_rows(); i++) {
if (db_get_result(i, 1)[0] == '0')
mb->unseen++;
if (db_get_result(i, 2)[0] == '1')
mb->recent++;
mb->seq_list[i] = db_get_result_u64(i, 0);
}
db_free_result();
Well, with db_num_rows() in the tens of thousands one would expect a CPU
hog here! Three calls to db_get_result for every message - and
db_get_result performs seeking every single time, too.
And only to count the number of recent_flags, and seen_flags for a
certain subset of message_idnrs all those message rows are actually
selected !! Clearly usage of COUNT() comes to mind as an optimization.
therefore, where in db_getmailbox() the following code is used to
obtain the total number of messages, the number of unseen, and number
of recent messages:
/* select messages */
snprintf(query, DEF_QUERYSIZE,
"SELECT message_idnr, seen_flag, recent_flag "
"FROM %smessages WHERE mailbox_idnr = '%llu' "
"AND status < '%d' "
"ORDER BY message_idnr ASC",DBPFX, mb->uid,
MESSAGE_STATUS_DELETE);
we would be better of to use:
select
count(message_idnr),
count(message_idnr) - sum(seen_flag),
sum(recent_flag)
from %smessages
where mailbox_idnr = '%llu' and status < '%d';
and defer fetching the list of message_idnr to a separate query. This
will reduce the number of db_get_result calls in this function by
approx. 66%, which must be good for large folders.
Thanks for pointing this out. I have this implemented already, if this
works out, I'll commit this change next week.
[snip a typical message-parsing fetch run]
And so it goes on, doing four queries per message! No wonder it can only
process about 20 messages per second, instead of 250 as promised in the
README.
Yep. You hit dbmail's sorest spot called _ic_fetch. Not so easy to
fix. And something I've been working on for some months now. Using a
better mime-parser will give us better model-view separation. We also
need a server-side list-view of messages as presented to the
controller (imapclient). I was working on a separate branch of the
code to investigate some of these issues but have now abbandoned the
branch in favor of using smaller atomic change-sets in separate
patches. Debian's dpatch tool really rules
Re: [Dbmail-dev] bugs fixed: where?
Alessandro Magnolo wrote: I submitted some bugs of dbmail_2_0_branch to the bug tracker (bug #97, #98, #99), and two of the have been fixed by paul. I would like to know how I can get the corrected version, or when I can expect to see the bugs fixed in 2.0. The bugs are still there in the latest dbmail_2_0_branch CVS, and the experimental 2.1 doesn't compile. Moreover, we planned to use dbmail in a production environment (a large installation), and wouldn't risk using an experimental release. Regards, Alessandro Magnolo ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev FYI: AFAIK _2_0_branch runs STABLE ALWAYS (to say experimental meaning unstable IMO seems most unfair) with exception of a few hours on September 14 which Paul fixed. * _2_0_branch handles a modest scale production environment just fine (i.e.: 5000-plus-accounts). * Minor known bugs have easy workarounds * I t builds on xBSD and Linux with Pgsql and Mysql without any persuasion whatsoever. 2.1 Development Branch hasn't built smoothly for many days on RH9, BSDs, SunOS. Problems appear to be known by all (glibc and gmime versioning + portability). But you can build it if you 'muck around a little' :o) best ... Mike
Re: [Dbmail-dev] Re: Dbmail Postgres Database Inconsistencies...
FYI select * from dbmail_messageblks; (5,075 InnoDB 48.4 MB ) 5075 rows in set (0.54 sec) mysql> SunOS5 MySQL4.0.21Max Gerrit P. Haase wrote: Hello Matthew, Second reply to this mail: Ok, I'm running this on Windows using Cygwin postgres / dbmail, everything is usually 10 to 100 times slower, but I know that database applications can run fast on Windows too. Sure, Cygwin slows down things. I installed it on the Windows box for testing, at least it builds ok and it runs. But often dbmail seems to hang. It finally finishes the work but I suspect htere to be some serious problems. At this point I would recomend installing the PostgreSQL 8.0 beta that has native support for Windows. Even though it's still a beta I think it performs better and is probably more reliable than the cygwin port. Please give it a try and let me know if you are still having problems. I have the native Windows version of postgres installed now. It is still very slow. I did a `select * from dbmail_messageblks` and it lasts about two minutes to fetch 100 rows! This is very slow, and I don't think that the Cygwin version is much slower here. Why is this so slow? This makes me think about other options. Wouldn't it be faster to store the actual messages on the harddisk? Gerrit
Re: [Dbmail-dev] IMAP "LIST" Fails in CVS2.0 / CVS2.1 but OK indbmail-2.0rc8 (?)
<[EMAIL PROTECTED]> wrote: > Has anyone noticed that IMAP "LIST" fails in current CVS2.0 / CVS2.1 but functions correctly in dbmail-2.0rc8? > > I just installed/removed each of the above while switching to the > corresponding conf files. > > Before I explore further... has anyone else checked this? I haven't seen it.. strange. I haven't experienced anything weird today. What do the logs say? Ilja I think this is easily missed. In Thunderbird, for example, I click on an account for a menu and select subscribe. The following results for me with no return on the 'list' request. This is the 2.1 CVS build. CVS2.0 gets the same result. Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01TY LSUB "" "*"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01TZ LIST "" "INBOX*"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U0 CREATE "INBOX"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U1 LIST "" "INBOX"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U2 LIST "" "Sent Items"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U3 CREATE "Sent Items"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U4 LIST "" "Sent Items"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U5 LIST "" "Drafts"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U6 CREATE "Drafts"] Sep 16 20:44:33 sunny dbmail/imap4d[580]: COMMAND: [01U7 LIST "" "Drafts"] If the email client already has a list of folders it will continue to fetch the content, no problems, but upon recreation of the account, selecting "subscribe" or "reselect list", or creating a new account, dbmail-imap will not return the list of mailboxes. This is not something you will run into if your email client account correctly lists the folders which exist in the database, unless you actually make a change or relist request following a current CVS build. It would however be fatal to IMAP in a new installation because the client would not have the folder list to poll. Reinstalling dbmail-2.0rc8 (and switching back to the corresponding dbmail.conf) fixes the problem instantly. I have tried this on several operating systems. There is no change to the database in any case and everything seems to function OK until a list request is made. best... Mike
[Dbmail-dev] IMAP "LIST" Fails in CVS2.0 / CVS2.1 but OK in dbmail-2.0rc8 (?)
Has anyone noticed that IMAP "LIST" fails in current CVS2.0 / CVS2.1 but functions correctly in dbmail-2.0rc8? I just installed/removed each of the above while switching to the corresponding conf files. Before I explore further... has anyone else checked this? Mike
Re: [Dbmail-dev] current cvs head doesn't compiles under freebsd 5.2.1 - FreeBSD users Note that issue is FIXED
>Does someone have a FreeBSD machine that they can provide accounts on? >I could set one up at home, but it would be on a DSL line with a slow >uplink rate. >Aaron >-- > M. J. [Mike] O'Brien <[EMAIL PROTECTED]> said: > what do you need? > It sounds like Ilja and Paul are hitting some FreeBSD issues, I guess mostly with the default options that FreeBSD's GCC uses. > > > Ilja Booij <[EMAIL PROTECTED]> said: > > Someone with access to freebsd please help me out here. Ilja? > BTW, I don't have a FreeBSD machine here, so I can't test it. I think Paul now has the kinks wrung out perfectly. One key issue had to do with strict C declaration rules adherence in the newer GCC versions on the dev branch of FreeBSD and Release 4.10. (There also have been some issues surrounding expat-1.95.8 in the more recent releases of FreeBSD.) Paul did a hero fix on pool.c etc. and it all pulled together. Running well, I have the current CVS with Paul's, Ilja's, Lief's and yours (Aaron's) changes running on a FreeBSD 5.2.1 dev machine for about 9 hours stable under light but constant load. (I haven't tested all features but expect no difference to a Linux build) Paul's changes inevitably fixed the build issues with the new preforking functions. After another 24 hours and some heavier stress testing I will build the CVS on a FreeBSD 4.10 production blade. So far preforking is working like a charm on 5.2.1 and I will further document the GLIB and GCC versions which worked favourably on this 'non-production' grade (5.2.1) variant of FreeBSD. The period of time in which DbMail CVS would not build on FreeBSD would have been a couple of days. The current CVS will fix this problem. (Update your CVS, 'gmake clean' and rebuild.) In the course of trying to build the 13/14 September CVS prior to Paul's fix I updated a number of build tools and I think I initially created some incompatabilities which first produced inexplicable results on the updated DbMail CVS. I showed Paul earlier. Nevertheless, the following is the combination that worked after stabilizing and integrating GCC/GLIB needs on FBSD5.2.1. GLIB 2.4.6, expat-1.95.8, gettext-0.13.1_1, gmake-3.80_2, libtool-1.5.8, perl-5.8.5, pkgconfig-0.15.0_1 libiconv-1.9.1_3 and gcc version 3.3.3. This represents a somewhat upgraded package from the original native-to-5.2.1 packages. It may not be necesary inasmuch as I have also built (USE GMAKE) this hours' CVS DbMail on FreeBSD 4.9 using native GCC 2.95.4 and appropriate native libraries and dependencies without a hitch. I anticipate no problems on 4.10 I strongly feel that FreeBSD users can be confident upgrading to the current CVS with preforking, server-side sort etc. and a new dbmail.conf on any recent FreeBSD 'Release' variants having at least GLIB2.0 and current stable MySQL (i.e.: 4.0.16Max) / PostgreSQL (i.e.: 7.3.4-7.4.5). In fact, the combination of Postfix, MySQL 4.0.18Max or PostgreSQL 7.4.5, postfix-2.2-20040829 (watch out for config variances in the bleeding edge Postfix versions) and FreeBSD 4.10 makes for a world-beater DbMail system. I compare DbMail on Sunos5.9 and Linux RH9. Well, they are all good. best... Mike > > > > ___ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev >
[Dbmail-dev] DbMail CVS on FreeBSD 4.8, 4.9, 4.10 error pool.c: In function `child_reg_connected'
/usr/local/bin/bash ./libtool --mode=compile gcc -DHAVE_CONFIG_H -I. -I. -I.-fomit-frame-pointer -g -O2 -I/usr/local/include/glib-2.0 -I/usr/local/lib/glib-2.0/include -W -Wall -Wpointer-arith -Wstrict-prototypes -c pool.c gcc -DHAVE_CONFIG_H -I. -I. -I. -fomit-frame-pointer -g -O2 -I/usr/local/include/glib-2.0 -I/usr/local/lib/glib-2.0/include -W -Wall -Wpointer-arith -Wstrict-prototypes -Wp,-MD,.deps/pool.pp -c pool.c -fPIC -DPIC -o .libs/pool.o pool.c: In function `child_reg_connected': pool.c:265: syntax error before `int' pool.c:266: `key' undeclared (first use in this function) pool.c:266: (Each undeclared identifier is reported only once pool.c:266: for each function it appears in.) pool.c:262: warning: unused variable `pid' pool.c: In function `child_reg_disconnected': pool.c:278: syntax error before `int' pool.c:279: `key' undeclared (first use in this function) pool.c:275: warning: unused variable `pid' pool.c: In function `child_unregister': pool.c:298: syntax error before `int' pool.c:299: `key' undeclared (first use in this function) pool.c:295: warning: unused variable `pid' pool.c: In function `manage_stop_children': pool.c:361: syntax error before `int' pool.c:364: `stillSomeAlive' undeclared (first use in this function) pool.c:364: `cnt' undeclared (first use in this function) pool.c:368: `i' undeclared (first use in this function) pool.c:369: `chpid' undeclared (first use in this function) gmake[2]: *** [pool.lo] Error 1 gmake[2]: Leaving directory `/usr/install/new/dbmail' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/usr/install/new/dbmail' gmake: *** [all-recursive-am] Error 2
Re: [Dbmail-dev] PostgreSQL migration script.
Works fine on 7.3.4 Mike Ilja Booij wrote: Hi all, I've made some changes to the PostgreSQL migration script, which add the 'dbmail_' prefix and also do some more changes 'in place' instead of copying the data. I don't have a large PostgreSQL installation. Can anyone test the script? Oh, and please also test if it's any faster than the old one (it should be). One word of advice: Run it on a backup :) Ilja ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev
