[pgpool-general: 2295] Re: Many short CGI connections - needed to downgrade to version 2.3
ishii at postgresql.org
Sat Nov 16 18:51:54 JST 2013
> My use case:
> 1. Streaming replication slave physically far from Master, about 250ms round trip time.
> 2. Application web server close to slave.
> 3. Application web server makes many short connections to database, with just one or two SQL commands - mostly SELECT - before ending session.
> 4. Need to send reads to slave, and writes to master.
> We had bad performance with pgpool 3.1 and 3.3 due to:
> 1. when session is established to a previously unused pgpool process to issue a single SELECT, as many as 7 sql commands are first sent to the master to set session variables, establish user rights, and check whether table is temporary. This process took 1750ms, after which SELECT query was sent to slave, which responded in 1ms.
> 2. Subsequent sessions to a pgpool process SELECTing from same table only caused a check to see if it was temporary, by way of 2 commands that took 500ms before SELECT was sent to slave.
You could eliminate temp table checking by:
check_temp_table = off
> This was solved by downgrading to 2.3 which has following results:
> 1. user rights are rapidly established by checking catalog on slave.
> 2. SELECTs are sent immediately to slave with no interaction with master
> 3. No check for temporary tables.
> SELECT are now returning in 1.5ms, down from 500ms to 1750ms in case of version 3+.
> I think our use case (geographically distributed slaves and many short CGI connections) is common, and would request it to be handled by an option in ini file to check temporary table status, user rights etc. from slave and to stop setting session variables on the master. It is not clear why pgpool has switched from checking catalogs on slave to master.
If a query is sent right after the table is created, querying to slave
may fail because the creation of the table might not be propagated to
Maybe we can eliminate all of the checking if user agree on the risks
(that's what pgpool-II 2.3 behaves). Do we want to add such a switch?
SRA OSS, Inc. Japan
More information about the pgpool-general