New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Specifying engine pool class in config #266

Open
ericremoreynolds opened this Issue Feb 27, 2015 · 5 comments

Comments

Projects
None yet
5 participants
@ericremoreynolds

ericremoreynolds commented Feb 27, 2015

Hello,

SQLAlchemy's create_engine function has keyword argument poolclass allowing you to ovverride the default connection pool for the engine.

Is there any way to specify this in Flask-SQLAlchemy? Specifically I want to use a disk-based SQLite db with the SingletonThreadPool class.

Thanks in advance!

Eric.

@makmanalp

This comment has been minimized.

makmanalp commented Mar 10, 2015

I don't have a perfect answer but check out apply_driver_hacks and apply_pool_defaults here:

def apply_driver_hacks(self, app, info, options):

@nbaggott

This comment has been minimized.

nbaggott commented Jul 23, 2015

I take it from the fact that this issue is still open that you are not suggesting subclassing FlaskSQLAlchemy.

I wanted to present another use case for setting the pool class (and offer to implement). I need to configure the pool class to the NullPool for scaling reasons. Here's a comment from my subclass explaining why this was necessary:

  Disable session pooling.

  The default QueuePool pool doesn't allow you to fully close a connection. Calling
  db.session.close() returns the session to the pool with the TCP connection
  still open and a new transaction started. Because of the state that mysql has to
  maintain for repeatable read isolation, long lived sessions are a drain on db
  resources and performance suffers. Under load the DB will terminate connections before 
  the configured timeout (but this is still too late to prevent performance problems).

  With the NullPool, sessions work as expected - db.session.close() actually terminates the
  TCP connection. (You can confirm behavior with 'netstat -ant |grep 3306')

  One might think that an app under constant load would recycle it's DB connections pretty
  frequently, starting new transactions in the process and reducing the number of long lived
  connections. This is not the case. Gunicorn does not round-robin across flask workers and
  we routinely see some flask workers not used for hours even when the app is under continuous
  load.

Let me know if you'd be open to a patch for configuring poolclass. I've worked around the problem but have multiple apps with this requirement and it would be convenient if this option where directly settable via flask-sqlalchemy.

Thanks!
Nick

@ericremoreynolds

This comment has been minimized.

ericremoreynolds commented Jul 23, 2015

FYI subclassing is the workaround I used in the end.

tmeryu added a commit to tmeryu/flask-sqlalchemy that referenced this issue Aug 20, 2015

@tmeryu

This comment has been minimized.

tmeryu commented Aug 20, 2015

I was having a problem on AppEngine losing MySQL server connection. I was told to use NullPool to solve so I made this change: tmeryu@a20c309

I didn't like the pool_size == 0 check (#215) because it's a valid option given the SQLAlchemy documentation.

I'll leave a pull request but I'm open to suggestions of how to improve the code.

@tmeryu tmeryu referenced a pull request that will close this issue Aug 20, 2015

Open

Added a way to specify engine pool class in config #333

@jdp

This comment has been minimized.

jdp commented Sep 9, 2015

Being able to specify poolclass to the engine, as well as connect_args, would be super helpful for testing with in-memory SQLite databases. Right now it's not possible because an in-memory SQLite database is destroyed when a connection closes. Check out this post for more details.

vsurjaninov added a commit to vsurjaninov/sqlalchemy-wrapper that referenced this issue Dec 2, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment