Re: [PERFORM] Best use of second controller with faster disks?

2007-06-13 Thread Francisco Reyes
Vivek Khera writes: FreeBSD, indeed. The vendor, Partners Data Systems, did a wonderful This one? http://www.partnersdata.com job ensuring that everything integrated well to the point of talking with various FreeBSD developers, LSI engineers, etc., and sent me a fully tested system end-

Re: [PERFORM] Update table performance problem

2007-06-13 Thread Mark Makarowsky
The version is: "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" Here is the table definition for co and fco. There aren't any rules constraints, triggers, etc. on the tables. Only an index on each table for the xno field. Any other thoughts? CREATE T

Re: [PERFORM] Performance Testing Utility

2007-06-13 Thread Vivek Khera
On Jun 13, 2007, at 6:25 AM, Christo Du Preez wrote: Is there some kind of performance testing utility available for postgresql Something I can run after installing postgresql to help me identify if my installation is optimal. Your own app is the only one that will give you meaningful resul

Re: [PERFORM] Update table performance problem

2007-06-13 Thread Tom Lane
Mark Makarowsky <[EMAIL PROTECTED]> writes: > "Hash Join (cost=15590.22..172167.03 rows=383654 > width=215) (actual time=1473.297..43032.178 > rows=383654 loops=1)" > " Hash Cond: (co.xno = fco.xno)" > " -> Seq Scan on co (cost=0.00..123712.64 > rows=384964 width=195) (actual time=440.196..373

Re: [PERFORM] [PG 8.1.0 / AIX 5.3] Vacuum processes freezing

2007-06-13 Thread Tom Lane
=?iso-8859-1?Q?RESTOUX=2C_Lo=EFc?= <[EMAIL PROTECTED]> writes: > Since some weeks, the amount of data hosted by the database grows, and, som= > e nights, the database vacuum seems to "freeze" during his execution. In v= > erbose mode, the logs show that the vacuum clean up a table (not always the=

[PERFORM] Update table performance problem

2007-06-13 Thread Mark Makarowsky
I am trying to update a field in one table with a field from another table like: update co set firest_id=fco.firest_id,fire_dist=fco.fire_dist from fco where co.xno=fco.xno Table co has 384964 records Table fco has 383654 records The xno fields in both tables are indexed but they don't seem to b

Re: [PERFORM] dbt2 NOTPM numbers

2007-06-13 Thread Mark Wong
On 6/13/07, Markus Schiltknecht <[EMAIL PROTECTED]> wrote: Hi, Mark Wong wrote: > Yeah, I ran with 500+ warehouses, but I had 6 14-disk arrays of 15K > RPM scsi drives and 6 dual-channel controllers... :) Lucky you! In the mean time, I've figured out that the box in question peaked at about 14

[PERFORM] [PG 8.1.0 / AIX 5.3] Vacuum processes freezing

2007-06-13 Thread RESTOUX , Loïc
Hello everybody, We're using PostgreSQL 8.1.0 on AIX 5.3 through NFS (a Netapp Filer hosts the database files), and we're encoutering somes issues with vaccums. PostgreSQL binaries are built with xlc 6 (C for AIX Compiler 6.0.0.6) on AIX 5.2 (yes, I know, building on 5.2 and running on 5.3 i

Re: [PERFORM] dbt2 NOTPM numbers

2007-06-13 Thread Markus Schiltknecht
Hi, Mark Wong wrote: Yeah, I ran with 500+ warehouses, but I had 6 14-disk arrays of 15K RPM scsi drives and 6 dual-channel controllers... :) Lucky you! In the mean time, I've figured out that the box in question peaked at about 1450 NOTPMs with 120 warehouses with RAID 1+0. I'll try to comp

Re: [PERFORM] dbt2 NOTPM numbers

2007-06-13 Thread Mark Wong
On 6/11/07, Markus Schiltknecht <[EMAIL PROTECTED]> wrote: Heikki Linnakangas wrote: > Markus Schiltknecht wrote: >> For dbt2, I've used 500 warehouses and 90 concurrent connections, >> default values for everything else. > > 500? That's just too much for the hardware. Start from say 70 warehouse

Re: [PERFORM] WAL shipping and ever expanding pg_xlog

2007-06-13 Thread Michael Dengler
Doug Knight just informed me about the pg_standby module. Works like a charm! Thanks Mike On 6/13/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: On 6/13/07, Michael Dengler <[EMAIL PROTECTED]> wrote: > OKit looks like the slave machine has run out of space and that caused > the xlog file

Re: [PERFORM] WAL shipping and ever expanding pg_xlog

