0

I have written Python app which fetches data from 8-10 different databases. It works great with SQLAlchemy Pooling but i as soon as started scaling with Multiprocessing, every process creates own connection to database. I can not create more than 32 connections to individual database as they are shared databases used by many. I plan to run with 64 processes.

I initially thought of creating a service that will have a single API to fetch data from underlying databases (mostly read from them - 9 out of 10). I was thinking about some solution which provides this then i stumbled upon ProxySQL. it looks like something that i want but i am not able to configure it properly. I am ready to spend more time but i am not able to judge if this is right solution for my use case? So if you have used ProxySQL could you please help me with decision making?

My Setup:

  1. Connect to 9 MariaDB database for reading data.
  2. Out of 9, i only write to 1 database.
  3. I use Python (SQLAlchemy) to interact with database, I use ORM for 1 database, rest i fetch using RAW SQL.
  4. I always connect to Primary for read & write.
  5. I dont need to replicate data on slaves, its handled by DBAs.
  6. I dont need any rules for reading/writing, just connect to Primary and fetch the data.
  7. I would like to have all these databases configured in ProxySQL with different connection pool size.
  8. My application will connect to Underlying database via ProxySQL from Python + SQLAlchemy.

Thanks in advance. Let me know if there is any other solution for my scenario.

Regards, Ninad

I tried to configure ProxySQL but its unclear to me if supports only primary instances, with Python client. I am currently planning to run my app with modification and limit processes to 24 cores and every process creates 1 - 2 connections to underlying databases.

Ninad Mhatre
  • 549
  • 1
  • 7
  • 17

0 Answers0