pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad update performance?
4+ messages / 4 participants
[nested] [flat]

* Bad update performance?
@ 1999-07-20 23:06 Gunnar Ingvi Thorisson <gunni@if.is>
  1999-07-21 02:28 ` Re: [SQL] Bad update performance? tjk@tksoft.com <tjk@tksoft.com>
  1999-07-21 09:52 ` Re: [SQL] Bad update performance? Herouth Maoz <herouth@oumail.openu.ac.il>
  1999-07-21 13:38 ` Re: [SQL] Bad update performance? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 3 replies; 4+ messages in thread

From: Gunnar Ingvi Thorisson @ 1999-07-20 23:06 UTC (permalink / raw)
  To: pgsql-general@postgreSQL.org; pgsql-sql

Hi there,

I´ve a table with about 142000 rows like shown below and I want to set field
"divis" to "unknown" by executing following update command:
update ipacct set divis = 'unknown';

However this seems to take hours, I´ve a PII 350MHz with 192Mb memory and
Quantum UIDE disk, is there a way for me to speed up the update process
except  for switching to a SCSI hard drive? Does indexing the field "divis"
speed up the update performance?

The load on the machine is about 1,5 to 2 all the time and it´s hacking on
the hard drive like hell.
I´ve the same problem when I do "UPDATE ipacct SET traf = (src+dst)", it
takes hours. Is this normal? Is there another way to do this?

However reading from the database doing select is very fast.

Many thanks in advance for any hint,
best regards,
Gunnar Ingvi Thorisson

CCIE 4826
Iceland
gunni@if.is



venus:~$ psql ipacct
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on i686-pc-linux-gnulibc1, compiled by gcc 2.7.2.3]

[cut]

ipacct=> select * from ipacct where id = 1;
id| datetime|pcol|saddr            |sport|daddr       |dport|pcnt| size|if
|login  |mac         |src|dst|traf|depart|divis
--+---------+----+-----------------+-----+------------+-----+----+-----+----
+-------+------------+---+---+----+------+-----
 1|928368167|   6|209.85.127.151/32|   80|10.10.2.3/32| 2448|
57|74856|eth0|unknown|00805FC1525C|100|  3| 103|      |
(1 row)

ipacct=> update ipacct set divis = 'unknown';

The table:
   ID               INT4  UNIQUE,
   DATETIME         TEXT,
   PCOL             INTEGER,
   SADDR            CIDR,
   SPORT            INTEGER,
   DADDR            CIDR,
   DPORT            INTEGER,
   PCNT             INT4
   SIZE             INT4;
   IF               TEXT;
   LOGIN            TEXT;
   MAC              TEXT;
   SRC              INTEGER;
   DST              INTEGER;
   TRAF             INTEGER;
   DEPART           TEXT;
   DIVIS            TEXT;






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Bad update performance?
  1999-07-20 23:06 Bad update performance? Gunnar Ingvi Thorisson <gunni@if.is>
@ 1999-07-21 02:28 ` tjk@tksoft.com <tjk@tksoft.com>
  2 siblings, 0 replies; 4+ messages in thread

From: tjk@tksoft.com @ 1999-07-21 02:28 UTC (permalink / raw)
  To: gunni@if.is; +Cc: pgsql-sql

Gunnar,
You would be better off separating the field to its
own table. 

The table is stored in one chunk of a file, and when
postgres modifies it, it takes a long time because 
it has to do a heck of a lot of seeking and modifying
in the file. This ends up being very time consuming.

When you use a separate table for the field you are 
isolating the field to its own file, which means
that when it's modified only a much smaller file
needs to be worked on.

It might be a nice idea to split large tables into 
smaller pieces within postgres, in order to speed
up updates. (This is a really a question to 
somebody more knowleadgeable about the backend
as to whether this would make sense.)

Because you are indexing the field, searches are fast,
as postgres knows where to look inside the file (using seek)
using the index as a guide.



Troy

Troy Korjuslommi                Tksoft OY, Inc.
tjk@tksoft.com                  Software Development
                                Open Source Solutions
                                Hosting Services







