High volume (200k+ cached daily) questions

Please excuse the length of this post. I'm not looking to
solve a 
"problem", but hoping for input about a high
volume configuration.

I'm reaching out for thoughts or experience on what the db
backend needs 
to be to handle, say, 1 million processed, and 200k+ cached
entries 
daily. I expect to expire out of quarantine at 3 days. Peak
loads could 
easily top several thousand messages/second.

We have 4 2xquad core Xeon, 8G machines as the
Postfix/amavisd-maia/SA 
front ends. Postfix rules are set up pretty strict.
All machines are running FreeBSD 7 amd64

The plan was to have them work with a single Mysql 5 machine
for all 
database functions and Maia interface. We brought this
on-line over the 
weekend, and it worked ok for maybe 36 hours, then started
getting db 
connect failures. My boss' analysis was showing the db
server dieing at 
about 100 connections/sec, when it needed to be more like
500. The 
constriction was definitely I/O.  All manner of InnoDB and
MySql tuning 
have been tried (all the right ones? correctly? We think so,
but could 
of course be wrong).

Its possible I triggered this by confirming two or three
thousand 
messages within 1/2 hour, but the system will need to handle
that.

Now, the db server definitely was a little lightweight
(SATAII drives, 
4G RAM), but our concern is that even if we go to 15K SCSI
drives and 
say 16G RAM, will it actually work? Thats a lot of money on
a bet, and 
unfortunately, time for real testing isn't available

MySQL 5 clustering looks out from my point of view, based
just on the 
requirement that the DB(s) be held in memory. Much memory
will be 
required as the caches grow to their max. But then I've
never done MySQL 
clustering, so maybe I'm not understanding correctly.

Splitting the bayes off to a separate machine seems pretty
necessary

Multi threaded process-quarantine, or another way to process
confirmed 
cache entries?

As I write this, I suppose its obvious that the answer is
primarily to 
sink money into drives, controller and memory, regardless of
any thing else.

Thanks for your time, and I will happily provide details
I've missed.

Tim
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

You need to provide more info.

How is the resource utilization on the mysql server, disk io
and cpu?

If I understand, you want to process 1 million emails daily
but your MySQL 
is dieing at 100 connections?  What kind of tuning have you
done to the 
MySQL config?  The default config would have to me modified
to handle the 
load you are talking about.

Todd



----- Original Message ----- 
From: "Tim Palmer" <timtany.com>
To: <maia-usersrenaissoft.com>
Sent: Monday, March 03, 2008 7:38 PM
Subject: [Maia-users] High volume (200k+ cached daily)
questions


> Please excuse the length of this post. I'm not looking
to solve a
> "problem", but hoping for input about a high
volume configuration.
>
> I'm reaching out for thoughts or experience on what the
db backend needs
> to be to handle, say, 1 million processed, and 200k+
cached entries
> daily. I expect to expire out of quarantine at 3 days.
Peak loads could
> easily top several thousand messages/second.
>
> We have 4 2xquad core Xeon, 8G machines as the
Postfix/amavisd-maia/SA
> front ends. Postfix rules are set up pretty strict.
> All machines are running FreeBSD 7 amd64
>
> The plan was to have them work with a single Mysql 5
machine for all
> database functions and Maia interface. We brought this
on-line over the
> weekend, and it worked ok for maybe 36 hours, then
started getting db
> connect failures. My boss' analysis was showing the db
server dieing at
> about 100 connections/sec, when it needed to be more
like 500. The
> constriction was definitely I/O.  All manner of InnoDB
and MySql tuning
> have been tried (all the right ones? correctly? We
think so, but could
> of course be wrong).
>
> Its possible I triggered this by confirming two or
three thousand
> messages within 1/2 hour, but the system will need to
handle that.
>
> Now, the db server definitely was a little lightweight
(SATAII drives,
> 4G RAM), but our concern is that even if we go to 15K
SCSI drives and
> say 16G RAM, will it actually work? Thats a lot of
money on a bet, and
> unfortunately, time for real testing isn't available
>
> MySQL 5 clustering looks out from my point of view,
based just on the
> requirement that the DB(s) be held in memory. Much
memory will be
> required as the caches grow to their max. But then I've
never done MySQL
> clustering, so maybe I'm not understanding correctly.
>
> Splitting the bayes off to a separate machine seems
pretty necessary
>
> Multi threaded process-quarantine, or another way to
process confirmed
> cache entries?
>
> As I write this, I suppose its obvious that the answer
is primarily to
> sink money into drives, controller and memory,
regardless of any thing 
> else.
>
> Thanks for your time, and I will happily provide
details I've missed.
>
> Tim
> _______________________________________________
> Maia-users mailing list
> Maia-usersrenaissoft.com
> http://www.renaissoft.com/mailman/listinfo/maia-users
> 

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On Mar 3, 2008, at 7:38 PM, Tim Palmer wrote:
>
> Now, the db server definitely was a little lightweight
(SATAII drives,
> 4G RAM), but our concern is that even if we go to 15K
SCSI drives and
> say 16G RAM, will it actually work? Thats a lot of
money on a bet, and
> unfortunately, time for real testing isn't available

