Speeding up when connecting from cx_Oracle to Autonomous Database
at first
In this article, DRCP (database resident connection pooling) is now available in Autonomous Database, so give it a try. The result was not good, so I changed the environment on the client side a little and tried it. If you write the conclusion first,
--The slow cause is that the application logic (processing tendency) and DRCP setting (tuning) do not match. Unfortunately, DRCP tuning is not possible in an Autonomous environment. --Explosive speed when using cx_Oracle connection pool rather than using DRCP
It will be.
Verification environment
--Oracle Cloud Osaka Region --Compute instance (test application execution environment): VM.Standard2.1 --OS Image: Oracle Cloud Developer Imange on Marketplace --Database instance: Autonomous Transaction Database 18c (1OCPU) --Create a test application using Python3 on a Compute instance and measure the time it takes to connect 300 times.
Investigating the cause of the DRCP not being as fast as expected
First, I will present the source of the verification application that does not use DRCP. Tnsnames.ora is used to specify the connection destination. Therefore, the use of DRCP is specified in tnsnames.ora.
test1.py
# -*- coding: utf-8 -*-
import cx_Oracle
import time
USERID = 'admin'
PASSWORD = 'Replacement required'
DESTINATION = 'atp_low'
REPEAT_TIMES = 300
elapsed_times = []
for i in range(REPEAT_TIMES):
t1 = time.time()
connection = cx_Oracle.connect(USERID, PASSWORD, DESTINATION)
t2 = time.time()
connection.close()
elapsed_times.append(t2 - t1)
print(f'Total connection time: {sum(elapsed_times)}Seconds')
print(f'Average connection time: {sum(elapsed_times) / REPEAT_TIMES}Seconds')
Next is the source of the verification application that utilizes DRCP.
test2.py
# -*- coding: utf-8 -*-
import cx_Oracle
import time
USERID = 'admin'
PASSWORD = 'Replacement required'
DESTINATION = 'atp_low_pooled'
REPEAT_TIMES = 300
elapsed_times = []
for i in range(REPEAT_TIMES):
t1 = time.time()
connection = cx_Oracle.connect(USERID, PASSWORD, DESTINATION, cclass='MYCLASS', purity=cx_Oracle.ATTR_PURITY_SELF)
t2 = time.time()
connection.close()
elapsed_times.append(t2 - t1)
print(f'Total connection time: {sum(elapsed_times)}Seconds')
print(f'Average connection time: {sum(elapsed_times) / REPEAT_TIMES}Seconds')
As a result of running these applications several times, the performance has almost doubled when using DRCP. The original article also includes SQL * Plus startup time, so it's better than the original article, but I still want more performance. So, first considering the possibility that communication encryption is a bottleneck, I switched the connection destination to Oracle Database Cloud Service (DBCS) that can tamper with the DB Server side, removed the encryption setting, and tried it, DRCP The result is that with is slower than without. Speeded up only without DRCP. In other words, communication encryption has little impact when using DRCP. Here, in consultation with the original article author, I tried tuning DRCP (increasing MINSIZE) on DBCS. As a result, in a DBCS environment, the DRCP connection speed has increased about 10 times even if the communication is encrypted. We determined that the direct cause was insufficient tuning of DRCP and the settings did not match the logic of the application. The problem here is that with ATP, you can't change the DRCP settings. So, as a result, ATP (which seems to be the same for ADW) can only improve the connection speed at a reasonable level with this verification application. This time, it is carried out by repeating connection / disconnection from a single application, but if you start multiple applications that make one connection with the test tool and execute them repeatedly, different results will be obtained.
Try cx_Oracle's connection pool feature
Cx_Oracle, the Oracle Database connection driver for Python, has connection pool functionality. By using this function, you can expect a faster connection speed even in the absence of DRCP. So, when I tried the following application, it worked very fast even when connecting to ATP (it took less than 1 second even if I connected 300 times). The ruthless conclusion was that DRCP would not be needed in an environment where a connection pool of connection drivers such as Python (cx_Oracle) could be used. Just in case, I think that DRCP itself has a significance because of the environment where the corresponding function does not exist.
test3.py
# -*- coding: utf-8 -*-
import cx_Oracle
import time
USERID = 'admin'
PASSWORD = 'Replacement required'
DESTINATION = 'atp_low'
REPEAT_TIMES = 300
elapsed_times = []
pool = cx_Oracle.SessionPool(USERID, PASSWORD, DESTINATION, min=4, max=40, increment=1)
for i in range(REPEAT_TIMES):
t1 = time.time()
connection = pool.acquire()
t2 = time.time()
pool.release(connection)
elapsed_times.append(t2 - t1)
pool.close()
print(f'Total connection time: {sum(elapsed_times)}Seconds')
print(f'Average connection time: {sum(elapsed_times) / REPEAT_TIMES}Seconds')
Performance ratio of each pattern
Finally, I present a graph of the performance ratio of the tests performed in this article. The performance when using cx_Oracle's connection pool was so good that it felt like a lie. .. ..
Recommended Posts