1

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.

vishal
  • 104
  • 13
  • 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 May 14 '20 at 05:05
  • "I have more than 30 databases" What!? Please explain! – Klaus D. May 14 '20 at 05:13
  • 1
    @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 May 15 '20 at 05:35
  • @danblack Yes, I have --skip-name-resolve enabled. And Yes, users created by IP, domain name of host, or '%' – vishal May 15 '20 at 05:36
  • how much time is the connection taking? – danblack May 15 '20 at 05:44
  • more than 200ms – vishal May 15 '20 at 05:52
  • Sometimes, it's taking 130ms. conn = mysql.connector.connect( host=MYSQL_HOST, user=MYSQL_USERNAME, passwd=MYSQL_PASSWORD, charset='utf8mb4', use_unicode=True, pool_name='connection_pool', pool_size=10 ) – vishal May 15 '20 at 06:46
  • Any solution guys? – vishal May 26 '20 at 13:33

1 Answers1

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.

Sai Vamshi
  • 1
  • 1