Probably doing many drives in a raid 1+0 would help - IO is
going to  
be a real issue.  This is not the place to go cheap on a
raid card!


> MySQL 5 clustering looks out from my point of view,
based just on the
> requirement that the DB(s) be held in memory. Much
memory will be
> required as the caches grow to their max. But then I've
never done  
> MySQL
> clustering, so maybe I'm not understanding correctly.
>

That is our current understanding too, and as such it seems
very  
insufficient to hold a cache of emails.

I'm curious about the latest postgresql, as there are some
clustering  
options, and the latest version has some other option that
look good  
for bayes performance.   But at this point, I have not
tested it at  
all.   I also suspect that clustering will always have some
problems  
since it still has to sync the data.


> Splitting the bayes off to a separate machine seems
pretty necessary

Yes, that's an easy one - the bayes handling is orthogonal
to Maia.

> Multi threaded process-quarantine, or another way to
process confirmed
> cache entries?

If the bayes processing is the problem, then splitting it
onto another  
database would help.  Another option would be to use bdb for
the  
bayes, but turn off all updating on the scanners ... ie, the
scanners  
all have read only access to the bayes... then do the
process- 
quarantine on a dedicated host, and rsync the bdb files to
the other  
servers on a regular basis.   I haven't set this up myself,
but I have  
heard of some who have done this.


> As I write this, I suppose its obvious that the answer
is primarily to
> sink money into drives, controller and memory,
regardless of any  
> thing else.

The database is certainly the money eater.

David Morton
Maia Mailguard http://www.maiamailguard
.com
mortondadgrmm.net



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (Darwin)

iD4DBQFHzNILUy30ODPkzl0RAsEBAKCNkPF8ATVOZctsTS94mWEOF9KWcwCY
xNaa
FC12sGTtvMukgOdLfzY2FQ==
=UKC7
-----END PGP SIGNATURE-----
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

I wasn't there when the primary event happened (db connects
failing 
regularly), but from what I heard, CPU and memory
utilization were fine 
- the problem was disk I/O. Don't have the numbers,
however.

100 db connects per second (or a little more) is where mysql
topped out. 
Each connection is fairly large query, generally requiring
database 
reads. My boss is pretty proficient in mysql, and general
optimization. 
I don't know exactly what he did for tuning, but he
certainly would have 
worked with connection pooling, InnoDB settings and anything
else he 
could think of.

Greylisting was not active when this was happening, but is
active now 
(we had to bump back to amavisd-new until we get this worked
out).

I suppose a more fundamental question is - can the Maia
architecture 
handle this load without spending 10s of thousands on the db
server. 
Several thousand we can deal with, but 10s gets to be a
problem.

Everyone expects spam filtering these days, but to charge
for it? well, 
thats another story 

thanks for your response.

tim

