Ballooning of Prepared_stmt_count on MYSQL

[rasberrylasagna]

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

prisma               : 2.17.0
@prisma/client       : 2.17.0
Current platform     : darwin
Query Engine         : query-engine 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.353.0

[janpio]

Interesting, I was not aware there is a limit on the # of prepared statements. After 2.14 we probably changed the usage and behavior of queries. We most probably did not consider this limitation yet, or did not expect it to be relevant.

Is this limit per connection or per server (restart)?
What does the error/crash look like?

[rasberrylasagna]

Interesting, I was not aware there is a limit on the # of prepared statements. After 2.14 we probably changed the usage and behavior of queries. We most probably did not consider this limitation yet, or did not expect it to be relevant.

Is this limit per connection or per server (restart)?
What does the error/crash look like?

Hey there!

I actually had no idea it was a thing either until I ran into this.

It's a per server limit, that can be potentially fixed by raising the limit (a global var in MySQL), but I didn't know if this was a symptom of something bigger and didn't want to band-aid something that may continue getting (much) bigger.

So the "normal" behavior (as of 2.14) was that the prepared statement count rises gradually through use, and quickly resets itself back down to <100 values.

Unfortunately I don't have a great error, but this is a snippet of it.

 INFO - [ERROR_LOG] - Message: Error occurred during query execution:;
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1461, message: 
"Can\'t create more than max_prepared_stmt_count statements (current value: 16382)", state: "42000" })) });

Error behavior:
This would lead to huge drops in coverage as no queries can go through, effectively halting all traffic.

Thanks for looking into this! :)

[janpio]

Ok, it is a per server limit but I think the prepared statements are held by connection - so "rotating" the connections could probably help. You could do so by getting a new PrismaClient instance - depending on your application that might be as simple as restarting, or maybe writing some logic that every x hours a new prisma is created and used (and the old calls prisma.$disconnect() first).

There is nothing really in the 2.15 release notes that pop out to me: https://github.com/prisma/prisma/releases/tag/2.15.0

Can you tell us a bit more about the queries your application is running?
How many connections to the database are open at any time?
How many app servers? Prisma connection_limit (/pool size)? Serverless?

[rasberrylasagna]

Ok, it is a per server limit but I think the prepared statements are held by connection - so "rotating" the connections could probably help. You could do so by getting a new PrismaClient instance - depending on your application that might be as simple as restarting, or maybe writing some logic that every x hours a new prisma is created and used (and the old calls prisma.$disconnect() first).

There is nothing really in the 2.15 release notes that pop out to me: https://github.com/prisma/prisma/releases/tag/2.15.0

Can you tell us a bit more about the queries your application is running?
How many connections to the database are open at any time?
How many app servers? Prisma connection_limit (/pool size)? Serverless?

That's fair. I'm happy to adapt and implement workarounds if the changes here are actually intended / a necessary side effect.

And understood. Before continuing, I'm actually just going to verify and make sure that 2.15 breaks it or if it's a later version to potentially make troubleshooting easier, and report back with findings.

I might have went straight to 2.17 to test a preview feature with groupBy/filter on relationals.

I'll test with 2.15 all the way up to my first confirmed version where I come across this issue.

Thanks again for the receptive help.

[janpio]

These workarounds are of course only meant as temporary fixes to allow you to upgrade your Prisma version - we will obviously still try to understand and fix this as soon as possible. Right now we are a bit lost though as first manual reproductions did not show an increasing prepared statement count.

[rasberrylasagna]

These workarounds are of course only meant as temporary fixes to allow you to upgrade your Prisma version - we will obviously still try to understand and fix this as soon as possible. Right now we are a bit lost though as first manual reproductions did not show an increasing prepared statement count.

Ahh understood. Much appreciated.
The results from my findings is that 2.15 is indeed where the issue starts.

It is a GQL based backend that handles all of the traffic for a production deployment, so it runs about 6 pods that use prisma.

I don't think we do anything special with our prisma deployment, we strictly just use the client feature (no migrate etc) and we use all the basic CRUD functionality, as well as groupBy/aggregate queries. We have used default config for connection limit/pool size (didn't edit it). We also utilize the basic dataloader as implemented here: https://github.com/graphql/dataloader for most of our nested gql resolvers.

Regarding to the things that connect to the DB, there are other things that connect to the database as well, so that bumps the total number up to 10, with between 40-55 simultaneous connections. There are no serverless access to the db.

Regarding reproducibility, for us it doesn't seem to take long to detect the discrepancy from one version to another, but I don't know if this would extend to other deployments.

Thank you!

[Simskii]

We migrated from Prisma 1 to prisma 2.20 yesterday and came across this issue aswell.
We had our app running for about 20 minutes until we hit the prepared statement limit.

However if we downgrade to 2.14 it works like expected and the prepared statements keeps reseting and we do not hit the limit.

[olif]

We have the same issue, the Prepared_stmt_count gets exhausted and reaches the max_prepared_stmt_count limit after a while. The prepared_stmt_count is monotonically increasing which means that this limit will eventually be reached regardless of load and/or number of connections.

