PgPool – PostgreSQL Connection Cache
Playing PgPool on a heavy load database server is fun. PgPool does connection caching and it also does database mirroring. Visit PgPool offical website to learn more about db miroring.
I have done some stress test on my database server, and it seem like PgPool really save the connection request. First, I have written a php page, which pull 100 rows result from database that consist of 15 columns. The database server max_connections is set to 300. I am using apache ab for the stress test by calling 50 requests and 500 concurent connections.
Here are the results for normal database connection.
First Test Result
Concurrency Level: 500
Time taken for tests: 13.697943 seconds
Complete requests: 50
Failed requests: 0
Write errors: 0
Total transferred: 5387549 bytes
HTML transferred: 5353216 bytes
Requests per second: 3.65 [#/sec] (mean)
Time per request: 136979.427 [ms] (mean)
Time per request: 273.959 [ms] (mean, across all concurrent requests)
Transfer rate: 384.07 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 65 92 19.3 92 115
Processing: 1181 2634 2921.9 1569 13627
Waiting: 1144 2311 2631.7 1349 13504
Total: 1273 2726 2920.0 1676 13693Percentage of the requests served within a certain time (ms)
50% 1676
66% 1826
75% 2345
80% 2382
90% 6346
95% 11794
98% 13693
99% 13693
100% 13693 (longest request)
Second Test Result
Concurrency Level: 500
Time taken for tests: 3.976388 seconds
Complete requests: 50
Failed requests: 0
Write errors: 0
Total transferred: 3261196 bytes
HTML transferred: 3206708 bytes
Requests per second: 12.57 [#/sec] (mean)
Time per request: 39763.880 [ms] (mean)
Time per request: 79.528 [ms] (mean, across all concurrent requests)
Transfer rate: 800.73 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 42 44 1.7 45 48
Processing: 1290 2799 882.2 3124 3920
Waiting: 1239 2043 792.4 1992 3502
Total: 1334 2843 882.0 3168 3966Percentage of the requests served within a certain time (ms)
50% 3168
66% 3488
75% 3558
80% 3812
90% 3871
95% 3916
98% 3966
99% 3966
100% 3966 (longest request)
Third Test Result
Concurrency Level: 500
Time taken for tests: 7.635724 seconds
Complete requests: 50
Failed requests: 0
Write errors: 0
Total transferred: 3192107 bytes
HTML transferred: 3138453 bytes
Requests per second: 6.55 [#/sec] (mean)
Time per request: 76357.241 [ms] (mean)
Time per request: 152.714 [ms] (mean, across all concurrent requests)
Transfer rate: 408.21 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 40 43 2.1 42 48
Processing: 1625 3005 1657.6 2442 7567
Waiting: 1388 2824 1724.7 2217 7441
Total: 1672 3048 1657.5 2484 7609Percentage of the requests served within a certain time (ms)
50% 2484
66% 3006
75% 3565
80% 4534
90% 4725
95% 7602
98% 7609
99% 7609
100% 7609 (longest request)
Here are the results with PgPool database connection caching.
First Test Result
Concurrency Level: 500
Time taken for tests: 13.352556 seconds
Complete requests: 50
Failed requests: 0
Write errors: 0
Total transferred: 10762648 bytes
HTML transferred: 10751250 bytes
Requests per second: 3.74 [#/sec] (mean)
Time per request: 133525.562 [ms] (mean)
Time per request: 267.051 [ms] (mean, across all concurrent requests)
Transfer rate: 787.12 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 41 43 2.4 43 48
Processing: 6325 8891 2397.1 7700 13302
Waiting: 290 2817 3191.7 544 8104
Total: 6370 8934 2396.7 7745 13347Percentage of the requests served within a certain time (ms)
50% 7745
66% 11129
75% 11273
80% 11316
90% 12993
95% 13142
98% 13347
99% 13347
100% 13347 (longest request)
Second Test Result
Concurrency Level: 500
Time taken for tests: 17.634481 seconds
Complete requests: 50
Failed requests: 0
Write errors: 0
Total transferred: 11478229 bytes
HTML transferred: 11466831 bytes
Requests per second: 2.84 [#/sec] (mean)
Time per request: 176344.814 [ms] (mean)
Time per request: 352.690 [ms] (mean, across all concurrent requests)
Transfer rate: 635.63 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 73 84 8.8 81 96
Processing: 10884 13328 2854.0 11595 17505
Waiting: 328 4530 5220.7 843 12476
Total: 10979 13413 2852.7 11675 17598Percentage of the requests served within a certain time (ms)
50% 11675
66% 16677
75% 16881
80% 17212
90% 17494
95% 17513
98% 17598
99% 17598
100% 17598 (longest request)
Third Test Result
Concurrency Level: 500
Time taken for tests: 14.672463 seconds
Complete requests: 50
Failed requests: 0
Write errors: 0
Total transferred: 12377247 bytes
HTML transferred: 12364598 bytes
Requests per second: 3.41 [#/sec] (mean)
Time per request: 146724.634 [ms] (mean)
Time per request: 293.449 [ms] (mean, across all concurrent requests)
Transfer rate: 823.79 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 43 46 2.4 46 51
Processing: 6770 9893 2901.4 8775 14609
Waiting: 419 3384 3150.5 1234 8421
Total: 6814 9939 2901.4 8819 14654Percentage of the requests served within a certain time (ms)
50% 8819
66% 12393
75% 12838
80% 13563
90% 13879
95% 14195
98% 14654
99% 14654
100% 14654 (longest request)
Let analyse longest request test result;
Test Result Without PgPool With PgPool
1st 13693 13347
2nd 3966 17598
3rd 7609 14654
During the testing without PgPool, the request time has dropped from 13693 to 7609 and during the test with PgPool, I would said that the average request time is 15000++. The reason why the request time has dropped during the test without PgPool is because the database cannot handle too many connections request.
FATAL: sorry, too many clients already
FATAL: sorry, too many clients already
FATAL: connection limit exceeded for non-superusers
FATAL: sorry, too many clients already
FATAL: connection limit exceeded for non-superusers
FATAL: connection limit exceeded for non-superusers
I did another test with 50 requests and 100 concurrent connections;
Test Result Without PgPool With PgPool
1st 17868 11919
2nd 17901 17625
3rd 17625 17901
In performance wise, there is no different. But it really save and cache the connections! If you are having problem with too many connection for PostgreSQL database connection, why not try to put PgPool on your database server?