hMailServer database backend

Post by **martin** » 2010-07-06 18:53

If you guys are wondering what's taking me so long, I can inform you that it's this's effect on choice of database backend.

I'm struggling with finding a database which is good enough for 95% of all users, which

Is free of charge
Supports concurrency
Can be shipped with open source software
Has good performance

The actual implementation of 5.4 is ready for beta, but this major part is still missing.

Bill48105 · Post by **Bill48105** » 2010-07-06 18:58

I vote for not including any database and just provide links to supported ones with pros/cons of each and maybe quick-install guides (with suggested settings)..
Bill

^DooM^ · Post by **^DooM^** » 2010-07-06 18:58

Let users choose their own database servers?

Post by **martin** » 2010-07-06 19:01

Yeah I'm aware of those opinions.

I'm worried that a lot of users will give up to early if they are forced to first install some other software prior to installing hMailServer. If there is a good database engine - and the new SQL Compact or SQLite which will both be released this week - I think it would be good to bundle them. Both the releases this week are designed to improve concurrency which is the main problem I've had with SQL Compact...

Bill48105 · Post by **Bill48105** » 2010-07-06 19:05

I understand wanting to provide a complete solution but I certainly wouldn't get hung up on including one.. Especially if you consider from one month to the next the one you feel is best suited to include could easily change and you're back to square one.

Besides, to me it seems confusing to refer to 'included' or 'built-in' sql vs just saying "mysql, ms sql, etc". Why not see sql as any other requirement & not as built-in feature & be done with it? I'm sure there will be people willing to back database interfaces for other databases and not including one helps that happen..
Bill

Bill48105 · Post by **Bill48105** » 2010-07-06 19:07

martin wrote:I'm worried that a lot of users will give up to early if they are forced to first install some other software prior to installing hMailServer. If there is a good database engine - and the new SQL Compact or SQLite which will both be released this week - I think it would be good to bundle them. Both the releases this week are designed to improve concurrency which is the main problem I've had with SQL Compact...

Our posts crossed but as much as I understand this (and it's quite likely true to a certain extent) if someone can't take the few minutes it takes to install SQL what makes you think they're suited to run a mail server?

Post by **martin** » 2010-07-06 19:08

Well, people could add support for alternative databases even if there's one included by default. I don't see that including one would prevent anything.

As I wrote, I'm guessing a lot of people will feel that "i just want to install an email server, why would I want to install a database server?" if the installation program asked them to do just that. One could argue that these users are too lazy to install an email server anyway, though..

Edit: Wrote my reply before you had submitted yours. But you're right, one could say that "you are too lazy, pay someone else to do it"...

Bill48105 · Post by **Bill48105** » 2010-07-06 19:14

martin wrote: One could argue that these users are too lazy to install an email server anyway, though..

Trust me I understand & agree but you hit head on nail right there! To me installing SQL is not tough (although knowing which settings to use for mysql can be intimidating) so just link to how-to's with step by step's with suggested settings and if they get past that test they graduate to being 'licensed' to run a mail server. lol

To me including some ready-to-go sql makes it easy to install but causes more headaches with support because people get in over their heads too easily instead of actually sitting down & doing some research/learning 1st..

Obviously there are arguments on both sides but to me I think best move is not to include any database although that should have been obvious a few posts ago. hehe

In the end it's your baby martin so all we can do is express our opinions & see where you go with it.
Bill

DeanoX · Post by **DeanoX** » 2010-07-06 19:34

What are other mail servers using now? Included relational database engines, flat files, windows registry?, It's been a while since I have looked at other mail servers.

I can see including something basic, so that someone can get up and running quickly, to test the software, with a minimal learning curve.

Maybe have two distros? one with an included db of some sort, and one with no db?

Post by **martin** » 2010-07-06 22:02

Sure, the alternative to have different installation programs exists. On the other hand, if the embedded database is ~1 MB, it doesn't matter much if all people install it as long as they are given the option to use an external engine.

I just gave the latest SQLite a shot and they have released a beta just days ago which improved performance by a huge amount. It would be fast enough for hMailServer and probably more stable than SQL Compact. Maybe I can hard-code a limit - if you've chosen SQLite, you can only create 5 user accounts.

On the other hand, it would be nice not having to implement support for additional database engines - as others have pointed out only takes a couple of minutes.

Bill48105 · Post by **Bill48105** » 2010-07-06 22:24

Oh the pressure! Decisions, decisions. hehe

You said it yourself you'd prefer not to deal with supporting more databases.. Perhaps you have your answer there.

Btw, don't get me wrong.. I am all for as many databases as possible (if you recall I asked you about using sqlite in PM's and was even advocating its use as 'built-in' possibility) but if db support is added by someone else seems you're off the hook for supporting it.

Perhaps release 5.4 beta without built-in database and refer beginners or those just looking to play to try 5.3 if they don't want to deal with installing a database.. (I assume 5.3 can keep the SQL CE even if 5.4 becomes open..)
Bill

