Обсуждение: Libpq Asynchronous Command Processing
Hello,
I am migrating a client/server application from Debian Sarge to Debian 5.0 and I am finding problems with the client
application.The facts are the following:
->The client application is an interface to a Postgresql DB so it uses libpq.
->The client application compiles properly in both Debian Sarge and Debian 5.0.
->The client application employs Libpq asynchronous command processing.
->The pseudo.code for the queries is the following:
PQconnectdb
PQsendQuery
PQflush
loop{
PQconsumeInput
PQisBusy
if not busy PQgetResult and leave
}
->The new DB server is postgresql 8.3.
->The old DB server is postgresql 7.4
->I am using the same SQL script to create the DB.
And the problems I am finding are the following:
->Queries from the client to the new DB server take a lot of time.
->Queries from the client to the old DB server are fast.
->The same query takes 150 secs in one case an 1 sec in the other case.
¿Any ideas regarding the origin of this strange behaviour?¿Could it be the configuration of the new DB?
Thanks in advance.
=?iso-8859-1?Q?Alonso_Garc=EDa_=2C_Bruno_Elier?= <bealonso@indra.es> wrote: > And the problems I am finding are the following: > ->Queries from the client to the new DB server take a lot of time. > ->Queries from the client to the old DB server are fast. > ->The same query takes 150 secs in one case an 1 sec in the other case. With that analysis, I'd be betting against it being a client problem. (If you wanted, you might confirm that by pointing an old client at the new server.) I'd look into how the data was loaded into the new server and how the database is configured: number of buffers, indexes, and whether analyze has been run or not. It would be strange indeed (possible, but very strange) to find such a slowdown between 7.x and 8.x when the team is preparing to push 9.0 out the door. Surely it would have been known before; therefore it's a practical certatinty that there is something different about the configuration of your two servers. Giles
On 31/05/2010 5:41 PM, Giles Lean wrote: > =?iso-8859-1?Q?Alonso_Garc=EDa_=2C_Bruno_Elier?=<bealonso@indra.es> wrote: > >> And the problems I am finding are the following: >> ->Queries from the client to the new DB server take a lot of time. >> ->Queries from the client to the old DB server are fast. >> ->The same query takes 150 secs in one case an 1 sec in the other case. > > With that analysis, I'd be betting against it being a client problem. > (If you wanted, you might confirm that by pointing an old client at > the new server.) > > I'd look into how the data was loaded into the new server and how > the database is configured: number of buffers, indexes, and whether > analyze has been run or not. > > It would be strange indeed (possible, but very strange) to find > such a slowdown between 7.x and 8.x when the team is preparing > to push 9.0 out the door. Surely it would have been known before; > therefore it's a practical certatinty that there is something > different about the configuration of your two servers. ... or that the planner is making a bad choice when it made a good one in 7.x . That's far from unheard of; the downside of a stats-based and very complex planner is that sometimes it doesn't make the perfect choice. Even with the same stats, etc, it's far from impossible that 7.x might hit a good plan when 8.x doesn't. I mention this because the OP really needs to supply EXPLAIN ANALYZE results for the query run via psql (not their custom code) on both their 7.x and 8.x servers. -- Craig Ringer
>> With that analysis, I'd be betting against it being a client problem. >> (If you wanted, you might confirm that by pointing an old client at >> the new server.) >> >> I'd look into how the data was loaded into the new server and how >> the database is configured: number of buffers, indexes, and whether >> analyze has been run or not. >> >> It would be strange indeed (possible, but very strange) to find >> such a slowdown between 7.x and 8.x when the team is preparing >> to push 9.0 out the door. Surely it would have been known before; >> therefore it's a practical certatinty that there is something >> different about the configuration of your two servers. > >... or that the planner is making a bad choice when it made a good one >in 7.x . That's far from unheard of; the downside of a stats-based and >very complex planner is that sometimes it doesn't make the perfect >choice. Even with the same stats, etc, it's far from impossible that 7.x >might hit a good plan when 8.x doesn't. > >I mention this because the OP really needs to supply EXPLAIN ANALYZE >results for the query run via psql (not their custom code) on both their >7.x and 8.x servers. If I perform the query using pgadmin I get the same result in both versions 7.4 and version 8.3. In fact I have written two test applications that perform the same query, one using the synchronous command processing (PQexec)an one using the asynchronous Command Processing (PQsendQuery / PQconsumeInput / PQisBusy / PQgetResult) and theresults are: -> synchronous command processing takes less than two seconds to retrieve the result. -> asynchronous command processing takes more than 120 seconds to retrieve the result. Both applications are connecting to the same DB so I don't know why I am getting different results. Well I know that PQIsBusyis returning true so I am not executing PQgetResult. Bruno,
On 31/05/2010 10:34 PM, Alonso García , Bruno Elier wrote: > If I perform the query using pgadmin I get the same result in both versions 7.4 and version 8.3. Please post the output of EXPLAIN ANALYZE for each. See: http://wiki.postgresql.org/wiki/SlowQueryQuestions and http://wiki.postgresql.org/wiki/Using_EXPLAIN -- Craig Ringer
On 31/05/2010 10:34 PM, Alonso García , Bruno Elier wrote: > If I perform the query using pgadmin I get the same result in both versions 7.4 and version 8.3. Just re-read your post and realized you were probably saying that you get (effectively) the same EXPLAIN ANALYZE results from both, ie this isn't your problem. > In fact I have written two test applications that perform the same query, one using the synchronous command processing(PQexec) an one using the asynchronous Command Processing (PQsendQuery / PQconsumeInput / PQisBusy / PQgetResult)and the results are: > -> synchronous command processing takes less than two seconds to retrieve the result. So PQexec works fine for you on both 7.4 and 8.3, producing a quick result no matter which server you run it against? > -> asynchronous command processing takes more than 120 seconds to retrieve the result. You mean that this is where you have your problem, and it's fine on both versions when you use plain PQexec? Consider using wireshark to examine the network traffic, and see if there's much activity during your long and slow PQconsumeInput / PQisBusy loop. The throughput analysis tool is handy for this. -- Craig Ringer
>So PQexec works fine for you on both 7.4 and 8.3, producing a quick >result no matter which server you run it against? Yes. If I use PQexec, both 7.4 and 8.3 produce a quick result but I if I use asynchronous command processing 8.3 producea slow result whereas 7.4 works fine. >Consider using wireshark to examine the network traffic, and see if >there's much activity during your long and slow PQconsumeInput / >PQisBusy loop. The throughput analysis tool is handy for this. I will be back with the results. Bruno, -- Craig Ringer
=?iso-8859-1?Q?Alonso_Garc=EDa_=2C_Bruno_Elier?= <bealonso@indra.es> writes:
> Yes. If I use PQexec, both 7.4 and 8.3 produce a quick result but I if I use asynchronous command processing 8.3
producea slow result whereas 7.4 works fine.
You're still being quite unclear. Is this 7.4 libpq + 7.4 server
against 8.3 libpq + 8.3 server, or some cross-version combination?
Have you tried switching to the other library version?
regards, tom lane