Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 25

Displaying posts with tag: query cache (reset)

Measure the impact of MySQL configuration changes with Percona Cloud Tools
+1 Vote Up -0Vote Down

When you make a change to your MySQL configuration in production it would be great to know the impact (a “before and after” type of picture). Some changes are obvious. For many variables proper values can be determined beforehand, i.e. innodb_buffer_pool_size or innodb_log_file_size. However, there is 1 configuration variable which is much less obvious for many people working with MySQL: query_cache.

The idea of query cache is great, however, there are a lot of

  [Read more...]
Performance impact of MySQL query cache on modern hardware
+4 Vote Up -1Vote Down

Recently, Morgan has been writing on deprecating some MySQL features and inspired by that while working on MySQL on POWER, I wondered “What is the impact of the MySQL query cache on modern hardware?”

We’ve known for over six years (since before we started Drizzle) that the query cache hurt performance. It was for that reason that the query cache was one of the early things to be removed from Drizzle, it just didn’t scale on multi

  [Read more...]
Advanced MySQL Query Tuning: Webinar followup Q&A
+1 Vote Up -0Vote Down

Thanks to all who attended my “MySQL Query Tuning” webinar on July 24.  If you missed it, you can you can download the slides and also watch the recorded video. Thank you for the excellent questions after the webinar as well. Query tuning is a big topic and, due to the limited time, I had to skip some material, especially some of the monitoring. I would like, however, to answer all the questions I did not get into during the webinar session.

Q: Did you reset the query cache before doing your benchmark on your query? 0.00

  [Read more...]
Implications of Metadata Locking Changes in MySQL 5.5
+6 Vote Up -0Vote Down

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

To read what Metadata Locking exactly is please read this section here in the MySQL manual.

Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3

Metadata  [Read more...]

The Query Cache and Partitions
+3 Vote Up -0Vote Down

Like others we were not satisfied with the fix for a bug in MySQL which caused the query cache and partitioning to not work reliably together. The bug, in simple terms, was that if the query cache was enabled and you used partitioned tables and if a partitioned table was using a transactional engine like InnoDB or XtraDB, the query cache could, under certain circumstances, return incorrect results.

Returning incorrect results is a definite, high-priority bug. However, the upstream fix was to disable all caching of queries from partitioned tables. We wanted a better solution

  [Read more...]
Heads up! No more query cache for partitioned tables as of MySQL 5.5.23.
+9 Vote Up -0Vote Down

A customer opened an issue recently to ask why the query cache wasn't working after he upgraded to MySQL 5.5.25. The reason really ended up surprising me.

As of MySQL 5.5.23, the Query Cache is disabled for partitioned tables!

This is a "fix" for bug #53775.

At first I thought perhaps the fix for the bug had resulted in the query cache being inadvertently disabled for partitioned tables, but the comments that go along with the commit make it pretty clear that disabling the query cache was the intended "fix". You can review the commit message and the code changed at revision 2661.803.1 in the MySQL Server 5.5 repository.

read more

Stripping Comments so Query Cache Works in MariaDB and XtraDB
+4 Vote Up -0Vote Down

I recently noticed both MariaDB and XtraDB (not MySQL yet) have a (newer) variable query_cache_strip_comments.

This variable is great for those who want to append comments to various queries, but still want the query cache to be able to serve such queries. Unfortunately, with MySQL, this is not currently possible.