^DooM^ · Post by **^DooM^** » 2010-07-06 23:40

Are there any SQLite free management tools around?

mattg · Post by **mattg** » 2010-07-07 04:16

I gotta say that the built in MySQL was a good thing when I started.

I wasn't that sure about databases, had played with MySQL and MS SQL a bit, but wasn't really sure about permissions and connectors and...

Anyway, once I did it easily with the 'built-in' MySQL I later tried external MySQL and have found it also to be easy.

There are a lot of people here who use the built-in database, more than few with too large an installation for MS SQL CE that has been included with Ver 5 so far. I think a built in database is a good idea, and I think that MySQL was a good choice for ver 4 and before.

Why is MySQL off the preferred list now?

Matt

DFitch · Post by **DFitch** » 2010-07-07 08:28

Postgresql
http://www.postgresql.org

Postgresql Admin Tools
http://www.pgadmin.org

dzekas · Post by **dzekas** » 2010-07-07 09:03

DeanoX wrote:What are other mail servers using now? Included relational database engines, flat files, windows registry?, It's been a while since I have looked at other mail servers.

Cyrus - Berkeley DB. Lots of small databases in files without central SQL server.
dbMail - everything is in SQL. Complains about performance of 750 GB MySQL database included.
qmail - system users by default + custom local delivery agent that can use anything (CDB, SQL) + patches that use LDAP.
postfix - system users by default, no SQL server dependency in default install, loads of options (including SQL) in config
sendmail - system users by default, custom local delivery agents, don't know what else. Stopped using it ages ago.
mercury/32 - something file based. never looked at it that close.

hMailServer needs DB to store information. Without it server would have to implement whole data storage subsystem. I suspect that your DB or no DB suggestion is closer to use basic file based database without server requirement or use full SQL server. Basic file based database will have concurrency issues, if data is concentrated in one place. Moore's Law does not apply to HDD sustained transfer rate.

StormChild · Post by **StormChild** » 2010-07-07 12:57

martin wrote:If you guys are wondering what's taking me so long, I can inform you that it's this's effect on choice of database backend.

I'm struggling with finding a database which is good enough for 95% of all users, which

Is free of charge

Supports concurrency

Can be shipped with open source software

Has good performance
The actual implementation of 5.4 is ready for beta, but this major part is still missing.

FireBird maybe? Just heard about it, but it fulfils - IMO - all of your (and so our

) requirements.
http://www.firebirdsql.org/index.php?id ... ird&nosb=1

And a feature comparison on WikiPedia:
http://en.wikipedia.org/wiki/Comparison ... nt_systems
FireBird's max DB size is unlimited, max table size is 32 GB...

StormChild · Post by **StormChild** » 2010-07-07 13:04

martin wrote:Well, people could add support for alternative databases even if there's one included by default. I don't see that including one would prevent anything.

Agreed!

hMailServer became the best because it just works out of the box with just a few settings.

martin wrote:As I wrote, I'm guessing a lot of people will feel that "i just want to install an email server, why would I want to install a database server?" if the installation program asked them to do just that. One could argue that these users are too lazy to install an email server anyway, though..

Yeah, I have selected hMailServer, because I'm a Linux noob, and I just hate 10-pages How-To's how to setup MySQL then the mail server - I know I'm lame, but I have just enough work to be happy if the installer takes care of almost everything...

[/quote]

So keep up the fantastic work, Martin, you're our hero, and I'm sure you'll find the best solution!

mattg · Post by **mattg** » 2010-07-07 17:00

StormChild wrote:FireBird maybe?

I have seen Firebird in action.

Great licence.
Not great at handling two instances on same machine. If something else on system already uses Firebird, there can be conflicts.
The application that I saw that used Firebird was very slow with small <150K database.
Tools to access Firebird are fairly limited and clunky in my very limited experience. I recall that the one I found was command line only (no GUI) and required a knowledge of SQL to get anywhere.

StormChild · Post by **StormChild** » 2010-07-07 17:12

mattg wrote:Tools to access Firebird are fairly limited and clunky in my very limited experience. I recall that the one I found was command line only (no GUI) and required a knowledge of SQL to get anywhere.

FireBird related stuff: http://www.ibphoenix.com/main.nfs?a=ibp ... b_download
As I said, I don't know if FireBird is good or bad as I've never used it - it's in rapid development, and according to the WikiPedia page it offers lots of interesting features. Maybe worth a try...

(A GUI: http://www.flamerobin.org/ - of course it's open source.

)

And the required SQL knowledge - Martin sure has some!

StormChild · Post by **StormChild** » 2010-07-09 02:24

... and another interesting article about FireBird vs. MS SQL Server Express - both are embedded versions:
http://www.firebirdsql.org/dotnetfirebi ... t-sql.html
(I know this article is more than five years old, but since then FireBird has progressed a lot, too.

)

