This note highlight performance penalty that can comes with using database connection with multiple address, contradicting what oracle document says, at least at "surface level".
this is worth considering when setting up application connection in DR/dataguard consideration
even when the first address is available i.e. DB is active, you notice stark difference in terms of percentage of time for connection. in this test, there was more than 6 times difference
between using one ADDRESS and two ADDRESS
1. Two TNS entry are created to test. TNS Client version 11.2.0.1.0
first TNS entry use one ADDRESS ,
second have two entry for ADDRESS.
IBA11DB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=first-dr-scan)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=IBA11DB)))Second TNS entry with two ADDRESS
IBA11DB_W=(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on) (DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=1)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=first-dr-scan.labcenter.priv)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=IBA11DB))) (DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=1)(ADDRESS_LIST=LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=secon-dr-scan.labcenter.priv)(PORT=1521)))(CONNECT_DATA=SERVICE_NAME=IBA11DB))))
2. exit3.sql contain "exit". this is to simulate logon and logout. this approach is use for simplicity.
strace -fc -o test_IBA11DB_W.strace sqlplus system/xxxxxxxxx@IBA11DB_W @exit3.sql
strace -fc -o test_IBA11DB.strace sqlplus system/xxxxxxxxx@IBA11DB @exit3.sql
see result below.
comparing the time of connection (+ disconnection). the first connection took 175 microseconds, second with two ADDRESS took 1176 microseconds, almost 7 times slower than the IBA11DB.
3. checking the detail of strace, there are calls on the second ADDRESS.
sendto transmitted message to secon-dr-scan (second ADDRESS)
connect - initiated connection to the second IP ADDRESS
recvfrom() used to to receive messages from a socket
$cat test__verbose_IBA11DB_W.strace…
21857 sendto(9, "\303\246\1\0\0\1\0\0\0\0\0\0\Rsecon-dr-scan\titsmt"..., 46, MSG_NOSIGNAL, NULL, 0) = 46….
21857 poll([{fd=9, events=POLLIN}], 1, 5000) = 1 ([{fd=9, revents=POLLIN}])
21857 ioctl(9, FIONREAD, [185]) = 0
21857 recvfrom(9, "\303\246\205\200\0\1\0\3\0\2\0\2\Rsecon-dr-scan\titsmt"..., 2048, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("172.2.27.1")}, [16]) = 185
21857 poll([{fd=9, events=POLLOUT}], 1, 4999) = 1 ([{fd=9, revents=POLLOUT}])
21857 sendto(9, "R\317\1\0\0\1\0\0\0\0\0\0\Rsecon-dr-scan\titsmt"..., 46, MSG_NOSIGNAL, NULL, 0) = 46
21857 poll([{fd=9, events=POLLIN}], 1, 4999) = 1 ([{fd=9, revents=POLLIN}])
21857 ioctl(9, FIONREAD, [104]) = 0
21857 recvfrom(9, "R\317\205\200\0\1\0\0\0\1\0\0\Rsecon-dr-scan\titsmt"..., 65536, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("172.2.27.1")}, [16]) = 104
even thou the first ADDRESS in the ADDRESS_LIST is same IBA11DB and LOAD_BALANCE=off, according to oracle doc it should check the first address before trying the connecting to next ADDRESS.
But as show in the strace, oracle issues system call such as "connect", probing the second ADDRESS, even when the first is available.
/TNS_TEST>>cat test_IBA11DB.strace
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
73.71 0.000129 1 162 2 read
14.29 0.000025 6 4 recvfrom
12.00 0.000021 0 106 19 open
0.00 0.000000 0 32 write
0.00 0.000000 0 97 close
0.00 0.000000 0 7 3 stat
0.00 0.000000 0 69 fstat
0.00 0.000000 0 10 poll
0.00 0.000000 0 42 lseek
0.00 0.000000 0 99 mmap
0.00 0.000000 0 25 mprotect
0.00 0.000000 0 54 munmap
0.00 0.000000 0 15 brk
0.00 0.000000 0 42 rt_sigaction
0.00 0.000000 0 5 rt_sigprocmask
0.00 0.000000 0 5 ioctl
0.00 0.000000 0 17 10 access
0.00 0.000000 0 13 1 socket
0.00 0.000000 0 18 6 connect
0.00 0.000000 0 6 sendto
0.00 0.000000 0 4 recvmsg
0.00 0.000000 0 2 bind
0.00 0.000000 0 10 getsockname
0.00 0.000000 0 6 setsockopt
0.00 0.000000 0 6 getsockopt
0.00 0.000000 0 1 execve
0.00 0.000000 0 14 uname
0.00 0.000000 0 19 fcntl
0.00 0.000000 0 2 getdents
0.00 0.000000 0 6 getcwd
0.00 0.000000 0 1 readlink
0.00 0.000000 0 5 getrlimit
0.00 0.000000 0 4 times
0.00 0.000000 0 10 getuid
0.00 0.000000 0 1 getppid
0.00 0.000000 0 2 statfs
0.00 0.000000 0 1 arch_prctl
0.00 0.000000 0 2 setrlimit
0.00 0.000000 0 5 1 futex
0.00 0.000000 0 1 set_tid_address
0.00 0.000000 0 1 set_robust_list
------ ----------- ----------- --------- --------- ----------------
100.00 0.000175 931 42 total
TNS_TEST>>cat test_IBA11DB_W.strace
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
87.84 0.001033 6 165 2 read
3.23 0.000038 0 110 19 open
3.15 0.000037 0 103 mmap
1.62 0.000019 1 34 write
1.53 0.000018 3 7 3 stat
1.36 0.000016 1 20 times
1.28 0.000015 0 42 rt_sigaction
0.00 0.000000 0 101 close
0.00 0.000000 0 73 fstat
0.00 0.000000 0 15 poll
0.00 0.000000 0 42 lseek
0.00 0.000000 0 25 mprotect
0.00 0.000000 0 58 munmap
0.00 0.000000 0 14 brk
0.00 0.000000 0 5 rt_sigprocmask
0.00 0.000000 0 5 ioctl
0.00 0.000000 0 17 10 access
0.00 0.000000 0 13 1 socket
0.00 0.000000 0 18 6 connect
0.00 0.000000 0 6 sendto
0.00 0.000000 0 4 recvfrom
0.00 0.000000 0 4 recvmsg
0.00 0.000000 0 2 bind
0.00 0.000000 0 11 getsockname
0.00 0.000000 0 2 setsockopt
0.00 0.000000 0 8 4 getsockopt
0.00 0.000000 0 1 execve
0.00 0.000000 0 22 uname
0.00 0.000000 0 15 fcntl
0.00 0.000000 0 2 getdents
0.00 0.000000 0 6 getcwd
0.00 0.000000 0 1 readlink
0.00 0.000000 0 5 getrlimit
0.00 0.000000 0 14 getuid
0.00 0.000000 0 1 getppid
0.00 0.000000 0 2 statfs
0.00 0.000000 0 1 arch_prctl
0.00 0.000000 0 2 setrlimit
0.00 0.000000 0 5 1 futex
0.00 0.000000 0 1 set_tid_address
0.00 0.000000 0 1 set_robust_list
------ ----------- ----------- --------- --------- ----------------
100.00 0.001176 983 46 total
Summary: if application is had issue with connection to database, using two “ADDRESS” will even make it worst. Of cos, better design such as connection pooling can be deployed to alleviate this overhead. The degree of difference depends network latency between ADDRESS and the client.
No comments:
Post a Comment