Re: [PERFORM] Performance problems on 4/8way Opteron (dualcore) HP
Anybody knows if RedHat is already supporting this patch on an enterprise version? Regards, Dirk J. Andrew Rogers wrote: On 7/29/05 10:46 AM, "Josh Berkus" wrote: does anybody have expierence with this machine (4x 875 dual core Opteron CPUs)? Nope. I suspect that you may be the first person to report in on dual-cores. There may be special compile issues with dual-cores that we've not yet encountered. There was recently a discussion of similar types of problems on a couple of the supercomputing lists, regarding surprisingly substandard performance from large dual-core opteron installations. The problem as I remember it boiled down to the Linux kernel handling memory/process management very badly on large dual core systems -- pathological NUMA behavior. However, this problem has apparently been fixed in Linux v2.6.12+, and using the more recent kernel on large dual core systems generated *massive* performance improvements on these systems for the individuals with this issue. Using the patched kernel, one gets the performance most people were expecting. The v2.6.12+ kernels are a bit new, but they contain a very important performance patch for systems like the one above. It would definitely be worth testing if possible. J. Andrew Rogers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance problems on 4/8way Opteron (dualcore) HP
Hi Jeff, which box are you running precisely and which OS/kernel? We need to run 32bit because we need failover to 32 bit XEON system (DL580). If this does not work out we probably need to switch to 64 bit (dump/restore) and run a nother 64bit failover box too. Regards, Dirk Jeffrey W. Baker wrote: On Fri, 2005-07-29 at 10:46 -0700, Josh Berkus wrote: Dirk, does anybody have expierence with this machine (4x 875 dual core Opteron CPUs)? I'm using dual 275s without problems. Nope. I suspect that you may be the first person to report in on dual-cores. There may be special compile issues with dual-cores that we've not yet encountered. Doubtful. However you could see improvements using recent Linux kernel code. There have been some patches for optimizing scheduling and memory allocations. However, if you are running this machine in 32-bit mode, why did you bother paying $14,000 for your CPUs? You will get FAR better performance in 64-bit mode. 64-bit mode will give you 30-50% better performance on PostgreSQL loads, in my experience. Also, if I remember correctly, the 32-bit x86 kernel doesn't understand Opteron NUMA topology, so you may be seeing poor memory allocation decisions. -jwb We run RHEL 3.0, 32bit and under high load it is a drag. We mostly run memory demanding queries. Context switches are pretty much around 20.000 on the average, no cs spikes when we run many processes in parallel. Actually we only see two processes in running state! When there are only a few processes running context switches go much higher. At the moment we are much slower that with a 4way XEON box (DL580). Um, that was a bit incoherent. Are you seeing a CS storm or aren't you? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance problems on 4/8way Opteron (dualcore)
On 7/30/05 12:57 AM, "William Yu" <[EMAIL PROTECTED]> wrote: > I haven't investigated the 2.6.12+ kernel updates yet -- I probably will > do our development servers first to give it a test. The kernel updates make the NUMA code dual-core aware, which apparently makes a big difference in some cases but not in others. It makes some sense, since multi-processor multi-core machines will have two different types of non-locality instead of just one that need to be managed. Prior to the v2.6.12 patches, a dual-core dual-proc machine was viewed as a quad-proc machine. The closest thing to a supported v2.6.12 kernel that I know of is FC4, which is not really supported in the enterprise sense of course. J. Andrew Rogers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0
John Arbash Meinel wrote: >Matthew Schumacher wrote: > > > >>All it's doing is trying the update before the insert to get around the >>problem of not knowing which is needed. With only 2-3 of the queries >>implemented I'm already back to running about the same speed as the >>original SA proc that is going to ship with SA 3.1.0. >> >>All of the queries are using indexes so at this point I'm pretty >>convinced that the biggest problem is the sheer number of queries >>required to run this proc 200 times for each email (once for each token). >> >>I don't see anything that could be done to make this much faster on the >>postgres end, it's looking like the solution is going to involve cutting >>down the number of queries some how. >> >>One thing that is still very puzzling to me is why this runs so much >>slower when I put the data.sql in a transaction. Obviously transactions >>are acting different when you call a proc a zillion times vs an insert >>query. >> >> >> >> >Well, I played with adding a COMMIT;BEGIN; statement to your exact test >every 1000 lines. And this is what I got: > > Just for reference, I also tested this on my old server, which is a dual Celeron 450 with 256M ram. FC4 and Postgres 8.0.3 Unmodified: real54m15.557s user0m24.328s sys 0m14.200s With Transactions every 1000 selects, and vacuum every 5000: real8m36.528s user0m16.585s sys 0m12.569s With Transactions every 1000 selects, and vacuum every 1: real7m50.748s user0m16.183s sys 0m12.489s On this machine vacuum is more expensive, since it doesn't have as much ram. Anyway, on this machine, I see approx 7x improvement. Which I think is probably going to satisfy your spamassassin needs. John =:-> PS> Looking forward to having a spamassassin that can utilize my favorite db. Right now, I'm not using a db backend because it wasn't worth setting up mysql. >Unmodified: >real17m53.587s >user0m6.204s >sys 0m3.556s > >With BEGIN/COMMIT: >real1m53.466s >user0m5.203s >sys 0m3.211s > >So I see the potential for improvement almost 10 fold by switching to >transactions. I played with the perl script (and re-implemented it in >python), and for the same data as the perl script, using COPY instead of >INSERT INTO means 5s instead of 33s. > >I also played around with adding VACUUM ANALYZE every 10 COMMITS, which >brings the speed to: > >real1m41.258s >user0m5.394s >sys 0m3.212s > >And doing VACUUM ANALYZE every 5 COMMITS makes it: >real1m46.403s >user0m5.597s >sys 0m3.244s > >I'm assuming the slowdown is because of the extra time spent vacuuming. >Overall performance might still be improving, since you wouldn't >actually be inserting all 100k rows at once. > > ... >This is all run on Ubuntu, with postgres 7.4.7, and a completely >unchanged postgresql.conf. (But the machine is a dual P4 2.4GHz, with >3GB of RAM). > >John >=:-> > > signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0
Ok, here is the current plan. Change the spamassassin API to pass a hash of tokens into the storage module, pass the tokens to the proc as an array, start a transaction, load the tokens into a temp table using copy, select the tokens distinct into the token table for new tokens, update the token table for known tokens, then commit. This solves the following problems: 1. Each email is a transaction instead of each token. 2. The update statement is only called when we really need an update which avoids all of those searches. 3. The looping work is done inside the proc instead of perl calling a method a zillion times per email. I'm not sure how vacuuming will be done yet, if we vacuum once per email that may be too often, so I may do that every 5 mins in cron. schu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0
On Sun, Jul 31, 2005 at 08:51:06AM -0800, Matthew Schumacher wrote: > Ok, here is the current plan. > > Change the spamassassin API to pass a hash of tokens into the storage > module, pass the tokens to the proc as an array, start a transaction, > load the tokens into a temp table using copy, select the tokens distinct > into the token table for new tokens, update the token table for known > tokens, then commit. You might consider: UPDATE tokens FROM temp_table (this updates existing records) INSERT INTO tokens SELECT ... FROM temp_table WHERE NOT IN (SELECT ... FROM tokens) This way you don't do an update to newly inserted tokens, which helps keep vacuuming needs in check. > This solves the following problems: > > 1. Each email is a transaction instead of each token. > 2. The update statement is only called when we really need an update > which avoids all of those searches. > 3. The looping work is done inside the proc instead of perl calling a > method a zillion times per email. > > I'm not sure how vacuuming will be done yet, if we vacuum once per email > that may be too often, so I may do that every 5 mins in cron. I would suggest leaving an option to have SA vacuum every n emails, since some people may not want to mess with cron, etc. I suspect that pg_autovacuum would be able to keep up with things pretty well, though. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0
Jim C. Nasby wrote: On Sun, Jul 31, 2005 at 08:51:06AM -0800, Matthew Schumacher wrote: Ok, here is the current plan. Change the spamassassin API to pass a hash of tokens into the storage module, pass the tokens to the proc as an array, start a transaction, load the tokens into a temp table using copy, select the tokens distinct into the token table for new tokens, update the token table for known tokens, then commit. You might consider: UPDATE tokens FROM temp_table (this updates existing records) INSERT INTO tokens SELECT ... FROM temp_table WHERE NOT IN (SELECT ... FROM tokens) This way you don't do an update to newly inserted tokens, which helps keep vacuuming needs in check. The subselect might be quite a big set, so avoiding a full table scan and materialization by DELETE temp_table WHERE key IN (select key FROM tokens JOIN temp_table); INSERT INTO TOKENS SELECT * FROM temp_table; or INSERT INTO TOKENS SELECT temp_table.* FROM temp_table LEFT JOIN tokens USING (key) WHERE tokens.key IS NULL might be an additional win, assuming that only a small fraction of tokens is inserted and updated. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance problems on 4/8way Opteron (dualcore)
A 4xDC would be far more sensitive to poor NUMA code than 2xDC so I'm not surprised I don't see performance issues on our 2xDC w/ < 2.6.12. J. Andrew Rogers wrote: On 7/30/05 12:57 AM, "William Yu" <[EMAIL PROTECTED]> wrote: I haven't investigated the 2.6.12+ kernel updates yet -- I probably will do our development servers first to give it a test. The kernel updates make the NUMA code dual-core aware, which apparently makes a big difference in some cases but not in others. It makes some sense, since multi-processor multi-core machines will have two different types of non-locality instead of just one that need to be managed. Prior to the v2.6.12 patches, a dual-core dual-proc machine was viewed as a quad-proc machine. The closest thing to a supported v2.6.12 kernel that I know of is FC4, which is not really supported in the enterprise sense of course. J. Andrew Rogers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0
Hi All,
As a SpamAssassin developer, who by my own admission has real problem
getting PostgreSQL to work well, I must thank everyone for their
feedback on this issue. Believe me when I say what is in the tree now
is a far cry from what used to be there, orders of magnitude faster
for sure. I think there are several good ideas that have come out of
this thread and I've set about attempting to implement them.
Here is a version of the stored procedure, based in large part by the
one written by Tom Lane, that accepts and array of tokens and loops
over them to either update or insert them into the database (I'm not
including the greatest_int/least_int procedures but you've all seen
them before):
CREATE OR REPLACE FUNCTION put_tokens(inuserid INTEGER,
intokenary BYTEA[],
inspam_count INTEGER,
inham_count INTEGER,
inatime INTEGER)
RETURNS VOID AS '
DECLARE
_token BYTEA;
new_tokens INTEGER := 0;
BEGIN
for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1)
LOOP
_token := intokenary[i];
UPDATE bayes_token
SET spam_count = greatest_int(spam_count + inspam_count, 0),
ham_count = greatest_int(ham_count + inham_count, 0),
atime = greatest_int(atime, inatime)
WHERE id = inuserid
AND token = _token;
IF NOT FOUND THEN
-- we do not insert negative counts, just return true
IF NOT (inspam_count < 0 OR inham_count < 0) THEN
INSERT INTO bayes_token (id, token, spam_count,
ham_count, atime)
VALUES (inuserid, _token, inspam_count, inham_count, inatime);
IF FOUND THEN
new_tokens := new_tokens + 1;
END IF;
END IF;
END IF;
END LOOP;
UPDATE bayes_vars
SET token_count = token_count + new_tokens,
newest_token_age = greatest_int(newest_token_age, inatime),
oldest_token_age = least_int(oldest_token_age, inatime)
WHERE id = inuserid;
RETURN;
END;
' LANGUAGE 'plpgsql';
This version is about 32x faster than the old version, with the
default fsync value and autovacuum running in the background.
The next hurdle, and I've just posted to the DBD::Pg list, is
escaping/quoting the token strings. They are true binary strings,
substrings of SHA1 hashes, I don't think the original data set
provided puts them in the right context. They have proved to be
tricky. I'm unable to call the stored procedure from perl because I
keep getting a malformed array litteral error.
Here is some example code that shows the issue:
#!/usr/bin/perl -w
# from a new db, do this first
# INSERT INTO bayes_vars VALUES (1,'nobody',0,0,0,0,0,0,2147483647,0);
use strict;
use DBI;
use DBD::Pg qw(:pg_types);
use Digest::SHA1 qw(sha1);
my $dbh = DBI->connect("DBI:Pg:dbname=spamassassin","postgres") || die;
my @dataary;
# Input is just a list of words (ie /usr/share/dict/words) stop after 150
while(<>) {
chomp;
push(@dataary, substr(sha1($_), -5));
# to see it work with normal string comment out above and uncomment below
# push(@dataary, $_);
last if scalar(@dataary) >= 150;
}
my $datastring = join(",", map { '"' . bytea_esc($_) . '"' }
@dataary);
my $sql = "select put_tokens(1, '{$datastring}', 1, 1, 1)";
my $sth = $dbh->prepare($sql);
my $rc = $sth->execute();
unless ($rc) {
print "Error: " . $dbh->errstr() . "\n";
}
$sth->finish();
sub bytea_esc {
my ($str) = @_;
my $buf = "";
foreach my $char (split(//,$str)) {
if (ord($char) == 0) { $buf .= "000"; }
elsif (ord($char) == 39) { $buf .= "047"; }
elsif (ord($char) == 92) { $buf .= "134"; }
else { $buf .= $char; }
}
return $buf;
}
Any ideas? or thoughts on the revised procedure? I'd greatly
appriciate them.
Sorry for the length, but hopefully it give a good enough example.
Thanks
Michael Parker
pgpGUVHtkro1y.pgp
Description: PGP signature
Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0
Michael Parker <[EMAIL PROTECTED]> writes: > The next hurdle, and I've just posted to the DBD::Pg list, is > escaping/quoting the token strings. If you're trying to write a bytea[] literal, I think the most reliable way to write the individual bytes is nnn where nnn is *octal*. The idea here is: * string literal parser takes off one level of backslashing, leaving \\nnn * array input parser takes off another level, leaving \nnn * bytea input parser knows about backslashed octal values Note it has to be 3 octal digits every time, no abbreviations. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0
Michael Parker <[EMAIL PROTECTED]> writes:
> sub bytea_esc {
> my ($str) = @_;
> my $buf = "";
> foreach my $char (split(//,$str)) {
> if (ord($char) == 0) { $buf .= "000"; }
> elsif (ord($char) == 39) { $buf .= "047"; }
> elsif (ord($char) == 92) { $buf .= "134"; }
> else { $buf .= $char; }
> }
> return $buf;
> }
Oh, I see the problem: you forgot to convert " to a backslash sequence.
It would probably also be wise to convert anything >= 128 to a backslash
sequence, so as to avoid any possible problems with multibyte character
encodings. You wouldn't see this issue in a SQL_ASCII database, but I
suspect it would rise up to bite you with other encoding settings.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
