MySQL PreparedStatement Performance Issue Reproduction Report

发表于 更新于 分类于 阅读次数：

MySQL PreparedStatement Performance Issue Reproduction Report

1. Executive Summary

1.1 Issue Description

A severe performance issue was reported when MySQL processes PreparedStatement IN queries with a large number of parameters (50,000) using useServerPrepStmts=true. According to the original report, server-side prepared statements were 190-207 times slower than client-side prepared statements.

1.2 Issue Source

1.3 Root Cause (Original Report)

When MySQL server processes PreparedStatement with large number of parameters:

  1. Excessive memory reallocation: 50,000 parameter replacement operations
  2. String operation complexity: O(SQL length) × number of parameters
  3. Memory fragmentation: Frequent large memory block allocation and deallocation

2. Test Environment

2.1 AWS Resource Information

Resource Configuration
AWS Account 872515255872
Region ap-southeast-1
VPC vpc-084114e405054a5b7

2.2 RDS MySQL 8.0 Instance

Configuration Value
Instance Identifier mysql-prepared-test
Instance Type db.t3.medium (2vCPU, 4GB)
Storage 50GB gp3
Engine Version MySQL 8.0.43
Endpoint mysql-prepared-test.cfsoq6muu0jv.ap-southeast-1.rds.amazonaws.com

2.3 RDS MySQL 5.7 Instance

Configuration Value
Instance Identifier mysql57-prepared-test
Instance Type db.t3.medium (2vCPU, 4GB)
Storage 50GB gp3
Engine Version MySQL 5.7.44-rds.20240408
Endpoint mysql57-prepared-test.cfsoq6muu0jv.ap-southeast-1.rds.amazonaws.com

2.4 EC2 Test Client

Configuration Value
Instance ID i-0b26f3cb781bae059
Instance Type t3.medium
AMI Amazon Linux 2023
Public IP 18.141.185.36
Java Amazon Corretto 17
Maven 3.8.4

3. Test Methodology

3.1 Test Code

1
2
3
4
git clone https://github.com/plantegg/MySQLPrepared.git
cd MySQLPrepared
./run_test.sh --host <mysql-host> --port 3306 --database test \
    --user admin --password '<password>' --rounds 3

3.2 Test Parameters

Parameter Value
IN query parameter count 50,000
Parameter type 15-digit large integers
SQL length (spaces=128) ~6.5MB
Test rounds 3

3.3 AWS CLI Commands

Create RDS MySQL 8.0 Instance

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
aws rds create-db-instance \
    --profile default --region ap-southeast-1 \
    --db-instance-identifier mysql-prepared-test \
    --db-instance-class db.t3.medium \
    --engine mysql \
    --engine-version 8.0 \
    --master-username admin \
    --master-user-password '<password>' \
    --allocated-storage 50 \
    --storage-type gp3 \
    --db-subnet-group-name mysql-benchmark-subnet-group \
    --vpc-security-group-ids sg-01541b078e0b483d0 \
    --no-multi-az \
    --publicly-accessible \
    --backup-retention-period 0 \
    --no-auto-minor-version-upgrade

Create RDS MySQL 5.7 Instance

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
aws rds create-db-instance \
    --profile default --region ap-southeast-1 \
    --db-instance-identifier mysql57-prepared-test \
    --db-instance-class db.t3.medium \
    --engine mysql \
    --engine-version 5.7.44-rds.20240408 \
    --master-username admin \
    --master-user-password '<password>' \
    --allocated-storage 50 \
    --storage-type gp3 \
    --db-subnet-group-name mysql-benchmark-subnet-group \
    --vpc-security-group-ids sg-01541b078e0b483d0 \
    --no-multi-az \
    --publicly-accessible \
    --backup-retention-period 0 \
    --no-auto-minor-version-upgrade

Create EC2 Test Instance

1
2
3
4
5
6
7
8
aws ec2 run-instances \
    --profile default --region ap-southeast-1 \
    --image-id ami-0d1c0ec9903f7b01f \
    --instance-type t3.medium \
    --key-name renxijun-ec2-key \
    --security-group-ids sg-0706d5fbe75649370 \
    --subnet-id subnet-09d864b724dce6434 \
    --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=mysql-prepared-test-ec2}]'

4. Test Results

4.1 MySQL 8.0.43 Test Results

Compact Format (spaces=0, SQL length ~100KB)

Configuration Average Execution Time Com_stmt_prepare
useServerPrepStmts=false 129.6ms 0
useServerPrepStmts=true 115.6ms 1
Performance Difference true is 1.1x faster -

With Spaces Format (spaces=128, SQL length ~6.5MB)

Configuration Average Execution Time Com_stmt_prepare
useServerPrepStmts=false 400.7ms 0
useServerPrepStmts=true 101.0ms 1
Performance Difference true is 4.0x faster -

4.2 MySQL 5.7.44 Test Results

With Spaces Format (spaces=128, SQL length ~6.5MB)

Configuration Average Execution Time Com_stmt_prepare
useServerPrepStmts=false 600.0ms 0
useServerPrepStmts=true 90.0ms 1
Performance Difference true is 6.7x faster -

4.3 Comparison with Original Report

Version Original Report (5.7.29) This Test (5.7.44) This Test (8.0.43)
false avg time ~52ms 600ms 400.7ms
true avg time ~27,839ms 90ms 101ms
Performance Diff true 535x slower true 6.7x faster true 4.0x faster

5. Conclusions

5.1 Reproduction Results

The performance issue described in the original report could NOT be reproduced on MySQL 5.7.44 and 8.0.43.