rolaids0 · Post by **rolaids0** » 2010-07-09 21:35

My vote is to go with SQLite. If the user's need a way to manage the DBs then look at MySQL or MSSQL. It would be nice if migration scripts could be included as well... But those are things that could be written later and included in the contrib section. One thing I noticed (since we are on the topics of DBs) was that the schemas could use some fine tuning with regards to indexes. Some were missing ones that could improve performance on a heavy loaded system. Unfortunately, I didn't take note of those queries, that was only from the query logs from MySQL and strings out of the hMail exe.

Bill48105 · Post by **Bill48105** » 2010-07-10 03:03

rolaids0,
I agree sqlite might be ideal *if* it properly handles some multithreading without issues but I'm sorry I disagree 110% with the comment about saying users should just go to mysql or mssql if they need to manage the db's.. It would be irresponsible to integrate a sql backend with hmail & not provide a means to manage (view/repair/import/export) that database if there are not already tools for such things. It would be an absolute nightmare to support & have to tell people 'sorry you are SOL & should have used mysql instead' when they have database issues. I get a similar feeling now with msql ce in that it's 'part' of hmail but often it's almost treated like it's only partially supported.

I hate to sound like a broken record but the main reason I say release 5.4 without a database is so that the database portion can be considered a requirement & not some piece of hmail that requires support here and providing tools essential to managing that data. To me support in here for mysql & mssql is more of a courtesy (much like support for squirrelmail, roundcube, etc) to hmail users but integrating it obligates support in every aspect required for use, repair, backup & recovery.

I'd say there is such a backlog of things to do on hmail that the time would be much better spent on hmail rather than building a new database & those tools. My opinion but hopefully others agree.
Bill

rolaids0 · Post by **rolaids0** » 2010-07-10 05:08

Those tools already exist, and could be bundled. http://www.sqlite.org/download.html

As far as repair, not all REPAIR TABLE or CHECK TABLE will work, rare times you have to bust out a hex editor and fix the tables, but that's way beyond any n00b will be able to handle. So yes, SQLite would be a good fit 'cause there isn't another service to configure, open ports and firewall issues to worry about, and increased management overhead.

With regards to MT, http://www.sqlite.org/compile.html#threadsafe

Those users SHOULD go to MySQL or MSSQL if they are unable to manage the servers themselves. Pay the support fees and a consultant to set it up for them. I can understand having everything in the box to get hM running.

Bill48105 · Post by **Bill48105** » 2010-07-10 05:44

rolaids0 wrote:If the user's need a way to manage the DBs then look at MySQL or MSSQL

rolaids0 wrote:Those tools already exist, and could be bundled. http://www.sqlite.org/download.html

So which is it?

rolaids0 wrote:As far as repair, not all REPAIR TABLE or CHECK TABLE will work, rare times you have to bust out a hex editor and fix the tables, but that's way beyond any n00b will be able to handle. So yes, SQLite would be a good fit 'cause there isn't another service to configure, open ports and firewall issues to worry about, and increased management overhead.

I've been scolded about hex editing hmail exe & here you are suggesting someone do that to their precious data! I agree sqlite should be easier than dealing with installing & setting up mysql (although usually the hardest thing is guessing which default values to use..) but who else would use sqlite except a n00b anyway?? Isn't that the whole point of wanting an integrated db in 1st place??

rolaids0 wrote:With regards to MT, http://www.sqlite.org/compile.html#threadsafe

Yeah I've read. From that page you linked to:
"To put it another way, SQLITE_THREADSAFE=1 sets the default threading mode to Serialized"
Shall I post a link to where "Serialized" is described so you can tell me how well that'd work with something like hmail which has a bunch of threads & likely TONS of concurrent active connections needing to read/write the database? Sure it could be worked around (with retry loops and likely needing to split the data into as many different database files as possible) but far from ideal. Of course that all could be moot if what martin said is true in that it's been solved or improved in latest sqlite..

rolaids0 wrote:Those users SHOULD go to MySQL or MSSQL if they are unable to manage the servers themselves. Pay the support fees and a consultant to set it up for them. I can understand having everything in the box to get hM running.

Btw when I think of 'manage' I think of the non technical mail server novice who easily got hmail running thanks to integrated sqlite but then when the server locks up mid write of the database and it becomes corrupted & they come in here asking what to do.. Or that same user who has tried hmail & now wants to run it for real with 100's of domains & 1000's of users & you have to tell them sqlite can't do that well & they now need to migrate to mysql or mssql.. (Pretty much same thing happens over & over with mssql ce now) If there are tools available then great but if any db is integrated with hmail it needs to be supported & supportable and if integrating sqlite is to make hmail ez to eval there needs to be a way to migrate them to a 'real' db like mysql or you're looking for more aggravation than just not including a db & dealing with helping get mysql setup..

