Re: [HACKERS] strange behavior of UPDATE

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(at)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(at)zeus]:~/test/sqlbench>gdb /usr/local/pgsql.65/bin/postgres 10130
GDB is free software and you are welcome to distribute copies of it
under certain conditions; type "show copying" to see the conditions.
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(at)sss(dot)pgh(dot)pa(dot)us>
> To: Edmund Mergl <E(dot)Mergl(at)bawue(dot)de>
> Cc: PostgreSQL Hackers Mailinglist <pgsql-hackers(at)postgreSQL(dot)org>
> Subject: Re: [HACKERS] strange behavior of UPDATE
>
> Edmund Mergl <E(dot)Mergl(at)bawue(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83