Mysql connector - How to use connection pool with multiple databases?

Question

I have more than 30 databases and I want to use connection pool in my python code.

conn = mysql.connector.connect(
        host=MYSQL_HOST,
        user=MYSQL_USERNAME,
        passwd=MYSQL_PASSWORD,
        database=database,
        charset='utf8',
        use_unicode=True,
        pool_name='connection_pool',
        pool_size=10
)

In API calls, based on sub-domain, I have to decide which database to connect. For example "abc.mydomain.com", so I have to connect to abc's database. It is working fine with a single connection but It's taking time each time it tries to connect to the database.

use utf8mb4 not utf8 before you get corrupted strings. You can use USE {database} to switch the default database eon a connection. Database connections should be quick. Do you have 'skip-name-resolve' enabled on the server? Are you database user users created by IP, domain name of host, or '%'? — danblack
– danblack, Commented May 14, 2020 at 5:05
@KlausD. I'm building a SaaS-based product where I'm using a separate database for each customer. For testing, I'm using 30 databases. — vishal
– vishal, Commented May 15, 2020 at 5:35
@danblack Yes, I have --skip-name-resolve enabled. And Yes, users created by IP, domain name of host, or '%' — vishal
– vishal, Commented May 15, 2020 at 5:36

Sai Vamshi · Accepted Answer · 2023-06-02 10:02:45Z

0

Old thread but, you want a connection pool for 30 databases, since it is taking time to break and establish a new connection, it is better to establish 30 different connection pools for 30 connections and store them in dictionary based on database name, and from my research a connection pool can only store a single database connection and even if we try add multiple db connections to it, when we try to access one of them, there is a half half chance of getting either one, so it is more efficient to use multiple connection pools.

answered Jun 2, 2023 at 10:02

Sai Vamshi

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql connector - How to use connection pool with multiple databases?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related