Anyway, in the end sure it'd be great if there was an ideal way to integrate db to make it ez but to me it seems there is no ideal as there are many trade-offs with the biggest being performance with a crippled db not really suited for an application like a mail server and the support headaches that come with it. And we're back to why I said don't include a db at all.
Bill

DeanoX · Post by **DeanoX** » 2010-07-10 06:15

This sure opened up a can of worms.

I truly believe, that in order to run a reliable database for an application of this nature, the database should be of a professional and production grade, with all the bells and whistles, and real commercial support, from the database vendor. And to me, that means MSSQL or MySql.

An included database, should just be for testing, and familiarization purposes only, and should have some restrictions, like Martin mentioned in an earlier post in this discussion.

This is just my humble opinion of the subject.

^DooM^ · Post by **^DooM^** » 2010-07-10 12:12

I agree on limitations. Having SQLite limited to a single domain and 10 accounts which is fine for testing and small home use, then if users want more, they should upgrade to a proper database of their choice.

Bill48105 wrote:I get a similar feeling now with msql ce in that it's 'part' of hmail but often it's almost treated like it's only partially supported.

I agree with that, a lot of people here prefer MySQL (myself included) and use it on a daily basis. I don't know many people that use MSSQL on here and thus support for it is largely left for the user to work out.

Caspar · Post by **Caspar** » 2010-07-11 11:20

^DooM^ wrote:I agree on limitations. Having SQLite limited to a single domain and 10 accounts which is fine for testing and small home use, then if users want more, they should upgrade to a proper database of their choice.

Bill48105 wrote:I get a similar feeling now with msql ce in that it's 'part' of hmail but often it's almost treated like it's only partially supported.
I agree with that, a lot of people here prefer MySQL (myself included) and use it on a daily basis. I don't know many people that use MSSQL on here and thus support for it is largely left for the user to work out.

I agreem with your satement about that MySQL is better supported, altough I have had issues in the past that restoring backups from MySQL won't work propperly, and is for me the reason to use MSSQL. And I must say, that even tough MSSQL is not always well known here, there are alot of online topics to help if needed.

Post by **martin** » 2010-07-11 13:01

Kind of unexpected, but it seems like Microsoft will change license for SQL Compact which will solve the problem with bundling it with hMailServer. Apparently it helps to send an email to them and simply complain of bad things in the license.

^DooM^ · Post by **^DooM^** » 2010-07-12 00:32

That works

bescher · Post by **bescher** » 2010-07-12 03:03

I am not a heavy user of Hmail. I run it on 3 different servers with 1 as a backup for the other 2. Total domains may be about 120 with about 400 users. Total emails rec and sent in 1 day may be close to about 4000 (that includes spam) I am not a programmer but I am a sys admin that deals half with hardware and half with software issues. I some some web work (html, java scripting etc) to be damgerous but I do know enough that as a new user I would understand the need to install MY SQL (Which I have and work with it using SQLYOG)
Martin as I said in the other post you do what you need to do. If people like your program they will avail themselves to install and learn what is needed (I did) part of that was with the great help of David Fitch) but I was willing. I started out with Post Office back in 1997, then went to Argosoft and now have been using Hmail for 3 years.
It works. It is pretty easy to set up (The manual could be simpler for new users) But it works
Just as the commercial says. It keeps on ticking and ticking and ticking

Keep up the good work

zeb · Post by **zeb** » 2010-07-13 11:36

Hi..
After a long time of using hMailserver I feel I have to contibute with some thoughts.
I have 4 hMailservers running along with an MS Exchange organisation with 150 users and
well over 100 servers. We use hMailserver for sending/recieving emails from servers-to-servers and auto import files from
emails to SQL.

A couple of thoughts:
hMailserver is by far the best free (and better then many commercial alternatives) mailserver on the Windows plattform.
It's very convenient to have everything in a database.
BUT the emails too should be in the database, not in files.
MS SQL 2008 Express with Management Studio is free and very supported in forums and by Microsoft,
and you can easy search the database,
It's very convenient to have a "built-in" option database engine.

Conclusions:
Choose a "built-in" DB engine, SQLite for example. Choose one(1) supported alternative SQL DB, MS SQL preferably.
Put the mail in the DB too. (and why not the configuration also?)
Those two options should be enough for 99.9% of the implementations.

dzekas · Post by **dzekas** » 2010-07-13 11:56

zeb wrote:Hi..
It's very convenient to have everything in a database.
BUT the emails too should be in the database, not in files.

Do you realize the consequences of having email data store in one huge file? What can go wrong?

Can you prove that you are DBA? I suspect that you are not or you forgot "do not store big binary blobs in DB" part.

hMailServer with MS SQL CompactEd had performance problems with indexing turned on. DB was only 30 MB. How what happens if 100K messages weighting over 2GB will go into that "database". What happens when you have 10M messages in data store?

zeb · Post by **zeb** » 2010-07-13 12:40

