MySQL has a caching mechanism and functionality for the result of executing sql,
it calls "Query Cache", and this function is very useful and it's helpful for improving the database performance. But in our Web System, Query Cache functionality has some problem as follows.
- When using JDBC PreparedStatement, Query Cache doesn't work properly, MySQL Database never use the query cache even though I send the same SQL stetement to the Database.
- when the hit ratio of Query Cache is very low, execution time of query cache functionality is bottlenecked.
About (1) described above, it is correct that MySQL doesn't work fine when using the PreparedStatement . MySQL Documentation says that Server-Side PreparedStatement never use the query cache. Please refer to http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
「Note: The query cache is not used for server-side prepared statements. If you're using server-side prepared statements consider that these statement won't be satisfied by the query cache. 」
But we can avoid this problem if we use Client-Side PreparedStatement instead of Server-Side
PreparedStatement. If we want to use Client-Side PreparedStatement instead of Server-Side in our System, add the "useServerPrepStmts=false" parameter into the JDBC URL when connecting to your database.
jdbcUrl="jdbc:MySQL://hostname:portnumber/database?useServerPrepStmts=false
About (2) described above, performance is porr if the hit ratio of query cache is very low. because mysql database engine will check whether system always caches the result of executing
sql statement or not before running SQL. hence the performance is slower than not using query cache functionality if the hit ratio of query cache is low.
0 件のコメント:
コメントを投稿