Yavor Shahpasov wrote: > Hello, > > I have been running dbmail 2.2 PostgreSQL for several months. My > intention was to move my users from pop3 to imap. I selected dbmail > because it looked like it would scale well with this senario. As my data > grows I have hit a few performance issues. Some I have been able to > resolve, others seem unresolvable for now.
Yes, I know that feeling. > > Some background > > My system has around 100 users and 100k messages (15GB postgres dump). > Only a small number of users have imap mailboxes and most use pop3. My > database is POstgreSQL 8.2 with around 200MB cache allocated to it. I am > using Horde as my webmail client and thunderbird and Outlook as clients. > > > Data Base schema > > The database is very denormalized. While this is generally good many > common operations require a join of at least 3 tables. On a system with > a few hundred thousand emails this can quickly exhaus cache memory and > reduce performace. I assume you mean *normalized*. I'm not an expert on database design theory, but """Higher degrees of normalization typically involve more tables and create the need for a larger number of joins, which can reduce performance.""", http://en.wikipedia.org/wiki/Database_normalization And from that description I'd call dbmail highly normalized. > > One relationship that bothers me is dbmail_messages <=> > dbmail_physmessage. This relationship seems like a one to one > relationship and I can't figure out why it's there. It is also the only > way to link messages to the dbmail_headervalues, dbmail_headername and > dbmail_messageblks. The result of this is that almmost any operation > requires 3 table joins to get the data. It is definitely *not* a 1-1 relationship. dbmail_messages link messages to folders. They are the abstract representation of dbmail_physmessages that are anchor for the tables containing the actual data of messages in store. This separation was introduced early in dbmail's development to allow cheap copy/move actions on messages. > > An other relationship which I think would be better of normalized is > dbmail_headername and dbmail_headervalue. While this would increase the > database size I think it will increase the performance of many areas > (searching). It was a decision that was discussed at great length during the design phase, though I was the one took up that challenge so I guess it was my call. Limiting storage requirements seemed more important at the time, while it was assumed possible that join performance could and would be improved in the backend. Dbmail has to walk the line between OLTP and OLAP. Imap is very much about both under high concurrencies, though that may be only my perception. Performance is not the only criterion. Data integrity is absolute priority. > Database Performance > > As the database grew I started getting squite sluggish performance from > webmail during login espesially. > > One of the problems I found was with postgres. The queries were not > using the indexes on dbmail_headervalue properly. I have around 2 Mil > rows in there currently. > It looked like all of the queries joining that table with others were > doing a full table scan. > I managed to improve performance by increasing the statistics count and > analyze the table. > > alter table dbmail_headervalue alter column physmessage_id set > statistics 100; > alter table dbmail_headervalue alter column headername_id set statistics > 100; > ANALYZE dbmail_headervalue > > This helped postgres to start using the index again, I would like to > suggest to include it in the postgres schema. Cool trick. I'm sure lots of other users are interested. Please send patches for any improvements you make. > > > A second problem I have is with searching. Searching is very slow. One > of the benefits of dbmail is supposed to be faster searching due to the > underlying database. > I rearly use imap search as my pramary client is Thunderbird. I stumbled > on this problem throu the webmail client. Login to the webmail would > take a long time. After doing a trace I saw that this command is taking > a long time. > > A005 SEARCH CHARSET UTF-8 TEXT "charsettest" 1.. > > On my system this command would take 40-50 seconds. After resolving the > above issue it went down to 12-15 seconds. Which is still too slow. > I have traced this command to the following SQL query > > > SELECT message_idnr > FROM > dbmail_messages m > JOIN dbmail_physmessage p ON m.physmessage_id=p.id <http://p.id> > JOIN dbmail_headervalue v on v.physmessage_id=p.id <http://p.id> > WHERE > mailbox_idnr = 1 > AND status IN (0,1) > AND SUBSTRING(headervalue,0,255) ILIKE '%charsettest%' ORDER BY > message_idnr > > > The search is done on a mailbox with 2k emails. These span around 45k > rows in dbmail_headervalue. I have tried modifying it to get better > performance but with no results. From my experiance with traditional > Maildir based imap server searching through 2000 emails is much faster, > given that the above searches only in the headers. Also it looks like > the index on "SUBSTRING(headervalue,0,255)" is completly ignored for the > ILIKE operator. That sucks. The whole idea of the headervalue table is to facilitate searches. But it grows so big so fast that it just can't scale. We'd need to group data better to reduce disk related IO. Just like filesystems have been doing for ages. There must be ways to solve this in databases as well. > > I don't know ho the above can be improved, if anyone has ideas let me know. I'm still head to toe in the threading server code so I wont have time to work on imap induced database access performance until all threadsafety issues are resolved. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