dzekas wrote:
zeb wrote:Hi..
It's very convenient to have everything in a database.
BUT the emails too should be in the database, not in files.
Do you realize the consequences of having email data store in one huge file? What can go wrong?

Can you prove that you are DBA? I suspect that you are not or you forgot "do not store big binary blobs in DB" part.

hMailServer with MS SQL CompactEd had performance problems with indexing turned on. DB was only 30 MB. How what happens if 100K messages weighting over 2GB will go into that "database". What happens when you have 10M messages in data store?

Was that with SQL Compact 3.5? I assume it was.
The only drawback with SQL 2008 Express R2 is the 1GB ram limit. The DB limit is 10GB though.
The SQL Compact 2008 R2 has no RAM limit but limits the DB to 4GB.

I'm running about 800GB of databases in SQL 2005 SP3 and have no issues with load or storing of files in the db's.
I'm a windows admin and SQLadmin but not a SQL developer, but a couple things do I know
becauce we develop .NET applications and SQL DB's.
It's up to the developer to solve those things, SQL works just fine...
With 10M emails in the store then you have a performance problem with any solution that can only be solved with RAM, CPU, disk and system I/O. Then you need a SQL 2008 R2 Workgroup Edition of higher.

Microsoft Exchange for example is a "single file" database with tranaction logs, and there is no such problems.

I'm not sure how to solve those possible problems with storing the mail in the DB but it would be very convenient.
Perhaps store the different parts(header, subject, body) of the email as text and link to the attatchment on the hard drive,
if it's hard to solve the attachment problems.

The two best things with a all-in-db solutions and that is backup/restore/moving the hMailserver to another server
and to be able to search all emails using TSQL.
Otherwise is just as good having the attachments on disk, or maybe even better then the attachments are directly accessible
for any other application.

So I revise my suggestion to storing the attachments on disk and the email in the DB.

^DooM^ · Post by **^DooM^** » 2010-07-13 13:01

zeb wrote:The two best things with a all-in-db solutions and that is backup/restore/moving the hMailserver to another server

I see no problems with the current setup, you have to backup the data directory along with the DB, it's not a difficult process to do.

zeb wrote:and to be able to search all emails using TSQL.

Windows text search works well, I also wrote a PHP application that scans through data folder looking for specific words, Emails do not need to be in the database to search through them.

zeb wrote:Otherwise is just as good having the attachments on disk, or maybe even better then the attachments are directly accessible
for any other application.

So I revise my suggestion to storing the attachments on disk and the email in the DB.

People do not always include attachments and directly paste the data to email body which would end up in the same situation. Current solution works perfectly well.

zeb · Post by **zeb** » 2010-07-13 13:24

^DooM^ wrote:
zeb wrote:The two best things with a all-in-db solutions and that is backup/restore/moving the hMailserver to another server
I see no problems with the current setup, you have to backup the data directory along with the DB, it's not a difficult process to do.

zeb wrote:and to be able to search all emails using TSQL.
Windows text search works well, I also wrote a PHP application that scans through data folder looking for specific words, Emails do not need to be in the database to search through them.

zeb wrote:Otherwise is just as good having the attachments on disk, or maybe even better then the attachments are directly accessible
for any other application.

So I revise my suggestion to storing the attachments on disk and the email in the DB.
People do not always include attachments and directly paste the data to email body which would end up in the same situation. Current solution works perfectly well.

Sure, things can be done in different ways, I just suggested the absolute best, easiest way. The Select command in SQL is very powerful and can be usd by different applications to access the emails, import data from them etc.
Is I said earlier we don't use hMailserver mail server for any employees but for importing power consumption files from our
partners and other data exchange between different systems. Then SQL would provided a nice interface to find emails which has not been imported by the "other" system, to export statistics etc. If the configuration was stored then it would also be easy to search and replace data.

This is from an admins point of view.

dzekas · Post by **dzekas** » 2010-07-13 13:32

zeb wrote: Sure, things can be done in different ways, I just suggested the absolute best, easiest way. The Select command in SQL is very powerful and can be usd by different applications to access the emails, import data from them etc.
Is I said earlier we don't use hMailserver mail server for any employees but for importing power consumption files from our
partners and other data exchange between different systems. Then SQL would provided a nice interface to find emails which has not been imported by the "other" system, to export statistics etc. If the configuration was stored then it would also be easy to search and replace data.

This is from an admins point of view.

SQL does not know anything about email formats and MIME stuff. Email data is binary blob for SQL. You can't do full text search in email within existing SQL specification.

Can you imagine how you backup 1 TB SQL data? I've already said. Moore's Law does not apply to hdd sustained transfer rate. If you must backup 1GB db file, your service might be unavailable until you copy that file. Do incremental backups work on SQL?

zeb · Post by **zeb** » 2010-07-13 14:01

