pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOID vs overall system performances on high load databases.
7+ messages / 5 participants
[nested] [flat]
* OID vs overall system performances on high load databases.
@ 2005-05-27 17:05 Eric Lauzon <eric.lauzon@abovesecurity.com>
2005-05-28 05:08 ` Re: OID vs overall system performances on high load Andrew McMillan <andrew@catalyst.net.nz>
0 siblings, 1 reply; 7+ messages in thread
From: Eric Lauzon @ 2005-05-27 17:05 UTC (permalink / raw)
To: pgsql-performance
What are the effect of having a table with arround 500 insert/update/delete on two to eight table in a time frame of 2 minutes 24/24h, when you have oid enabled versus the same setup when you dont have oid?
That deployment is done on a postgres with 8 to 9 databases, each having those 2 to 8 high load tables with oid enabled.
Would the oid colum slow down table scan when you have over 20 millions row?
Would the cost of maintaining the oid column inside thoses high load tables when there is no oid reference used for data seeking costy for postgres ressources!?
Eric Lauzon
[Recherche & Développement]
Above Sécurité / Above Security
Tél : (450) 430-8166
Fax : (450) 430-1858
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: OID vs overall system performances on high load
2005-05-27 17:05 OID vs overall system performances on high load databases. Eric Lauzon <eric.lauzon@abovesecurity.com>
@ 2005-05-28 05:08 ` Andrew McMillan <andrew@catalyst.net.nz>
2005-05-28 17:36 ` Re: OID vs overall system performances on high load PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew McMillan @ 2005-05-28 05:08 UTC (permalink / raw)
To: Eric Lauzon <eric.lauzon@abovesecurity.com>; +Cc: pgsql-performance
On Fri, 2005-05-27 at 13:05 -0400, Eric Lauzon wrote:
> What are the effect of having a table with arround 500
> insert/update/delete on two to eight table in a time frame of 2
> minutes 24/24h, when you have oid enabled versus the same setup when
> you dont have oid?
>
> That deployment is done on a postgres with 8 to 9 databases, each
> having those 2 to 8 high load tables with oid enabled.
>
> Would the oid colum slow down table scan when you have over 20
> millions row?
>
> Would the cost of maintaining the oid column inside thoses high load
> tables when there is no oid reference used for data seeking costy for
> postgres ressources!?
The OID column is an extra few bytes on each row. If you don't have any
use for it (and let's face it: most of us don't), then create your
tables "without OID".
The amount of impact that it makes will depend on what the general row
size is. If they are rows with a couple of integers then the size of an
OID column will be a significant portion of the size of each row, and
removing it will make the physical on-disk data size significantly
smaller. If the size of the average row is (e.g.) 2k then the OID will
only be a very small fraction of the data, and removing it will only
make a small difference.
Regards,
Andrew McMillan.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
... I want a COLOR T.V. and a VIBRATING BED!!!
-------------------------------------------------------------------------
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: OID vs overall system performances on high load
2005-05-27 17:05 OID vs overall system performances on high load databases. Eric Lauzon <eric.lauzon@abovesecurity.com>
2005-05-28 05:08 ` Re: OID vs overall system performances on high load Andrew McMillan <andrew@catalyst.net.nz>
@ 2005-05-28 17:36 ` PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 7+ messages in thread
From: PFC @ 2005-05-28 17:36 UTC (permalink / raw)
To: Andrew McMillan <andrew@catalyst.net.nz>; Eric Lauzon <eric.lauzon@abovesecurity.com>; +Cc: pgsql-performance
> The OID column is an extra few bytes on each row. If you don't have any
> use for it (and let's face it: most of us don't), then create your
> tables "without OID".
Also there are some useful hacks using the oid which don't work if it
wraps around, thus preventing it from wrapping around by not using on
every table could be useful in some cases...
^ permalink raw reply [nested|flat] 7+ messages in thread
* OID vs overall system performances on high load
@ 2005-05-29 20:17 Eric Lauzon <eric.lauzon@abovesecurity.com>
2005-05-29 21:46 ` Re: OID vs overall system performances on high load Andrew McMillan <andrew@catalyst.net.nz>
2005-05-29 21:47 ` Re: OID vs overall system performances on high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-30 04:02 ` Re: OID vs overall system performances on high load Neil Conway <neilc@samurai.com>
0 siblings, 3 replies; 7+ messages in thread
From: Eric Lauzon @ 2005-05-29 20:17 UTC (permalink / raw)
To: pgsql-performance
I am still in the dark due to my lack of knowledge on internal OID management,but
i would presume that a table with OID enable and that has high load would require
some more work from pgsql internal to maintain the OID index for the database.
So OID can be beneficial on static tables, or tables that you want to be able to manipulate
with pgadmin X , but can a table without OID increase performances on insert,delete,update,COPY?
I am not really worried about disk space that an OID collumn can take, but i was wandering if an
insert in a table of 20 millions and more that has oid would slow the insert process. Since OID seem
to act as a global index mabey maintaning that index can become costy over high table load by postgresql
backend.
-Eric Lauzon
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: OID vs overall system performances on high load
2005-05-29 20:17 OID vs overall system performances on high load Eric Lauzon <eric.lauzon@abovesecurity.com>
@ 2005-05-29 21:46 ` Andrew McMillan <andrew@catalyst.net.nz>
2 siblings, 0 replies; 7+ messages in thread
From: Andrew McMillan @ 2005-05-29 21:46 UTC (permalink / raw)
To: Eric Lauzon <eric.lauzon@abovesecurity.com>; +Cc: pgsql-performance
On Sun, 2005-05-29 at 16:17 -0400, Eric Lauzon wrote:
> I am still in the dark due to my lack of knowledge on internal OID management,but
> i would presume that a table with OID enable and that has high load would require
> some more work from pgsql internal to maintain the OID index for the database.
>
> So OID can be beneficial on static tables, or tables that you want to be able to manipulate
> with pgadmin X , but can a table without OID increase performances on insert,delete,update,COPY?
>
> I am not really worried about disk space that an OID collumn can take, but i was wandering if an
> insert in a table of 20 millions and more that has oid would slow the insert process. Since OID seem
> to act as a global index mabey maintaning that index can become costy over high table load by postgresql
> backend.
There is no OID index, unless you create one.
The disk space that an OID column can take has an effect on performance:
reducing the amount of physical disk reads will mean that more of your
real data is cached, and so forth. How much effect it will have will
depend on the relative size of the OID column and the other columns in
your data.
Regards,
Andrew McMillan.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
http://survey.net.nz/ - any more questions?
-------------------------------------------------------------------------
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: OID vs overall system performances on high load
2005-05-29 20:17 OID vs overall system performances on high load Eric Lauzon <eric.lauzon@abovesecurity.com>
@ 2005-05-29 21:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-05-29 21:47 UTC (permalink / raw)
To: Eric Lauzon <eric.lauzon@abovesecurity.com>; +Cc: pgsql-performance
"Eric Lauzon" <eric.lauzon@abovesecurity.com> writes:
> I am still in the dark due to my lack of knowledge on internal OID management,but
> i would presume that a table with OID enable and that has high load would require
> some more work from pgsql internal to maintain the OID index for the database.
There is no "OID index"; at least not unless you choose to create one
for a given table. The only thing particularly special about OID is
that there is an internal database-wide sequence generator for assigning
new values. Otherwise it works a whole lot like a serial column.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: OID vs overall system performances on high load
2005-05-29 20:17 OID vs overall system performances on high load Eric Lauzon <eric.lauzon@abovesecurity.com>
@ 2005-05-30 04:02 ` Neil Conway <neilc@samurai.com>
2 siblings, 0 replies; 7+ messages in thread
From: Neil Conway @ 2005-05-30 04:02 UTC (permalink / raw)
To: Eric Lauzon <eric.lauzon@abovesecurity.com>; +Cc: pgsql-performance
On Sun, 2005-05-29 at 16:17 -0400, Eric Lauzon wrote:
> So OID can be beneficial on static tables
OIDs aren't beneficial on "static tables"; unless you have unusual
requirements[1], there is no benefit to having OIDs on user-created
tables (see the default_with_oids GUC var, which will default to "false"
in 8.1)
-Neil
[1] Such as a column that references a column in the system catalogs.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-05-30 04:02 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-27 17:05 OID vs overall system performances on high load databases. Eric Lauzon <eric.lauzon@abovesecurity.com>
2005-05-28 05:08 ` Re: OID vs overall system performances on high load Andrew McMillan <andrew@catalyst.net.nz>
2005-05-28 17:36 ` Re: OID vs overall system performances on high load PFC <lists@boutiquenumerique.com>
2005-05-29 20:17 OID vs overall system performances on high load Eric Lauzon <eric.lauzon@abovesecurity.com>
2005-05-29 21:46 ` Re: OID vs overall system performances on high load Andrew McMillan <andrew@catalyst.net.nz>
2005-05-29 21:47 ` Re: OID vs overall system performances on high load Tom Lane <tgl@sss.pgh.pa.us>
2005-05-30 04:02 ` Re: OID vs overall system performances on high load Neil Conway <neilc@samurai.com>
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