Jack,
> Right, because re-architecture of a cross-platform query makes sense if
> performance is bad on all systems, but is questionable activity when
> performance is fine on some systems and lousy on others. Hence my
> statement that while SQL optimization is certainly something we want to
> do
> 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
> expec
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
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 glaring to me, but is there something I'm missin
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 an
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 re
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
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
d'oh, just realized what you're telling me here. /me smacks forehead.
Let's try effective_cache of 183105... (75%). Starting both servers,
waiting for big fetch to start, and...
procs memoryswap io
system cpu
r b w swpd free buff cache si so
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 mak
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
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
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 yo
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
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,
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
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
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 tha
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: <[EMA
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 somethi
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 jo
On Thu, 2003-12-04 at 09:12, Rob Fielding wrote:
> >
> > 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.
>
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 transformati
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. pg_av
is designed for "regular use" not hu
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 journal
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 i
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 (totallin
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 m
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. Your
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
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 t
Hi,
sorry for duplication, I asked this on pgsql-admin first before
realizing it wasn't the appropriate list.
I'm having trouble optimizing PostgreSQL for an admittedly heinous
worst-case scenario load.
testbed:
dual P3 1.3 GHz box with 2GB RAM
two IDE 120G drives on separate channels (DMA on),
32 matches
Mail list logo
