Hello,
I am seeking advice/comment/experience you may have had for the performance
cost for remote access to postgresql 8.1.X?
I have two servers, one is Sun V240 (say server A) and the other is dual
intel Xeon (say Server B) and both installed Solaris 10.
With Server A, there is postgresql 8.1.3 installed with pgpool
(pgpool-3.0.2), with server B, there is a pgpool (v3.0.2) installed.
The test program is installed on both A and B, where the test application on
server B is accessing to DBMS on A through pgpool.
Note that the test code is not fancy but can insert a large number of record
(say 100k rows) with configurable transaction size.
Following are the results (repeated many times with the mean value and shall
be accurate) for various setting by fixed 100k insertion operation with a
transaction size as 100 rows):
------------------------------------------
1. Test program running on server A directly access to LOCAL postgresql:
24.03 seconds
2. Test progam running on server A access to LOCAL postgresql through
pgpool: 30.05 seconds
3. Test progam running on server A access REMOTE postgresql through local
pgpool: 74.06 seconds
------------------------------------------
I have to say both machines are very light load and interconnected with
local LAN.
From 1 and 2, pgpool add 20% overhead, it sounds reasonable but any way to
reduce it???
From 2 and 3, it suggests the remote access is much slower than local
access.
My question is:
a) Anyone has the similar experience? How do you deal with it?
b) Why TCP stack imposes such big delay? any tuning point I shall do?
The time call reports
for test 2 is
real 0m32.71s
user 0m2.42s
sys 0m2.65s
for test 3 is
real 1:14.0
user 2.5
sys 3.2
c) Obviously, CPU time for (user + sys) for both tests are very similar,
but the overall time is quite different. I assume the time used on TCP stack
makes the difference.
Many thanks,
Regards,
Guoping Zhang