5 Replies Latest reply on Oct 6, 2005 6:31 AM by Al Caponi

    Mysql query cache

    Nate Drake Newbie

      Hi,

      We are using Jboss with Mysql 4.0.15. Everything runs fine, but we are now looking into tuning Mysql. One thing we've discovered in Mysql's query cache.

      I've successfully got the cache turned on and working when running queries through Mysql's command-line client. When I run our app, none of the queries are getting cached.

      Is it possible to use the query cache with Entity beans? Has anyone gotten it working?

      Thanks!

      Nate

        • 1. Re: Mysql query cache
          Sascha Kulawk Newbie

          Hi,

          no - the query cache should only work in "autocommit" environments - so you only can patch the jdbc driver and not use any transaction in your entity bean....

          • 2. Re: Mysql query cache
            Simon Godden Newbie

            Can someone explain why the autocommit option has any impact on the MySQL query cache?

            Being simplistic - isn't it just a case of issuing a query to the MySQL server, it finding it in its cache, and then using the cached results?

            • 3. Re: Mysql query cache
              Simon Godden Newbie

              If you want to use the query cache with InnoDB tables, you need to install MySQL 4.1 or greater.

              We have done so, and can see that all finder statements are being hit in the query cache now.

              • 4. Re: Mysql query cache
                Al Caponi Newbie

                Hi All,
                I'm facing the same problem - Queries from JBoss app are not inserted into MySQL query cache - and neither MySQL 4.1.14 nor 5.0.13RC could solve it.
                Do EJB transactions have any impact on that behaviour? What are the params/factors that affect the use of query cache?

                I've got my app running on the following:
                JBoss 3.2.2. (Unable to upgrade right now)
                MySQL 4.0.20d, 4.1.14, 5.0.13RC (Tried all of them)
                mysql-connector-java-3.1.10-bin.jar

                I have switched on the query_cache as follows:

                query_cache_limit=2M
                query_cache_size=32M
                query_cache_type=1

                It's working fine from MySQL-Front.

                Thanks,
                Al

                • 5. Re: Mysql query cache
                  Al Caponi Newbie

                  Hi again,
                  The query cache finally works after I've reverted to
                  mm.mysql-2.0.12-bin.jar.

                  The problem was that the query cache somehow wouldn't work within
                  transactions with mysql-connector-java-3.1.10-bin.jar. Anyone care to dig
                  further?

                  Here is one working combination:
                  Jboss 3.2.2 with transactions
                  JDBC: mm.mysql-2.0.12-bin.jar (3.1.10 doesn't work for me)
                  MySQL: 4.1.14 (Haven't tried 5.0)

                  Extract from http://dev.mysql.com/doc/mysql/en/query-cache-how.html
                  ' In MySQL 4.0, the query cache is disabled within transactions (it does not
                  return results). Beginning with MySQL 4.1.1, the query cache also works
                  within transactions when using InnoDB tables (it uses the table version
                  number to detect whether or not its contents are still current).'

                  Regards,
                  Al