From what I can tell, it seems like the connection-pooling did not work in prisma 2.14 which meant that new connections where spawned and old ones closed. When the old connections where closed, the prepared statements belonging to that connection/session where subsequently dropped by MySql.

[janpio]

Thanks, we will look into this soon.

[ericluwj]

Also facing this same issue with prisma 2.21.0 and mysql 5.7

[Simskii]

When is a fix for this planned?
We are currently stuck on 2.14 for the moment.

[pantharshit00]

I am unable to reproduce this with 2.23. I also tried with 2.21.1 and 2.14. I ran a full 15 min load test with k6 and hit all the endpoints from this example: https://github.com/prisma/prisma-examples/tree/latest/typescript/rest-express

Here are the results that I am getting:

Can anyone share a more concrete reproduction?

[olif]

The problem is that prisma is using prepared statements when making the request to the mysql db and never drops them. Mysql has a default limit of about 16000 prepared statements which puts an upper limit on how many different prepared statements are allowed to be used globally for the db engine.

For instance, when querying the /post/:id endpoint the resulting parameterized query is

prisma:query SELECT `db`.`Post`.`id`, `db`.`Post`.`createdAt`, `db`.`Post`.`updatedAt`, `db`.`Post`.`title`, `db`.`Post`.`content`, `db`.`Post`.`published`, `db`.`Post`.`viewCount`, `db`.`Post`.`authorId` FROM `db`.`Post` WHERE `db`.`Post`.`id` = ? LIMIT ? OFFSET ?

and it will be sent by the prisma engine to the database as a prepared statement. If the exact same query is run again but with other parameters the prepared statement will be reused by mysql. However, if the query is slightly modified, for instance by adding a new statement in the WHERE clause, it will be cached by mysql as a new prepared statement.

The prepared statements belongs to the session/connection so if we have multiple instances of the same app and also a connection pool, each session/connection will have their prepared statements cached and these statements will no be dropped until the connection is closed.

This means that the overall number of different queries that an application can have is limited to 16000/(nr of app instances * size of connection pool).

To make things worse, prepared statements will be created for each size of parameters of an IN statement. For instance, this handler:

app.get(`/posts`, async(req, res) => {
  const queryParams = req.query
  const ids = queryParams.ids as string[]
  const post = await prisma.post.findMany({
    where: {
      id: {
        in: ids.map(x => Number(x))
      }
    }
  })

  res.json(post)
})

will issue a new prepared statement for every number of provided ids.

$> curl 'localhost:3000/posts?ids[]=1'

SELECT `db`.`Post`.`id`, `db`.`Post`.`createdAt`, `db`.`Post`.`updatedAt`, `db`.`Post`.`title`, `db`.`Post`.`content`, `db`.`Post`.`published`, `db`.`Post`.`viewCount`, `db`.`Post`.`authorId` FROM `db`.`Post` WHERE `db`.`Post`.`id` IN (?)

Prepared_stmt_count=1

$> curl 'localhost:3000/posts?ids[]=1&ids[]=2'

SELECT `db`.`Post`.`id`, `db`.`Post`.`createdAt`, `db`.`Post`.`updatedAt`, `db`.`Post`.`title`, `db`.`Post`.`content`, `db`.`Post`.`published`, `db`.`Post`.`viewCount`, `db`.`Post`.`authorId` FROM `db`.`Post` WHERE `db`.`Post`.`id` IN (?,?)

Prepared_stmt_count=2

which further reduces the number of queries possible in the application since they are treated as different prepared statements.

As I said earlier, the reason this problem arises in versions of prisma > 2.14 is that the connection pool did not work in earlier versions which resulted in a new connection per request (which then dropped the associated prepared statements).

I don't know if I would classify this as a bug but since we don't have control over which queries are sent as prepared statements or not it has a severe negative impact on our scaling possibilities. Also, since IN clauses also are sent as prepared_statements it is hard to determine an upper bound that is guaranteed to cover the Prepared_stmt_count.

[janpio]

Yes, this is a clear bug as it impacts your ability to use Prisma - and we want you to be able to use Prisma 😄

Your explanation makes a lot of sense, can you help us craft a reproduction app that is optimally set up to reproduce the hitting the limit? We always want to have these before we hand issues like this one over to an Engineer. It will also allow us to test and evaluate quick fixes and workarounds (e.g. a middleware to catch this error, that then instantly cycles the connections somehow and so on). That would be very helpful.

[olif]

I have forked the prisma-examples repo and added repro instructions together with some utilities to demonstrate the issue. The easiest way I came up with was to lower the max_prepared_stmt_count so that you can reproduce the issue on a smaller scale. The main issue is as I said that the prepared statement cache never is released so the only difference by having a larger value on max_prepared_stmt_count is that it will take much longer time until the limit eventually is reached, i.e it is a scaling issue.

https://github.com/Voady/prisma-examples/tree/latest/typescript/rest-express

[janpio]

Thanks, @pantharshit00 will take another look at this reproduction. If he can reproduce, we will try some instant workarounds and kick of the work on fixing this properly (unclear to me how this really would look like to be honest - how do other ORMs do this?).

[pantharshit00]

