Benchmarking MySQL drivers (Python 3.4)

bench.py

from __future__ import print_function
import time

def query_10k(cur):
    t = time.time()
    for _ in range(10000):
        cur.execute("SELECT 1,2,3,4,5")
        res = cur.fetchall()
        assert len(res) == 1
        assert res[0] == (1,2,3,4,5)
    return time.time() - t


def mysql_connector_python():
    import mysql.connector
    conn = mysql.connector.connect(user='root', host='localhost')
    print("MySQL Connector/Python:", query_10k(conn.cursor()), "[sec]")


def mysqlclient():
    import MySQLdb
    conn = MySQLdb.connect(user='root', host='localhost')
    print("mysqlclient:", query_10k(conn.cursor()), "[sec]")


def pymysql():
    import pymysql
    conn = pymysql.connect(user='root', host='localhost')
    print("PyMySQL:", query_10k(conn.cursor()), "[sec]")


for _ in range(10):  # for PyPy warmup
    mysql_connector_python()
    mysqlclient()
    pymysql()

result-CPython 3.4.1.txt

MySQL Connector/Python: 4.554934978485107 [sec]
mysqlclient: 0.8555710315704346 [sec]
PyMySQL: 5.129631996154785 [sec]

result-PyPy.txt

MySQL Connector/Python: 2.88878798485 [sec]
mysqlclient: 3.7735979557 [sec]
PyMySQL: 2.70332789421 [sec]
MySQL Connector/Python: 1.64176392555 [sec]
mysqlclient: 3.87362599373 [sec]
PyMySQL: 1.43844795227 [sec]
MySQL Connector/Python: 2.09882998466 [sec]
mysqlclient: 3.89048695564 [sec]
PyMySQL: 1.41811800003 [sec]
MySQL Connector/Python: 2.10359406471 [sec]
mysqlclient: 3.88971209526 [sec]
PyMySQL: 1.43477678299 [sec]
MySQL Connector/Python: 1.74524617195 [sec]
mysqlclient: 3.88212299347 [sec]
PyMySQL: 1.43231987953 [sec]
MySQL Connector/Python: 1.61375522614 [sec]
mysqlclient: 3.86587405205 [sec]
PyMySQL: 1.39657282829 [sec]
MySQL Connector/Python: 1.55139803886 [sec]
mysqlclient: 3.87236499786 [sec]
PyMySQL: 1.41039204597 [sec]
MySQL Connector/Python: 1.6928999424 [sec]
mysqlclient: 3.94669413567 [sec]
PyMySQL: 1.40507411957 [sec]
MySQL Connector/Python: 1.91752696037 [sec]
mysqlclient: 4.04861402512 [sec]
PyMySQL: 1.46230983734 [sec]
MySQL Connector/Python: 1.87104988098 [sec]
mysqlclient: 4.25610113144 [sec]
PyMySQL: 1.37460494041 [sec]

On May 4th, 2022; I have tested the performance of the following three MySQL libraries for simple repetitive queries with CPython 3.9.12 :
mysql-connector-python==8.0.29
mysqlclient==2.1.0
PyMySQL==1.0.2

import time

def query_10k(cur):
    t = time.time()
    for _ in range(10000):
        cur.execute("SELECT * FROM kafka_trial.students")
        res = cur.fetchall()
        assert len(res) == 52
    return time.time() - t


def mysql_connector_python():
    import mysql.connector
    conn = mysql.connector.connect(user='root', host='localhost', password='root')
    print("MySQL Connector/Python:", query_10k(conn.cursor()), "[sec]")


def mysqlclient():
    import MySQLdb
    conn = MySQLdb.connect(user='root', host='localhost', password='root')
    print("mysqlclient:", query_10k(conn.cursor()), "[sec]")


def pymysql():
    import pymysql
    conn = pymysql.connect(user='root', host='localhost', password='root')
    print("PyMySQL:", query_10k(conn.cursor()), "[sec]")

for _ in range(3):
    mysql_connector_python()
    mysqlclient()
    pymysql()

Results:

MySQL Connector/Python: 11.890755891799927 [sec]
mysqlclient: 2.6362569332122803 [sec]
PyMySQL: 10.247495889663696 [sec]
MySQL Connector/Python: 11.939946174621582 [sec]
mysqlclient: 2.6375508308410645 [sec]
PyMySQL: 10.155224561691284 [sec]
MySQL Connector/Python: 11.910897731781006 [sec]
mysqlclient: 2.6389763355255127 [sec]
PyMySQL: 9.95967960357666 [sec]

But if you benchmark the above three libraries in real-world (Querying data from AWS RDS), the results are approximately the same:

--100 iterations--

MySQL Connector/Python: 10.35865306854248 [sec]
mysqlclient: 10.370359659194946 [sec]
PyMySQL: 10.42400574684143 [sec]
MySQL Connector/Python: 10.63708209991455 [sec]
mysqlclient: 10.592255592346191 [sec]
PyMySQL: 10.330322027206421 [sec]
MySQL Connector/Python: 10.474179983139038 [sec]
mysqlclient: 10.67713451385498 [sec]
PyMySQL: 10.323721647262573 [sec]

@tkanhe do you know why querying from AWS RDS output similar result? Is it because of bottleneck on RDS side or something?

Yes, it seems to be a bottleneck from the RDS server, and results might be getting amplified because our rds servers are not in the local zone/country (causing higher latency).

Caching?

Yes, caching occurs when we repeat the same queries multiple times; that is why I should have committed the connection after execution or set autocommit=True.