Growing Prepared Statement Objects in MySQL Policy/Session Store

Products

• SITEMINDER

Issue/Introduction

When using the "MySQL Native ODBC Driver" for a SiteMinder Policy Store or Session Store, the number of prepared statement objects in the MySQL "performance_schema" continues to grow indefinitely.

Monitoring the "performance_schema.prepared_statements_instances" table (1) shows unique entries for queries that include timestamp or session data, such as:

• SELECT ... FROM smservercommand4 WHERE smtimestamp > [unique_timestamp]
• UPDATE ss_sessionspec5 SET ... WHERE sessionid = [unique_id]

This behavior occurs even if server-side prepared statements are not explicitly intended, leading to potential memory overhead on the database server.

The following statement is used to count the occurence of these statement from a Policy Store:

    SELECT 
        owner_thread_id, 
        t.processlist_user AS user, 
        t.processlist_host AS host, 
        t.processlist_db AS database_name,
    COUNT(*) AS numero_prepared_stmt
    FROM performance_schema.prepared_statements_instances p
    JOIN performance_schema.threads t ON p.owner_thread_id = t.thread_id
    GROUP BY owner_thread_id, t.processlist_user, t.processlist_host, t.processlist_db;

The result is:

  | owner_thread_id | user   | host               | database_name | numero_prepared_stmt |
  |-----------------+--------+--------------------+---------------+----------------------|
  |         9032163 | smuser | server.example.com | <PolicyStore> |                   78 |
  |         9021668 | smuser | server.example.com | <PolicyStore> |                  106 |

Environment

• Policy Server 12.8 / 12.9 (Windows/Linux)
• Database MySQL 8.x (Oracle Enterprise)
• Driver MySQL Native ODBC Driver

Cause

By default, the MySQL Native ODBC driver may send statements to the server for preparation even if the application (Policy Server) has not explicitly requested a server-side prepared statement for that specific execution (2).

Because SiteMinder queries (like "smservercommand4" polls) frequently change the "WHERE" clause values, the MySQL server treats each unique string as a new prepared statement instance, filling up the "prepared_statements_instances" table.

Resolution

To prevent the driver from creating these objects on the MySQL server, enable "Client-Side Preparation".

1. Open the "ODBC Data Source Administrator" on the Policy Server machine.
2. Select the "System DSN" used for the Policy Store or Session Store.
3. Click "Configure".
4. Navigate to the "Details" > "Cursors/Results" tab (depending on the driver version).
5. Check the box for: "Prepare by default on client in SQLPrepare".
6. Click "OK" and save the configuration.
7. Restart the Policy Server to flush existing connections.

Note:

If using a connection string or "system_odbc.ini" file on Linux, ensure the following parameter is set:

"NO_SSPS=1" (3) (or "PREPONCLIENT=1" (4) depending on specific driver builds).

Additional Information

1. 29.12.6.4 The prepared_statements_instances Table
   
   
2. 15.5 Prepared Statements
   
   
3. 5.2 Connector/ODBC Connection Parameters
   
   
4. What’s new in MariaDB Connector/ODBC 3.2