Test results show:

  • On both versions, useServerPrepStmts=true is actually faster than false
  • MySQL 5.7.44: Server-side prepared statements are 6.7x faster
  • MySQL 8.0.43: Server-side prepared statements are 4.0x faster

5.2 Possible Explanations

  1. MySQL Version Differences

    • Original issue was discovered on MySQL 5.7.29
    • This test used MySQL 5.7.44 and 8.0.43
    • The issue may have been fixed in versions after 5.7.29

  2. AWS RDS Optimizations

    • AWS RDS may include specific performance optimizations
    • Behavior may differ from native MySQL installations

  3. JDBC Driver Version

    • Test used mysql-connector-j-8.0.33
    • Driver may have optimized parameter handling logic

5.3 Recommendations

  1. To Reproduce the Original Issue

    • Use MySQL 5.7.29 or earlier versions
    • Use native MySQL instead of AWS RDS

  2. Production Environment Recommendations

    • On newer MySQL versions, useServerPrepStmts=true performs better
    • Conduct performance testing with actual business scenarios
    • For IN queries with large parameter counts, consider batch queries or temporary table approaches

6. Appendix

6.1 Test Logs

MySQL 8.0.43 Complete Output

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
=== MySQL PreparedStatement Performance Comparison Test ===
Connection: mysql-prepared-test.cfsoq6muu0jv.ap-southeast-1.rds.amazonaws.com:3306/test
Parameter count: 50000
Test rounds: 3

Configuration: useServerPrepStmts=false
Parameter spaces: 128
SQL length: 6500023 characters
Executing query 1...
Query 1: 571ms (0 records returned)
Executing query 2...
Query 2: 317ms (0 records returned)
Executing query 3...
Query 3: 314ms (0 records returned)

=== useServerPrepStmts=false Test Results ===
Total execution time: 1202ms
Average execution time: 400.7ms
Records returned: 0
Com_stmt_prepare: 0 -> 0

Configuration: useServerPrepStmts=true
Parameter spaces: 128
SQL length: 6500023 characters
Executing query 1...
Query 1: 138ms (0 records returned)
Executing query 2...
Query 2: 92ms (0 records returned)
Executing query 3...
Query 3: 73ms (0 records returned)

=== useServerPrepStmts=true Test Results ===
Total execution time: 303ms
Average execution time: 101.0ms
Records returned: 0
Com_stmt_prepare: 0 -> 1

MySQL 5.7.44 Complete Output

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
=== MySQL PreparedStatement Performance Comparison Test ===
Connection: mysql57-prepared-test.cfsoq6muu0jv.ap-southeast-1.rds.amazonaws.com:3306/test
Parameter count: 50000
Test rounds: 3

Configuration: useServerPrepStmts=false
Parameter spaces: 128
SQL length: 6500023 characters
Executing query 1...
Query 1: 639ms (0 records returned)
Executing query 2...
Query 2: 681ms (0 records returned)
Executing query 3...
Query 3: 480ms (0 records returned)

=== useServerPrepStmts=false Test Results ===
Total execution time: 1800ms
Average execution time: 600.0ms
Records returned: 0
Com_stmt_prepare: 0 -> 0

Configuration: useServerPrepStmts=true
Parameter spaces: 128
SQL length: 6500023 characters
Executing query 1...
Query 1: 117ms (0 records returned)
Executing query 2...
Query 2: 77ms (0 records returned)
Executing query 3...
Query 3: 76ms (0 records returned)

=== useServerPrepStmts=true Test Results ===
Total execution time: 270ms
Average execution time: 90.0ms
Records returned: 0
Com_stmt_prepare: 0 -> 1

6.2 Original Issue Stack Trace (from original report)

The original report included a pstack analysis showing the server spending excessive time in string replacement operations:

1
2
3
4
5
6
7
8
9
10
11
Thread 1 (Thread 0x7feeb8043640 (LWP 2508284)):
#0 __memmove_avx512_unaligned_erms () from /lib64/libc.so.6
#1 my_realloc () at mysys/my_malloc.c:112
#2 String::mem_realloc () at sql-common/sql_string.cc:128
#3 String::replace () at sql-common/sql_string.cc:804
#4 String::replace () at sql-common/sql_string.cc:783
#5 Prepared_statement::insert_params () at sql/sql_prepare.cc:924
#6 Prepared_statement::set_parameters () at sql/sql_prepare.cc:3496
#7 Prepared_statement::execute_loop () at sql/sql_prepare.cc:3559
#8 mysqld_stmt_execute () at sql/sql_prepare.cc:2582
...

This stack trace indicates the server was performing extensive memory reallocation and string manipulation operations during parameter substitution.

6.3 References

7. Submission Notes for MySQL Community

7.1 Summary for Bug Report

Title: Performance regression with useServerPrepStmts=true for large IN queries appears to be fixed in MySQL 5.7.44+

Description:
A previously reported performance issue where server-side prepared statements (useServerPrepStmts=true) were significantly slower than client-side prepared statements for IN queries with 50,000 parameters could not be reproduced on MySQL 5.7.44 and 8.0.43.

Original Behavior (MySQL 5.7.29):

  • useServerPrepStmts=true: ~27,839ms average
  • useServerPrepStmts=false: ~52ms average
  • Performance difference: 535x slower with server-side

Current Behavior (MySQL 5.7.44 / 8.0.43):

  • useServerPrepStmts=true: ~90-101ms average
  • useServerPrepStmts=false: ~400-600ms average
  • Performance difference: 4-6.7x faster with server-side

Request:
Could the MySQL team confirm if this issue was intentionally fixed in a specific version? If so, please document the fix in the release notes for user awareness.

Report Generated: 2026-01-12
Test Executor: Kiro CLI
AWS Account: 872515255872