Reaching max_prepared_stmt_count

[badr-ghatasheh]

I'm using Mariaex (0.7.8) with Ecto (2.0.5) in the following setup: 3 Servers -> 200 connections pool size per server -> 600 DB connections in total

Under moderate traffic (~5-6k qpm), I'm getting those errors in production:

(Mariaex.Error) (1461): Can't create more than max_prepared_stmt_count statements (current value: 16382)

I'm assuming this will also increase if I decided to add a new server to the setup.

Right now I doubled the max value, things are working fine, but this is clearly not the solution I'm looking for

[josevalim]

Hrm, why are you using a pool size of 200 per server? For a traffic of 6k qpm, 20 connections (the default) should be more than enough, unless you have really long running queries. If not that, increasing the connection pool will make things slower, as the chance of sharing a cache or having stale connections increase.

If you really believe that you need 200 connections per server, then you do want to increase the limit of prepared statements, because prepared statements are per connection so the more connections you have, the biggest the number of prepared statements will be required.

We could also introduce a mechanism to disable prepared statements but it does not look like the way to go in your case.

[badr-ghatasheh]

@josevalim I'm expecting 10-15 times the throughput once the system is fully rolled out, is there a proper approach for determining the appropriate pool size?

[josevalim]

@badr-ghatasheh you would have to benchmark and simulate the load you are expecting. Alternatively you can monitor the queue time, which Ecto does provide in its logs, and see if it is increasing. But if you are performing regular queries, the default of 20 should be enough.

6000 qpm (or even 10 times that) is relatively low traffic. We have run load testing on systems that would have higher throughput than that, such as 4000req/s on a single server, and it was totally fine with a pool of 20. I have never heard of a pool of 200 connections.

[badr-ghatasheh]

I'll downsize the pool and check the results.

[badr-ghatasheh]

@josevalim dropping the pool size did both resolve the issue and relief the DB, queries throughput went down from ~630 query/s to ~95 query/s, so did CPU utilization.

Thanks a lot, closing the issue.