Why do I have 2 SQLAlchemy connections when pool_size is set to 1?

user_10090711 0
Viewed 83

Hello I have a telegram bot connected to a MySQL db. I'm using a free account of PythonAnywhere which allows me to have max 3 connections to db per user. But I've been getting a max_user_connections error, so I've configured my SQLAlchemy engine like this:

engine = create_engine(db_url, echo=False, echo_pool=True, pool_size=1, pool_recycle=1200)

I was under the impression that with pool_size=1, there would be only one connection to db. But there is 2! Can you help me understand why that is?

Here's what SHOW PROCESSLIST shows me:

processlist

The first one is the console I'm checking the process list with. But the next two were created as the bot worked.

I also added max_overflow=0 just to test but that didn't make any difference either.

pythonanywhere python-telegram-bot sqlalchemy mysql python
2 Answers

PythonAnywhere developer here -- I think that the problem is that you're calling create_engine at the module level (that is, outside your view function). When your app is started up, it will run the code inside your module, and then fork off the worker process -- so if you call create_engine outside a function, that will create a connection at that time, and then another will be created when your worker process needs one.

In general I'd recommend against calling create_engine from outside views.

user_328460

The only connection to python-telegram-bot that I can think of is threads. PTB uses the following threads:

  • main thread where you call updater.start_polling/webhook() and updater.idle()
  • One thread for fetching updates (started by updater.start_polling/webhook())
  • One thread for processing updates (started by Dispatcher.start, calling dispatcher.process_update)
  • worker threads for run_async handlers - by default that's 4 workers, may be customized by the workers argument of Updater/Dispatcher
  • 1 thread for each job in the JobQueue

If a new connection is created in each thread, then you might have to think about where you call create_engine or so.

Disclaimer: I'm currently the maintainerof python-telegram-bot

user_106069620
Related