Todd wrote:
> You need to provide more info.
>
> How is the resource utilization on the mysql server,
disk io and cpu?
>
> If I understand, you want to process 1 million emails
daily but your 
> MySQL is dieing at 100 connections?  What kind of
tuning have you done 
> to the MySQL config?  The default config would have to
me modified to 
> handle the load you are talking about.
>
> Todd
>
>
>
> ----- Original Message ----- From: "Tim
Palmer" <timtany.com>
> To: <maia-usersrenaissoft.com>
> Sent: Monday, March 03, 2008 7:38 PM
> Subject: [Maia-users] High volume (200k+ cached daily)
questions
>
>
>> Please excuse the length of this post. I'm not
looking to solve a
>> "problem", but hoping for input about a
high volume configuration.
>>
>> I'm reaching out for thoughts or experience on what
the db backend needs
>> to be to handle, say, 1 million processed, and
200k+ cached entries
>> daily. I expect to expire out of quarantine at 3
days. Peak loads could
>> easily top several thousand messages/second.
>>
>> We have 4 2xquad core Xeon, 8G machines as the
Postfix/amavisd-maia/SA
>> front ends. Postfix rules are set up pretty
strict.
>> All machines are running FreeBSD 7 amd64
>>
>> The plan was to have them work with a single Mysql
5 machine for all
>> database functions and Maia interface. We brought
this on-line over the
>> weekend, and it worked ok for maybe 36 hours, then
started getting db
>> connect failures. My boss' analysis was showing the
db server dieing at
>> about 100 connections/sec, when it needed to be
more like 500. The
>> constriction was definitely I/O.  All manner of
InnoDB and MySql tuning
>> have been tried (all the right ones? correctly? We
think so, but could
>> of course be wrong).
>>
>> Its possible I triggered this by confirming two or
three thousand
>> messages within 1/2 hour, but the system will need
to handle that.
>>
>> Now, the db server definitely was a little
lightweight (SATAII drives,
>> 4G RAM), but our concern is that even if we go to
15K SCSI drives and
>> say 16G RAM, will it actually work? Thats a lot of
money on a bet, and
>> unfortunately, time for real testing isn't
available
>>
>> MySQL 5 clustering looks out from my point of view,
based just on the
>> requirement that the DB(s) be held in memory. Much
memory will be
>> required as the caches grow to their max. But then
I've never done MySQL
>> clustering, so maybe I'm not understanding
correctly.
>>
>> Splitting the bayes off to a separate machine seems
pretty necessary
>>
>> Multi threaded process-quarantine, or another way
to process confirmed
>> cache entries?
>>
>> As I write this, I suppose its obvious that the
answer is primarily to
>> sink money into drives, controller and memory,
regardless of any 
>> thing else.
>>
>> Thanks for your time, and I will happily provide
details I've missed.
>>
>> Tim
>> _______________________________________________
>> Maia-users mailing list
>> Maia-usersrenaissoft.com
>> http://www.renaissoft.com/mailman/listinfo/maia-users
>>
>
>

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

David - thanks for your response, and while I'm at it, for a
tremendous 
bit of software!

David Morton wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> On Mar 3, 2008, at 7:38 PM, Tim Palmer wrote:
>>
>> Now, the db server definitely was a little
lightweight (SATAII drives,
>> 4G RAM), but our concern is that even if we go to
15K SCSI drives and
>> say 16G RAM, will it actually work? Thats a lot of
money on a bet, and
>> unfortunately, time for real testing isn't
available
>
> Probably doing many drives in a raid 1+0 would help -
IO is going to 
> be a real issue.  This is not the place to go cheap on
a raid card!
This will be my main point in discussions at work this
morning...
>
>
>> MySQL 5 clustering looks out from my point of view,
based just on the
>> requirement that the DB(s) be held in memory. Much
memory will be
>> required as the caches grow to their max. But then
I've never done MySQL
>> clustering, so maybe I'm not understanding
correctly.
>>
>
> That is our current understanding too, and as such it
seems very 
> insufficient to hold a cache of emails.
>
> I'm curious about the latest postgresql, as there are
some clustering 
> options, and the latest version has some other option
that look good 
> for bayes performance.   But at this point, I have not
tested it at 
> all.   I also suspect that clustering will always have
some problems 
> since it still has to sync the data.
>
>
>> Splitting the bayes off to a separate machine seems
pretty necessary
>
> Yes, that's an easy one - the bayes handling is
orthogonal to Maia.
>
>> Multi threaded process-quarantine, or another way
to process confirmed
>> cache entries?
>
> If the bayes processing is the problem, then splitting
it onto another 
> database would help.  Another option would be to use
bdb for the 
> bayes, but turn off all updating on the scanners ...
ie, the scanners 
> all have read only access to the bayes... then do the 
> process-quarantine on a dedicated host, and rsync the
bdb files to the 
> other servers on a regular basis.   I haven't set this
up myself, but 
> I have heard of some who have done this.
I'm thinking bayes processing would be the next bottleneck -
I know 
process-quarantine wasn't running when they were having the
problem.