In the past, I wrote a post on using MySQL Proxy which described a technique of monitoring queries through the proxy by appending IP addresses to the queries so one could track where they originated from. However, one pitfall to that was the MySQL query cache *does not* ignore the comment and treats them all as different queries (see the user comments for further discussion). (I did subsequently enhance that functionality implementing the

  [Read more...]
5 Ways to Boost MySQL Scalability
+0 Vote Up -0Vote Down

There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50 or even 100 items, but we shortened it down to the biggest five issues we see.

1. Tune those queries

By far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn't necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn't receive

  [Read more...]
Shinguz: MySQL Query Cache does not work with Complex Queries in Transactions
+1 Vote Up -0Vote Down

We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
This was verified on a testing system which had the Query Cache enabled by accident.

But we all thought that the Query Cache would make sense in this situation so we investigated a bit more.

They have a Java application where they do pretty complex queries (10 to 30-way Joins) and they Connect with Connector/J to the database. We tried it out in the application on a dedicated system and verified that the Query Cache was not serving our queries but the query did a full dive to the

  [Read more...]
Regularly flushing the MySQL Query Cache without cron
+0 Vote Up -0Vote Down
This is a reply on Regularly flushing the MySQL Query Cache.

The original acticle is about regulary flushing the MySQL Query Cache as it will fragment over time.

There are some drawbacks for the cron method for flushing the query cache:
  • It will only work on UNIX like platforms as MS Windows uses the task scheduler to schedule tasks.
  • It needs credentials to login to the database.
  • It's not included in your database backup
There is another method, which is native to MySQL: the event scheduler.

Step 1: Enable the event

  [Read more...]
Shinguz: Regularly flushing the MySQL Query Cache
+0 Vote Up -0Vote Down

When we analyze our customers systems we see typically a high fragmentation of the query cache after a while. This leads to a less optimal use of the Query Cache than possible.

With the following Query you can see the values for your Query Cache:

| Variable_name           | Value    |
| Qcache_free_blocks      | 11328    |
| Qcache_free_memory      | 89442000 |
| Qcache_hits             | 6595644  |
| Qcache_inserts          | 1041831  |
| Qcache_lowmem_prunes    | 717896   |
| Qcache_not_cached       | 1040936  |
| Qcache_queries_in_cache | 17775    |
| Qcache_total_blocks     | 46990    |

Watch out for the value of Qcache_free_blocks and

  [Read more...]
MySQL caching methods and tips
+2 Vote Up -0Vote Down
“The least expensive query is the query you never run.”

Data access is expensive for your application. It often requires CPU, network and disk access, all of which can take a lot of time. Using less computing resources, particularly in the cloud, results in decreased overall operational costs, so caches provide real value by avoiding using those resources. You need an efficient and reliable cache in order to achieve the desired result. Your end users also care about response times because this affects their work productivity or their enjoyment of your service. This post describes some of the most common cache methods for MySQL.

Popular cache methods

The MySQL query cache

When the query cache is enabled, MySQL examines each query to see if the contents have been stored in the query cache. If the results have been cached they are

  [Read more...]
On generating unique IDs using LAST_INSERT_ID() and other tools
+1 Vote Up -0Vote Down

There’s a trick for using LAST_INSERT_ID() to generate sequences in MySQL. Quoting from the Manual:

  • Create a table to hold the sequence counter and initialize it:
    mysql> CREATE TABLE sequence (id INT NOT NULL);
    mysql> INSERT INTO sequence VALUES (0);
  • Use the table to generate sequence numbers like this:
    mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
  • This trick calls for trouble.


    A customer was using this trick to generate unique session IDs for

      [Read more...]
    Making query cache contention more obvious
    +4 Vote Up -0Vote Down

    The newest release of Percona Server includes a trivial change that I think will be extremely valuable. This is the addition of a new thread state, “Waiting on query cache mutex.” Fixing the query cache to make it scalable is hard. Fixing the server to report when the query cache is a bottleneck is not hard. It has historically been very difficult for users to diagnose what’s wrong with their server when the query cache is locking it intermittently. Now it will be trivial: they will look at SHOW PROCESSLIST and the evidence will be unmistakable.

    Related posts:

  • Making Maatkit more Open Source one step at a time
  •   [Read more...]
    How to Improve Query Cache Performance
    +0 Vote Up -0Vote Down

    The MySQL query cache can be very useful in environments where data is not modified often, and traffic consists of mostly reads. It can improve performance by quite a bit if used correctly, but can actually degrade performance if configuration, queries, and traffic patterns are not optimized for it.

    Let me quickly go over what the query cache is, and what it is not. The query cache does not cache the query execution plan, the full page on disk (which is what the InnoDB buffer pool is used for), DDL statements, or any queries that modify data (INSERT/UPDATE/etc). The query cache *does* cache the full result set of “cacheable” SELECT queries. For a query to be “cacheable”, it must have the following properties:

    • It must be deterministic. The query must return the same result set each time that it is run. This means that it may not contain any
      [Read more...]
    mk-query-digest, query comments and the query cache
    +1 Vote Up -0Vote Down

    I very much like the fact that MySQL allows you to embed comments into SQL statements. These comments are extremely convenient, because they are written into MySQL log files as part of the query. This includes the general log, the binary log and the slow query log. Maatkit includes tools which interact with these logs, including mk-query-digest. This tool, in particular, has a very nice option called --embedded-attributes which can process data embedded in query comments.

    The support for embedded attributes makes some cool tricks possible. Peter and I co-presented a talk at this past MySQL Conference and Expo. In this talk I presented my

      [Read more...]
    Is the query cache useful?
    +4 Vote Up -4Vote Down

    Mark Callaghan posted a good test of the MySQL query cache in different versions. His tests clearly show that in 5.0.44 and 5.0.84 and 5.1.38, there is more query throughput when the query cache is disabled.

    However, the tests are skewed — not on purpose, I am sure, and Mark admits he has not used the query cache before — but they are skewed all the same. Mark’s error was that he assumed he could just turn on the query cache and see if it works. Most features of MySQL do not work that way — you have to understand the strengths and weaknesses of the feature in order to use it properly.

    Mark’s benchmark definitely reinforces that turning on the query cache without any knowledge of your system is a bad idea, and I agree with him on that. But it does not in any way mean that

      [Read more...]
    MySQL Query Cache path
    +3 Vote Up -1Vote Down

    Just how effective is the Query Cache on performance? If you are not comfortable reading the MySQL code you can cheat very easily with the SHOW PROFILE command. This demonstration will show you at a high level the relative impact without and with the Query Cache. First let us confirm the Query Cache is not used.

    mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
    | Variable_name                | Value   |
    | query_cache_limit            | 1048576 |
    | query_cache_min_res_unit     | 4096    |
    | query_cache_size             | 0       |
    | query_cache_type             | ON      |
    | query_cache_wlock_invalidate | OFF     |
    5 rows in set (0.00 sec)

    We now enable

      [Read more...]
    Using the Query Cache effectively
    +3 Vote Up -0Vote Down

    Maximize your strengths, minimize your weaknesses.

    You can apply this approach to many things in life, I apply it to describing and using MySQL the product, and it’s components. The Query Cache like many features in MySQL, and indeed features in many different RDBMS products (don’t get me started on Oracle *features*) have relative benefits. In one context it can be seen as ineffective, or even detrimental to your performance, however it’s course grain nature makes it both trivial to disable dynamically (SET GLOBAL query_cache_size=0;), and also easy to get basic statistics on current performance (SHOW GLOBAL STATUS LIKE ‘QCache%’;) to determine effectiveness and action appropriately.

    The Query Cache is course grained, that is it is rather simple/dumb in nature. When you understand the path of execution of a query

      [Read more...]
    Using memcached functions for MySQL; an automated alternative to Query Cache
    +0 Vote Up -0Vote Down

    There’s a lot of buzz around memcached. memcached is widely used, and has clients for many programming languages and platforms. TangentOrg have developed a memcached client in the form of MySQL UDFs (User Defined Functions).

    I wish to discuss the memcached functions for MySQL: if and how they should be used.

    Disclaimer: I do not work with memcached functions for MySQL on a production system; all that is written here reflects my opinion on how things should be done.

    With memcached functions for MySQL, we can do the following:

    SELECT memc_set('mykey', 'The answer is 42');
    SELECT memc_get('mykey');

    (See my previous post on how to

      [Read more...]
    MySQL Locks (and a bit of the Query Cache)
    Employee +0 Vote Up -0Vote Down

    MySQL uses locks for concurrency control. Whenever a client/thread acquires a lock, it will have exclusive access to that table or row (depending on the granularity of the lock). Other clients however, will be prevented from writing and possibly reading to/from the locked resource. The two main existing locks are:

    READ LOCK – A read lock will allow the other clients to read from the locked resource but not write to it.
    WRITE LOCK – a write lock will prevent the other clients from reading or writing to the locked resource.

    Also, different storage engines have different lock granularity. For example, MyISAM will lock the whole table while InnoDB will only lock the rows it needs.

    Let’s try this out using a MyISAM table. We open two concurrent sessions and in the first lock the City table from the

      [Read more...]
    Why You Want to Switch to MySQL 5.1
    +0 Vote Up -0Vote Down

    In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

    • online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
    • Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.
    • (more…)

    Inspect the Query Cache using MySQL Information Schema Plug-ins
    +0 Vote Up -0Vote Down
    A while ago I wrote about MySQL 5.1 information schema plug-ins.

    At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.

    I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new MySQL 5.1.26-rc server. It seems to work there, so I put the code up on the web.

    Inside the source file, there's instructions to build

      [Read more...]
    MySQL Conference and Expo 2008, Day Two
    +0 Vote Up -0Vote Down

    Day two of the conference was a little disappointing, as far as sessions went. There were several time blocks where I simply wasn’t interested in any of the sessions. Instead, I went to the expo hall and tried to pry straight answers out of sly salespeople. Here’s what I attended.

    Paying It Forward: Harnessing the MySQL Contributory Resources

    This was a talk focused on how MySQL has made it possible for community members to contribute to MySQL. There was quite a bit of talk about IRC channels, mailing lists, and the like. However, the talk gave short shrift to how MySQL plans to become truly open source (in terms of its development model, not its license). I think there was basically nothing to talk about there. I had a good conversation about some of my concerns with the speaker and some others from MySQL right afterwards.

      [Read more...]
    My presentations at the 2008 MySQL Conference and Expo
    +0 Vote Up -0Vote Down

    I'll be attending the 2008 MySQL Conference and Expo again this year, and I'm looking forward to hearing some great sessions, meeting new and old friends, and giving sessions myself. As a proposal reviewer, I looked at and voted on 250+ proposals for sessions and tutorials for this conference. There are going to be some great sessions and tutorials.

    Showing entries 1 to 25

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.