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. On Mon, Jan 28, 2019, 1:56 AM Carson Ip <carsonip...@gmail.com wrote: > 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! > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.