Is Proxy SQL prepared statements client cache usable?
Владимир Рудев
We have JVM app with connection pool to mysql. We use PreparedStatements heavily since we have not more than 100 different queries. Driver has ability to cache prepared statements, so we gain performance improvement - each statement prepared only once and no sql parsing occurs in future for this query(until connection is closed). From internet I've found that proxysql supports prepared statements, and can return already prepared statement to client without preparing it in mysql(if already prepared before). But I haven't found any information how it behave with client when prepStmtCache=true?
Is it safe to use cachePrepStmts=true on client side with ProxySQL? Or client should prepare statement for every query? Or better not to use prepared statements at all with ProxySQL?
For example client send com_stmt_prepare to proxysql, proxysql takes connection from pool(multiplexing), checks if this statement already prepared and if not - sends command to mysql, then it registers prepared statement in its own cache and returns prepare result to client(prepared statement id, 5 for example). Client caches it in connection to ProxySQL. Then later client decides to use this prepared statement and sends com_stmt_execute(5, params). Is this legal situation? Will ProxySQL use connection to mysql in which this statement was prepared?
It looks like for such possibility ProxySQL should have some kind of mapping <client stmt id, backend_connection -> mysql stmt id>. And if for current chosen connection there is no mapping - it should prepare it first. But I haven't found anything about such mapping existence... and also haven't found any warning that client statement cache should be disabled.