Обсуждение: JDBC speed question.

Anyone have any ideas how to speed up performance using the JDBC driver?  A
query that takes me 20 seconds to execute from command line takes 1 full
minute with the jdbc driver.  Thanks.

John

"John Oakes" <john@networkproductions.net> writes:

> Anyone have any ideas how to speed up performance using the JDBC driver?  A
> query that takes me 20 seconds to execute from command line takes 1 full
> minute with the jdbc driver.  Thanks.

Since you don't guve any details of your query or network setup, it's
hard to help much.  What could account for the difference, assuming
that the query is exactly the same in both cases, is that the JDBC
driver connects through TCP/IP (even when on the same host) whereas
'psql' will by default connect through a Unix socket.  On most
systems, Unix sockets are considerably faster than even local TCP
sockets.  If the JDBC client is running on a different machine, the
difference becomes even more pronounced.

This is assuming that your query is returning a great deal of data, so
the network pipe is a factor in the execution time.  If it isn't, then
it's unclear how to help you without more details on your table
layouts and the query itself.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

"Clayton Vernon" <cvernon@enron.com> writes:

> Related question: how do you run postgreSQL to simultaneously support a Unix
> socket and TCP/IP?

Just add '-i' to the postmaster startup options.  The Unix socket will
still be available.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

"Clayton Vernon" <cvernon@enron.com> writes:

> Thanks, but can I specify each port? I've only seen the one documented
> command option "-p" which I assume was for the TCP/IP port.

Well, Unix sockets don't have a "port".  The port number you specify
is appended to the name of the socket in the filesystem (eg
'/tmp/.s.PGSQL.5432') so postmasters running on different ports won't
try to create the same socket.

What exactly are you trying to do?

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

Thanks, but can I specify each port? I've only seen the one documented
command option "-p" which I assume was for the TCP/IP port.

Again, thanks for the help,

Clayton

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "Clayton Vernon" <cvernon@enron.com>
Cc: "John Oakes" <john@networkproductions.net>;
<pgsql-general@postgresql.org>
Sent: Thursday, April 26, 2001 6:19 PM
Subject: Re: [GENERAL] JDBC speed question.


> "Clayton Vernon" <cvernon@enron.com> writes:
>
> > Related question: how do you run postgreSQL to simultaneously support a
Unix
> > socket and TCP/IP?
>
> Just add '-i' to the postmaster startup options.  The Unix socket will
> still be available.
>
> -Doug
> --
> The rain man gave me two cures; he said jump right in,
> The first was Texas medicine--the second was just railroad gin,
> And like a fool I mixed them, and it strangled up my mind,
> Now people just get uglier, and I got no sense of time...          --Dylan
>

Related question: how do you run postgreSQL to simultaneously support a Unix
socket and TCP/IP?

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "John Oakes" <john@networkproductions.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, April 26, 2001 5:35 PM
Subject: Re: [GENERAL] JDBC speed question.


> "John Oakes" <john@networkproductions.net> writes:
>
> > Anyone have any ideas how to speed up performance using the JDBC driver?
A
> > query that takes me 20 seconds to execute from command line takes 1 full
> > minute with the jdbc driver.  Thanks.
>
> Since you don't guve any details of your query or network setup, it's
> hard to help much.  What could account for the difference, assuming
> that the query is exactly the same in both cases, is that the JDBC
> driver connects through TCP/IP (even when on the same host) whereas
> 'psql' will by default connect through a Unix socket.  On most
> systems, Unix sockets are considerably faster than even local TCP
> sockets.  If the JDBC client is running on a different machine, the
> difference becomes even more pronounced.
>
> This is assuming that your query is returning a great deal of data, so
> the network pipe is a factor in the execution time.  If it isn't, then
> it's unclear how to help you without more details on your table
> layouts and the query itself.
>
> -Doug
> --
> The rain man gave me two cures; he said jump right in,
> The first was Texas medicine--the second was just railroad gin,
> And like a fool I mixed them, and it strangled up my mind,
> Now people just get uglier, and I got no sense of time...          --Dylan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Hi Clayton,

From what I remember, using -p will change BOTH the TCP/IP port and the
Unix domain socket.

i.e.   pg_ctl start -o '-i -p 7777'

Should make PostgreSQL listen on TCP port 7777, and on most unix systems
will create a socket file in /tmp/.s.PGSQL.7777 (and its corresponding
lock file).

Regards and best wishes,

Justin Clift

Clayton Vernon wrote:
>
> Thanks, but can I specify each port? I've only seen the one documented
> command option "-p" which I assume was for the TCP/IP port.
>
> Again, thanks for the help,
>
> Clayton
>
> ----- Original Message -----
> From: "Doug McNaught" <doug@wireboard.com>
> To: "Clayton Vernon" <cvernon@enron.com>
> Cc: "John Oakes" <john@networkproductions.net>;
> <pgsql-general@postgresql.org>
> Sent: Thursday, April 26, 2001 6:19 PM
> Subject: Re: [GENERAL] JDBC speed question.
>
> > "Clayton Vernon" <cvernon@enron.com> writes:
> >
> > > Related question: how do you run postgreSQL to simultaneously support a
> Unix
> > > socket and TCP/IP?
> >
> > Just add '-i' to the postmaster startup options.  The Unix socket will
> > still be available.
> >
> > -Doug
> > --
> > The rain man gave me two cures; he said jump right in,
> > The first was Texas medicine--the second was just railroad gin,
> > And like a fool I mixed them, and it strangled up my mind,
> > Now people just get uglier, and I got no sense of time...          --Dylan
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

