4 Replies Latest reply on Oct 6, 2005 6:31 AM by alcaponi

    Mysql query cache

    ndrake

      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

          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?

          • 2. Re: Mysql query cache

            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.

            • 3. Re: Mysql query cache
              alcaponi

              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

              • 4. Re: Mysql query cache
                alcaponi

                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