Best Practices for Handling Prepared Statements in a Proxied Percona XtraDB Cluster Setup

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi there,

We’re running a Java application that connects to a Percona XtraDB Cluster (v8). The DB cluster is proxied by HAProxy which is configured to keep connections on the same backend node as much as possible - we don’t do any load balancing or read/write split. The application uses a connection pool and relies on server-side prepared statements.

During backend node failover events, we’ve observed that prepared statements don’t typically survive the transition, leading to session-level issues that typically require an application restart to recover.

We’re aware that client-side prepared statements can avoid this problem, but switching away from server-side statements isn’t currently feasible.

I’m looking for general guidance or best practices on:

  1. How to proxy a Percona XtraDB Cluster in a way that’s compatible with server-side prepared statements.

  2. Whether HAProxy can be configured to better support session persistence or graceful failover in this context.

  3. Whether ProxySQL or other proxy solutions offer better handling of session state and prepared statements.

  4. Any Percona-specific recommendations or features that help mitigate this issue.

Would really appreciate any insights or real-world experiences from others running similar setups.

Thanks in advance,
Neil

2

Hello @Neil_Billett,
Server-side prepared statements are created/stored in memory to the session (ie: connection) which created it. Thus they are a session-level isolated construct. It is not possible for anything in the protocol to “move” a prepared statement between servers in the event of a node failure because that statement only existed in the memory of the failed node.

There isn’t because of the above.

This can’t happen because sessions are in-memory constructs located on specific mysql servers. You can’t “move” a session to another mysql server. HAProxy can move a connection, but that establishes a new session on the new node.

While ProxySQL does manage connections, as above, prepared statements are not part of the connection, they are stored on the node. When a node fails, proxysql creates a new backend connection to a failover node, and then reconnects the frontend connection to this connection. While the frontend session is the same, the backend is now different/new.

Customers doing 20,000+ qps don’t use prepared statements. In my observed experience, prepared statements don’t really provide much performance improvement, if that’s your reasoning for using them.

3

Thanks Matthew, as always, for the helpful response.

I think you’ve set me straight on a few grey areas so I’ll go away and have a think.

best regards,

Neil