Re: Asynchronous queries with bound data. - Mailing list pgsql-general
| From | Вячеслав Блинников |
|---|---|
| Subject | Re: Asynchronous queries with bound data. |
| Date | |
| Msg-id | AANLkTi=L6F1vRw1BMdvBTnwLjU-kaP1L7vfdf2RyK+rA@mail.gmail.com Whole thread Raw |
| In response to | Re: Asynchronous queries with bound data. (Peter Geoghegan <peter.geoghegan86@gmail.com>) |
| Responses |
Re: Asynchronous queries with bound data.
(Peter Geoghegan <peter.geoghegan86@gmail.com>)
|
| List | pgsql-general |
1: Didn't figured out what it does mean - can you explain it better?
2: Operation system will refuse me to create thousand threads and, anyway, database will return responds averagely just when all of them will be accomplished.
3: I never close a connection once it was created, so any pool will not help me (I google says right about "connection pool").
I found the expression: "you can't issue another PQsendQuery until you've gotten that NULL." - so if it is true, then it's impossible (because it's only one function available to send an asynchronous request) to send multiple requests before any result will be gotten.
Problem can be observed from this abstract point of view:
Transferring data from application server (which connects to the database) takes 200 ms (and the same amount to transfer backward); adding data to the database and then selecting data (all in one request) from it takes 250 ms, so each database operation (of such type) will take 200 + 250 + 200 = 650 ms. Two such operations will take 650 + 650 = 1300 ms, but if there existed the way to send two queries at once and then get two results at once (of course tracking the correspondence between requests/responds) we could decrease such two "dialogs" from 1300 ms to 200 + 250 + 250 + 200 = 900 ms. So, we win 400 ms - when there are thousand requests per several minutes - it bocomes to be a very good time.
2: Operation system will refuse me to create thousand threads and, anyway, database will return responds averagely just when all of them will be accomplished.
3: I never close a connection once it was created, so any pool will not help me (I google says right about "connection pool").
I found the expression: "you can't issue another PQsendQuery until you've gotten that NULL." - so if it is true, then it's impossible (because it's only one function available to send an asynchronous request) to send multiple requests before any result will be gotten.
Problem can be observed from this abstract point of view:
Transferring data from application server (which connects to the database) takes 200 ms (and the same amount to transfer backward); adding data to the database and then selecting data (all in one request) from it takes 250 ms, so each database operation (of such type) will take 200 + 250 + 200 = 650 ms. Two such operations will take 650 + 650 = 1300 ms, but if there existed the way to send two queries at once and then get two results at once (of course tracking the correspondence between requests/responds) we could decrease such two "dialogs" from 1300 ms to 200 + 250 + 250 + 200 = 900 ms. So, we win 400 ms - when there are thousand requests per several minutes - it bocomes to be a very good time.
6 января 2011 г. 1:07 пользователь Peter Geoghegan <peter.geoghegan86@gmail.com> написал:
2011/1/5 Вячеслав Блинников <slavmfm@gmail.com>:> The whole thing is:I'm afraid I don't understand your problem, but may I suggest that you:
> - server connect to the database and wait for incoming connections
> - when incoming connection occurs, server request the database for some data
> about connected client - server must do it asynchronously and without
> creating any threads just for connected client (there can be more than
> thousand clients)
> - at moment of requesting data from the database there already can be some
> active requests
> - when database respond it is need to know for which client this data
> received (and what kind of request it was)
> That's all.
>
> P.S. it is possible to implement it requesting data one-by-one (adding each
> request to the queue and popping (FIFO) each request after each database'
> respond) but it is slow because it's need to wait data transferring through
> the net while database (server) itself will be idling.
1. Wrap the connection in a semaphore. This isn't pretty, but if I've
understood you correctly, it will do the job.
or
2. Have one database connection per "server" client. Having one
process that itself has up to a thousand clients but uses only one PG
connection is a very questionable approach - the single database
connection is certain to become a bottleneck.
and
3. Use a connection pooler.
--
Regards,
Peter Geoghegan
pgsql-general by date: