Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread ohp
Hi Tom, You still have an account on my Unixware Bi-Xeon hyperthreded machine. Feel free to use it for your tests. On Mon, 19 Apr 2004, Tom Lane wrote: Date: Mon, 19 Apr 2004 20:53:09 -0400 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Joe Conway [EMAIL PROTECTED], scott.marlowe

Re: [PERFORM] Deleting certain duplicates

2004-04-20 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 So I was thinking maybe of doing the deletion in chunks, perhaps based on reception time. Are there any suggestions for a better way to do this, or using multiple queries to delete selectively a week at a time based on the reception_time. I

Re: [PERFORM] Why will vacuum not end?

2004-04-20 Thread Shea,Dan [CIS]
No, but data is constantly being inserted by userid scores. It is postgres runnimg the vacuum. Dan. -Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 12:02 AM To: Shea,Dan [CIS] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Dave Cramer
Dual Athlon With one process running 30 cs/second with two process running 15000 cs/second Dave On Tue, 2004-04-20 at 08:46, Jeff wrote: On Apr 19, 2004, at 8:01 PM, Tom Lane wrote: [test case] Quad P3-700Mhz, ServerWorks, pg 7.4.2 - 1 process: 10-30 cs / second

Re: [PERFORM] Why will vacuum not end?

2004-04-20 Thread Bill Moran
Shea,Dan [CIS] wrote: No, but data is constantly being inserted by userid scores. It is postgres runnimg the vacuum. Dan. -Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 12:02 AM To: Shea,Dan [CIS] Cc: [EMAIL PROTECTED] Subject: Re:

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Matt Clark
As a cross-ref to all the 7.4.x tests people have sent in, here's 7.2.3 (Redhat 7.3), Quad Xeon 700MHz/1MB L2 cache, 3GB RAM. Idle-ish (it's a production server) cs/sec ~5000 3 test queries running: procs memoryswap io system cpu r b w swpd

Re: [PERFORM] Index Problem?

2004-04-20 Thread Jochem van Dieten
Ron St-Pierre wrote: I am using postgres 7.4.1 and have a problem with a plpgsql function. When I run the function on the production server it takes approx 33 minutes to run. I dumped the DB and copied it to a similarly configured box and ran the function and it ran in about 10 minutes. Can

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Sven Geisler
Hi Tom, Just to explain our hardware situation releated to the FSB of the XEON's. We have older XEON DP in operation with FSB 400 and 2.4 GHz. The XEON MP box runs with 2.5 GHz. The XEON MP box is a Fujitsu Siemens Primergy RX600 with ServerWorks GC LE as chipset. The box, which Dirk were use to

[PERFORM] seeking consultant for high performance, complex searching with Postgres web app

2004-04-20 Thread Mark Stosberg
Hello, I work for Summersault, LLC. We've been using Postgres since the days of Postgres 6.5. We're focused on building database-driven websites using Perl and Postgres. We are currently seeking help developing a search system that needs to perform complex queries with high performance. Although

[PERFORM] Any way to 'analyze' indexes to get updated sizes?

2004-04-20 Thread Jeremy M. Guthrie
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is there a way to analyze indexes to provide updated sizes? Is a vacuum the only way to determine the size of an index? Analyze updates the stats so I can see table space sizes but I cannot find an alternative to vacuum for indexes. - -- -

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Dirk Lutzebaeck
I would agree to Tom, that too much parameters are involved to blame bigmem. I have access to the following machines where the same application operates: a) Dual (4way) XEON MP, bigmem, HT off, ServerWorks chipset (a Fujitsu-Siemens Primergy) performs ok now because missing indexes were

Re: [PERFORM] Toooo many context switches (maybe SLES8?)

2004-04-20 Thread Dirk Lutzebaeck
Could this be related to the O(1) scheduler backpatches from 2.6 to 2.4 kernel on newer 2.4er distros (RedHat, SuSE)? Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Improve spinlock code for recent x86 processors: insert a PAUSE instruction in the s_lock() wait loop, and use test

Re: [PERFORM] possible improvement between G4 and G5

2004-04-20 Thread Aaron Werman
There are a few things that you can do to help force yourself to be I/O bound. These include: - RAID 5 for write intensive applications, since multiple writes per synch write is good. (There is a special case for logging or other streaming sequential writes on RAID 5) - Data journaling file