We will revisit exactly what tuning was done on Sunday to be
sure 
nothing was missed
>
>
>> As I write this, I suppose its obvious that the
answer is primarily to
>> sink money into drives, controller and memory,
regardless of any 
>> thing else.
>
> The database is certainly the money eater.
>
> David Morton
> Maia Mailguard http://www.maiamailguard
.com
> mortondadgrmm.net
>
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.7 (Darwin)
>
>
iD4DBQFHzNILUy30ODPkzl0RAsEBAKCNkPF8ATVOZctsTS94mWEOF9KWcwCY
xNaa
> FC12sGTtvMukgOdLfzY2FQ==
> =UKC7
> -----END PGP SIGNATURE-----
>

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On Mar 4, 2008, at 7:40 AM, Tim Palmer wrote:
>
>
> I suppose a more fundamental question is - can the Maia
architecture
> handle this load without spending 10s of thousands on
the db server.
> Several thousand we can deal with, but 10s gets to be a
problem.

I haven't managed a system quite that large, though there
are a few  
around that may be close.  Maia certainly hits the database
hard (and  
if there are ways to streamline that I'm open to
suggestions).  This  
might be a good point to spend some money with the mysql
folks to get  
some tuning advice.  According to some of their sales
material, this  
shouldn't be a problem. ;)


David Morton
Maia Mailguard http://www.maiamailguard
.com
mortondadgrmm.net



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (Darwin)

iD8DBQFHzWO+Uy30ODPkzl0RAsMWAJ96JfPoS3be+4JCXBWSp5NF40cQEACe
KxJz
rmhXz27ALI3mRD22eHxKq8A=
=FMys
-----END PGP SIGNATURE-----
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

David Morton wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> On Mar 4, 2008, at 7:40 AM, Tim Palmer wrote:
>>
>>
>> I suppose a more fundamental question is - can the
Maia architecture
>> handle this load without spending 10s of thousands
on the db server.
>> Several thousand we can deal with, but 10s gets to
be a problem.
>
> I haven't managed a system quite that large, though
there are a few 
> around that may be close.  Maia certainly hits the
database hard (and 
> if there are ways to streamline that I'm open to
suggestions).  This 
> might be a good point to spend some money with the
mysql folks to get 
> some tuning advice.  According to some of their sales
material, this 
> shouldn't be a problem. ;)
>
>
> David Morton
> Maia Mailguard http://www.maiamailguard
.com
> mortondadgrmm.net
>
Ahh, they'll just say get bigger hardware ;)
But seriously, we'd like to optimize Maia as best we can
before dancing 
with mysql
One thought we had was to have readonly replicas of the maia
tables 
sitting on the postfix machines, and split the read and
write queries - 
read queries local, write queries to the primary db. Sound
doable? Or 
sensible? Certainly, many people pulling up cache lists of
some 
thousands of ham/spam can be a serious load.
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

There are guys with a lot more experience than me here.  We
run Maia on two 
boxes, one holds the MySQL database.  Both boxes are Quad
Core 2.4 GHz with 
SATAII drives in Raid 1 and 4 gig ram.  The box with MySQL
handles the 
majority of the email/Maia traffic plus 130+ MySQL
connections regularly.

There is no doubt that better hardware will help, but it
sounds to me like 
if your db server is failing at 100 connections either way
you should 
investigate more MySQL tuning.

Perhaps there is someone on the list with a large install
that can add 
something here.

If your system is new then it may benefit from some
performance improvements 
as a whole.  We made some changes early on that had a
significant impact on 
improving system performance.  Among them was blocking at
the envelope in 
postfix for certain things and a relay recipients map. 
These items reduced 
the traffic on our boxes easily 50% before it ever got to
Maia and MySQL.

Todd





----- Original Message ----- 
From: "Tim Palmer" <timtany.com>
To: "Todd" <toddsmart-mail.net>
Cc: <maia-usersrenaissoft.com>
Sent: Tuesday, March 04, 2008 7:40 AM
Subject: Re: [Maia-users] High volume (200k+ cached daily)
questions


