How to use cx_OracleSessionPool in multiprocess while can't pickle cx_OracleSessionPool objects

[bestathena]

1. What versions are you using?

Database version: 12c
platform.platform: Linux-redhat 7.8,windows 7
platform.python_version: 3.6.8
cx_Oracle.version: 8.1.0

2. Describe the problem

I want to share OracleSessionPool in multiprocesses to do data etl works . There are many jobs so I don't want to create db conncet in each job ,but the python can't pickle cx_OracleSessionPool objects when pass OracleSessionPool to subprocess.

if I can't create a session pool in one process and then acquire a connection from that pool in a worker process, what is the purpose of cx_oracle sessionpool ? How can i make a db conn when intitialization subprocess and close it before subprocess (not worker method ) quit

there are about 1000 data etl jobs , each execute time varies from 1 minute to 20 minutes.
the db server is 8C32G, so I plan to open 8 processes.
I know the db workload is not very heavy for now ,even if open and close a connect in each subprocess func, but in consideration of rubust and expansibility, I try to use an elegent method to take db connet in subprocess

3. Include a runnable Python script that shows the problem.

#!/usr/bin/python3
# -*- coding: utf-8 -*-
from multiprocessing import Pool
import cx_Oracle


def worker(job_id,conn_pool):
    print('sub process :' + str(job_id) + 'start')
    conn = conn_pool.acquire()
    # do something with db
    conn_pool.release(conn)

def worker_error(info):
    print('run_error:'+ str(info))

if __name__ == '__main__':
    process_pool = Pool(5)

    conn_pool = cx_Oracle.SessionPool("rpsdb", 'rpsdb', "11.12.4.51:1521/rpsdb", min=2, max=5, increment=1, encoding="UTF-8")

    for i in range(5):
        process_pool.apply_async(func=worker, args=(i, conn_pool,),error_callback=worker_error)

    process_pool.close()
    process_pool.join()

[cjbj]

You won't be able to share the Python side of connections across processes, but there are various techniques useful for very large, and multi-processes to help reduce DB load. What are your load and resource expectation? How many connections do you need open at a time? How long do they remain open? How frequently are they opened? Are they all using the same DB schema etc etc. The more detail, the better.

[bestathena]

there are about 1000 data etl jobs , each execute time varies from 1 minute to 20 minutes.
they all using the same DB schema
the db server is 8C32G, so I plan to open 8 processes.
I know the db workload is not very heavy for now ,even if open and close a connect in each subprocess func, but in consideration of rubust and expansibility, I try to use an elegent method to take db connet in subprocess

[cjbj]

Does each of those processes run its jobs sequentially, or is there some degree of parallelism within each of the processes? For each process, is there a time delay between consecutive ETL's use of the DB, e.g. between the last commit of one job and the first insert of the next job? Are the connections "the same" in terms of sessions settings like NLS settings - i.e. is reuse of connections OK? What are your high-availability requirements? How do you restart jobs? Do you use RAC?

For your forward planning, what's the expected growth - how many connections, will the execute time change?

Often simple is elegant.

[bestathena]

Often simple is elegant.

The job will be allocated to processes when its previous jobs are all finished, thus processes can run its jobs sequentially.
From the view of jobs, they are executed parallelly as possible as they can. For each process, there must a little time delay between consecutive ETL's use of the DB, as the code
process_pool.apply_async(func=worker, args=(i, conn_pool,),error_callback=worker_error).
The connections are "the same" so reuse of connections is OK.
The first step of each job is "rollback", so the jobs are re-executeble。restart jobs is just as the same as its first executeion.
For forward planning, execute time of a single job will not change to much. A long execute time indicates that the job shoud be divided to subjobs。

simple is elegant. that is great, simple is the best.

[anthony-tuininga]

If the execute time is 1 to 20 minutes, why are you concerned about 50-100 ms??? :-)

If the jobs can use any connection, why not simply create the connection in the worker process (as a global) and smply use it in the worker function? That way you still save the 50-100 ms but you aren't trying to share connections across processes -- which isn't supported.

[bestathena]

If the jobs can use any connection, why not simply create the connection in the worker process (as a global) and smply use it in the worker function? That way you still save the 50-100 ms but you aren't trying to share connections across processes -- which isn't supported.

Thanks for your advice. I find a similiar question in stackoverflow which was asked 8 years ago and the conclusion is the same.
I can open a global connection by initializer() method in apply_async ,but how to close it ? Is there any problem let the python program disconnect the db automaticly when the worker process ends? When should some one consider to use cx_OracleSessionPool instead of cx_Oracle ?

[cjbj]

You are right to wonder about futures. You won't have a problem with opening & closing just 8 connections over 1-20 minutes. But it will be better & more scalable to keep the connections open - and don't let worker processes die between ETL jobs.

Session pooling is most useful for multi-threaded applications. But it does let apps take best advantage of Oracle high availability features, such as load balancing. And if you ever want to rearchitect your processes to be multi threaded, session pools will facilitate that. You could consider using a session pool of max size 1. Then acquire and release the connection around each ETL job (or let end of scope do the release); again don't let the processes die between ETL jobs.

You can use a Python class overriding SessionPool or Connection to check what's going on; or use DPI tracing (see the cx_Oracle and ODPI-C manuals); or use AWR to check connection rates.

[bestathena]

Then acquire and release the connection around each ETL job (or let end of scope do the release); again don't let the processes die between ETL jobs.

ok! the last question: is there any thing I should do when all the ETL jobs are finished and it's time to release the connection which created in the subprocess ? and how~

[anthony-tuininga]

If the process ends normally, the connections will also be closed properly without any additional effort from you.

[cjbj]

To summarize, what is being suggested is that your 8 processes remains running.

• If you only ever open a single connection in each process, and that connection is in continuous use, then do what @anthony-tuininga has said.
  One thing to think about is how you are going to restart a failed job. If the network to the DB dies, then how are you going to reconnect? Using a session pool could make this easier.
• If you decide to create a session pool in each process, then each process should acquire and release the connection from the pool before and after each job. I believe you'll just have one connection per pool in your current workload. The pool.acquire() call will detect a number of cases of failed connections (even without additional configuration like setting up FAN or RAC) and give you a new, usable connection in those cases. At the end of each job, each process can release the connection explicitly, or let it fall out of scope of some code block. The latter has some benefits in that the correct order of closing cursors etc is handled by cx_Oracle. At process termination, the pool shutdown will automatically happen cleanly - or you can close it explicitly, but it's hardly worth it. You will probably want a pool 'getmode' that waits, so if a connection has to be recreated there won't be an immediate return failure from acquire().

[bestathena]

@cjbj @anthony-tuininga Thank you very much！ I am going to use session pool of max size 1 in work process right now!