Ok I'm lost here. Maybe this is a philosophical discussion that I missed earlier.
If you want one DB per user why not go use mbox, or maildir format and store the mail on disk since you are really just adding the overhead of SQL to an individual mailbox. Relational SQL databases are designed to do large amount of queries and join data together. If you can't get Postgres or MySQL to perform, then you need to look at your settings for them. DBMail was a disasterous conversion for me because I didn't understand how to tune my PostgreSQL database. Once someone helped me out with that, it's been awsome ever since. I've got 5500 accounts taking up 28G of space in the DB, and the response time is faster than ever. Could it be faster, sure, but I think a user can wait 2s v. 1s for a listing of their mailbox. Could I tune it better, probably, and that's coming soon. The power of having all the users data in one place is awsome. I can run a process that removes everyone's trash that's 2 weeks old each night, I can do the same to the SPAM folder. I get security because the data lives on a server that doesn't even know the Internet is out there. I can have 4 SMTP/LMTP servers with 4 webmail servers with 4 IMAP/POP servers. It can scale and grow as big as I need. I wouldn't want to even think of replication with the design you are talking about. -- David A. Niblett | email: [EMAIL PROTECTED] Network Administrator | Phone: (352) 334-3400 Gainesville Regional Utilities | Web: http://www.gru.net/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of DK Sent: Thursday, June 22, 2006 4:20 PM To: DBMail mailinglist Subject: [Dbmail] dbmail with sqlite Hello I been doing some researching on sqlite. I now think sqlite is the best db for dbmail if used as one sqlite db file per user. SQLite uses reader/writer locks on the entire database file. If many users via IMAP and Postfix try to access one single sqlite db file at the same time they would have to wait for each other. With many users and many incoming emails this could become a problem. The lock is just a few milliseconds each time so if each user has his own db file this should not be a problem. In the contrary each user would access his own file without any delays other then processor and harddrive speed. Each access would be much faster because the data in each file is just the users own data. No need to sort data from hundreds of other users to get access to your own stuff. Connecting to SQLite over the Internet is not a standard task. If you like to keep your IMAP and MTA on separate serves to be accessed over the Internet then sqlite will not work for you. This setup wouldn't be smart one anyway. However if you like to have MTA and IMAP on different servers within the same network. You could mount a shared filesytem between the two servers and connect them via high speed Ethernet. In this case SQLight would again be the better solution. I have installed dbmail-sqlite on my test server. The sqlite db structure includes acl and dbmail-user tables. So it isn't designed to be used as one db per user. It is designed just like the other sql's. (I knew that Paul had told me so) One of the developers however is already using this setup meaning one db per user. Having those extra tables isn't really a problem. My questions: 1) What I like to know is how can I tell dbmail to use sqlite one db file per user? In the dbmail config file you can just specify 1 db. In an ideal world when creating a user dbmail-user would first create a sqlite db at the users home directory and then load the tables. Another way this could be done is to create a skeleton directory with the dbmail sqlite db in it. 2) Second challenge would be having all the aliases in separate db files. This would make Postfix (MTA) look ups not exactly efficient. So one solution could be to mirror some tables to a common sqlite db file. If someone knows how to do that let me know I will research this otherwise. Any comments, thinks I am missing - would be appreciated -- Demi _______________________________________________ Dbmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
