Re: [PERFORM] tuning questions

2003-12-09 Thread Jack Coates
On Mon, 2003-12-08 at 11:19, Tom Lane wrote: Jack Coates [EMAIL PROTECTED] writes: Theories at this point, in no particular order: a) major differences between my 7.3.4 from source (compiled with no options) and dev's 7.3.2-1PGDG RPMs. Looking at the spec file doesn't reveal anything

Re: [PERFORM] tuning questions

2003-12-09 Thread Matt Clark
I ended up going back to a default postgresql.conf and reapplying the various tunings one-by-one. Turns out that while setting fsync = false had little effect on the slow IDE box, it had a drastic effect on this faster SCSI box and performance is quite acceptable now (aside from the expected

Re: [PERFORM] tuning questions

2003-12-08 Thread Jack Coates
On Fri, 2003-12-05 at 17:22, Jack Coates wrote: ... That's it, I'm throwing out this whole test series and starting over with different hardware. Database server is now a dual 2GHz Xeon with 2GB RAM 2940UW SCSI, OS and PG's logs on 36G drive, PG data on 9GB drive. Data is importing now and

Re: [PERFORM] tuning questions

2003-12-05 Thread Thierry Missimilly
Jack Coates wrote: latest changes: shared_buffers = 35642 max_fsm_relations = 1000 max_fsm_pages = 1 wal_buffers = 64 sort_mem = 32768 vacuum_mem = 32768 effective_cache_size = 1 /proc/sys/kernel/shmmax = 5 IO is active, but hardly saturated. CPU load is hefty

Re: [PERFORM] tuning questions

2003-12-05 Thread Josh Berkus
Jack, The frustrating thing is, we also have a UP P3-500 with 512M RAM and two IDE drives with the same PG install which is doing okay with this load -- still half the speed of MS-SQL2K, but usable. I'm at a loss. Overall, I'm really getting the feeling that this procedure was optimized for

Re: [PERFORM] tuning questions

2003-12-05 Thread Jack Coates
On Fri, 2003-12-05 at 09:26, Josh Berkus wrote: Jack, The frustrating thing is, we also have a UP P3-500 with 512M RAM and two IDE drives with the same PG install which is doing okay with this load -- still half the speed of MS-SQL2K, but usable. I'm at a loss. Overall, I'm really

Re: [PERFORM] tuning questions

2003-12-04 Thread Josh Berkus
Jack, The application is on another server, and does this torture test: it builds a large table (~6 million rows in one test, ~18 million in another). Rows are then pulled in chunks of 4 to 6 thousand, acted on, and inserted back into another table (which will of course eventually grow to

Re: [PERFORM] tuning questions

2003-12-04 Thread Jeff
On Thu, 04 Dec 2003 08:06:23 -0800 Jack Coates [EMAIL PROTECTED] wrote: testbed: dual P3 1.3 GHz box with 2GB RAM two IDE 120G drives on separate channels (DMA on), OS on one, DB on the other, some swap on each (totalling 2.8G). RH Linux 8. Side Note: be sure to turn off write caching on

Re: [PERFORM] tuning questions

2003-12-04 Thread Rob Fielding
I've tweaked shared buffers to 8192, pushed sort memory to 2048, vacuum memory to 8192, and effective cache size to 1. /proc/sys/kernel/shmmax is set to 16 and /proc/sys/fs/file-max is set to 65536. Ulimit -n 3192. Your sharedmemory is too high, and not even being used effectivey.

Re: [PERFORM] tuning questions

2003-12-04 Thread scott.marlowe
On Thu, 4 Dec 2003, Jack Coates wrote: Another problem is that performance of the 6 million row job is decent if I stop the job and run a vacuumdb --analyze before letting it continue; is this something that 7.4 will help with? vacuumb --analyze doesn't seem to have much effect on the 18

Re: [PERFORM] tuning questions

2003-12-04 Thread Dror Matalon
On Thu, Dec 04, 2003 at 11:59:32AM -0500, Jeff wrote: On Thu, 04 Dec 2003 08:06:23 -0800 Jack Coates [EMAIL PROTECTED] wrote: testbed: dual P3 1.3 GHz box with 2GB RAM two IDE 120G drives on separate channels (DMA on), OS on one, DB on the other, some swap on each (totalling 2.8G).

Re: [PERFORM] tuning questions

2003-12-04 Thread Jord Tanner
If I understand the problem correctly, the issue is that IDE drives signal that data has been written to disk when they actually are holding the data in the write cache. In the case of a power down (and I remember someone showing some test results confirming this, check the list archive) the data

Re: [PERFORM] tuning questions

2003-12-04 Thread Andrew Sullivan
On Thu, Dec 04, 2003 at 09:57:38AM -0800, Dror Matalon wrote: I've seen this comment several times from different people. Would someone care to explain how you would get data corruption? I thought that the whole idea of the log is to provide a journal similar to what you get in a journaling

Re: [PERFORM] tuning questions

2003-12-04 Thread scott.marlowe
On Thu, 4 Dec 2003, Josh Berkus wrote: Scott, Just to add to what the others have said here, you probably want to run the pg_autovacuum daemon in the background. It comes with 7.4 but will work fine with 7.3. I don't recommend using pg_autovacuum with a data transformation task.

Re: [PERFORM] tuning questions

2003-12-04 Thread Eric Soroos
On Dec 4, 2003, at 10:11 AM, Andrew Sullivan wrote: On Thu, Dec 04, 2003 at 09:57:38AM -0800, Dror Matalon wrote: I've seen this comment several times from different people. Would someone care to explain how you would get data corruption? I thought that the whole idea of the log is to provide a

Re: [PERFORM] tuning questions

2003-12-04 Thread Josh Berkus
Jack, Following this, I've done: 2gb ram = 2,000,000,000 bytes This calculation is fun, but I really don't know where you got it from. It seems quite baroque. What are you trying to set, exactly? getting the SQL query better optimized for PG is on my todo list, but not something I

Re: [PERFORM] tuning questions

2003-12-04 Thread Jack Coates
On Thu, 2003-12-04 at 11:20, Josh Berkus wrote: Jack, Following this, I've done: 2gb ram = 2,000,000,000 bytes This calculation is fun, but I really don't know where you got it from. It seems quite baroque. What are you trying to set, exactly? Message-ID: [EMAIL PROTECTED]

Re: [PERFORM] tuning questions

2003-12-04 Thread Jack Coates
On Thu, 2003-12-04 at 12:27, Richard Huxton wrote: On Thursday 04 December 2003 19:50, Jack Coates wrote: I'm trying to set Postgres's shared memory usage in a fashion that allows it to return requested results quickly. Unfortunately, none of these changes allow PG to use more than a

Re: [PERFORM] tuning questions

2003-12-04 Thread Richard Huxton
On Thursday 04 December 2003 19:50, Jack Coates wrote: I'm trying to set Postgres's shared memory usage in a fashion that allows it to return requested results quickly. Unfortunately, none of these changes allow PG to use more than a little under 300M RAM. vacuumdb --analyze is now taking an

Re: [PERFORM] tuning questions

2003-12-04 Thread Josh Berkus
Jack, latest changes: shared_buffers = 35642 This is fine, it's about 14% of available RAM. Though the way you calculated it still confuses me. It's not complicated; it should be between 6% and 15% of available RAM; since you're doing a data-transformation DB, yours should be toward the

Re: [PERFORM] tuning questions

2003-12-04 Thread scott.marlowe
On Thu, 4 Dec 2003, Jack Coates wrote: On Thu, 2003-12-04 at 12:27, Richard Huxton wrote: On Thursday 04 December 2003 19:50, Jack Coates wrote: I'm trying to set Postgres's shared memory usage in a fashion that allows it to return requested results quickly. Unfortunately, none of

Re: [PERFORM] tuning questions

2003-12-04 Thread Eric Soroos
IO is active, but hardly saturated. CPU load is hefty though, load average is at 4 now. procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 2 1 2808 11432 39616 1902984 0 0 204

Re: [PERFORM] tuning questions

2003-12-04 Thread Jack Coates
On Thu, 2003-12-04 at 13:24, Josh Berkus wrote: Jack, latest changes: shared_buffers = 35642 This is fine, it's about 14% of available RAM. Though the way you calculated it still confuses me. It's not complicated; it should be between 6% and 15% of available RAM; since you're

Re: [PERFORM] tuning questions

2003-12-04 Thread Jack Coates
On Thu, 2003-12-04 at 14:59, Eric Soroos wrote: IO is active, but hardly saturated. CPU load is hefty though, load average is at 4 now. procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs

Re: [PERFORM] tuning questions

2003-12-04 Thread Richard Huxton
On Thursday 04 December 2003 23:16, Jack Coates wrote: effective_cache_size = 1 This is way the heck too low. it's supposed to be the size of all available RAM; I'd set it to 2GB*65% as a start. This makes a little bit of difference. I set it to 65% (15869 pages). That's still

Re: [PERFORM] tuning questions

2003-12-04 Thread Jack Coates
On Thu, 2003-12-04 at 15:47, Richard Huxton wrote: On Thursday 04 December 2003 23:16, Jack Coates wrote: effective_cache_size = 1 This is way the heck too low. it's supposed to be the size of all available RAM; I'd set it to 2GB*65% as a start. This makes a little bit of