Sagewire Logo

Query caching issue

9 Message(s) by 7 Author(s) originally posted in java databases


From: flarosa Date:   Tuesday, August 28, 2007
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 literal s. I
did enable prepared statement pooling in BasicDataSource by setting
poolPreparedStatements=true, but this does not seem to have much
effect.

Thanks,
Frank


From: Roedy Green Date:   Tuesday, August 28, 2007
On Tue, 28 Aug 2007 10:43:59 -0700, flarosa <frank@xxxxxxxxxxx>
wrote in message , quoted or indirectly quoted someone who said :

I
did enable prepared statement pooling in BasicDataSource by setting
poolPreparedStatements=true, but this does not 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 reuse d it?

2. did you use PreparedStatement and reuse it?

3. Did you experiment with any sort of close methods? Perhaps the
trick isn't closing something.

Prepare proof you did these things. In the process the problem may
surface.
--
Roedy Green Canadian Mind Products
The JAVA Glossary
http://mindprod.com


From: flarosa Date:   Tuesday, August 28, 2007
Thanks for the reply.

I use Spring JDBC for all my database activity. I don't 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...@xxxxxxxxxxx>
wrote in message:
On Tue, 28 Aug 2007 10:43:59 -0700, flarosa <fr...@xxxxxxxxxxx>
wrote in message, quoted or indirectly quoted someone who said :
> I
>did enable prepared statement pooling in BasicDataSource by setting
>poolPreparedStatements=true, but this does not 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 isn't closing something.
Prepare proof you did these things. In the process the problem may
surface.
--
Roedy Green Canadian Mind Products


> The JAVA Glossaryhttp://mindprod.com


From: Good Man Date:   Tuesday, August 28, 2007
wrote in message in
@xxxxxxxxxxx:

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 does not 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 will 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'll 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/


From: flarosa Date:   Tuesday, August 28, 2007
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 have 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

wrote in message:
wrote in message in
@xxxxxxxxxxx:
> 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 does not 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 will 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'll 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/


From: Donkey Hot Date:   Tuesday, August 28, 2007
wrote in message in


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 have 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?



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

But this switch maybe isn't possible, then you've to cope with MySQL's
limitations.


From: motion Date:   Tuesday, August 28, 2007
wrote in message:

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



be sure to send the same exact query, no spaces, tab, break line and so on.--
there is no real life, only AFK


From: Lew Date:   Tuesday, August 28, 2007
wrote in message:
wrote in message in

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 have 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?

Change MySQL to Oracle. It can reuse queries with bind variables. At least
in C/OCI level.
But this switch maybe isn't possible, then you've 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>

--
Lew


From: lark Date:   Tuesday, August 28, 2007
== Quote from motion musso: aka sathia (sathia.musso@xxxxxxxxxxx)'s article
wrote in message:
> 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
be sure to send the same exact query, no spaces, tab, break line and so on.Maybe it is the class that is messing you up. there should be a bunch of other
classes you can test on.


--
POST BY: lark with PHP News Reader



Next Message: [ANN] SQLfX Beta


Blogs related to Query caching issue

A Perfect Guide for MYSQL and Security - Creating the Best Linux ...
E. Query cache: first you must on query cache by setting query-cache-type=1 and query-cache-size can ideally be around 15-20 MB. F. Tmp_table_size: This variable set the maximum size for a temporary table in memory. ...

1 - Optimizing MySQL
An alternative to implementing an application cache is to use the MySQL query cache. By enabling the query cache, the server handles the details of determining whether a query result can be reused. This simplifies your application. ...

Postfix + MySQL
Tune, based on how busy the MySQL is going to be. There are a few sample cnf files supplied so choose the one that best matches your needs. (IMPORTANT NOTE : MySQL query caching isn’t enabled in any of the sample files except for ...

MySQL’s query cache explained
The MySQL query cache is available in MySQL 4.0, 4.1, 5.0, 5.1, and 6.0 (3.23 has no query cache). The goal of the query cache is to hold result sets that are retrieved repeatedly. Since the data is held in memory, MySQL only feeds the ...

Access denied for user root@localhost using password NO Ive been ...
Hi .. can somebody tell me please if there is a function in mysql like the php strip_tags to remove HTML - Tags ??? THX. kimseong, nope , I will access it through google’s cache. *.mysql.com down, we’re working on it ...

RE: Node corruption - simple testcase - UUID-to-binary issue?
Hi all, After detailed debugging of the corruption issue I 'think' we've drilled down to the likely cause of the issue. In summary it looks like the binary values read back from MySql where the UUID contains 0's is not the same as that ...


Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional