11

I read that MySQL does not support server side query plan caching. So if i want to use PreparedStatements for performance benefits, what i can do is enable statement caching in JDBC Connection. So as per the docs it will enable caching of prepared statements on per connection basis.

What is the performance gain of PreparedStatement caching by JDBC connection compared to if MySQL had server side query plan caching ? So if a PreparedStatement is indeed found in the physical connection's cache, does it mean that when it gets to mysql server, mysql would not run query optimizaton on it and will be able to directly execute it ?

Should i even use Statement caching at JDBC connection level while using MySQL as my database ? I am using Hikari database connection pool with Mysql JDBC connector.

snegi
  • 636
  • 1
  • 6
  • 22
  • what versions of MySQL and JDBC are you using? – emecas Feb 12 '14 at 01:33
  • https://dev.mysql.com/doc/refman/5.7/en/statement-caching.html – Anthony Accioly Feb 12 '14 at 01:37
  • @emecas I am using mysql-connector-java version 5.1.18. MySQL version is 5.7 as i intend to use InnoDB Cache plugin that came up with mysql in mysql version 5.7 – snegi Feb 12 '14 at 01:42
  • @AnthonyAccioly : Thanks for the link. But the statement caching is still per connection basis. Does it make things any better than doing statement caching at JDBC level ? – snegi Feb 12 '14 at 01:45
  • You can do both :). Plus your connection pool will make "per connection" cache worth it. – Anthony Accioly Feb 12 '14 at 05:30

4 Answers4

10

Yes, caching won't hurt if you know what you are doing. Combining client side cache with server side cache wilds performance benefits if you reuse prepared statements as they are supposed to (a lot of people forget about the most important part :D). Just set Connector/J properties properties correctly: 

cachePrepStmts=true&useServerPrepStmts=true

While I'm not a big fan of micro benchmarks, here is one to back my statements up (lame pun intended). The interesting part about the benchmark is that it shows that enabling server side caching may actually slow things down without some client side caching, but with both layers of cache enabled as well as the proper reuse of prepared statements you may actually get a good speed up.

Anthony Accioly
  • 21,918
  • 9
  • 70
  • 118
8

One of the authors of HikariCP here. See the HikariCP wiki for how to properly configure MySQL for prepared statement caching. Prepared statement caching can dramatically speed up SQL. Additionally, it can avoid SQL-injection attacks on your code that would otherwise succeed if you concatenate user-provided input into SQL ordinary statements. Never compose a SQL string that contains user-provided input. Always use prepared statements, and set user-provided input as replacement values.

brettw
  • 10,664
  • 2
  • 42
  • 59
  • 1
    Hi brettw. Thanks for the wonderful projet. BTW, benchmarks agains't c3p0 looks impressive. Just one thing, why do you guys are not recommending `useServerPrepStmts=true` for MySQL? it can also squeeze some extra performance. – Anthony Accioly Feb 13 '14 at 18:13
  • 1
    Anthony, I was unaware of ``userServerPrepStmts``. I will add it to the documentation, thanks! – brettw Feb 13 '14 at 23:00
  • 1
    Some simple tests reveal that `userServerPrepStmts = false` is faster. The [answer](https://stackoverflow.com/a/65608774/) of Vlad Mihalcea illustrates it too. – bjmi May 25 '21 at 12:00
4

There are two properties you could set:

  • useServerPrepStmts - that enables server-side prepared statements since, by default, prepared statements are emulated o the client-side.
  • cachePrepStmts - that enables the statement caching mechanism

Performance results

For client-side statements, the throughput is improved by enabling the cachePrepStmts setting, as illustrated by the following chart:

Client-side statement caching

And, for server-side statements, the throughput is also improved by enabling the cachePrepStmts property:

Server-side statement caching

So, the Statement Caching mechanism works for both client-side and server-side prepared statements as well.

When testing on both MySQL 8.0.22 and 8.0.18, using single-statement and multiple-statement transactions, client-side prepared statements performed better than server-side prepared statements.

Therfeofre, the following configuration options seem to yield the best results:

useServerPrepStmts=false
cachePrepStmts=true
prepStmtCacheSize=500
prepStmtCacheSqlLimit=1024

The last two properties were set so that we increase the cache limits as the default values are way too low for many data-driven applications.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
2

Other makes and models of table servers will give you more performance benefits from JDBC prepared statements than will MySQL. Oracle, for example, can reuse execution plans.

But you should still use prepared statements in JDBC. There are plenty of reasons to use them, including the injection resistance that comes from binding your variables.

O. Jones
  • 103,626
  • 17
  • 118
  • 172