> 
> Hi there,
> 
> I´ve a table with about 142000 rows like shown below and I want to set field
> "divis" to "unknown" by executing following update command:
> update ipacct set divis = 'unknown';
> 
> However this seems to take hours, I´ve a PII 350MHz with 192Mb memory and
> Quantum UIDE disk, is there a way for me to speed up the update process
> except  for switching to a SCSI hard drive? Does indexing the field "divis"
> speed up the update performance?
> 
> The load on the machine is about 1,5 to 2 all the time and it´s hacking on
> the hard drive like hell.
> I´ve the same problem when I do "UPDATE ipacct SET traf = (src+dst)", it
> takes hours. Is this normal? Is there another way to do this?
> 
> However reading from the database doing select is very fast.
> 
> Many thanks in advance for any hint,
> best regards,
> Gunnar Ingvi Thorisson
> 
> CCIE 4826
> Iceland
> gunni@if.is
> 
> 
> 
> venus:~$ psql ipacct
> Welcome to the POSTGRESQL interactive sql monitor:
>   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> [PostgreSQL 6.5.0 on i686-pc-linux-gnulibc1, compiled by gcc 2.7.2.3]
> 
> [cut]
> 
> ipacct=> select * from ipacct where id = 1;
> id| datetime|pcol|saddr            |sport|daddr       |dport|pcnt| size|if
> |login  |mac         |src|dst|traf|depart|divis
> --+---------+----+-----------------+-----+------------+-----+----+-----+----
> +-------+------------+---+---+----+------+-----
>  1|928368167|   6|209.85.127.151/32|   80|10.10.2.3/32| 2448|
> 57|74856|eth0|unknown|00805FC1525C|100|  3| 103|      |
> (1 row)
> 
> ipacct=> update ipacct set divis = 'unknown';
> 
> The table:
>    ID               INT4  UNIQUE,
>    DATETIME         TEXT,
>    PCOL             INTEGER,
>    SADDR            CIDR,
>    SPORT            INTEGER,
>    DADDR            CIDR,
>    DPORT            INTEGER,
>    PCNT             INT4
>    SIZE             INT4;
>    IF               TEXT;
>    LOGIN            TEXT;
>    MAC              TEXT;
>    SRC              INTEGER;
>    DST              INTEGER;
>    TRAF             INTEGER;
>    DEPART           TEXT;
>    DIVIS            TEXT;
> 
> 
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Bad update performance?
  1999-07-20 23:06 Bad update performance? Gunnar Ingvi Thorisson <gunni@if.is>
@ 1999-07-21 09:52 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  2 siblings, 0 replies; 4+ messages in thread

From: Herouth Maoz @ 1999-07-21 09:52 UTC (permalink / raw)
  To: gunni@if.is; +Cc: pgsql-sql

At 05:28 +0300 on 21/07/1999, tjk@tksoft.com wrote:


> The table is stored in one chunk of a file, and when
> postgres modifies it, it takes a long time because
> it has to do a heck of a lot of seeking and modifying
> in the file. This ends up being very time consuming.
>
> When you use a separate table for the field you are
> isolating the field to its own file, which means
> that when it's modified only a much smaller file
> needs to be worked on.

This will give a penalty in the selects, however. When you split a table
into two, you have to have a relation between the field you separated and
the original table, and from then on, you can see them together only using
a join. A join takes more time than a search on one table.

I think that update is slow, because it has to read each record, and write
it in its new form in the end of the file. It does sequential scanning plus
jump to the end of the file and writing. That's essentially what you said,
too. In addition, it has to re-index each new entry.

But in my opinion, the chance to make this faster is to select the new
values into a new table,  drop the old table, rename the new table, and add
indices after that. If your table is called A:

CREATE TABLE B ...; -- Same schema and constraints as A
INSERT INTO B SELECT f1, f2, 'unknown', f4... FROM A; -- Note new value
DROP TABLE A;
ALTER TABLE B RENAME TO A;
CREATE INDEX .....; -- Recreate the indices

When defining the table, don't use SERIAL and PRIMARY KEY, as these create
sequences and indices. You would do better to control these yourself, so as
not to break the sequences used, and not to create an index before all
values are inserted.

One last comment: indices always accelerate retrieval, at the expense of
slowing inserts and updates.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Bad update performance?
  1999-07-20 23:06 Bad update performance? Gunnar Ingvi Thorisson <gunni@if.is>
@ 1999-07-21 13:38 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 1999-07-21 13:38 UTC (permalink / raw)
  To: gunni@if.is; +Cc: pgsql-general@postgreSQL.org; pgsql-sql

"Gunnar Ingvi Thorisson" <gunni@if.is> writes:
> I´ve a table with about 142000 rows like shown below and I want to
> set field "divis" to "unknown" by executing following update command:
> update ipacct set divis = 'unknown';
> However this seems to take hours,

Well, updating 142000 rows is going to take a little while...

Are you starting the postmaster with -o -F ?  That makes for a pretty
considerable speedup in most cases (at the cost of trouble if you
have a system crash during an update).

> Does indexing the field "divis" speed up the update performance?

No, it would not help a query like that --- though if you added a clause
like "where divis = 'oldvalue'" then an index would help to find the
rows that need updated.  Actually, every index you add *slows down*
updates, since all the indexes must be updated along with the table.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-07-21 13:38 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-20 23:06 Bad update performance? Gunnar Ingvi Thorisson <gunni@if.is>
1999-07-21 02:28 ` tjk@tksoft.com <tjk@tksoft.com>
1999-07-21 09:52 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-07-21 13:38 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox