Saving messages in hm_messages table

This forum contains features that has been archived. This section contains implemented features, duplicate requests, and requests which we have decided not to implement.
Locked
User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Saving messages in hm_messages table

Post by martin » 2004-04-30 19:42

Lechuga wants hMailServer to save messages in the hm_messages table instead of the data directory.

As I see it, the problem with this is that hMailServer will have to keep entire emails in memory which lead to high memory consumption if you have a lot of users downloading large emails at the same time.
It wouldn't be good if evil people could bring down the whole windows machine just by sending a huge attachment.

Anyone has any thoughts about this?

User avatar
Lechuga
New user
New user
Posts: 16
Joined: 2004-04-30 12:32
Location: Madrid, Spain

Post by Lechuga » 2004-04-30 23:42

I have several reasons as to why storing a message in a database rather than on the hard disk:
1. Simpler backup. If the messages are stored in the database a simple mysql_dump would spill all the goods.
2. Scalability and fault tolerance. You could setup multiple hmailserver machines acting as frontend to a central database. Users could connect to any frontend machine and fetch their email.
3. Others are doing it, so why not.

AFAIK, the memory used is the same regardless if the message is stored in a database or on the harddisk. True you could double the memory if you hold the database record in memory in addition to the variable used by hM; however I fail to see the difference with opening a file handle (it also doubles the memory). The memory usage is still the same.

If memory is a concern:
a. split the message body over several records in the database. Then load each record in sequence; this way you will not double the memory requirements.
b. Parse the envelope and body, then store significant info (eg: how many attachments, their names and size) in another table.

I am sorry, I did not want to start a flame-war or anything. I think hmailserver is a wonderful and beautiful tool; I wish I could contribute more. I have some ideas (eg: if martin can code a call to an external app - say a .bat file - I can code the interface to the windows version of clamav :wink: ), but I don't have much spare time.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2004-05-01 08:41

Hehe. I'll do a test implementation for it in version 3.1. If it works out fine and I can keep down the memory usage, I'll include it.
:)

gglaze
Normal user
Normal user
Posts: 51
Joined: 2004-04-20 15:02

Post by gglaze » 2004-05-03 11:37

Yeah I'd definitely like to see this as an (optionally) available feature. Please don't force us to use it, but give us the option!
8)

User avatar
olger901
Normal user
Normal user
Posts: 186
Joined: 2004-02-07 20:44

Post by olger901 » 2004-05-05 11:19

Could you please make an option to choose from like use files, or use MySQL database for mailstorage, since I like this mailserver because it doesn't use much memory and I dont have much memory in my server.
Thanks in Advance

BigJim
New user
New user
Posts: 2
Joined: 2004-03-22 15:55

Post by BigJim » 2004-06-02 09:55

olger901 wrote:Could you please make an option to choose from like use files, or use MySQL database for mailstorage, since I like this mailserver because it doesn't use much memory and I dont have much memory in my server.
Thanks in Advance
I think it is a good choice

adipose
New user
New user
Posts: 24
Joined: 2004-05-28 23:55

Huge emails bringing down server

Post by adipose » 2004-06-02 23:31

First of all, mysql has an option to limit the size of queries, which would probably have to be tweaked to allow large attachments. On the other hand, most mail servers have reasonable limitations for email size. Obviously, you'll need to implement something like this, preferably user-modifiable, that is smaller than the limits imposed by mysql.

You should implement a max message size whether or not you store data in the db, I think.

-Dan

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Huge emails bringing down server

Post by martin » 2004-06-03 00:22

adipose wrote:You should implement a max message size whether or not you store data in the db, I think.
Good idéa. Add it in the tracker! :-)

Jon
New user
New user
Posts: 5
Joined: 2004-04-25 08:35
Location: London

Post by Jon » 2004-06-03 19:24

I'd like to see messages stored in the database, however I think its a mad idea to start saving messages in different places depending on message size. why bother ? Just split up the message into chunks. There's a project called dbmail (http://www.dbmail.org) which is linux based and uses a mysql backend, they save the messages in the database, maybe worth a look as to how they do it.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2004-06-10 22:42

I have made some tests and it's messy to save it in the database. I will have to write a lot of ugly code to get this to work.
Messages will have to be split up in chunks, escaped, and inserted.. Then fetched, un-escaped and "un-chuncked again"

I feel that there's a risk that the stability of the server will be decreased if I implement this... :-<


On a scale from 1 to 10, how important is this?

calvi
Normal user
Normal user
Posts: 65
Joined: 2004-03-17 23:34
Location: Melbourne, Australia

Post by calvi » 2004-06-11 00:26

I thought it sounded like a good feature, but I can see the road to data corruption when you start splitting files up and databasing them. If the messages are stored as files its easy enough to recover them, if something goes wrong. I think as long as you can nominate where they are stored, backing them up is not a problem. hmailserver.ini has an entry to nominate the data directory. This suits me fine. I can make this directory secure and located with all the other data backed up on the system.

I can then easily search for messages if something goes wrong.

All said and done I would not introduce alot of "messy" code for this feature when I think there are alot of other more important features that can be worked on.

John.

polarunion
Normal user
Normal user
Posts: 245
Joined: 2004-04-05 20:21
Location: Ottawa, Canada
Contact:

Post by polarunion » 2004-06-11 02:01

Personally I see no advantage to this.... does anyone have any "clear" advantages? How is it more efficient that they are handled in this complicated way by the database?


At the moment hmailserver can be very easily backed up.

I ran into problems the other day and just took a copy of a backed up hmailserver folder and reinstalled it. all turned out fine and i lost only a minimal amount of mails.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2004-06-11 07:53

Lechuga mentions the following reasons:
1. Simpler backup. If the messages are stored in the database a simple mysql_dump would spill all the goods.
2. Scalability and fault tolerance. You could setup multiple hmailserver machines acting as frontend to a central database. Users could connect to any frontend machine and fetch their email.
3. Others are doing it, so why not.
1. I guess this one is true. It will be simpler to backup since there's only one thing to backup instead of two. But I'm already thinging of built in backup that creates zip files from all the data in the db and disk.
2. This can be achived without saving messages in the database.
3. No good reason.

grynet
Normal user
Normal user
Posts: 53
Joined: 2004-02-16 10:51
Location: Värnamo, Sweden
Contact:

Post by grynet » 2004-06-11 09:52

I like the zipfile idea, our backupsystem is said to handle replication of "active" MySQL files. But The real world is a bit different. There are losses to files in process.

Using the SQL statement: "BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'" (For a snapshot use LOCKTABLE and FLUSH prior BACKUP) ...seems tho BACKUP TABLE is depricated.

The manual referes to a "mysqlhotcopy" script ...so it might be better to wait.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2004-06-11 12:59

IMAP clients move a message between two folders by first doing a COPY and after that executing a DELETE. If the backup software makes a backup between theese two commands, the backup will be invalid.

Can't see any way that this could be properly implemented in external backup software.

grynet
Normal user
Normal user
Posts: 53
Joined: 2004-02-16 10:51
Location: Värnamo, Sweden
Contact:

Post by grynet » 2004-06-11 14:10

The backup can never be better than the actual snapshot. It's possible if the services are all temporarily shutdown during the backup.

Therefore would the applikation-scheduled-.zip-model-backup be nice.

Locked