From 269042f8191aaf3f7a7547c26092e90fbd11e219 Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Sat, 27 Apr 2019 07:17:49 +0900 Subject: [PATCH] Doc: first release of performance section. --- doc/src/sgml/performance.sgml | 115 ++++++++++++++++++++++++++++++++-- 1 file changed, 111 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/performance.sgml b/doc/src/sgml/performance.sgml index e824d38a5..3bdf548aa 100644 --- a/doc/src/sgml/performance.sgml +++ b/doc/src/sgml/performance.sgml @@ -9,7 +9,8 @@ - There are many configuration parameters that affect the performance of + There are number of configuration parameters that affect the + performance of Pgpool-II. In this chapter we present how to tune them. @@ -168,7 +169,7 @@ Process memory requirement in total (in mega bytes) = for more details), it is possible to distribute read queries among those database nodes to get more throughput since each database nodes processes @@ -200,8 +201,8 @@ Process memory requirement in total (in mega bytes) = is set to on, the load balance node is determined at the time each query - starts. This is useful in case when application has its own - connection pooling as it keeps on connecting + starts. This is useful in case that application has its own + connection pooling which keeps on connecting to Pgpool-II and the load balance node will not be changed once the application starts. Another use case is a batch application. It issues tremendous number @@ -209,12 +210,118 @@ Process memory requirement in total (in mega bytes) = + Creating Specific Purpose Database Node + + In OLAP environment sometimes it is desirable to have a large + read-only database for specific purpose. By creating such a + database is possible by creating a replica database using + streaming replication. In this case it is possible to redirect + read queries to the database in two ways: specifying database + names(s) or specifying application name(s). For former, + use . For + latter use . + + + In Memory Query Caching + Pgpool-II allows to cache read query + results for later use. This will bring huge benefit for a type + of applications which issue same read queries many times. If + there are two queries and the query strings (parameter for + prepared statements if any) are identical, two queries are + regarded as "same". For the first time the query is + sent, Pgpool-II saves the query + result, and use it for the second query without asking anything + to PostgreSQL. This technique is + explained in . + + + + When not to Use in Memory Query Caching + + When a table is modified, query results against the table + could be changed. To avoid + inconsistency, Pgpool-II discards + query cache data when corresponding table is modified. So + frequently updated database will not be suitable to use in + memory query caching. You can check if your database is + suitable to use query caching or not, you could + use . If query cache hit + ration is lower than 70%, probably you want to avoid using the + query cache. + + + + + + Relation Cache + + Sometimes Pgpool-II needs to + ask PostgreSQL to get meta + information, such as whether a table is a temporay one or + not. To get those + information, Pgpool-II sends queires + primary PostgreSQL which could be up + to as many as 10 queries. To reduce the + overhead, Pgpool-II maintains + "relation cahche". Next time same table is included in a + query, Pgpool-II extracts the + information from the cahe. + + There are some parameters to configure the relation + cahce. See , + for more details. + + + + Shared Relation Cache + + The relation cache basically lives in process private memory, + which is bound to a session. So even if a relation cache is + created to for a table, in different session the relation + cache might not be created yet. After all, until a relation + cache entry is created in all process, queries continue to + sent to PostgreSQL. + Pgpool-II 4.1 overcomes the issue + by creating relation cahce in shared memory. If a session + creates a relation cache entry in the shared memory, other + sessions will get the cache result by looking at the shared + relation + cache. See + configuration parameter section for more details. This feature + is pretty effective and we recommend this feature be enabled. + + + + Other Performance Considerations + + This section introduces some other performance considerations. + + + + Thundering Herd Problem + + If is large, it is + possible that many Pgpool-II process + are woke up and heavy context switching happens. This leads to + high system load and hurt the overall system performance. This + problem is called "the thundering herd + problem". Enabling could + solve the problem. Please note that for + smaller , + might make the system performance worse. Please take a look at + the guidance in section. + + + + -- 2.39.5