Opened 3 months ago

Closed 3 months ago

#35702 closed Cleanup/optimization (fixed)

Clarify mysqlclient does not provide connection pooling

Reported by: Hisham Mahmood Owned by: Hisham Mahmood
Component: Documentation Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The mysqlclient driver does not provide connection pooling, we also don't ship it. I think we should clarify this here.

Change History (5)

comment:1 by Simon Charette, 3 months ago

Triage Stage: UnreviewedAccepted

The note about mysqlclient being thread safe and implementing connection pooling was inherited in #23446 (7f089ac2e3e6a7f4b2b41085a37d35e074fad805). The thread safety and connection pooling claims were added for MySQLDB originally but they are certainly not true for either pymysql or mysqlclient.

As pointed out in their docs

The MySQL protocol can not handle multiple threads using the same connection at once. Some earlier versions of MySQLdb utilized locking to achieve a threadsafety of 2. While this is not terribly hard to accomplish using the standard Cursor class (which uses mysql_store_result()), it is complicated by SSCursor (which uses mysql_use_result(); with the latter you must ensure all the rows have been read before another query can be executed. It is further complicated by the addition of transactions, since transactions start when a cursor executes a query, but end when COMMIT or ROLLBACK is executed by the Connection object. Two threads simply cannot share a connection while a transaction is in progress, in addition to not being able to share it during query execution. This excessively complicated the code to the point where it just isn’t worth it.

The general upshot of this is: Don’t share connections between threads. It’s really not worth your effort or mine, and in the end, will probably hurt performance, since the MySQL server runs a separate thread for each connection. You can certainly do things like cache connections in a pool, and give those connections to one thread at a time. If you let two threads use a connection simultaneously, the MySQL client library will probably upchuck and die. You have been warned.

I would remove the thread safety claims as well.

comment:2 by Hisham Mahmood, 3 months ago

Owner: set to Hisham Mahmood
Status: newassigned

The mysqlclient driver is a fork of MySQLdb. It included a reference to a pool module, that was removed. Besides this mention, I don’t think MySQLdb has had pool support either.

comment:3 by Hisham Mahmood, 3 months ago

Has patch: set

comment:4 by Sarah Boyce, 3 months ago

Triage Stage: AcceptedReady for checkin

comment:5 by Sarah Boyce <42296566+sarahboyce@…>, 3 months ago

Resolution: fixed
Status: assignedclosed

In 2b2a2c0e:

Fixed #35702 -- Removed connection pooling note for mysql drivers.

Note: See TracTickets for help on using tickets.
Back to Top