2007-06-13 Thread Merlin Moncure
On 6/13/07, Michael Dengler <[EMAIL PROTECTED]> wrote: OKit looks like the slave machine has run out of space and that caused the xlog files to pile up on the master. Still...how do I prevent such all of the shipped WAL segments from remaining on the slave machine? Do I need to retain every

Re: [PERFORM] VACUUM vs auto-vacuum daemon

2007-06-13 Thread Joshua D. Drake
Bill Moran wrote: In response to "Sabin Coanda" <[EMAIL PROTECTED]>: Hi Bill, ... However, you can get some measure of tracking my running VACUUM VERBOSE on a regular basis to see how well autovacuum is keeping up. There's no problem with running manual vacuum and autovacuum together, and yo

Re: [PERFORM] VACUUM vs auto-vacuum daemon

2007-06-13 Thread Bill Moran
In response to "Sabin Coanda" <[EMAIL PROTECTED]>: > Hi Bill, > > ... > > > > However, you can get some measure of tracking my running VACUUM VERBOSE > > on a regular basis to see how well autovacuum is keeping up. There's > > no problem with running manual vacuum and autovacuum together, and yo

Re: [PERFORM] Best use of second controller with faster disks?

2007-06-13 Thread Vivek Khera
On Jun 12, 2007, at 8:33 PM, Francisco Reyes wrote: Vivek Khera writes: what raid card have you got? 2 3ware cards. I believe both are 9550SX i'm playing with an external enclosure which has an areca sata raid in it and connects to the host via fibre channel. What is the OS? FreeBSD?

Re: [PERFORM] WAL shipping and ever expanding pg_xlog

2007-06-13 Thread Michael Dengler
OKit looks like the slave machine has run out of space and that caused the xlog files to pile up on the master. Still...how do I prevent such all of the shipped WAL segments from remaining on the slave machine? Do I need to retain every single one? Can they be safely removed after the slave m

[PERFORM] WAL shipping and ever expanding pg_xlog

2007-06-13 Thread Michael Dengler
Hi, I'm doing WAL shipping to do a warm standby system (8.2.4). The problem is that the pg_xlog dir on the master just gets bigger and bigger (never seems to truncate) and the corresponding archive directory on the slave also gets bigger and bigger. Is there a way to moderate this? Thanks Mike

Re: [PERFORM] Optimize slow query

2007-06-13 Thread Cédric Villemain
Le mercredi 13 juin 2007, Tyler Durden a écrit : > Hi, > I've a table with 300 000 records and I'm trying to do a search: > > SELECT * FROM addresses WHERE address ILIKE '%Jean Paul%' AND > (l_pc='4250' or r_pc='4250') AND (l_struc='O' or r_struc='O') AND > (prenm ILIKE 'Street') > > It performs in

Re: [PERFORM] Optimize slow query

2007-06-13 Thread Tyler Durden
On 6/13/07, Tyler Durden <[EMAIL PROTECTED]> wrote: Hi, I've a table with 300 000 records and I'm trying to do a search: SELECT * FROM addresses WHERE address ILIKE '%Jean Paul%' AND (l_pc='4250' or r_pc='4250') AND (l_struc='O' or r_struc='O') AND (prenm ILIKE 'Street') It performs in 2 second

[PERFORM] Optimize slow query

2007-06-13 Thread Tyler Durden
Hi, I've a table with 300 000 records and I'm trying to do a search: SELECT * FROM addresses WHERE address ILIKE '%Jean Paul%' AND (l_pc='4250' or r_pc='4250') AND (l_struc='O' or r_struc='O') AND (prenm ILIKE 'Street') It performs in 2 seconds in a dual Xeon 2.4mhz with 2Gb of RAM. I'm using Po

Re: [PERFORM] Performance Testing Utility

2007-06-13 Thread Tobias Brox
[Christo Du Preez - Wed at 12:25:20PM +0200] > Is there some kind of performance testing utility available for > postgresql Something I can run after installing postgresql to help me > identify if my installation is optimal. > > I've been battling for days now trying to sort out performance issues

Re: [PERFORM] Performance Testing Utility

2007-06-13 Thread Heikki Linnakangas
Christo Du Preez wrote: Is there some kind of performance testing utility available for postgresql Something I can run after installing postgresql to help me identify if my installation is optimal. Not really. There's contrib/pgbench, but I wouldn't recommend using it for that purpose since th

[PERFORM] Performance Testing Utility

2007-06-13 Thread Christo Du Preez
Hi All, Is there some kind of performance testing utility available for postgresql Something I can run after installing postgresql to help me identify if my installation is optimal. I've been battling for days now trying to sort out performance issues and something like that may just identify iss