I will agree with Kym on this one..
I am very pro-sql, and would love to be able to pull message from SQL..
However...
I see the core reason as maintenance..
In a sql server, your "header" message record, and the detail message data
is in 1 record (or related at least);
however currently, you have a header message record and a file - it's very
easy for these 2 to get out of sync.
I am working with a financial reporting system, there are several hundred
thousand PDF files stored in SQL (average size of 55k - (27gig database)),
and although maintenance is QUITE easy, the performance in MANY aspects
sucks.
It's not the size of the db either - some benchmarks were done to compare
the time it take for sql to pull out that data from the db vs. the file
format. Also (I think this is a big thing) - it's not just cpu time, but
memory - sql likes to load stuff in memory to be more efficient. If you
read a file, it doesn't keep it in memory, sql doesn't release memory the
way it *should* in this scenario.
I still think that sql is very cool, but the e-mail files should be kept in
the e-mail text files.. any other company that puts them in adatabase will
be making a boo-boo (at least for any intense mail server.. just my opinion
though..
And no, I don't have number benchmarks either -they aren't significant per
"Request" - but as we know, all it takes is 100ms difference to really screw
up something - or the extra 5k per requet of unreleased memory (x's that by
a bunch, and you have some problems)..
eh, just my opinion - it's late, and I'm waaaay buzzed :)
lata -Tom
----- Original Message -----
From: Kym Kovan <[EMAIL PROTECTED]>
To: inFusion Support List <[EMAIL PROTECTED]>
Sent: Friday, July 28, 2000 9:19 PM
Subject: Re: [iMS] SQL File Storage
> Hi all,
>
> Saturday morning, and here I am back at it, why do we do this? :-)
>
> Referring in general to the overnight posts I will fill in a bit of detail
> and hopefully answer a few things, from our perspective here at least.
>
> You cannot store "everything" in an SQL text field. some chars are still
> interpreted as operators even tho' they are in the middle of a bit of
text,
> its a real pain. Some examples:-
>
> standard bit of SQL:
> insert into Table
> (textfield)
> values ('this is a bit of text')
>
> that works fine, this won't:
> values ('this is a bit of text with a < in it')
>
> This means you have to escape or encode everything you put in the field.
> That means that searching on that field is made a lot harder unless you
> make an encoding tag that leaves the spaces there so that it still makes
> vague sense to the search indexing engine.
>
> Regarding performance between a database or a file the answer would depend
> on platforms and all sorts of things like mail server system size. A big,
> busy mail server would very rapidly have a very large mail database which
> would create a performance hit. A file-based system doesn't care how big
> and busy things are, the overhead is the same all the time.
>
> To give an idea, we run our production mail server on the same platform as
> when we started testing the whole thing back at the end of last year, when
> I was hassling Howie almost daily as we worked thru the "hidden code
> curiosities" :-) That platform is a 120MHZ pentium box with CF4 on it,
not
> very powerful at all by modern standards, with an external database, on
one
> of our production DB servers. It pounds out somewhere between 10 to 30
> thousand messages a day. When it is posting it runs flat out, 100% useage,
> when you POP in to collect mail it spits things out as fast as the network
> and the mail client can handle it. That is using a file-based message
store.
>
> We are about to move the server to a 700MHz Athlon box and we will leave
> everything else the same and then we will do a fair bit of performance
> comparison to see how things have changed.
>
> Currently I feel a hybrid system is the way to go with message data in a
> DB, messages themselves in files (overcoming database/ODBC limitations on
> the way) with a search engine parsing those files with its results in a
DB.
>
>
> Now I must pop out and get some food. Its sometimes an advantage living
> over the shop in the middle of a small shopping centre, I never have far
to
> go to stop myself from starving :-)
>
> BTW, its 18C outside, (say 70F for you old imperial folk), sun blazing
> down, more than just warm on the skin, blue sky, lovely weather. And its
> winter :^)
>
>
>
> --
>
> Yours,
>
> Kym
>
> ========================================================================
> This list server is Powered by iMS
> 'The Swiss Army Knife of Mail Servers'
> --------------------------------------
> To leave this list please complete the form at
> http://www.CoolFusion.com/iMS.htm
>
> List archives: http://www.mail-archive.com/infusion-email%40eoscape.com/
> ========================================================================
========================================================================
This list server is Powered by iMS
'The Swiss Army Knife of Mail Servers'
--------------------------------------
To leave this list please complete the form at
http://www.CoolFusion.com/iMS.htm
List archives: http://www.mail-archive.com/infusion-email%40eoscape.com/
========================================================================
