Re: [HACKERS] strange behavior of UPDATE - Mailing list pgsql-hackers

Tom,

did you wait until test finished.
I also tried to reproduce test with current 6.5 cvs, Linux 2.0.36,
DUAL PPRO 256Mb. It's still running, it's extremely slow, but
memory usage was about 10-11Mb, CPU usage about 5-9%. 
I use -B 1024 option. No surprize people won't use Postgres 
for large application.

9:12[postgres@zeus]:~/test/sqlbench> cat cat L9905232104.txt

postgresql-6.5pre on linux-2.2.7
Start of inserting 1000000 rows:       Sun May 23 21:04:32 MSD 1999
Start of indexing 1000000 rows:        Mon May 24 00:09:47 MSD 1999
Start of SetQuery single user:    Mon May 24 03:24:01 MSD 1999
Start of NewQuery single user:    Mon May 24 05:23:41 MSD 1999

9:15[postgres@zeus]:~/test/sqlbench>gdb /usr/local/pgsql.65/bin/postgres 10130
GDB is free software and you are welcome to distribute copies of itunder certain conditions; type "show copying" to see
theconditions.
 
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.16 (i486-slackware-linux),
Copyright 1996 Free Software Foundation, Inc...

/usr2/u/postgres/test/sqlbench/10130: No such file or directory.
Attaching to program /usr/local/pgsql.65/bin/postgres', process 10130
Reading symbols from /lib/libdl.so.1...done.
Reading symbols from /lib/libm.so.5...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /lib/libncurses.so.3.0...done.
Reading symbols from /lib/libc.so.5...done.
Reading symbols from /lib/ld-linux.so.1...done.
0x400c0564 in __read ()
(gdb) bt
#0  0x400c0564 in __read ()
#1  0x80e5abb in FileRead ()
#2  0x80ec793 in mdread ()
#3  0x80ed3b5 in smgrread ()
#4  0x80e34d2 in ReadBufferWithBufferLock ()
#5  0x80e33b2 in ReadBuffer ()
#6  0x806ff28 in heap_fetch ()
#7  0x809ec19 in IndexNext ()
#8  0x809b3e9 in ExecScan ()
#9  0x809ed61 in ExecIndexScan ()
#10 0x8099a46 in ExecProcNode ()
#11 0x809d1bd in ExecAgg ()
#12 0x8099ab6 in ExecProcNode ()
#13 0x80989f0 in ExecutePlan ()
#14 0x80982eb in ExecutorRun ()
#15 0x80eff54 in ProcessQueryDesc ()
#16 0x80effce in ProcessQuery ()
#17 0x80ee783 in pg_exec_query_dest ()
#18 0x80ee664 in pg_exec_query ()
#19 0x80ef8d8 in PostgresMain ()
#20 0x80d7290 in DoBackend ()
#21 0x80d6dd3 in BackendStartup ()
#22 0x80d6496 in ServerLoop ()
---Type <return> to continue, or q <return> to quit---
#23 0x80d603c in PostmasterMain ()
#24 0x80a9287 in main ()
#25 0x806502e in _start ()
(gdb) 

Top shows:

10130 postgres   7   0 11020  10M  9680 D       0  5.9  4.0   5:04 postmaster

Regards,
    Oleg

On Sun, 23 May 1999, Tom Lane wrote:

> Date: Sun, 23 May 1999 20:43:33 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Edmund Mergl <E.Mergl@bawue.de>
> Cc: PostgreSQL Hackers Mailinglist <pgsql-hackers@postgreSQL.org>
> Subject: Re: [HACKERS] strange behavior of UPDATE 
> 
> Edmund Mergl <E.Mergl@bawue.de> writes:
> > When loading 100.000 rows into the table everything works ok. Selects
> > and updates are reasonable fast. But when loading 1.000.000 rows the
> > select statements still work, but a simple update statement shows this
> > strange behavior. A never ending disk-activity starts. Memory
> > consumption increases up to the physical limit (384 MB) whereas the
> > postmaster uses only a few % of CPU time. After 1 hour I killed the
> > post-master.
> 
> I tried to reproduce this with current sources on a rather underpowered
> Linux box (64Mb of memory, about 40Mb of which is locked down by a
> high-priority data collection process).  It took a *long* time, but
> as far as I could see it was all disk activity, and that's hardly
> surprising given the drastic shortage of buffer cache memory.
> In particular I did not see any dramatic growth in the size of the
> backend process.  The test case
> 
> update bench set k500k = k500k + 1 where k100 = 30;
> 
> required a maximum of 10Mb.
> 
> Perhaps you could try it again with a current 6.5 snapshot and see
> whether things are any better?
> 
> Also, I suspect that increasing the postmaster -B setting beyond its
> default of 64 would be quite helpful.
> 
>             regards, tom lane
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83