Query caching issue

Thread view:

flarosa - 28 Aug 2007 18:43 GMT

Hi,

My application is a Java program using the Spring JDBC framework. For
a connection pool, I'm using the
org.apache.commons.dbcp.BasicDataSource class.

I have several complex queries that take 500-1000 milliseconds to run.
When I run these queries within my application, they take about the
same time to run no matter when I run them, even if I run the same
query several times in a row.

However, if I run the query from within the MySQL query browser, I
notice a different behavior. The first time I run I get the normal
execution time, but if I run again, I get a much shorter time. I
presume that this is due to the server caching the results. I do have
query caching enabled on the server. I can reproduce the behavior of
the Java application if I add the SQL_NO_CACHE hint to the query.

I'm wondering what if anything I can do to get the caching behavior to
appear in my Java application. Obviously there are significant
differences between executing a query in Java and running it in the
query browser. For one, the Java application is using a prepared query
whereas the query browser has the arguments expressed as literals. I
did enable prepared statement pooling in BasicDataSource by setting
poolPreparedStatements=true, but this doesn't seem to have much
effect.

Thanks,
Frank

Reply to this Message

Roedy Green - 28 Aug 2007 21:28 GMT

> I
>did enable prepared statement pooling in BasicDataSource by setting
>poolPreparedStatements=true, but this doesn't seem to have much
>effect.

Just some obvious "did you remember to plug it in" things:

1. are you sure you created the connection once and reused it?

2. did you use PreparedStatement and reuse it?

3. Did you experiment with any sort of close methods? Perhaps the
trick is NOT closing something.

Prepare proof you did these things. In the process the problem may
surface.

Signature

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

Reply to this Message

flarosa - 28 Aug 2007 21:40 GMT

Thanks for the reply.

I use Spring JDBC for all my database activity. I do not manage
connections or statements myself nor do I close anything myself. The
framework does these things.

On Aug 28, 3:28 pm, Roedy Green <see_webs...@mindprod.com.invalid>
wrote:

> > I
> >did enable prepared statement pooling in BasicDataSource by setting
[quoted text clipped - 15 lines]
> Roedy Green Canadian Mind Products
> The Java Glossaryhttp://mindprod.com

Reply to this Message

Good Man - 28 Aug 2007 21:58 GMT

flarosa <frank@franklarosa.com> wrote in news:1188323039.384917.117610
@y42g2000hsy.googlegroups.com:

> I'm wondering what if anything I can do to get the caching behavior to
> appear in my Java application. Obviously there are significant
[quoted text clipped - 4 lines]
> poolPreparedStatements=true, but this doesn't seem to have much
> effect.

Hi, I have zero experience with Java, but as far as I know, setting MySQL
to use the query cache means that it's on, period, regardless of the
application/language interacting with the database.

Of course, you'll only notice the cache speed when submitting the EXACT
query more than one time, providing no updates have been made to tables
that you're querying...

Also, ultimately MySQL chooses what queries it will cache partially based
on init settings... have you tried playing with query cache variables in
my.cnf ?

Also, MySQL Administrator gives you a peek into the cache being used/unused
in real time... http://www.mysql.com/products/tools/administrator/

Reply to this Message

flarosa - 28 Aug 2007 22:29 GMT

Thanks for the tip. The hit rate on my query cache is zero. I think I
see why. This statement appears at the very end of the query cache
discussion in the MySQL manual:

"No query that uses bind variables can be reused."

All my queries use bind variables -- I've been told for years that you
should always do prepared queries with bind variables instead of
sending your query as a literal string.

This sucks - what can I do?

Frank

> flarosa <fr...@franklarosa.com> wrote in news:1188323039.384917.117610
> @y42g2000hsy.googlegroups.com:
[quoted text clipped - 22 lines]
> Also, MySQL Administrator gives you a peek into the cache being used/unused
> in real time...http://www.mysql.com/products/tools/administrator/

Reply to this Message

Donkey Hot - 29 Aug 2007 00:01 GMT

> Thanks for the tip. The hit rate on my query cache is zero. I think I
> see why. This statement appears at the very end of the query cache
[quoted text clipped - 7 lines]
>
> This sucks - what can I do?

Change MySQL to Oracle. It can reuse queries with bind variables. At least
in C/OCI level.

But this switch maybe is not possible, then you have to cope with MySQL's
limitations.

Reply to this Message

Lew - 29 Aug 2007 01:01 GMT

>> Thanks for the tip. The hit rate on my query cache is zero. I think I
>> see why. This statement appears at the very end of the query cache
[quoted text clipped - 13 lines]
> But this switch maybe is not possible, then you have to cope with MySQL's
> limitations.

PostgreSQL will use prepared statements:
<http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html>
<http://jdbc.postgresql.org/documentation/82/server-prepare.html>

Signature

Lew

Reply to this Message

motion musso: aka sathia - 29 Aug 2007 00:04 GMT

> Hi,
>
[quoted text clipped - 10 lines]
> notice a different behavior. The first time I run I get the normal
> execution time, but if I run again, I get a much shorter time. I

be sure to send the same exact query, no spaces, tab, break line and so on.

Signature

there is no real life, only AFK

Reply to this Message

Query caching issue

Free Magazines