>I wasn't there when the primary event happened (db
connects failing 
>regularly), but from what I heard, CPU and memory
utilization were fine - 
>the problem was disk I/O. Don't have the numbers,
however.
>
> 100 db connects per second (or a little more) is where
mysql topped out. 
> Each connection is fairly large query, generally
requiring database reads. 
> My boss is pretty proficient in mysql, and general
optimization. I don't 
> know exactly what he did for tuning, but he certainly
would have worked 
> with connection pooling, InnoDB settings and anything
else he could think 
> of.
>
> Greylisting was not active when this was happening, but
is active now (we 
> had to bump back to amavisd-new until we get this
worked out).
>
> I suppose a more fundamental question is - can the Maia
architecture 
> handle this load without spending 10s of thousands on
the db server. 
> Several thousand we can deal with, but 10s gets to be a
problem.
>
> Everyone expects spam filtering these days, but to
charge for it? well, 
> thats another story 
>
> thanks for your response.
>
> tim
>
> Todd wrote:
>> You need to provide more info.
>>
>> How is the resource utilization on the mysql
server, disk io and cpu?
>>
>> If I understand, you want to process 1 million
emails daily but your 
>> MySQL is dieing at 100 connections?  What kind of
tuning have you done to 
>> the MySQL config?  The default config would have to
me modified to handle 
>> the load you are talking about.
>>
>> Todd
>>
>>
>>
>> ----- Original Message ----- From: "Tim
Palmer" <timtany.com>
>> To: <maia-usersrenaissoft.com>
>> Sent: Monday, March 03, 2008 7:38 PM
>> Subject: [Maia-users] High volume (200k+ cached
daily) questions
>>
>>
>>> Please excuse the length of this post. I'm not
looking to solve a
>>> "problem", but hoping for input about
a high volume configuration.
>>>
>>> I'm reaching out for thoughts or experience on
what the db backend needs
>>> to be to handle, say, 1 million processed, and
200k+ cached entries
>>> daily. I expect to expire out of quarantine at
3 days. Peak loads could
>>> easily top several thousand messages/second.
>>>
>>> We have 4 2xquad core Xeon, 8G machines as the
Postfix/amavisd-maia/SA
>>> front ends. Postfix rules are set up pretty
strict.
>>> All machines are running FreeBSD 7 amd64
>>>
>>> The plan was to have them work with a single
Mysql 5 machine for all
>>> database functions and Maia interface. We
brought this on-line over the
>>> weekend, and it worked ok for maybe 36 hours,
then started getting db
>>> connect failures. My boss' analysis was showing
the db server dieing at
>>> about 100 connections/sec, when it needed to be
more like 500. The
>>> constriction was definitely I/O.  All manner of
InnoDB and MySql tuning
>>> have been tried (all the right ones? correctly?
We think so, but could
>>> of course be wrong).
>>>
>>> Its possible I triggered this by confirming two
or three thousand
>>> messages within 1/2 hour, but the system will
need to handle that.
>>>
>>> Now, the db server definitely was a little
lightweight (SATAII drives,
>>> 4G RAM), but our concern is that even if we go
to 15K SCSI drives and
>>> say 16G RAM, will it actually work? Thats a lot
of money on a bet, and
>>> unfortunately, time for real testing isn't
available
>>>
>>> MySQL 5 clustering looks out from my point of
view, based just on the
>>> requirement that the DB(s) be held in memory.
Much memory will be
>>> required as the caches grow to their max. But
then I've never done MySQL
>>> clustering, so maybe I'm not understanding
correctly.
>>>
>>> Splitting the bayes off to a separate machine
seems pretty necessary
>>>
>>> Multi threaded process-quarantine, or another
way to process confirmed
>>> cache entries?
>>>
>>> As I write this, I suppose its obvious that the
answer is primarily to
>>> sink money into drives, controller and memory,
regardless of any thing 
>>> else.
>>>
>>> Thanks for your time, and I will happily
provide details I've missed.
>>>
>>> Tim
>>>
_______________________________________________
>>> Maia-users mailing list
>>> Maia-usersrenaissoft.com
>>> http://www.renaissoft.com/mailman/listinfo/maia-users
>>>
>>
>>
>
> 

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

