Re: improve performance

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

Alexaki Sofia <alexaki(at)ics(dot)forth(dot)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

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

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

On Mon, Jan 15, 2001 at 06:16:00PM -0400, Mauricio Hipp Werner wrote:
> I need help, which is the symbol used in postgreSql to carry out the outer
> join.
>
> in oracle the is used (+)
> in sybase the is used * and
> in postgreSql?

I don't really understand your question, but from src/test/regress/sql/join.sql
some examples of use are:

--
-- Outer joins
-- Note that OUTER is a noise word
--

SELECT '' AS "xxx", *
FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
FROM J1_TBL RIGHT JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
FROM J1_TBL FULL JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);

SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1);

Cheers,

Patrick

I need help, which is the symbol used in postgreSql to carry out the outer
join.

in oracle the is used (+)
in sybase the is used * and
in postgreSql?

thank you

Hipp Mauricio

Mauricio Hipp Werner writes:

> I need help, which is the symbol used in postgreSql to carry out the outer
> join.
>
> in oracle the is used (+)
> in sybase the is used * and
> in postgreSql?

No symbol, just words.

http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

Mauricio Hipp Werner wrote:
>
> I need help, which is the symbol used in postgreSql to carry out the outer
> join.
>
> in oracle the is used (+)
> in sybase the is used * and
> in postgreSql?

The PostgreSQL outer join is accomplished using SQL92 syntax. You will
not find real outer joins *except* in the current beta release, and
beware that there may be some tweaks to the grammar to help with
conformance to the standard.

In any case, check the standard or try something like "select * from t1
left outer join t2 on (i)".

On Mon, 15 Jan 2001, Mauricio Hipp Werner wrote:

MHW> in oracle the is used (+)
MHW> in sybase the is used * and
MHW> in postgreSql?

In PostgreSQL we have no outer joins at all. But it is promised they
are going to arrive soon, perhaps in 7.1?

Bye Borek

--

=====================================================================
BOREK LUPOMESKY Usti nad Labem, Czech Republic, Europe
WWW: http://www.volny.cz/borekl/ PGP keyid: B6A06AEB
==========[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===========

Thomas Lockhart wrote:
>
> Mauricio Hipp Werner wrote:
> >
> > I need help, which is the symbol used in postgreSql to carry out the outer
> > join.
> >
> > in oracle the is used (+)
> > in sybase the is used * and
> > in postgreSql?
>
> The PostgreSQL outer join is accomplished using SQL92 syntax. You will
> not find real outer joins *except* in the current beta release, and
> beware that there may be some tweaks to the grammar to help with
> conformance to the standard.
>
> In any case, check the standard or try something like "select * from t1
> left outer join t2 on (i)".

To get a feel you could use MS Access visual query builder and then view
the source.
I have not checked it lately, but it very likely produces SQL92
compliant outer joins.

-----------
Hannu

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Tue, Jan 16, 2001 at 06:44:18PM +0200, Hannu Krosing wrote:
>
> To get a feel you could use MS Access visual query builder and then view
> the source.
> I have not checked it lately, but it very likely produces SQL92
> compliant outer joins.
>

I fired up MS-Access 97SR1, just to see, and here's one result:

SELECT Institution.InstitutionName, InstituteAssignment.PersonID FROM
Institution LEFT JOIN InstituteAssignment ON Institution.InstID =
InstituteAssignment.InstID;

I'm surprised: looks pretty standard, to me.

Ross
--
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers
and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.