Re: [PERFORM] sunquery and estimated rows

2004-04-20 Thread Rod Taylor
On Sun, 2004-04-18 at 19:09, Tom Lane wrote: Markus Bertheau [EMAIL PROTECTED] writes: , 17.04.2004, 01:45, Tom Lane : The planner sees that as where scope = some complicated expression and falls back to a default estimate. It won't simplify a sub-select to a constant. (Some people

Re: [PERFORM] Toooo many context switches (maybe SLES8?)

2004-04-20 Thread Dave Cramer
Don't think so, mine is a vanilla kernel from kernel.org Dave On Thu, 2004-04-15 at 16:03, Dirk Lutzebaeck wrote: Could this be related to the O(1) scheduler backpatches from 2.6 to 2.4 kernel on newer 2.4er distros (RedHat, SuSE)? Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes:

[PERFORM] Use of subquery causes seq scan???

2004-04-20 Thread Chris Hoover
I need some help. I have a query that refuses to use the provided index and is always sequentially scanning causing me large performance headaches. Here is the basic situation: Table A: inv_num int typechar . . . pkey (inv_num, type) indx(inv_num) Table B (has the same primary

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Dirk Lutzebäck
Dirk Lutzebaeck wrote: c) Dual XEON DP, non-bigmem, HT on, E7500 Intel chipset (Supermicro) performs well and I could not observe context switch peaks here (one user active), almost no extra semop calls Did Tom's test here: with 2 processes I'll reach 200k+ CS with peaks to 300k CS. Bummer..

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Paul Tuckfield
I tried to test how this is related to cache coherency, by forcing affinity of the two test_run.sql processes to the two cores (pipelines? threads) of a single hyperthreaded xeon processor in an smp xeon box. When the processes are allowed to run on distinct chips in the smp box, the CS storm

Re: [PERFORM] Horribly slow hash join

2004-04-20 Thread Jim C. Nasby
Dammit, I somehow deleted a bunch of replies to this. Did a TODO ever come out of this? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows:

[PERFORM] Moving postgres to FC disks

2004-04-20 Thread Anjan Dave
I am planning to move the pg databases from the internal RAID to external Fiber Channel over SAN. Question is -With the db size being as big as, say, 30+GB, how do I move it on the new logical drive? (stop postgresql, and simply move it over somehow and make a link?) -Currently,

Re: [PERFORM] Use of subquery causes seq scan???

2004-04-20 Thread Tom Lane
Chris Hoover [EMAIL PROTECTED] writes: Select * from table a where inv_num in (select inv_num from table b where ) I'm running 7.3.4 on RedHat EL 2.1. IN (SELECT) constructs pretty well suck in PG releases before 7.4. Update, or consult the FAQ about rewriting into an EXISTS form.

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Paul Tuckfield
Ooops, what I meant to say was that 2 threads bound to one (hyperthreaded) cpu does *NOT* cause the storm, even on an smp xeon. Therefore, the context switches may be a result of cache coherency related delays. (2 threads on one hyperthreaded cpu presumably have tightly coupled 1,l2 cache.)

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Josh Berkus
Dirk, Tom, OK, off IRC, I have the following reports: Linux 2.4.21 or 2.4.20 on dual Pentium III : problem verified Linux 2.4.21 or 2.4.20 on dual Penitum II : problem cannot be reproduced Solaris 2.6 on 6 cpu e4500 (using 8 processes) : problem not reproduced -- -Josh Berkus Aglio Database

Re: [PERFORM] Use of subquery causes seq scan???

2004-04-20 Thread Bruno Wolff III
Please don't reply to messages to start new threads. On Tue, Apr 20, 2004 at 10:20:05 -0400, Chris Hoover [EMAIL PROTECTED] wrote: I need some help. I have a query that refuses to use the provided index and is always sequentially scanning causing me large performance headaches. Here is

Re: [PERFORM] Moving postgres to FC disks

2004-04-20 Thread Joshua D. Drake
-With the db size being as big as, say, 30+GB, how do I move it on the new logical drive? (stop postgresql, and simply move it over somehow and make a link?) I would stop the database, move the data directory to the new volume using rsync then start up postgresql pointed at the new

