I wasted some time yesterday and today implementing a SQL storage plugin. It seems to be working, but: - Saving new messages is done in a regular INSERT statement, which is bad. PostgreSQL has at least this COPY TO command which could be used instead. - It breaks in stress testing - It's not that well optimized. Especially it could support caching some commonly requested fields (same as dovecot.index.cache file). - Currently works only with PostgreSQL. Nothing is committed to CVS yet. I'm not sure if I should even do that. I'm not sure if those lib-sql API changes were that great. Maybe there's a better way.. If you're interested in trying it, you need the latest CVS HEAD sources and these patches: http://dovecot.org/patches/sql-storage-changes.diff http://dovecot.org/patches/sql-storage-plugin.diff The SQL schema is in src/plugins/mail-sql/schema.sql file. It creates a "tss" named user by default, so you might want to modify that. :) Then set: mail_location = sql:pgsql:host=localhost dbname=mails user=mailuser password=foo mail_plugins = mail_sql -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 189 bytes Desc: This is a digitally signed message part URL: <http://dovecot.org/pipermail/dovecot/attachments/20070331/b4c4973e/attachment.bin>
Timo Sirainen wrote:> I wasted some time yesterday and today implementing a SQL storage > plugin. It seems to be working, but: > > - Saving new messages is done in a regular INSERT statement, which is > bad. PostgreSQL has at least this COPY TO command which could be used > instead. > - It breaks in stress testing > - It's not that well optimized. Especially it could support caching > some commonly requested fields (same as dovecot.index.cache file). > - Currently works only with PostgreSQL. > > Nothing is committed to CVS yet. I'm not sure if I should even do that. > I'm not sure if those lib-sql API changes were that great. Maybe there's > a better way.. > > If you're interested in trying it, you need the latest CVS HEAD sources > and these patches: > > http://dovecot.org/patches/sql-storage-changes.diff > http://dovecot.org/patches/sql-storage-plugin.diff > > The SQL schema is in src/plugins/mail-sql/schema.sql file. It creates a > "tss" named user by default, so you might want to modify that. :) > > Then set: > > mail_location = sql:pgsql:host=localhost dbname=mails user=mailuser > password=foo > mail_plugins = mail_sql > >I've been waiting till 1.0 came out to ask for new features like SQL. I don't think that SQL is a waste of time at all. In fact I believe that SQL is the future of email storage. SQL has a lot of advantages that will be tapped once people start using it. Yes - it is probably slower than mbox or maildir but you can make up for that with faster computers. SQL offers. Replication - the ability to have instant redundancy on multiple servers. The ability to undeliver messages - if the indexes are configured right, if you get a spam attack from an IP and after the attack you realize it was spam you can run a quick query and delete delivered email. Statistical advantages - admins can do email queries across all domains and all usersand all folders to gather useful information. An example might be to generate white list information that can be used for trusted email sources to bypass spam filtering and reduce false positives. Search Speed - should be a lot faster to search messages than maildir. And - there's probably a lot of things that will be realized once it's implemented because the indexing ability opens new posibilities. So - I definitely am someone who supports this idea. It will also make Dovecot stand out as a superior IMAP server. Go for it!
hi. I'm sure this is on hold in your head, but just in case.. Can this be V2.0 minimum, please. Thanks, Ejay On Sat, 2007-03-31 at 17:34 +0300, Timo Sirainen wrote:> I wasted some time yesterday and today implementing a SQL storage > plugin. It seems to be working, but: > > - Saving new messages is done in a regular INSERT statement, which is > bad. PostgreSQL has at least this COPY TO command which could be used > instead. > - It breaks in stress testing > - It's not that well optimized. Especially it could support caching > some commonly requested fields (same as dovecot.index.cache file). > - Currently works only with PostgreSQL. > > Nothing is committed to CVS yet. I'm not sure if I should even do that. > I'm not sure if those lib-sql API changes were that great. Maybe there's > a better way.. > > If you're interested in trying it, you need the latest CVS HEAD sources > and these patches: > > http://dovecot.org/patches/sql-storage-changes.diff > http://dovecot.org/patches/sql-storage-plugin.diff > > The SQL schema is in src/plugins/mail-sql/schema.sql file. It creates a > "tss" named user by default, so you might want to modify that. :) > > Then set: > > mail_location = sql:pgsql:host=localhost dbname=mails user=mailuser > password=foo > mail_plugins = mail_sql >
On Sat, 31 Mar 2007 17:34:23 +0300, Timo Sirainen wrote:>I wasted some time yesterday and today implementing a SQL storage >plugin. It seems to be working, but:> - Saving new messages is done in a regular INSERT statement, which is >bad. PostgreSQL has at least this COPY TO command which could be used >instead. > - It breaks in stress testing > - It's not that well optimized. Especially it could support caching >some commonly requested fields (same as dovecot.index.cache file). > - Currently works only with PostgreSQL.>Nothing is committed to CVS yet. I'm not sure if I should even do that. >I'm not sure if those lib-sql API changes were that great. Maybe there's >a better way..>If you're interested in trying it, you need the latest CVS HEAD sources >and these patches:>http://dovecot.org/patches/sql-storage-changes.diff >http://dovecot.org/patches/sql-storage-plugin.diff>The SQL schema is in src/plugins/mail-sql/schema.sql file. It creates a >"tss" named user by default, so you might want to modify that. :)>Then set:>mail_location =3D sql:pgsql:host=3Dlocalhost dbname=3Dmails user=3Dmailuser >password=3Dfoo >mail_plugins =3D mail_sqlHi Timo, After thoughs about SQL: no time wasted, this is a very interesting project. But instead of storing the mail in the SQL database, which would generate a huuuge database, wouldn't it be more optimised to store only the indexes (and some of the header fields) in SQL and keep the mails on file system? With maybe only the smallest mails in SQL as well... This would sort out a lot of locking problems with the indexes, and give very easy sharing/backup/replication/migration facilities. And it might look a bit like the GMail solution, wich proved to work. As I am looking for that type of solution, I'll be able to put some time to help on developpement. Regards, Thierry