I wasn't able to reliably reproduce and I was only able to reproduce after I added some concurrency but I still think we should review our cache implementation.

cc @pimeys

[pimeys]

Hey @rasberrylasagna and @olif, could you test the development build prisma@2.25.0-dev.9 to see if the problem still exists. You can try it with default settings, and by adding statement_cache_size=N to the connection string, where N is the preferred maximum number of statements to store in a connection (multiplied by the pool size). Set that to zero to disable it, the default value is for legacy reasons 1000.

[olif]

Hi, sorry for the late response.

I have tested the prisma@2.25.0-dev.9 locally and now it seems to work.

First I tried it with statement_cache_size=20 and connection_limit=1 and the Prepared_stmt_count was kept below 20, then I tried it with statement_cache_size=0 and then no statements where cached. I also did the same with the prisma@2.24.0 version to confirm that the issue was there.

Good work!

[rasberrylasagna]

Confirming this, wasn't able to repro. Thanks again Jan & Prisma Team!

[janpio]

@pimeys did all the work - I just made him take a look at this after your description and analysis. Thanks for that, we made Prisma better for everyone (and actually fixed quite a nasty bug).

Now only to figure out what we do with that crazy previous default of 1000... Does not seem like a good idea in hindsight 🤣 What do you think a reasonable number would be?

[rasberrylasagna]

@pimeys did all the work - I just made him take a look at this after your description and analysis. Thanks for that, we made Prisma better for everyone (and actually fixed quite a nasty bug).

Now only to figure out what we do with that crazy previous default of 1000... Does not seem like a good idea in hindsight 🤣 What do you think a reasonable number would be?

Maybe not ...🤣.

How about the default of mysql_async (32)?

[pimeys]

Looks like this is fixed then :)

[Simskii]

Is this available in 2.25?

[pimeys]

Yes

[omar-dulaimi]

Hey @pimeys
I'm facing the same issue with v2.19
But can't upgrade to 2.25(or 2.26) yet.

What value do you recommend for max_prepared_stmt_count parameter?

[omar-dulaimi]

Also, I don't see this fix part of 2.25 release logs.

[omar-dulaimi]

Now only to figure out what we do with that crazy previous default of 1000... Does not seem like a good idea in hindsight rofl What do you think a reasonable number would be?

ProxySQL allocates 20 prepared statements per connection.

This explanation by it's author might help:
sysown/proxysql#1251 (comment)

[janpio]

2.25.0 added a configuration to change this:

You can try it with default settings, and by adding statement_cache_size=N to the connection string, where N is the preferred maximum number of statements to store in a connection (multiplied by the pool size). Set that to zero to disable it, the default value is for legacy reasons 1000.

We might change the default value via #7727

[thomaschaaf]

I actually believe this is actually caused by #8870

[ssukienn]

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1461, message:
"Can't create more than max_prepared_stmt_count statements (current value: 16382)", state: "42000" })) });

We hit this for the first time during load testing in our architecture. Prisma version 2.29.1.
Setup is something like 4 services * 2 pods * 3 connections in pool.

We will probably increase max value but do you recommend approaching it differently maybe?

EDIT: Sorry, one service was 2.24.1. Might be it, will confirm.
EDIt2: Ok two services were 2.24.1 but another one with 2.29.1 also faced the same problem. Can it be because of bug from the former two even when these are different connections? The max value is server-based so I assume so?

[pimeys]

Mught be this issue... #8870

[homerjam]

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1461, message: "Can't create more than max_prepared_stmt_count statements (current value: 16382)", state: "42000" })) })

I just hit this issue whilst seeding using 3.7.0, unfortunately, it seems to be inconsistent. Is the suggested fix as below (statement_cache_size=0 being pertinent)?

DATABASE_URL='mysql://root@localhost:3306/mydb?pool_timeout=0&statement_cache_size=0'

[cclaflin89]

I'm also having the same max_prepared_stmt_count issue after setting pool_timeout=0 on prisma 3.7.0. Let me set statement_cache_size=0 and see how it works.

[janpio]

• pool_timeout=0 is a way to make sure all the underlying errors have a chance to bubble up and not be swallowed by the timeout.
• statement_cache_size=0 also is the big hammer, completely disabling the statement cache. You should do some math instead to see what value you can set here (per container/pod/server) that will still fit into your overall prepared_stmt_count limit of your MySQL installation.

[chanlito]

We're currently experiencing this issue. Is there a recommended approach to handle this?

[PanMan]

We seem to be also running into this: today and yesterday our db connection "crashed" at the busiest time with the Can’t create more than max_prepared_stmt_count statements error. Is there anything we can do besides lower the statement_cache_size to 500?

[janpio]

That should be enough (if the # of applications/servers using Prisma Client, multiplicated by the configured cache size is lower than the limit). If you are still getting the error, you probably have more applications created prepared statements or something else is weird.

[ygoyal25]

Hi,

We are still facing this issue for prisma version 4.10.1 the prepared_statement_count blows up on production. We have tried setting statement_cache_size value as well but it doesn't seem to work.

[janpio]

Please open a new issue and provide all the information the issue template asks for, so we can hopefully reproduce, debug and fix. Thanks.