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

Edmund,

Here is what I got running that test on DUAL PII 350Mhz, 256 RAM,
FreeBSD-3.1 elf release, current 6.5 cvs:

Start of inserting 1000000 rows:       SB  22 MAJ 1999 13:14:58 MSD
Start of indexing 1000000 rows:        SB  22 MAJ 1999 14:47:06 MSD
Start of SetQuery single user:    SB  22 MAJ 1999 18:18:05 MSD
Start of NewQuery single user:    SB  22 MAJ 1999 19:38:06 MSD
End of iX SQLBench 2.1:      WS  23 MAJ 1999 11:05:25 MSD

It was so slow, does FreeBSD has slow IO operation or it's swapping 
problem ? 
SB  22 MAJ 1999 22:07:26 MSD
Q8A
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is impossible. Terminating.
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is impossible. Terminating.
WS  23 MAJ 1999 11:05:24 MSD
Q8B
Connection to database 'test' failed.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I did upgrade of postgres and accidentally stopped testing - I didn't expect
it's still running !

After reinstalling of postgres I did 
test=> select count(*) from bench;
anf it takes forever ! Anybody tried that test ?
Here is a bt from gdb:
0x8078b8d in TransactionIdDidAbort ()
(gdb) bt 
#0  0x8078b8d in TransactionIdDidAbort ()
#1  0x806b460 in heapgettup ()
#2  0x806bf3f in heap_getnext ()
#3  0x809c816 in SeqNext ()
#4  0x8097609 in ExecScan ()
#5  0x809c8cb in ExecSeqScan ()
#6  0x8095c56 in ExecProcNode ()
#7  0x80993dd in ExecAgg ()
#8  0x8095cd6 in ExecProcNode ()
#9  0x8094c10 in ExecutePlan ()
#10 0x809450b in ExecutorRun ()
#11 0x80ec121 in ProcessQueryDesc ()
#12 0x80ec19e in ProcessQuery ()
#13 0x80eaab3 in pg_exec_query_dest ()
#14 0x80ea994 in pg_exec_query ()
#15 0x80ebb28 in PostgresMain ()
#16 0x80d3608 in DoBackend ()
#17 0x80d30f3 in BackendStartup ()
#18 0x80d2716 in ServerLoop ()
#19 0x80d226f in PostmasterMain ()
#20 0x80a5517 in main ()
#21 0x80611fd in _start ()
(gdb)

I don't know what exactly this test did but I shocked from such a
poor performance. We really need to find out what's the problem.
I'm in a way to open a new very big Web+DB project and I'm a little bit
scare to do this with postgres :-)

Regards,
    Oleg


On Sat, 22 May 1999, Edmund Mergl wrote:

> Date: Sat, 22 May 1999 06:39:25 +0200
> From: Edmund Mergl <E.Mergl@bawue.de>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: PostgreSQL Hackers Mailinglist <pgsql-hackers@postgreSQL.org>
> Subject: Re: [HACKERS] strange behavior of UPDATE
> 
> Tom Lane wrote:
> > 
> > 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.
> > 
> > Can you provide a script or something to reproduce this behavior?
> > 
> > There are a number of people using Postgres with large databases
> > and not reporting any such problem, so I think there has to be some
> > special triggering condition; it's not just a matter of things
> > breaking at a million rows.  Before digging into it, I'd like to
> > eliminate variables like whether I have the right test case.
> > 
> >                         regards, tom lane
> 
> 
> the original benchmark can be found at
> 
>    ftp://ftp.heise.de/pub/ix/benches/sqlb-21.tar
> 
> for a stripped-down version see the attachment.
> For loading the database and running the first
> and second part (selects and updates) just do
> the following: 
> 
>   createdb test
>   ./make_wnt 1000000 pgsql >make.out 2>&1 &
> 
> This needs about 700 MB of diskspace.
> On a PII-400 it takes about 40 minutes to
> load the database, 20 minutes to create the indeces
> and 20 minutes to run the first part of the
> benchmark (make_sqs). For running the benchmark
> in 20 minutes (without swapping) one needs 384 MB RAM.
> 
> The second part (make_nqs) contains update
> statements which can not be performed properly
> using PostgreSQL.
> 
> For testing it is sufficient to initialize the
> database and then to perform a query like
> 
>    update bench set k500k = k500k + 1 where k100 = 30
> 
> 
> Edmund
> 
> -- 
> Edmund Mergl          mailto:E.Mergl@bawue.de
> Im Haldenhau 9        http://www.bawue.de/~mergl
> 70565 Stuttgart       fon: +49 711 747503
> Germany

_____________________________________________________________
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