dzekas wrote:
zeb wrote: Sure, things can be done in different ways, I just suggested the absolute best, easiest way. The Select command in SQL is very powerful and can be usd by different applications to access the emails, import data from them etc.
Is I said earlier we don't use hMailserver mail server for any employees but for importing power consumption files from our
partners and other data exchange between different systems. Then SQL would provided a nice interface to find emails which has not been imported by the "other" system, to export statistics etc. If the configuration was stored then it would also be easy to search and replace data.

This is from an admins point of view.
SQL does not know anything about email formats and MIME stuff. Email data is binary blob for SQL. You can't do full text search in email within existing SQL specification.

Can you imagine how you backup 1 TB SQL data? I've already said. Moore's Law does not apply to hdd sustained transfer rate. If you must backup 1GB db file, your service might be unavailable until you copy that file. Do incremental backups work on SQL?

No SQL doesn't "speak" mime, but there is where the developer comes in and reads/writes to SQL. And the application knows what the data is.
Therefor did I suggest putting all readable data in the database and the attachments to disk. If the email is readable in plain text then fine otherwise it isn't.

You can backup the transction logs on a daily basis or every hour if you want and restore single transactions if you like.
Backing up SQl databases does not lock the file. You can still access the database.
Having a single mailstore on 1TB is not acceptable for any implementation. You should then have subdomains and more servers.
This isn't specific to hMailserver but for any mailsystem or database.
But you should still limit individual mailbox sizes, otherwise you will end up with a full disk.

dzekas · Post by **dzekas** » 2010-07-13 14:34

zeb wrote: No SQL doesn't "speak" mime, but there is where the developer comes in and reads/writes to SQL.

Developer can also read and write file without SQL processing overhead.

If hMailServer stores data in SQL server, it does not control the way data is stored on SQL server.

zeb · Post by **zeb** » 2010-07-13 15:17

dzekas wrote:
zeb wrote: No SQL doesn't "speak" mime, but there is where the developer comes in and reads/writes to SQL.
Developer can also read and write file without SQL processing overhead.

If hMailServer stores data in SQL server, it does not control the way data is stored on SQL server.

Correct me if I'm wrong but yes you have control over the data. You are yourself as an developer creating the database, tables, properties, stored procedures, tables etc. Everything. If you store it in SQL you can also easily develop plug-ins which can access the data directly from SQL without hMailserver.
But it's just suggestions for hMailserver to do it this way so you doesn't have to re-invent everything.

If hMailserver was "feature complete", then yes, you wouldn't need anything else. Like Microsoft Exchange, which is only lacking of built-in backup tools.
If everything was stored in SQL you can use any backup tool you like, export data anyway you like etc.
You could develop a webgui directly against the DB, if you like.
Then hMailserver only would be one way to access and alter the data, you could combine it if you, again, would like.

But this thread wasn't about using SQL or not but which database backend to choose, was it?
I just pointed out a coupe of locical choices, MS SQL over MySQL or other free MS Windows DB servers.
AND gave a couple of more features that you could get if you moved more stuff into the DB.

I may have some problems with understanding the technical issues with storing/reading MIME data in a table
but otherwise I think my thoughts on the benefits of SQL as storage for hMailServer is correct.

I have developers of an LARGE application who insists on storing the data in XML files (they like some thing in XML) but they doesn't understand
the problems they are causing for the people hosting and administering the application.
Like failover, clustering, writing to the same files from different application servers etc.
Stuff that could be solved in a minute by using SQL instead.