You may be right about mysql tuning. We're certainly doing
our best to 
have postfix drop as much as possible at the door. But
greylisting does 
more than anything, really.

Todd wrote:
> There are guys with a lot more experience than me here.
 We run Maia 
> on two boxes, one holds the MySQL database.  Both boxes
are Quad Core 
> 2.4 GHz with SATAII drives in Raid 1 and 4 gig ram. 
The box with 
> MySQL handles the majority of the email/Maia traffic
plus 130+ MySQL 
> connections regularly.
>
> There is no doubt that better hardware will help, but
it sounds to me 
> like if your db server is failing at 100 connections
either way you 
> should investigate more MySQL tuning.
>
> Perhaps there is someone on the list with a large
install that can add 
> something here.
>
> If your system is new then it may benefit from some
performance 
> improvements as a whole.  We made some changes early on
that had a 
> significant impact on improving system performance. 
Among them was 
> blocking at the envelope in postfix for certain things
and a relay 
> recipients map.  These items reduced the traffic on our
boxes easily 
> 50% before it ever got to Maia and MySQL.
>
> Todd
>
>
>
>
>
> ----- Original Message ----- From: "Tim
Palmer" <timtany.com>
> To: "Todd" <toddsmart-mail.net>
> Cc: <maia-usersrenaissoft.com>
> Sent: Tuesday, March 04, 2008 7:40 AM
> Subject: Re: [Maia-users] High volume (200k+ cached
daily) questions
>
>
>> I wasn't there when the primary event happened (db
connects failing 
>> regularly), but from what I heard, CPU and memory
utilization were 
>> fine - the problem was disk I/O. Don't have the
numbers, however.
>>
>> 100 db connects per second (or a little more) is
where mysql topped 
>> out. Each connection is fairly large query,
generally requiring 
>> database reads. My boss is pretty proficient in
mysql, and general 
>> optimization. I don't know exactly what he did for
tuning, but he 
>> certainly would have worked with connection
pooling, InnoDB settings 
>> and anything else he could think of.
>>
>> Greylisting was not active when this was happening,
but is active now 
>> (we had to bump back to amavisd-new until we get
this worked out).
>>
>> I suppose a more fundamental question is - can the
Maia architecture 
>> handle this load without spending 10s of thousands
on the db server. 
>> Several thousand we can deal with, but 10s gets to
be a problem.
>>
>> Everyone expects spam filtering these days, but to
charge for it? 
>> well, thats another story 
>>
>> thanks for your response.
>>
>> tim
>>
>> Todd wrote:
>>> You need to provide more info.
>>>
>>> How is the resource utilization on the mysql
server, disk io and cpu?
>>>
>>> If I understand, you want to process 1 million
emails daily but your 
>>> MySQL is dieing at 100 connections?  What kind
of tuning have you 
>>> done to the MySQL config?  The default config
would have to me 
>>> modified to handle the load you are talking
about.
>>>
>>> Todd
>>>
>>>
>>>
>>> ----- Original Message ----- From: "Tim
Palmer" <timtany.com>
>>> To: <maia-usersrenaissoft.com>
>>> Sent: Monday, March 03, 2008 7:38 PM
>>> Subject: [Maia-users] High volume (200k+ cached
daily) questions
>>>
>>>
>>>> Please excuse the length of this post. I'm
not looking to solve a
>>>> "problem", but hoping for input
about a high volume configuration.
>>>>
>>>> I'm reaching out for thoughts or experience
on what the db backend 
>>>> needs
>>>> to be to handle, say, 1 million processed,
and 200k+ cached entries
>>>> daily. I expect to expire out of quarantine
at 3 days. Peak loads 
>>>> could
>>>> easily top several thousand
messages/second.
>>>>
>>>> We have 4 2xquad core Xeon, 8G machines as
the Postfix/amavisd-maia/SA
>>>> front ends. Postfix rules are set up pretty
strict.
>>>> All machines are running FreeBSD 7 amd64
>>>>
>>>> The plan was to have them work with a
single Mysql 5 machine for all
>>>> database functions and Maia interface. We
brought this on-line over 
>>>> the
>>>> weekend, and it worked ok for maybe 36
hours, then started getting db
>>>> connect failures. My boss' analysis was
showing the db server 
>>>> dieing at
>>>> about 100 connections/sec, when it needed
to be more like 500. The
>>>> constriction was definitely I/O.  All
manner of InnoDB and MySql 
>>>> tuning
>>>> have been tried (all the right ones?
correctly? We think so, but could
>>>> of course be wrong).
>>>>
>>>> Its possible I triggered this by confirming
two or three thousand
>>>> messages within 1/2 hour, but the system
will need to handle that.
>>>>
>>>> Now, the db server definitely was a little
lightweight (SATAII drives,
>>>> 4G RAM), but our concern is that even if we
go to 15K SCSI drives and
>>>> say 16G RAM, will it actually work? Thats a
lot of money on a bet, and
>>>> unfortunately, time for real testing isn't
available
>>>>
>>>> MySQL 5 clustering looks out from my point
of view, based just on the
>>>> requirement that the DB(s) be held in
memory. Much memory will be
>>>> required as the caches grow to their max.
But then I've never done 
>>>> MySQL
>>>> clustering, so maybe I'm not understanding
correctly.
>>>>
>>>> Splitting the bayes off to a separate
machine seems pretty necessary
>>>>
>>>> Multi threaded process-quarantine, or
another way to process confirmed
>>>> cache entries?
>>>>
>>>> As I write this, I suppose its obvious that
the answer is primarily to
>>>> sink money into drives, controller and
memory, regardless of any 
>>>> thing else.
>>>>
>>>> Thanks for your time, and I will happily
provide details I've missed.
>>>>
>>>> Tim
>>>>
_______________________________________________
>>>> Maia-users mailing list
>>>> Maia-usersrenaissoft.com
>>>> http://www.renaissoft.com/mailman/listinfo/maia-users
>>>>
>>>
>>>
>>
>>
>

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

