Question about connection pool by host instead of db

[Carson Ip]

Hi, is it possible to have the connection pool pooling connections by host instead of db? i.e. it will change db upon reuse.

I'm asking because I have a lot of shards (databases) on every MySQL host and if I create a connection pool size of N, and the host contains M shards, it will create N*M connections to MySQL. Now that I use ProxySQL to do connection pooling, the actual number of connections to MySQL is lowered, but the number of connections to ProxySQL is still N*M, causing high CPU on ProxySQL due to system call poll and sleep.

Thanks!

[Mike Bayer]

When you say shards it's not clear if you want per-shard connect points or if you are looking for a round robin distribution under one connect point.  For the latter case, I did work up a new kind of connection pool which does this, which is at https://bitbucket.org/zzzeek/haalchemy/src/6623f93c122f5e4ac9de208c09512699e30a76f8/haalchemy/clients/sqlalchemy/pool.py?at=master&fileviewer=file-view-default ,  however the whole haalchemy thing I abansoned explicitly because proxysql exists and solves the same problem.

That is, you're using proxysql.  Sqlalchemys connection pool is per process no matter what so this means it can't do distributed behaviors without a middle tier.   Since proxysql already pools you can just use NullPool on the sqlalchemy side and be done with it.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.