Обсуждение: Problem with asynchronous connect in 8.0.1
I'm having a problem with asynchronous connections, and I can't seem to find
a good example of this anywhere. To make things simple, I've reduced my
program to the bare minimums, not even using select(). Here's my program
flow:
1. Call PQconnectStart. I'm told CONNECTION_STARTED.
2. Sleeping 300ms in between calls, call PQconnectPoll.
The FIRST time I call this I'm told CONNECTION_STARTED and
PGRES_POLLING_WRITING. Every additional time I call this I'm told
CONNECTION_SSL_STARTUP and PGRES_POLLING_READING. It never leaves this state,
and goes on in an infinite loop until I kill the attempt.
In my Postgres logs I see: LOG: incomplete startup packet
If I PQtrace the connection I see:
To backend> Msg
To backend> <binary junk>
To backend> Msg complete, length 8
What could be going on? Incidentally, the only thing I found that comes close
is a pg_ping tool posted by another user, and that does the same thing. Yet
psql works fine using the same connection parameters, and if I rewrite my
program to use blocking functions, it works too.
This is with Postgres 8.0.1 using TCP/IP connections over a local LAN.
Thanks,
Chad
__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo
Chad Robinson <taskswap@yahoo.com> writes:
> I'm having a problem with asynchronous connections, and I can't seem to find
> a good example of this anywhere. To make things simple, I've reduced my
> program to the bare minimums, not even using select().
I believe that the async I/O code expects you to obey the API protocol,
in particular to wait for read-ready or write-ready where it says to.
Offhand I would expect an error return in case of failure to wait
long enough, though.
> In my Postgres logs I see:
> LOG: incomplete startup packet
Hmm, is it possible pqFlush never succeeded in writing out the whole
startup packet? It'd be useful to look at the state of the connection
object with a debugger, esp. to see if anything is still pending in the
outbound I/O buffer.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chad Robinson <taskswap@yahoo.com> writes:
> > I'm having a problem with asynchronous connections, and I can't seem to
> find
> > a good example of this anywhere. To make things simple, I've reduced my
> > program to the bare minimums, not even using select().
>
> I believe that the async I/O code expects you to obey the API protocol,
> in particular to wait for read-ready or write-ready where it says to.
> Offhand I would expect an error return in case of failure to wait
> long enough, though.
>
> > In my Postgres logs I see:
> > LOG: incomplete startup packet
>
> Hmm, is it possible pqFlush never succeeded in writing out the whole
> startup packet? It'd be useful to look at the state of the connection
> object with a debugger, esp. to see if anything is still pending in the
> outbound I/O buffer.
I found the problem here. A logic error in the event-handling loop was
causing me to call PQconsumeInput at some point during the connect sequence.
It might be a good idea in the future to have all from-server communications
handled by a single polling function, but in the end it was still my fault.
I'm not sure I can use Postgres though. I'm having a terrible time getting
large numbers of clients connected. I need to have upwards of 8000 clients
attached at any one time. I can do this on MySQL without problems, but that
DB has limitations I was hoping to get rid of with PostgreSQL (especially
asynchronous query support - for a neat client-side performance trick, check
out combining libpq with libepoll, which is what I'm doing).
When I connect the clients directly to the server, I have no real problems. A
few clients will occasionally get EAGAIN errors during the SSL startup, but I
wrote them to retry after a few seconds and the second time around they get
in - probably a backlog issue. But memory usage on the server is ABYSMAL -
1200 clients use almost a GB of RAM, no matter how I tune things. I'm not
trying to be cheap, but that seems a little excessive. I shouldn't have to
deploy 8GB of RAM in this server just to connect my clients, especially since
they'll all be doing pretty basic queries (a few tables, maybe even no
joins). Heck, the data itself is probably in the 1-2GB range!
I tried using pgpool, and that seems promising, but I can't seem to get more
than a few hundred connections per front-end there before my clients start
losing connections (seems like around 190 and up). It doesn't seem to be a
tuning parameter, but I'm still exploring that and I'll take that up on the
pgpool list.
Thanks for your reply.
-Chad
__________________________________
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com
Chad Robinson <taskswap@yahoo.com> writes:
> I'm not sure I can use Postgres though. I'm having a terrible time getting
> large numbers of clients connected. I need to have upwards of 8000 clients
> attached at any one time.
Unless you actually need 8000 simultaneous transactions in process,
connection pooling is the way to go. I haven't played much with pgpool
though --- it may have a few issues to shake out ...
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chad Robinson <taskswap@yahoo.com> writes:
> > I'm not sure I can use Postgres though. I'm having a terrible time
> > getting large numbers of clients connected. I need to have upwards
> > of 8000 clients attached at any one time.
>
> Unless you actually need 8000 simultaneous transactions in process,
> connection pooling is the way to go. I haven't played much with pgpool
> though --- it may have a few issues to shake out ...
It seems so. I have to have this many connections, because for security and
privacy reasons the processes involved are isolated. There are about
1000-1200 per box, 4-8 boxes, depending on how I load-balance things. Unless
you know some way to REDUCE memory usage per Postgres client connection,
we'll see how things go with pgpool.
Thanks for your help.
-Chad
__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo
Chad Robinson <taskswap@yahoo.com> writes: > It seems so. I have to have this many connections, because for security and > privacy reasons the processes involved are isolated. There are about > 1000-1200 per box, 4-8 boxes, depending on how I load-balance things. Unless > you know some way to REDUCE memory usage per Postgres client connection, > we'll see how things go with pgpool. It sounds to me like you might even want something more constricted than pgpool. Perhaps you want to write some sort of daemon that keeps a single connection to the database and performs these short simple queries in response to simple requests it receives from the clients. This could actually improve security since it would limit what types of queries can be performed and what data can be retrieved. You could use something like ldap or snmp or even just a simple xdr rpc call instead of writing database queries into your application. -- greg