Ballooning of Prepared_stmt_count on MYSQL
Problem
Bug description Hey guys, I hope you all are doing well! I'm running prisma @ 2.14 on MySQL, and after updating to and trying multiple versions (2.18, 2.20.1, 2.21.1), I'm running into issues where my Prepared_stmt_count global MySQL variable is increased by a huge factor compared to 2.14, which leads to me hitting the MAX_PREPARED_STATEMENTS limit. It seems like there is a difference in behavior of these prepared statements from 2.14 and onwards. Just to reiterate, this is not a problem on 2.14, and was I wondering if there was anything I could do to get the old behavior back on a newer version. Thank you! How to reproduce I'm running this on a production kube deployment, so I'm unsure what specific queries may be triggering this, but I do know that 2.14 and previous never had this behavior 1. Be on similar env ? 2. Use normally (create/update/delete) (many) Expected behavior Expect the Prepared_stmt_count to stay low, this is the current behavior on 2.14. Actual behavior Prepared statement count balloons, and stays that way for a long enough time to where my set MAX_PREPARED_STMT_COUNT gets triggered and hangs the db. Note that the actual value in prod gets to 64000+. Prisma information Environment & setup - OS: Mac / Ubuntu - Database: MySQL 8.0.21 - Node.js version: 12.16.1 - Prisma version: Tested on 2.17, 2.18, 2.20.x, 2.21.x [code block]
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Mitigate Prepared_stmt_count Ballooning in MySQL with Prisma
The increase in Prepared_stmt_count after upgrading Prisma versions is likely due to changes in how Prisma manages prepared statements. In newer versions, Prisma may be creating more prepared statements for the same queries, leading to an increase in the global Prepared_stmt_count variable in MySQL. This can occur if the connection pooling or statement caching mechanisms have changed, resulting in statements not being reused effectively.
Awaiting Verification
Be the first to verify this fix
- 1
Adjust MySQL Configuration
Increase the MAX_PREPARED_STATEMENTS limit in your MySQL configuration to accommodate the higher count observed in newer Prisma versions. This can be done by modifying the MySQL configuration file (my.cnf) or by executing a SQL command.
sqlSET GLOBAL max_prepared_stmt_count = 100000; - 2
Enable Statement Caching
Enable statement caching in your MySQL server to improve the reuse of prepared statements. This can help reduce the overall count of prepared statements by caching frequently used queries.
ini[mysqld] query_cache_type = 1 query_cache_size = 1048576 - 3
Review Prisma Connection Pooling
Check your Prisma connection pooling settings. Ensure that the connection pool is configured optimally to reuse connections and prepared statements effectively. Adjust the pool size if necessary.
prismadatasource db { provider = "mysql" url = env("DATABASE_URL") pool_size = 10 } - 4
Monitor Prepared Statements
Implement monitoring for the Prepared_stmt_count variable over time to observe its behavior after applying the changes. This will help you determine if the adjustments are effective.
sqlSHOW GLOBAL STATUS LIKE 'Prepared_stmt_count'; - 5
Test with Different Prisma Versions
If the issue persists, consider testing with different minor versions of Prisma to identify if a specific version has better performance regarding prepared statement management.
bashnpm install @prisma/client@2.16.0
Validation
After applying the above steps, monitor the Prepared_stmt_count using the SQL command provided in step 4. Ensure that the count stabilizes and does not exceed the new limit set in step 1. Additionally, perform normal operations to confirm that the application behaves as expected without hitting the MAX_PREPARED_STATEMENTS limit.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep