Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
On 17-Nov-05, at 2:50 PM, Alex Turner wrote: Just pick up a SCSI drive and a consumer ATA drive. Feel their weight. You don't have to look inside to tell the difference. At one point stereo manufacturers put weights in the case just to make them heavier. The older ones weighed more and

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
On 18-Nov-05, at 1:07 AM, Luke Lonergan wrote: Greg, On 11/17/05 9:17 PM, Greg Stark [EMAIL PROTECTED] wrote: Ok, a more productive point: it's not really the size of the database that controls whether you're I/O bound or CPU bound. It's the available I/O bandwidth versus your CPU

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Richard Huxton
Dave Cramer wrote: On 18-Nov-05, at 1:07 AM, Luke Lonergan wrote: Postgres + Any x86 CPU from 2.4GHz up to Opteron 280 is CPU bound after 110MB/s of I/O. This is true of Postgres 7.4, 8.0 and 8.1. A $1,000 system with one CPU and two SATA disks in a software RAID0 will perform exactly the

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Luke Lonergan wrote: Alan, On 11/18/05 5:41 AM, Alan Stange [EMAIL PROTECTED] wrote: That's interesting, as I occasionally see more than 110MB/s of postgresql IO on our system. I'm using a 32KB block size, which has been a huge win in performance for our usage patterns. 300GB database

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Title: Re: [PERFORM] Hardware/OS recommendations for large databases ( Dave, On 11/18/05 5:00 AM, Dave Cramer [EMAIL PROTECTED] wrote: Now there's an interesting line drawn in the sand. I presume you have numbers to back this up ? This should draw some interesting posts. Part 2: The

Re: [PERFORM] Hardware/OS recommendations for large databases

2005-11-18 Thread Ron
While I agree with you in principle that pg becomes CPU bound relatively easily compared to other DB products (at ~110-120MBps according to a recent thread), there's a bit of hyperbole in your post. a. There's a big difference between the worst performing 1C x86 ISA CPU available and the best

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Title: Re: [PERFORM] Hardware/OS recommendations for large databases ( Dave, On 11/18/05 5:00 AM, Dave Cramer [EMAIL PROTECTED] wrote: Now there's an interesting line drawn in the sand. I presume you have numbers to back this up ? This should draw some interesting posts. OK, here we

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
On 18-Nov-05, at 8:30 AM, Luke Lonergan wrote: Richard, On 11/18/05 5:22 AM, "Richard Huxton" dev@archonet.com wrote: Well, I'm prepared to swap Luke *TWO* $1000 systems for one $80,000 system if he's got one going :-) Finally, a game worth playing! Except it’s backward – I’ll show you 80

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Vivek Khera
On Nov 18, 2005, at 10:13 AM, Luke Lonergan wrote:Still, there is a CPU limit here – this is not I/O bound, it is CPU limited as evidenced by the sensitivity to readahead settings.   If the filesystem could do 1GB/s, you wouldn’t go any faster than 244MB/s.Yeah, and mysql would probably be faster

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Vivek Khera
On Nov 18, 2005, at 1:07 AM, Luke Lonergan wrote: A $1,000 system with one CPU and two SATA disks in a software RAID0 will perform exactly the same as a $80,000 system with 8 dual core CPUs and the world's best SCSI RAID hardware on a large database for decision support (what the poster

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Bill McGonigle
On Nov 18, 2005, at 08:00, Dave Cramer wrote: A $1,000 system with one CPU and two SATA disks in a software RAID0 will perform exactly the same as a $80,000 system with 8 dual core CPUs and the world's best SCSI RAID hardware on a large database for decision support (what the poster asked

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Alan, On 11/18/05 5:41 AM, Alan Stange [EMAIL PROTECTED] wrote: That's interesting, as I occasionally see more than 110MB/s of postgresql IO on our system. I'm using a 32KB block size, which has been a huge win in performance for our usage patterns. 300GB database with a lot of

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Richard Huxton wrote: Dave Cramer wrote: On 18-Nov-05, at 1:07 AM, Luke Lonergan wrote: Postgres + Any x86 CPU from 2.4GHz up to Opteron 280 is CPU bound after 110MB/s of I/O. This is true of Postgres 7.4, 8.0 and 8.1. A $1,000 system with one CPU and two SATA disks in a software RAID0

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Title: Re: [PERFORM] Hardware/OS recommendations for large databases ( Richard, On 11/18/05 5:22 AM, Richard Huxton dev@archonet.com wrote: Well, I'm prepared to swap Luke *TWO* $1000 systems for one $80,000 system if he's got one going :-) Finally, a game worth playing! Except its backward

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Luke Lonergan wrote: Alan, On 11/18/05 8:13 AM, Alan Stange [EMAIL PROTECTED] wrote: I told you in my initial post that I was observing numbers in excess of what you claiming, but you seemed to think I didn't know how to measure an IO rate. Prove me wrong, post your data.

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Alex, On 11/18/05 8:28 AM, Alex Turner [EMAIL PROTECTED] wrote: Ok - so I ran the same test on my system and get a total speed of 113MB/sec. Why is this? Why is the system so limited to around just 110MB/sec? I tuned read ahead up a bit, and my results improve a bit.. OK! Now we're on the

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Bill, On 11/18/05 7:55 AM, Bill McGonigle [EMAIL PROTECTED] wrote: There is some truth to it. For an app I'm currently running (full-text search using tsearch2 on ~100MB of data) on: Do you mean 100GB? Sounds like you are more like a decision support /warehousing application. Dev

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alex Turner
Ok - so I ran the same test on my system and get a total speed of 113MB/sec. Why is this? Why is the system so limited to around just 110MB/sec? I tuned read ahead up a bit, and my results improve a bit.. Alex On 11/18/05, Luke Lonergan [EMAIL PROTECTED] wrote: Dave, On 11/18/05 5:00

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Luke Lonergan wrote: Alan, On 11/18/05 9:31 AM, Alan Stange [EMAIL PROTECTED] wrote: Here's the output from one iteration of iostat -k 60 while the box is doing a select count(1) on a 238GB table. avg-cpu: %user %nice%sys %iowait %idle 0.990.00 17.97 32.40

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Alan, On 11/18/05 10:30 AM, Alan Stange [EMAIL PROTECTED] wrote: Actually, this was dual cpu and there was other activity during the full minute, but it was on other file devices, which I didn't include in the above output. Given that, and given what I see on the box now I'd raise the 20%

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Greg Stark
Alan Stange [EMAIL PROTECTED] writes: Luke Lonergan wrote: Alan, On 11/18/05 9:31 AM, Alan Stange [EMAIL PROTECTED] wrote: Here's the output from one iteration of iostat -k 60 while the box is doing a select count(1) on a 238GB table. avg-cpu: %user %nice%sys %iowait

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Greg, On 11/18/05 11:07 AM, Greg Stark [EMAIL PROTECTED] wrote: That said, 130MB/s is nothing to sneeze at, that's maxing out two high end drives and quite respectable for a 3-disk stripe set, even reasonable for a 4-disk stripe set. If you're using 5 or more disks in RAID-0 or RAID 1+0 and

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Luke Lonergan wrote: opterons from Sun that we got some time ago. I think the 130MB/s is slow given the hardware, but it's acceptable. I'm not too price sensitive; I care much more about reliability, uptime, etc. I don't know what the system cost. It was part of block of dual Then I

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Greg Stark wrote: Alan Stange [EMAIL PROTECTED] writes: Luke Lonergan wrote: Alan, On 11/18/05 9:31 AM, Alan Stange [EMAIL PROTECTED] wrote: Here's the output from one iteration of iostat -k 60 while the box is doing a select count(1) on a 238GB table. avg-cpu: %user

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Title: Re: [PERFORM] Hardware/OS recommendations for large databases ( Vivek, On 11/18/05 8:07 AM, Vivek Khera [EMAIL PROTECTED] wrote: On Nov 18, 2005, at 10:13 AM, Luke Lonergan wrote: Still, there is a CPU limit here this is not I/O bound, it is CPU limited as evidenced by the

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Title: Re: [PERFORM] Hardware/OS recommendations for large databases ( Alan, On 11/18/05 8:13 AM, Alan Stange [EMAIL PROTECTED] wrote: I told you in my initial post that I was observing numbers in excess of what you claiming, but you seemed to think I didn't know how to measure an IO rate.

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
Luke,Interesting numbers. I'm a little concerned about the use of blockdev —setra 16384. If I understand this correctly it assumes that the table is contiguous on the disk does it not ?DaveOn 18-Nov-05, at 10:13 AM, Luke Lonergan wrote: Dave, On 11/18/05 5:00 AM, "Dave Cramer" [EMAIL PROTECTED]

Re: [PERFORM] Hardware/OS recommendations for large databases

2005-11-18 Thread Ron
Breaking the ~120MBps pg IO ceiling by any means is an important result. Particularly when you get a ~2x improvement. I'm curious how far we can get using simple approaches like this. At 10:13 AM 11/18/2005, Luke Lonergan wrote: Dave, On 11/18/05 5:00 AM, Dave Cramer [EMAIL PROTECTED]

Re: [PERFORM] Help speeding up delete

2005-11-18 Thread Leigh Dyer
Steve Wampler wrote: Is the performance behavior that we're experiencing a known problem with 7.2 that has been addressed in 7.4? Or will the upgrade fix other problems while leaving this one? I'm pretty sure that in versions earlier than 7.4, IN clauses that use a subquery will always use

[PERFORM] ERROR: no value found for parameter 1 with JDBC and Explain Analyze

2005-11-18 Thread Virag Saksena
Hi, I am trying to use Explain Analyze to trace a slow SQL statement called from JDBC. The SQL statement with the parameters taked 11 seconds. When I run a explain analyze from psql, it takes 50 ms with a reasonable explain plan. However when I try to run an explain analyze from JDBC with the

[PERFORM] sort/limit across union all

2005-11-18 Thread Marc Morin
We have a large DB with partitioned tables in postgres. We have had trouble with a ORDER/LIMIT type query. The order and limit are not pushed down to the sub-tables CREATE TABLE base ( foo int ); CREATE TABLE bar_0 extra int ) INHERITS (base); ALTER TABLE bar ADD PRIMARY KEY

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Mark Kirkwood
Luke Lonergan wrote: (mass snippage) time psql -c select count(*) from ivp.bigtable1 dgtestdb [EMAIL PROTECTED] IVP]$ cat sysout3 count -- 8000 (1 row) real1m9.875s user0m0.000s sys 0m0.004s [EMAIL PROTECTED] IVP]$

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Mark, On 11/18/05 3:46 PM, Mark Kirkwood [EMAIL PROTECTED] wrote: If you alter this to involve more complex joins (e.g 4. way star) and (maybe add a small number of concurrent executors too) - is it still the case? 4-way star, same result, that's part of my point. With Bizgres MPP, the