We cache between 100k - 200k depending on the day.  We're
using 
greylisting (policyd) and some conservative RBLS to limit
our intake to 
that range.
We run the policyd and bayes parts of the database on a
separate, 
dedicated mysql server.  Greylisting can be pretty brutal,
and frankly, 
I don't think that they spamassassin bayes is very DB
friendly.

The main maia database we house on a pair of machines with
dual xeon 
3.00Ghz processors with 4GB of ram, and a mirrored pair of
sca drives 
for the OS / innodb logs, and a 4 drive raid 10 for the main
data.   
We're using master<->master replication behind a
loadbalancer, however, 
a single server is capable of bearing the whole load.  It's
worth 
mentioning that this is running a 32bit os, I'd expect more
from a 64bit 
version.

***
***  DISCLAIMER:  I am not an expert, I did tons of
research, and this 
works for us.  There are probably ways to improve these
settings.
***

 If you don't plan on doing master->master replication,
don't use 
innodb_flush_log_at_trx_commit = 1.  The
innodb_buffer_pool_size = 1G is 
small, however, because the allowed packet size is high, it
chews 
through the available memory.  You'd do better to have at
least double 
the ram, and run a 64bit OS.  If you're running linux, use
the deadline 
scheduler.

Pertinent mysql parameters from my.cnf (For the maia db
server):
max_connections = 1000
max_connect_errors = 10
max_allowed_packet = 50M
binlog_cache_size = 1M
max_heap_table_size = 32M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache = 8
thread_concurrency = 16
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = INNODB
thread_stack = 192K
tmp_table_size = 256M

log-bin=filter-sql-02-bin
sync-binlog=1
log-err=/var/log/mysqld.log
max_binlog_size = 100M
max-relay-log-size=100M
relay-log-purge=1
relay-log-space-limit=1000M
slave-net-timeout=10
#log_slave_updates
#log
#log_warnings
#log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /tmp
log-bin=filter-sql-02-bin
sync-binlog=1
log-err=/var/log/mysqld.log
max_binlog_size = 100M
max-relay-log-size=100M
relay-log-purge=1
relay-log-space-limit=1000M
slave-net-timeout=10
#log_slave_updates
#log
#log_warnings
#log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /tmp

