Postgresql query runs fast, but then becomes slow
Added by Bruce Toll about 4 years ago
This is only tangentially a Wt Dbo issue, but I thought I'd post here in case anyone else encounters it. I have a Wt App with a WTableView backed by a QueryModel that has been running well for some time. After making a minor change to the SQL, performance initially appeared fine. After some activity, however, the query would become very slow: performance in the sub-second range would increase to over 8 seconds. Occasionally, a query would still run quickly. Restarting the Wt server would reset the behavior --- fast for a while, then slow.
The database backend is Postgresql 9.6.15 and it turns out that the performance issue is due to how Postgresql handles prepared queries. Basically, it recomputes the plan for a prepared query based on the execute parameters for the first five executes on a connection. If the estimated cost of those plans is higher than the estimated cost of a generic plan, it switches to using the generic plan for all subsequent execute calls. This was what was causing the observed behavior, and it could be reproduced with psql (outside Wt) using "prepare" and "execute". Using "explain" and/or "explain analyze", it was possible to observe the switch in plans after the fifth execute.
There is more information in the Notes section of this page: https://www.postgresql.org/docs/9.6/sql-prepare.html
A potential workaround on the performance issue is to use setMaximumLifetime() to limit the lifetime of the Postgresql connection to a small time period. This helped, in testing, but is probably not an optimal solution. In my case, I was able to restructure the query to get acceptable performance from the generic plan.
Postgresql 12 provides additional tools to control the behavior of prepared queries. See the Notes section here: https://www.postgresql.org/docs/12/sql-prepare.html.
I don't know if this issue occurs with any frequency, but it might be worth considering adding Dbo support for the new Postgresql plan_cache_mode feature and/or providing a mechanism to limit the lifetime of cached statements in Wt.
Replies (2)
RE: Postgresql query runs fast, but then becomes slow - Added by Koen Deforche about 4 years ago
Hey Bruce,
Sounds plausible that indeed the long connection times cause issues with prepared statement planning being held. I think reducing the connection lifetime is not such a bad solution --- the pool allows to avoid opening/closing connection for each transaction; but having connections reopened for example every 1 hour would not have notice downsides.
Adding more complexity to the connection management is maybe not necessary?
Regards,
koen
RE: Postgresql query runs fast, but then becomes slow - Added by Bruce Toll about 4 years ago
Hey Koen,
Thanks for following-up. I think Dbo's support for connection pooling and statement caching are strong features that work very well. I think you are right to avoid additional complexity.
With Postgresql 12, it may be possible to achieve more fine-grained control over the planning of prepared statements without any changes to Wt Dbo by executing a "SET LOCAL plan_cache_mode TO force_custom_plan" within a transaction. I haven't yet tried it, but I think that would be a workable solution for the odd case where Postgresql has trouble coming up with a good default plan. I'm not sure when I'll be upgrading to Postgresql 12, but I'll try to remember to give this a try and report back.
Regards,
Bruce