SCM

[#1011241] Release connection into pool with "Discard All"

View Trackers | Patches | Download .csv | Monitor

Date:
2012-10-08 11:56
Priority:
3
State:
Open
Submitted by:
chen huajun (chenhuajun)
Assigned to:
Nobody (None)
Category:
None
Group:
None
Resolution:
None
 
Summary:
Release connection into pool with "Discard All"

Detailed description
Now, Npgsql execute the following SQL statments to reset the status of a NpgsqlConnecor when put it into ConnectorPool.
-unlisten *
-deallocate $planName

But only execute the two SQL statments is not enough to clear the status of a connection. The "Discard All" which support from PostgreSQL 8.3 is better.

http://www.postgresql.org/docs/8.3/static/sql-discard.html

Followup

Message
Date: 2012-10-23 13:13
Sender: Francisco Figueiredo jr.


My previous message should have been: Later we dumped ascii in favor to use only unicode because Postgresql supports automatic charset conversion...
Date: 2012-10-23 13:11
Sender: Francisco Figueiredo jr.


Npgsql uses UNICODE also for historical reasons. Npgsql 1 series used to have support for ASCII and unicode charset. Later we dumped ascii in favor of unicode only because it supports automatic charset conversion in the server side regardless of the database charset.
Date: 2012-10-23 09:24
Sender: chen huajun

I think the function of UTF8 codeset should can replace UNICODE,so ignoring this condition should not cause any trouble.
Why Npgsql use this condition? for performance?
Date: 2012-10-22 11:50
Sender: Francisco Figueiredo jr.


Yes, it seems those configurations can be modified in the connection request packet. The docs even say: " The values will act as session defaults".

It's been a long time since NpgsqlStartupPacket has been changed that I didn't remember datestyle was being set there :)

We would need to change the protocol 2 version too.


Sorry but I didn't follow you in the explanation of the client encoding and we can ignore this condition you talked. Would you mind to explain it again?

In the weekend I made some performance tests to check the impact the discard all and the execution of (DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;)

AS we suspected, the execution did have a performance impact but it wasn't very big. The actual numbers are in my dev machine and I'll post them later, sorry for that.
But the performance hit was of ~100-200ms for each 10k connection open and release.

I'll make some more tests and check how to refactor the code in order to send the parameter settings in the startup packet.

Date: 2012-10-22 10:58
Sender: chen huajun

I only try the "application_name"(version of the server is 9.1.3). But I think all configuration which can modified by SET commands also can be send in the connection request packet.

Now some of the configuration had been send in the connection request packet,for example DateStyle
Npgsql.NpgsqlStartupPacket.cs
private void WriteToStream_Ver_3(Stream output_stream)
{
...
// DateStyle.
PGUtil.WriteString("DateStyle", output_stream);
// DateStyle.
PGUtil.WriteString("ISO", output_stream);
...
}

For the code above ,the following "set DATESTYLE TO ISO" should be needless.
Connector.cs:
internal void Open()
{
...
using(NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this))
...
}

All of configuration modified by SET commands in Connector.Open() may able be move to connection request packet ,except "SET CLIENT_ENCODING TO UTF8" because it's execute condition come from the response of connection request packet.But we may ignoring the condition( postgresql-jdbc just do as that).
Connector.cs:
internal void Open()
{
...
if(
!ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) ||
(!string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase))
)
new NpgsqlCommand("SET CLIENT_ENCODING TO UTF8", this).ExecuteBlind();
...
}
Date: 2012-10-15 14:28
Sender: Francisco Figueiredo jr.


Which configuration were you able to send in the connection request packet?

I agree with you that the 7 statements would slow down the process of release, as also would the discard all statement. But I think the step which should be more optimized would be the connection request from the pool as the release time could be added to the client processing time in the cycle: request connection, use (processing) time, release time.

Last versions of Npgsql have some good improvements in this area of requesting which showed to have a big positive impact in the performance of applications.

Another idea I was thinking about was to make those clearing not when releasing the connection every time. We could make this release at each 10 connections release, for example. We could add a parameter in the connection string which would control that.

I thought about this count strategy in order to not have a big negative impact in the performance of connection pool, in the case of very busy servers, as well as giving users control of how often they can perform a full cleaning in the connection.

But of course those are only initial ideas. We would need to make more tests to check how much impact those releases and session reinitializations would have in the performance of the request/release connection from the pool. Maybe they don't have so big impact as I'm thinking and it would be a straight fix. :)


Date: 2012-10-15 04:08
Sender: chen huajun

How about move some configurations(whose context is "backend") setting to the connection request packet?
"RESET ALL" does not affect the configurations which made by the connection request packet according to my test.
And the other configurations which can not be setted in the connection request packet could be redo.
But I think the number of them is few,so the effect of performance is samll.
Similarly,if executing the following SQL statments, 7 statments must be executed when close a connection.This would slow down the process of release a connection to pool.

SET SESSION AUTHORIZATION DEFAULT;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;
Date: 2012-10-14 14:34
Sender: Francisco Figueiredo jr.

It should have been: we lose the configurations...
Date: 2012-10-14 14:30
Sender: Francisco Figueiredo jr.


While testing your modifications, I noticed some side effects regarding discard all:

We loose the configurations we did when first starting the connection, like schema search path, datetime style, client encoding settings, application_name and a lot more.

Of course we could redo all those settings, but we would need to do it everytime we get a connection from the pool and this would slow down the process of getting a new connection.

Maybe we could use a subset of the discard all which doesn't affect the session settings we already did and don't need to be cleared?

In the documentation it says discard all is the same as executing those statements:

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;

Maybe we could start executing them but the RESET ALL.
I'll make some tests to check if it works ok without the RESET ALL.

Maybe we could add a connection string flag to turn this RESET ALL execution a user option.

Attached Files:

Attachments:
ReleaseResourcesWithDiscardAll.zip

Changes:

Field Old Value Date By
summaryRelease connection into pool with "Discard All"2012-10-23 13:13fxjr
close_dateNone2012-10-23 13:13fxjr
close_dateNone2012-10-23 13:11fxjr
summaryRelease connection into pool with "Discard All"2012-10-23 13:11fxjr
summaryRelease connection into pool with "Discard All"2012-10-22 11:50fxjr
close_dateNone2012-10-22 11:50fxjr
summaryRelease connection into pool with "Discard All"2012-10-15 14:28fxjr
close_dateNone2012-10-15 14:28fxjr
summaryRelease connection into pool with "Discard All"2012-10-14 14:34fxjr
close_dateNone2012-10-14 14:34fxjr
summaryRelease connection into pool with "Discard All"2012-10-14 14:30fxjr
close_dateNone2012-10-14 14:30fxjr
File Added764: ReleaseResourcesWithDiscardAll.zip2012-10-08 11:56chenhuajun
Powered By FusionForge