We have a couple of thousends of clients accessing one of our servers and if that server is down all of them are down.
A combination of application servers in a DNS cluster (a host name with many ip's behind it) and SQL cluster would solve this
with standard Microsoft Windows Server services. (the MS SQL part of this costs "some" extra money but you could achieve this with MySQl also)
They don't have to re-invent it.

By putting the data and config in SQl you are making the data non-proprietary i.e. you can access/use it however you like.

dzekas · Post by **dzekas** » 2010-07-13 15:41

zeb wrote:By putting the data and config in SQl you are making the data non-proprietary i.e. you can access/use it however you like.

Data is still proprietary, because you must know DB layout.

You don't like XML developers, who forget to make their data human readable and write all xml in one line, yet you are ok with storing similar f..ked up data in SQL.

hMailServer can control the way data is stored, but it can't control the way server stores database. It does not matter how many tables you have in SQLite, you still have one huge db file with file locking issues. Having myriad of user+folder_name+somename generated tables in SQL server does not make it nice.

If you want to learn how mail in DB is doing, see DBmail. See complains by users with large databases and all the nursing needed to keep these databases in shape.

There are some things that hMailServer can store in database, but in some cases these things should not be stored in main db. They should go to smaller db files in users' data directory.

zeb · Post by **zeb** » 2010-07-13 15:52

Data is still proprietary, because you must know DB layout.

If hMailServer is Open Source and the SQL server is in your hand then it's not proprietary in reality.
And you document the DB layout, of cource.
You could also have more then 1 DB, 1 per domain for example,
with the conncetion string settings in the domain properties in hMailServer...
And you could have 1 DB for SMTP queue... loads of possibilities in my opinion.

Bill48105 · Post by **Bill48105** » 2010-07-13 17:07

I for one vote a big NO against having all emails in the database for reasons dzekas clearly pointed out among others. Although I could see the potential for benefit (perhaps multiple hmail servers being able to have a go at it for redunancy/failover) I don't see those benefits outweighing the headaches.

Btw I am no DB pro (quite a novice actually) but would the mail need to be stored as binary blob since it's already encoded as text for emailing?? Still a huge amount of large chunks of data to deal with across multiple means (one large data file, commands to store/retrieve over & above OS file system and transport over TCP/IP for most DB systems) vs just accessing the txt file directly as it is now..

Good news is once martin releases hmail as open source again, soon zeb or whoever wants to can modify away until they are happy with their own customized version.

Bill

rolaids0 · Post by **rolaids0** » 2010-07-13 17:11

It is considered bad form to put lots of blob data into a DB. Exchange does the single (MDB) format database, however, uses huge indexes to manage that amount of data. There are pro's and con's to each.

The biggest pro, in my mind, is automatic replication with out having to rely on a single iSCSI target for clustering. Each server could have its own MySQL instance with circular replication setup. This requires some tuning of variables (auto-increment) but is doable and has been shown to work in production (for other types of systems.)

The biggest con is database maintenance. Consider 1000's of entries being added/deleted every day. The rebuilding of indexes and compaction of the tables (scrubbing of deleted records) will cause all kinds of headaches, no DB server is immune to this. Also, as a word of warning, if you use MySQL (I'm not sure about MSSQL) you'll want to look at max_allowed_packet. As a suggestion, it may need to be set to 2X the max message size (not rooted in facts, but an educated guess for other blob implementations.)

hMail does already store some metadata about emails, see the hm_message_metadata table. I saw at least a 10X speed improvement when this was enabled over not being enabled (having to scan all files.) It would be interesting to see what happens if someone were to move all messages into the DB, have 1000's of entries added/deleted and do some real research into this. Once the source is released I could take a look and see. I can see how this could be useful for more advanced users. Again, those users will have 1000's of items to manage so that needs to be accounted for in testing. This may also move into SIS as well. This would also clear up all of those complaints about all of the folders created ;-D

zeb · Post by **zeb** » 2010-07-13 17:21

A large database without proper indexing is a waste of time,
as a fact, carefully indexed database is crucial.
It makes the difference between superslow and superfast on the same hardware.
There is no doubt about it, MS SQL is absolutely capable for the job.
It leaves it up to the developer to tune the DB for the application.

rolaids0 · Post by **rolaids0** » 2010-07-13 17:49

MSSQL CE/Express is absolutely NOT capable for the job. Users MUST go with at least Workgroup ED which is NOT free. If it is going to be built into the software to store the messages in the DB, then the DB that is shipped should be of comparable price and up to the task. I'm well over the 4GB limit of Compact 3.5 and I'm almost to the 10GB limit of Express (2008) in the amount of stored messages.

If one already had workgroup or better licenses, then sure no problem. It seems, however, the general audience of this product (people not spending a fortune on a mail server) will probably look to MySQL to fit their needs.

I 100% agree about indexes, and as is (last release) there could stand to use a few more in the default install. I added several after it was installed (looking at the query log and the hmail binaries.)

MSSQL 2005 EDs
MSSQL 2008/R2 EDs
MSSQL 2008 R2 Pricing

zeb · Post by **zeb** » 2010-07-13 17:58

If one already had workgroup or better licenses, then sure no problem. It seems, however, the general audience of this product (people not spending a fortune on a mail server) will probably look to MySQL to fit their needs.

Then I don't know if MySQL or PostgreSQL or any other will be the best chioce..
I know some ppl that chose PostgreSQL over MySQL for performance. But for administratio, clustering or other issues I don't know.
BUT, all of these SQL servers are better alternatives then file based databases in my opinion.

rolaids0 · Post by **rolaids0** » 2010-07-13 18:02

Well, as it is, PostgreSQL isn't supported. I have read and seen that PostgreSQL is quicker than MySQL. My point was that MySQL support is there, and once the source is released then it would probably not take much to add DB based storage. But benchmark, benchmark, benchmark.

I like the idea and might actually do some clustering that way.

pepsi · Post by **pepsi** » 2010-07-14 08:50

rolaids0 wrote:Well, as it is, PostgreSQL isn't supported. I have read and seen that PostgreSQL is quicker than MySQL. My point was that MySQL support is there, and once the source is released then it would probably not take much to add DB based storage. But benchmark, benchmark, benchmark.

I like the idea and might actually do some clustering that way.

Why is ProgreSQL not supported.
http://www.hmailserver.com/documentatio ... ase_engine
it runs smooth and I hope that it stays supported

rolaids0 · Post by **rolaids0** » 2010-07-14 13:04

