pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedimprove performance
5+ messages / 5 participants
[nested] [flat]
* improve performance
@ 2001-01-15 13:07 Alexaki Sofia <alexaki@ics.forth.gr>
0 siblings, 2 replies; 5+ messages in thread
From: Alexaki Sofia @ 2001-01-15 13:07 UTC (permalink / raw)
To: pgsql-sql@postgresql.org; +Cc: pgsql-interfaces
Hello,
A)
I am going to load a huge amount of data in the DBMS using JDBC
and I want to reduce as much as possible the required loading time.
Initially I loaded all data in one transaction. Subsequently, I increased
the number of buffers and disabled fsync() (-o -F) and I loaded the
data again but the performance was almost unchanged. Does it make sense??
How can I improve performance? To note that no indexes are created on the
tables and that I load both "insert into" and "create table" statements.
------------------------------
B)
I want to reduce the space (8 Kb) allocated by
DBMS when more space is required to load the data in a table. I reduced
the parameter BLCKSZ but the space allocated remains the same, that is 8
KB.
How can the allocated space be reduced? If the space was reduced
would loading time and query time increase?
Thank you in advance for your help
Sofia Alexaki
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] improve performance
@ 2001-01-15 15:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexaki Sofia <alexaki@ics.forth.gr>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2001-01-15 15:27 UTC (permalink / raw)
To: Alexaki Sofia <alexaki@ics.forth.gr>; +Cc: pgsql-sql@postgresql.org; pgsql-interfaces
Alexaki Sofia <alexaki@ics.forth.gr> writes:
> Initially I loaded all data in one transaction. Subsequently, I increased
> the number of buffers and disabled fsync() (-o -F) and I loaded the
> data again but the performance was almost unchanged. Does it make sense??
> How can I improve performance? To note that no indexes are created on the
> tables and that I load both "insert into" and "create table" statements.
If you can load the data with a COPY command, instead of individual
INSERTs, it'll go a lot faster. See also
http://www.postgresql.org/devel-corner/docs/postgres/populate.htm
> I want to reduce the space (8 Kb) allocated by
> DBMS when more space is required to load the data in a table. I reduced
> the parameter BLCKSZ but the space allocated remains the same, that is 8
> KB.
> How can the allocated space be reduced? If the space was reduced
> would loading time and query time increase?
I've heard lots of people want to increase BLCKSZ, but you're the first
one who ever wanted to reduce it. You sure you want to do this? It's
going to make the maximum row length uncomfortably short.
Anyway, you probably forgot to do a full rebuild after changing config.h.
"make clean" before "make all" is the only way to be sure the
configuration change propagates to all the code. Don't forget you will
have to do an initdb, also, so back up your data with the old code first.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: [SQL] improve performance
@ 2001-01-15 15:42 Hannu Krosing <hannu@tm.ee>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Hannu Krosing @ 2001-01-15 15:42 UTC (permalink / raw)
To: Alexaki Sofia <alexaki@ics.forth.gr>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@postgresql.org; pgsql-interfaces
Tom Lane wrote:
>
>
> I've heard lots of people want to increase BLCKSZ, but you're the first
> one who ever wanted to reduce it. You sure you want to do this? It's
> going to make the maximum row length uncomfortably short.
And it may even not work, as some system tables (that are also affected
by this)
may need the full 8k. AFAIK it has never been tested with BLCKSZ < 8k
----------
Hannu
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: improve performance
@ 2001-01-15 16:37 Tom Samplonius <tom@sdf.com>
parent: Alexaki Sofia <alexaki@ics.forth.gr>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Samplonius @ 2001-01-15 16:37 UTC (permalink / raw)
To: Alexaki Sofia <alexaki@ics.forth.gr>; +Cc: pgsql-sql@postgresql.org; pgsql-interfaces
On Mon, 15 Jan 2001, Alexaki Sofia wrote:
> Hello,
>
> A)
> I am going to load a huge amount of data in the DBMS using JDBC
> and I want to reduce as much as possible the required loading time.
>
> Initially I loaded all data in one transaction. Subsequently, I increased
> the number of buffers and disabled fsync() (-o -F) and I loaded the
> data again but the performance was almost unchanged. Does it make sense??
Yes. Syncing is only done at the end of a transaction. The difference
between one fsync() and none is not measurable.
Large amounts of buffers helps in read performance, not write
performance.
> How can I improve performance? To note that no indexes are created on the
> tables and that I load both "insert into" and "create table" statements.
> ------------------------------
> B)
> I want to reduce the space (8 Kb) allocated by
> DBMS when more space is required to load the data in a table. I reduced
> the parameter BLCKSZ but the space allocated remains the same, that is 8
> KB.
> How can the allocated space be reduced? If the space was reduced
> would loading time and query time increase?
No. Reducing the blocksize does not sound like a good idea. Why would
you want to reduce the amount of allocated space? I actually wish that
Postgres would preallocate a lot of blocks in advance (ie. 100 x 8KB).
That would probably improve performance, as it would not have to keep
re-extending the file.
The best way to improve write performance, is to get faster disks
connecting to a fast interface. Also, get lots of disks (4 to 6). Pair
them into RAID1 sets, then strip over them. Use a RAID controler with a
battery backed cache in write-back (write cache) mode.
> Thank you in advance for your help
> Sofia Alexaki
Tom
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: [SQL] improve performance
@ 2001-01-16 19:33 Jan Wieck <janwieck@Yahoo.com>
parent: Hannu Krosing <hannu@tm.ee>
0 siblings, 0 replies; 5+ messages in thread
From: Jan Wieck @ 2001-01-16 19:33 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: Alexaki Sofia <alexaki@ics.forth.gr>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@postgresql.org; pgsql-interfaces
Hannu Krosing wrote:
> Tom Lane wrote:
> >
> >
> > I've heard lots of people want to increase BLCKSZ, but you're the first
> > one who ever wanted to reduce it. You sure you want to do this? It's
> > going to make the maximum row length uncomfortably short.
>
> And it may even not work, as some system tables (that are also affected
> by this)
> may need the full 8k. AFAIK it has never been tested with BLCKSZ < 8k
Except for two different sorted (but correct) results while
selecting inherited tables in "misc", regression tests passed
with 2K.
Why shouldn't it work? All the catalogs that require really
big data have toast tables now.
Anyway, the 8K default BLCKSZ already restricts index tuples
to 2700 bytes. So I wouldn't recommend it at all.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-01-16 19:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-15 13:07 improve performance Alexaki Sofia <alexaki@ics.forth.gr>
2001-01-15 15:27 ` Re: [SQL] improve performance Tom Lane <tgl@sss.pgh.pa.us>
2001-01-15 15:42 ` Re: Re: [SQL] improve performance Hannu Krosing <hannu@tm.ee>
2001-01-16 19:33 ` Re: Re: [SQL] improve performance Jan Wieck <janwieck@Yahoo.com>
2001-01-15 16:37 ` Tom Samplonius <tom@sdf.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