DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=bBX2d1CIUgrUZXlPGdZu3nAAxk+JyrWUcJViStSc2QIdY5V3v6iX4aZMwgQcoaCMVS
	jlJxiMt5rxGlwN+RqTzn5CfHe+/izilbds2xMVTEEp1Df8gsUSN2ZmFDSgHiZh2PwT+3
	7YhX+uv1YDaNw9UufUc/BTt+yxUHwQCZb2Uso=
MIME-Version: 1.0
In-Reply-To: 
 <1370423997.141099.1262877797658.JavaMail.root@zimbra.cis.uab.edu>
References: <950265612.141041.1262877637416.JavaMail.root@zimbra.cis.uab.edu>
	<1370423997.141099.1262877797658.JavaMail.root@zimbra.cis.uab.edu>
Date: Thu, 7 Jan 2010 18:59:39 -0700
Message-ID: <dcc563d11001071759l49a10d8dw4f0a5a4bf714cdff@mail.gmail.com>
Subject: Re: "large" spam tables and performance: postgres memory parameters
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Gary Warner <gar@cis.uab.edu>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Jan 7, 2010 at 8:23 AM, Gary Warner <gar@cis.uab.edu> wrote:
> Hello,
>
> I've been lurking on this list a couple weeks now, and have asked some "s=
ide questions" to some of the list members, who have been gracious, and hel=
pful, and encouraged me to just dive in and participate on the list.
>
> I'll not tell you the whole story right off the bat, but let me give you =
a basic outline.
>
> I dual report into two academic departments at the University of Alabama =
at Birmingham - Computer & Information Sciences and Justice Sciences. =A0Al=
though my background is on the CS side, I specialize in cybercrime investig=
ations and our research focuses on things that help to train or equip cyber=
crime investigators.
>
> One of our research projects is called the "UAB Spam Data Mine". =A0Basic=
ally, we collect spam, use it to detect emerging malware threats, phishing =
sites, or spam campaigns, and share our findings with law enforcement and o=
ur corporate partners.
>
> We started off small, with only around 10,000 to 20,000 emails per day ru=
nning on a smallish server. =A0Once we had our basic workflow down, we move=
d to nicer hardware, and opened the floodgates a bit. =A0We're currently re=
ceiving about 1.2 million emails per day, and hope to very quickly grow tha=
t to more than 5 million emails per day.
>
> I've got very nice hardware - many TB of very fast disks, and several ser=
vers with 12GB of RAM and 8 pentium cores each.

Are you running 8.3.x?  I'd go with that as a minimum for now.

You'll want to make sure those fast disks are under a fast RAID setup
like RAID-10, perhaps with a high quality RAID controller with battery
backed cache as well.  I/O is going to be your real issue here, not
CPU, most likely.  Also look at increasing RAM to 48 or 96Gig if if
you can afford it.  I assume your pentium cores are Nehalem since
you've got 12G of ram (multiple of 3).  Those are a good choice here,
they're fast and have memory access.

> For the types of investigative support we do, some of our queries are of =
the 'can you tell me what this botnet was spamming for the past six months'=
, but most of them are more "real time", of the "what is the top spam campa=
ign today?" or "what domains are being spammed by this botnet RIGHT NOW".

Then you'll probably want to look at partitioning your data.

> We currently use 15 minute batch queues, where we parse between 10,000 to=
 20,000 emails every 15 minutes. =A0Each message is assigned a unique messa=
ge_id, which is a combination of what date and time "batch" it is in, follo=
wed by a sequential number, so the most recent batch processed this morning=
 starts with "10Jan07.0" and goes through "10Jan07.13800".

> I don't know what this list considers "large databases", but I'm going to=
 go ahead and call 170 million records a "large" table.


> =A0- if you have 8 pentium cores, 12GB of RAM and "infinite" diskspace, w=
hat sorts of memory settings would you have in your start up tables?

crank up shared_buffers and effective cache size.  Probably could use
a bump for work_mem, something in the 16 to 32 Meg range.  Especially
since you're only looking at a dozen or so, not hundreds, of
concurrent users.  work_mem is per sort, so it can get out of hand
fast if you crank it up too high, and for most users higher settings
won't help anyway.

> =A0My biggest question mark there really has to do with how many users I =
have and how that might alter the results. =A0My research team has about 12=
 folks who might be using the UAB Spam Data Mine at any given time, plus we=
 have the "parser" running pretty much constantly, and routines that are fe=
tching IP addresses for all spammed URLs and nameservers for all spammed do=
mains and constantly updating the databases with that information. In the v=
ery near future, we'll be accepting queries directly from law enforcement t=
hrough a web interface and may have as many as another 12 simultaneous user=
s, so maybe 25 max users. =A0We plan to limit "web users" to a "recent" sub=
set of the data, probably allowing "today" "previous 24 hour" and "previous=
 7 days" as query options within the web interface. =A0The onsite researche=
rs will bang the heck out of much larger datasets.

You might want to look at pre-rolling common requests if that isn't
what you're already doing with the routines you're mentioning up
there.

> (I'll let this thread run a bit, and then come back to ask questions abou=
t "vacuum analyze" and "partitioned tables" as a second and third round of =
questions.)

You shouldn't have a lot of vacuum problems since you won't be
deleting anything.  You might wanna crank up autovacuum aggressiveness
as regards analyze though.  Partitioning is in your future.