I stand corrected.

Bill48105 · Post by **Bill48105** » 2010-07-14 16:30

Wow I thought ProgreSQL wasn't supported either but maybe it's just seems that no one ever discusses it in here in the forums.. I assume it must be command/function compatible with mysql or msql to work (without explicit support) since the old 4.4.4 source code only shows mssql vs mysql conditions before executing SQL commands.. Never saw ProgreSQL anywhere in the code.. I guess if I looked close enough I'd see which is the else 'default' and know but again never had a reason to as I assumed it was ms or my & nothing else.

Guess any day without learning something new is a wasted day.

Bill

mattg · Post by **mattg** » 2010-07-15 14:01

PostgreSQL (as opposed to ProgreSQL) is supported by hMailserver, but has only been supported since version 5.

I don't recall seeing a query here about it's use...

Matt

westdam · Post by **westdam** » 2010-07-19 13:42

becouse postgres runs perfect on a linux machine, on windows it's slooooow.....
anyway , why you would leave ms compact edition martin? licensing? performance?

Post by **martin** » 2010-07-19 13:46

westdam, if you read through the thread, you will probably get a hint. I don't feel like repeating the same thing over and over again, just because the discussions have gone off-topic..

westdam · Post by **westdam** » 2010-07-19 14:53

bof, was just a question,i've read and understand.

johndow · Post by **johndow** » 2010-07-20 00:14

We would never ever find a perfect / suitable / for a all welcome database engine...
Many people have many opinions.

My suggestion:
SQL Syntax is in most parts similar as long as you don't use stored procedures etc.

Sybase, MS - both are talking in a TSQL Slang.
MySQL, Postgres, SQLite, etc. - I don't know.

Add some of the databases to your mailserver as you want and make a kind of "open API" for the database connection which means if someone is a coder he/she can write his/her own database connection. The only difference is, that in your software is a setting for which .dll should be used for a database communication. Or - more simply - if the communication API to the database .dll is well known, someone can rename his database .dll for using as connector.

Storing emails in a database:
Pro:
- "could" be faster as a file only storage (depending on your hardware).

Contra:
- Repair a mailserver with emails stored in a database server with a 12 hrs Backup system for the database... You would like it one time in your live and then never again.
- No, thank you.
- So long, and thanks for all the fish

westdam · Post by **westdam** » 2010-07-20 10:29

bescher wrote:I am not a heavy user of Hmail. I run it on 3 different servers with 1 as a backup for the other 2. Total domains may be about 120 with about 400 users. Total emails rec and sent in 1 day may be close to about 4000 (that includes spam) I am not a programmer but I am a sys admin that deals half with hardware and half with software issues. I some some web work (html, java scripting etc) to be damgerous but I do know enough that as a new user I would understand the need to install MY SQL (Which I have and work with it using SQLYOG)
Martin as I said in the other post you do what you need to do. If people like your program they will avail themselves to install and learn what is needed (I did) part of that was with the great help of David Fitch) but I was willing. I started out with Post Office back in 1997, then went to Argosoft and now have been using Hmail for 3 years.
It works. It is pretty easy to set up (The manual could be simpler for new users) But it works
Just as the commercial says. It keeps on ticking and ticking and ticking

Keep up the good work

i Remember bob with argo....the only one with no trouble with it

yes hmail is simple, fast and stable.

it's the perfect mail backend for an SMTP gateway ( like ASSP,eWall,Caronte ecc )

i think sql lite should be the real competitor for mssql compact edition.

PeterK2003 · Post by **PeterK2003** » 2010-07-20 16:34

I think i am out the loop a bit but why are we abandoning MySQL?

I happen to like it though i am sure oracle will fubar it a few years.

will i still be able to use my dedicated mysql server in the new version if you choose to go with something else?

Whatever you pick it would be nice if it could be installed on Linux seeing as my DB server is running Ubuntu. postgre maybe? or mariaDB?

Thanks,
Peter

Bill48105 · Post by **Bill48105** » 2010-07-20 16:52

Peter,
There is no plan/talk of abandoning mysql or other databases currently supported.. The reason for the post was martin announced hmail was going open source again & as such the licensing of the mssql ce that he had been INCLUDING (read that as built-in by default not as replacing other DB options..) wasn't going to be allowed to be used any more and he was asking people for input on what to use instead. (The 50 posts earlier in this thread..) In there he announced that Microsoft was going to let him still use mssql ce (or Express I forgot now) so I'm guessing he plans to use that still.
Bill

PeterK2003 · Post by **PeterK2003** » 2010-07-20 17:01

ohh i thought that MySQL was what was bundled in...

mattg · Post by **mattg** » 2010-07-20 17:07

it used to be in ver 4 and previosu versions.

Since Ver 5.0 hMailserver hasn't been open source, and then changed to MS SQL CE so as not to violate the MY SQL licence.

Now hMailserver is going back to open source