Re: [PERFORM] ERROR: no value found for parameter 1 with JDBC and Explain Analyze

2005-11-18 Thread Tom Lane
Virag Saksena [EMAIL PROTECTED] writes: ERROR: no value found for parameter 1 Here is sample code which causes this exception ... pst=prodconn.prepareStatement(explain analyze select count(*) from jam_heaprel r where heap_id = ? and parentaddr = ?); I don't think EXPLAIN can take

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Luke Lonergan
Title: Re: [PERFORM] Hardware/OS recommendations for large databases ( Mark, On 11/18/05 3:46 PM, Mark Kirkwood [EMAIL PROTECTED] wrote: If you alter this to involve more complex joins (e.g 4. way star) and (maybe add a small number of concurrent executors too) - is it still the case? I may

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Mark Kirkwood
Luke Lonergan wrote: Mark, On 11/18/05 3:46 PM, Mark Kirkwood [EMAIL PROTECTED] wrote: If you alter this to involve more complex joins (e.g 4. way star) and (maybe add a small number of concurrent executors too) - is it still the case? I may not have listened to you - are you

[PERFORM] What is the max number of database I can create in an instance of pgsql?

2005-11-18 Thread anon permutation
Hi, We want to create a database for each one of our departments, but we only want to have one instance of postgresql running. There are about 10-20 departments. I can easily use createdb to create these databases. However, what is the max number of database I can create before

Re: [PERFORM] What is the max number of database I can create in an instance of pgsql?

2005-11-18 Thread Michael Glaesemann
On Nov 19, 2005, at 12:24 , anon permutation wrote: However, what is the max number of database I can create before performance goes down? Assuming each database is performing well alone, how would putting 10-20 of them together in one instance affect postgres? In terms of getting a new

Re: [PERFORM] What is the max number of database I can create in an instance of pgsql?

2005-11-18 Thread Jaime Casanova
On 11/18/05, anon permutation [EMAIL PROTECTED] wrote: Hi, We want to create a database for each one of our departments, but we only want to have one instance of postgresql running. There are about 10-20 departments. I can easily use createdb to create these databases. However, After of