MySQL/MariaDB - Repeatedly Calling Prepared Statement using C API Performance Degradation

Question

I have a somewhat complicated SQL command that uses a dozen or so common table expressions and window functions that I need to call repeatedly. As such, I'm trying to use a prepared statement. It has 10 input bindings, and 6 output bindings.

It touches a few tables, the biggest is a few GB with 15 million records. A single execution of the query takes between 50-200 ms.

When I structure the code as:

    MYSQL_STMT* statement = mysql_stmt_init(db_connection);
    Assert(statement);
    char* sql = ...
    s32 status = mysql_stmt_prepare(statement, sql, sql_length);
    Assert(status == 0);

    // ...setup input binding (10 params)...

    s32 input_bind_result = mysql_stmt_bind_param(statement, input_bind);
    Assert(input_bind_result == 0);
    
    // ...setup output binding (6 columns)..
        
    s32 output_bind_result = mysql_stmt_bind_result(statement, output_bind);
    Assert(output_bind_result == 0);

    for (int i = 0; i < 1000; i++)
    {
        s32 execute_result = mysql_stmt_execute(statement);
        Assert(execute_result == 0);
        
        while (true)
        {
            s32 fetch_result = mysql_stmt_fetch(statement);
            if (fetch_result == MYSQL_NO_DATA)
                break;
            
            Assert(fetch_result == 0);
            
            // ...process row...
            // ...printf(timestamp and query output)...
        }

        // ...increment input parameters...
    }
    
    mysql_stmt_close(statement);

Each time the query is executed it gets slower and slower, and quite quickly at that:

    2021-06-30 21:24:40: 0.996904
    2021-06-30 21:24:40: 0.995356
    2021-06-30 21:24:41: 0.000000
    2021-06-30 21:24:41: 0.000000
    2021-06-30 21:24:41: 0.000000
    2021-06-30 21:24:41: 0.000000
    <hangs indefinitely>

It is hanging at returning from mysql_stmt_execute(). It is not deterministic. If I reset the query cache and restart, it might hang at the 5th query, or the 8th.

By contrast, if I structure the code such as:

    // ...setup input binding (10 params)...
    // ...setup output binding (6 columns)...
    
    for (int i = 0; i < 1000; i++)
    {
        MYSQL_STMT* statement = mysql_stmt_init(db_connection);
        Assert(statement);
        char* sql = ...
        s32 status = mysql_stmt_prepare(statement, sql, sql_length);
        Assert(status == 0);

        s32 input_bind_result = mysql_stmt_bind_param(statement, input_bind);
        Assert(input_bind_result == 0);     
        
        s32 output_bind_result = mysql_stmt_bind_result(statement, output_bind);
        Assert(output_bind_result == 0);
            
        s32 execute_result = mysql_stmt_execute(statement);
        Assert(execute_result == 0);
        
        while (true)
        {
            s32 fetch_result = mysql_stmt_fetch(statement);
            if (fetch_result == MYSQL_NO_DATA)
                break;
            
            Assert(fetch_result == 0);
            
            // ...process row...
            // ...printf(timestamp and query output)...
        }

        // ...increment input parameters...
        
        mysql_stmt_close(statement);
    }

That is, literally creating and releasing the prepared statement every iteration, there is no such degradation and I process each iteration quite quickly:

    2021-06-30 21:26:59: 0.996904
    2021-06-30 21:27:00: 0.995356
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.994969
    2021-06-30 21:27:00: 0.997291
    2021-06-30 21:27:00: 0.997678
    2021-06-30 21:27:00: 0.998452
    2021-06-30 21:27:00: 0.998452
    2021-06-30 21:27:00: 0.998452
    2021-06-30 21:27:00: 0.998839
    2021-06-30 21:27:01: 0.998839
    2021-06-30 21:27:01: 0.998839
    2021-06-30 21:27:01: 0.998454
    2021-06-30 21:27:01: 0.999230
    2021-06-30 21:27:01: 0.999230
    2021-06-30 21:27:01: 0.999230
    2021-06-30 21:27:01: 0.998459
    2021-06-30 21:27:01: 0.998844
    2021-06-30 21:27:01: 0.998844
    2021-06-30 21:27:02: 0.999230
    2021-06-30 21:27:02: 0.999231
    2021-06-30 21:27:02: 0.999231
    2021-06-30 21:27:02: 0.999231
    2021-06-30 21:27:02: 0.999615
    2021-06-30 21:27:02: 1.000000
    2021-06-30 21:27:02: 1.000000
    2021-06-30 21:27:02: 0.999232
    2021-06-30 21:27:02: 0.999617
    2021-06-30 21:27:02: 0.999617
    2021-06-30 21:27:02: 0.999617
    ...continues at this speed for 1000+ iterations...

But this doesn't make a lot of sense to me, and negates one of the main benefits for using prepared statements. Any insight as to what could be causing this?

It feels like some resources aren't being freed, but I have tried adding:

 mysql_stmt_free_result(statement);

or

 mysql_stmt_reset(statement);

at the end of each iteration, but the degradation still persists.

Running MariaDB 10.5.10, mariadb-connector 3.1.13.

score 0 · Answer 1 · answered Jun 30 '21 at 17:32

0

Show us the statement.

If one of the parameters is for OFFSET, be aware that all the "offset" rows must be stepped over.

If the statement is DELETE ... LIMIT, then it could be searching farther and farther to find the desired rows.

ETC!

answered Jun 30 '21 at 17:32

Rick James

135,179
13
127
222

I don't think there is much utility in posting the statement, it's over 150 lines long and probably hard to digest. There is no delete or offset. It is just select statements and a row_number() over partition. The input parameters are on indexed columns filtering the 15M record table for specific date ranges. Further, it is the same statement on each iteration, regardless of whether I prepare the statement in or outside of the loop, so it's not the command is taking longer to process because as mentioned, it processes the 1000+ loops fine, when I prepare/release the statement each time. – 1653020 Jul 01 '21 at 04:25
@1653020 - Is a CTE involved? ROW_NUMBER sounds like it needs to scan more and more rows as the table grows. – Rick James Jul 01 '21 at 05:48
Yes, there are dozen or so CTEs. But I do not believe it is the query itself which is causing it to hang. In the first output posted, when the prepared statement persisted through the entire loop, you can see it hung indefinitely on the 7th query. When I created/destroyed the prepared statement every iteration, the 7th query, despite having the exact same input parameters bound on exactly the same db contents, it executed the same query in less than a second. This is why I think it might be a library thing, either my usage with it or otherwise. – 1653020 Jul 01 '21 at 12:11
`Prepare` is usually a small fraction of the effort. I suspect the query is too complex. To get another feel for that, check the Handler counts: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts – Rick James Jul 01 '21 at 17:50

MySQL/MariaDB - Repeatedly Calling Prepared Statement using C API Performance Degradation

1 Answers1