pool(size) prepared statements adjustable?

[Former Member]

Hi,

As i understand the overall poolsize of the prepared statements is 65535 + (number of connections * 20). Is it possible to enlarge this by for instance adjusting the pagesize, now 4096?

Connected users: 2023 select db_property('prepstmt') returns 90346. pagesize 4096

SQL Anywhere 12.0.1.4403 Windows2012R2

[Breck_Carter]

It appears the maximum is not adjustable: Number of concurrent statements per database server 20 x number-of-database-connections + 65534

( 2023 * 20 ) + 65534 = 105994... so is that a problem, if you're only using 90346?

Is it possible you have some runaway connections that are preparing zillions of single-use statements and not freeing anything?

What is the max_statement_count option set to? (the default is 50)

Are all 2023 connections active? or are many connections asleep on the couch, bloated up with prepared statements 🙂

[Former Member]

Hi Breck,

Thanks a lot for your input.

The application is a softvelocity clarion 9.1 windows application. Connection DB by ODBC, we have no idle time mechanism, no max statement/cursor count. The number of users has grown. I cannot find a logical way to have influence. It seems opening and closing a bunch of files starting the application has a positive effect, but do not have this in production yet. I found out that opening an application window generates eg 100 prepared statements. Closing this window drops (most) of these statements and opening the same window for the 2nd time only generates a few prepared statements for that connection.

Is it possible you have some runaway connections that are preparing >zillions of single-use statements and not freeing anything?

I don't think so. It seems just a lot of prepared statement are generated by the application.

max_statement_count

It is set to 0, default 50 is too low. If I take a look at the connections most of them are below 250, but some threads just need almost 250..

Are all 2023 connections active

Not all the time. From the main window people start threads (window), and leave these open while working in other threads (windows). Not all threads get a new connection, but 1 user has more connections to the DB.

If i only could just clear prepared statements of some connections using a scheduled stored function, of enlarge the pool somehow. Disconnecting connectiosn will result in application errors, and thus angry users.

Regards,

marc