What's the standard methodology in the Postgres world for
creating functions (SQL Server calls them 'stored procedures')
that return more than one column of data per row?

I have tried using the SETOF operand in the CREATE FUNCTION
statement, and that allows me to return more than one row,
but haven't had any luck with specifying more than one return
type and hence more than one column.

Thanks in advance for any assistance,
    eliel

"Eliel Mamousette" <eliel@panix.com> writes:
> I have tried using the SETOF operand in the CREATE FUNCTION
> statement, and that allows me to return more than one row,
> but haven't had any luck with specifying more than one return
> type and hence more than one column.

You don't specify more than one return type --- you specify one return
type that is a composite type.  Composite types are currently tied to
tables; creating a table also creates a type that represents one of its
rows.  Thus

    create table foo (a int, b int);

    create function foobar (...) returns foo as ...

Note that there are some annoying syntactic limitations on what you can
actually *do* with a function returning tuples.  We have plans to
improve that situation in 7.2 or beyond, but for now, this facility
isn't nearly as useful as one might think.

            regards, tom lane

"Tom Lane" <sss.pgh.pa.us> writes:
> "Eliel Mamousette" <eliel@panix.com> writes:
[question re: returning rows deleted to conserve bits]
>
> You don't specify more than one return type --- you specify one return
> type that is a composite type.  Composite types are currently tied to
> tables; creating a table also creates a type that represents one of its
> rows.  Thus
>
>     create table foo (a int, b int, c int);
>     create function foobar (...) returns foo as ...

Does rule also apply to views?

For example, what is the best practice when one doesn't want to return
a whole row?  Given the restriction as stated, if I only wanted to
return column a and c from the table above, would I create a view
fooview and then say that function foobarview returns fooview?

If I write a paragraph about this process, to whom should I mail it for
inclusion in the documentation?  I imagine it will be a FAQ for we who
are striving to escape from the legacy of Sybase and Microsoft's SQL
Server....

> Note that there are some annoying syntactic limitations on what you can
> actually *do* with a function returning tuples.  We have plans to
> improve that situation in 7.2 or beyond, but for now, this facility
> isn't nearly as useful as one might think.

Thanks Tom, but can you be a bit more specific about what one can't do
with returned tuples?  It might save some folks (and me) some time.

thanks again, this process has been extremely helpful,
    eliel

>             regards, tom lane
>

You just use a view. Period.

CREATE VIEW foobarview AS SELECT a, c FROM foo;

What else do you need?

CREATE VIEW foobarview AS SELECT a*2 AS double, b*c AS multiple,
max(a,c) AS maximum FROM foo;

I believe views does it all.

/Roger

Eliel Mamousette wrote:
>
> "Tom Lane" <sss.pgh.pa.us> writes:
> > "Eliel Mamousette" <eliel@panix.com> writes:
> [question re: returning rows deleted to conserve bits]
> >
> > You don't specify more than one return type --- you specify one return
> > type that is a composite type.  Composite types are currently tied to
> > tables; creating a table also creates a type that represents one of its
> > rows.  Thus
> >
> >       create table foo (a int, b int, c int);
> >       create function foobar (...) returns foo as ...
>
> Does rule also apply to views?
>
> For example, what is the best practice when one doesn't want to return
> a whole row?  Given the restriction as stated, if I only wanted to
> return column a and c from the table above, would I create a view
> fooview and then say that function foobarview returns fooview?
>
> If I write a paragraph about this process, to whom should I mail it for
> inclusion in the documentation?  I imagine it will be a FAQ for we who
> are striving to escape from the legacy of Sybase and Microsoft's SQL
> Server....
>
> > Note that there are some annoying syntactic limitations on what you can
> > actually *do* with a function returning tuples.  We have plans to
> > improve that situation in 7.2 or beyond, but for now, this facility
> > isn't nearly as useful as one might think.
>
> Thanks Tom, but can you be a bit more specific about what one can't do
> with returned tuples?  It might save some folks (and me) some time.
>
> thanks again, this process has been extremely helpful,
>         eliel
>
> >                       regards, tom lane
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

We have a table with a lot of user sessions (basically end -time and
length of connection).  We would like to query this table to count the max
number of simultaneous sessions, but we are stumped on how to do that.

The only thing I have been able to think of is to iterate over the entire
table and count the number of connections at the beginning and end of each
user session, keeping a variable that records the time and max number of
sessions at each iteration.  We can do this in either in Perl or PL/SQL, but
it seems like it would be *horribly* slow, especially considering we have
about 250,000 of these records come in a day.

I also wonder if there might be some intermediate data structure that we
could create to make this easy, but I thought I would rely on other people's
experience before trying to do this myself...:)

Thanks

Is there a way to flush them more often?  Otherwise, they can fill up the
disk space and crash the postmaster.  I am doing bulk copies, but the logs
hang around for up to 20 minutes after the copy is finished.

Thanks

webb sprague <wsprague@o1.com> writes:
> Is there a way to flush them more often?

Reduce the time interval between WAL checkpoints.  (But don't overdo it.)
Perhaps every three or so minutes, rather than five?

> Otherwise, they can fill up the
> disk space and crash the postmaster.  I am doing bulk copies, but the logs
> hang around for up to 20 minutes after the copy is finished.

Another possibility is to issue a manual CHECKPOINT command as soon as
you've finished a bulk copy.

            regards, tom lane