Re: [PERFORM] 225 times slower

2004-04-20 Thread Pailloncy Jean-Gérard
Hi, I apologize for the mistake. So, I dump the database, I reload it then VACUUM ANALYZE. For each statement: I then quit postgres, start it, execute one command, then quit. Le 14 avr. 04, à 14:39, Pailloncy Jean-Gérard a écrit : dps=# explain analyze SELECT rec_id FROM url WHERE crc32!=0

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread J. Andrew Rogers
I verified problem on a Dual Opteron server. I temporarily killed the normal load, so the server was largely idle when the test was run. Hardware: 2x Opteron 242 Rioworks HDAMA server board 4Gb RAM OS Kernel: RedHat9 + XFS 1 proc: 10-15 cs/sec 2 proc: 400,000-420,000 cs/sec j. andrew

Re: [PERFORM] Why will vacuum not end?

2004-04-20 Thread Christopher Kings-Lynne
No, but data is constantly being inserted by userid scores. It is postgres runnimg the vacuum. Dan. Well, inserts create some locks - perhaps that's the problem... Otherwise, check the pg_locks view to see if you can figure it out. Chris ---(end of

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Anjan Dave
If this helps - Quad 2.0GHz XEON with highest load we have seen on the applications, DB performing great - procs memory swap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 0 0 1616 351820

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Bruce Momjian
Dirk Lutzebäck wrote: Dirk Lutzebaeck wrote: c) Dual XEON DP, non-bigmem, HT on, E7500 Intel chipset (Supermicro) performs well and I could not observe context switch peaks here (one user active), almost no extra semop calls Did Tom's test here: with 2 processes I'll reach 200k+ CS

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Josh Berkus
Anjan, Quad 2.0GHz XEON with highest load we have seen on the applications, DB performing great - Can you run Tom's test? It takes a particular pattern of data access to reproduce the issue. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of

Re: [PERFORM] Persistent Connections

2004-04-20 Thread Thomas Swan
Randolf Richardson wrote: [EMAIL PROTECTED] (Nick Barr) stated in comp.databases.postgresql.performance: [EMAIL PROTECTED] wrote: [sNip] Sorry I m a little bit confused about the persistent thing!! Is it smart to use persistent connections at all if i expect 100K Users to hit the

Re: [PERFORM] seeking consultant for high performance, complex searching

2004-04-20 Thread Rajesh Kumar Mallah
Have you checked Tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ is the most feature rich Full text Search system available for postgresql. We are also using the same system in the revamped version of our website. Regds Mallah. Mark Stosberg wrote: Hello, I work for

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Dave Cramer
I modified the code in s_lock.c to remove the spins #define SPINS_PER_DELAY 1 and it doesn't exhibit the behaviour This effectively changes the code to while(TAS(lock)) select(1); // 10ms Can anyone explain why executing TAS 100 times would increase context switches ?

Re: [PERFORM] Moving postgres to FC disks

2004-04-20 Thread Aaron Werman
I agree on not linking and adding non-SAN disk dependancy to your DB. I'm trying to understand your FS reasoning. I have never seen XFS run faster than ReiserFS in any situation (or for that matter beat any FS in performance except JFS). XFS has some nifty very large file features, but we're

[PERFORM] pgbench written in Pro*C

2004-04-20 Thread Bruce Momjian
I received a copy of pgbench rewritten in Pro*C, which is similar to embedded C. I think it was done so the same program could be tested on Oracle and PostgreSQL. Are folks interested in this code? Should it be put on gborg or in our /contrib/pgbench? -- Bruce Momjian

Re: [PERFORM] pgbench written in Pro*C

2004-04-20 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: I received a copy of pgbench rewritten in Pro*C, which is similar to embedded C. I think it was done so the same program could be tested on Oracle and PostgreSQL. Are folks interested in this code? Should it be put on gborg or in our

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Joe Conway
Joe Conway wrote: In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a

Re: [PERFORM] pgbench written in Pro*C

2004-04-20 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I received a copy of pgbench rewritten in Pro*C, which is similar to embedded C. I think it was done so the same program could be tested on Oracle and PostgreSQL. Are folks interested in this code? Should it be put on gborg or in