#replication settings
server-id = 2
master-host = filter-sql-01.reachone.com
master-user = <sanitized>
master-password = <sanitized>
relay-log=filter-sql-02-relay-bin
report-host=filter-sql-02.reachone.com
auto_increment_increment = 5
auto_increment_offset = 2
slave-skip-errors=1062,1053

key_buffer_size = 64M
read_buffer_size = 1M
bulk_insert_buffer_size = 64M
skip-bdb


#innodb settings
innodb_file_per_table
innodb_data_file_path = filter_innodb1:10M:autoextend
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 1G
innodb_data_home_dir = /var/lib/mysql
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /var/lib/mysql-logs
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
open-files-limit = 8192
pid-file=/var/run/mysqld/mysql.pid


Here's the config that we use for the bayes / greylisting
server:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
table_cache = 2048

skip-locking
max_connections = 1000
max_connect_errors = 10
max_allowed_packet = 1M
binlog_cache_size = 1M
max_heap_table_size = 32M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache = 8
thread_concurrency = 16
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
#transaction_isolation = READ-UNCOMMITTED
default_table_type = INNODB
thread_stack = 192K
tmp_table_size = 64M

key_buffer_size = 64M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
skip-bdb

log-slow-queries
log-long-format
long-query-time=2

#innodb settings
innodb_file_per_table
innodb_data_file_path = filter_innodb1:10M:autoextend
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_home_dir = /var/lib/mysql
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /var/lib/mysql-logs
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120
open-files-limit = 8192
pid-file=/var/run/mysqld/mysql.pid


Notice that the packet size is smaller, and we use more
memory for 
innodb_buffer_pool_size.  The thead concurrency is higher,
and we don't 
force a flush at the end of a transaction.   Look for slow
queries, it's 
possible you've got an index shortage.   Much of the info
for tweaking 
mysql was garnered from googling "mysql innodb
performance".

Hope this helps someone,

Jacob Leaver
Sr. Systems Admin
ReachONE Internet.


Tim Palmer wrote:
> Please excuse the length of this post. I'm not looking
to solve a 
> "problem", but hoping for input about a high
volume configuration.
>
> I'm reaching out for thoughts or experience on what the
db backend needs 
> to be to handle, say, 1 million processed, and 200k+
cached entries 
> daily. I expect to expire out of quarantine at 3 days.
Peak loads could 
> easily top several thousand messages/second.
>
> We have 4 2xquad core Xeon, 8G machines as the
Postfix/amavisd-maia/SA 
> front ends. Postfix rules are set up pretty strict.
> All machines are running FreeBSD 7 amd64
>
> The plan was to have them work with a single Mysql 5
machine for all 
> database functions and Maia interface. We brought this
on-line over the 
> weekend, and it worked ok for maybe 36 hours, then
started getting db 
> connect failures. My boss' analysis was showing the db
server dieing at 
> about 100 connections/sec, when it needed to be more
like 500. The 
> constriction was definitely I/O.  All manner of InnoDB
and MySql tuning 
> have been tried (all the right ones? correctly? We
think so, but could 
> of course be wrong).
>
> Its possible I triggered this by confirming two or
three thousand 
> messages within 1/2 hour, but the system will need to
handle that.
>
> Now, the db server definitely was a little lightweight
(SATAII drives, 
> 4G RAM), but our concern is that even if we go to 15K
SCSI drives and 
> say 16G RAM, will it actually work? Thats a lot of
money on a bet, and 
> unfortunately, time for real testing isn't available
>
> MySQL 5 clustering looks out from my point of view,
based just on the 
> requirement that the DB(s) be held in memory. Much
memory will be 
> required as the caches grow to their max. But then I've
never done MySQL 
> clustering, so maybe I'm not understanding correctly.
>
> Splitting the bayes off to a separate machine seems
pretty necessary
>
> Multi threaded process-quarantine, or another way to
process confirmed 
> cache entries?
>
> As I write this, I suppose its obvious that the answer
is primarily to 
> sink money into drives, controller and memory,
regardless of any thing else.
>
> Thanks for your time, and I will happily provide
details I've missed.
>
> Tim
> _______________________________________________
> Maia-users mailing list
> Maia-usersrenaissoft.com
> http://www.renaissoft.com/mailman/listinfo/maia-users
>   

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users