Executing MySQL SELECT * query in parallel

Question

I have a multithreaded application that periodically fetches the whole content of the MySQL table (with SELECT * FROM query) The application is written in python, uses threading module to multithreading and uses mysql-python (mysqldb) as MySQL driver (using mysqlalchemy as a wrapper produces similar results). I use InnoDB engine for my MySQL database.

I wrote a simple test to check the performance of SELECT * query in parallel and discovered that all of those queries are implemented sequentially.

I explicitly set the ISOLATION LEVEL to READ UNCOMMITTED, although it does not seem to help with performance.

The code snipper making the DB call is below:


@performance.profile()
def test_select_all_raw_sql(conn_pool, queue):
    ''' 
    conn_pool - connection pool to get mysql connection from
    queue - task queue
    '''
    query = '''SELECT * FROM table'''
    try:
        conn = conn_pool.connect()
        cursor = conn.cursor()
        cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
        # execute until the queue is empty (Queue.Empty is thrown)
        while True:
            id = queue.get_nowait()
            cursor.execute(query)
            result = cursor.fetchall()
    except Queue.Empty:
            pass
    finally:
        cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ")
        conn.close()

Am I right expecting this query to be executed in parallel? If yes, how can I implement that in python?

I don't fully understand why you care how the database executes the query. The results have to be serialized to be returned to the application. — Gordon Linoff, Feb 07 '14 at 05:14
Well I care because if database locks the whole table for the execution, it affects all other queries executing in parallel and therefore affects the performance of the service using the database — Sergey, Feb 07 '14 at 05:28

score 1 · Answer 1 · answered Jan 30 '20 at 23:58

MySQL allows many connections from a single user or many users. Within that one connection, it uses at most one CPU core and does one SQL statement at a time.

A "transaction" can be composed of multiple SQL statements while the transaction is treated as atomically. Consider the classic banking application:

BEGIN;
UPDATE ...  -- decrement from one user's bank balance.
UPDATE ...  -- increment another user's balance.
COMMIT;

Those statements are performed serially (in a single connection); either all of them succeed or all of them fail as a unit ("atomically").

If you need to do things in "parallel", have a client (or clients) that can run multiple threads (or processes) and have each on make its own connection to MySQL.

A minor exception: There are some extra threads 'under the covers' for doing background tasks such as read-ahead or delayed-write or flushing stuff. But this does not give the user a way to "do two things at once" in a single connection.

What I have said here applies to all versions of MySQL/MariaDB and all client packages accessing them.

Executing MySQL SELECT * query in parallel

1 Answers1

Linked