Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 79 10 Older Entries

Displaying posts with tag: optimizer (reset)

Fun with Bugs #11 - Top 10 Optimizer Regression Bugs in MySQL 5.6
+1 Vote Up -0Vote Down
I've got a question from colleague last night on what bugs should users take into account if they plan to upgrade to MySQL 5.6 now. Simple answer is: it depends. If one of the new features or scalability improvements are really important, then bugs in other features or clearly identified problematic use cases may be just ignored or avoided.

But to be on a safe side users should at least check if they are (or may be) affected by a known regression bugs, when new version is slower or produce wrong results or crashes in cases that worked without problems before.

List of bugs in MySQL 5.6 that can be formally considered as regressions comparing to previous major versions would be long enough for a single post. So I'd like to concentrate on regression bugs in Optimizer here:





  [Read more...]
MySQL Query Patterns, Optimized – Webinar questions followup
+3 Vote Up -0Vote Down

On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as we had time for  during the session, but here are all the questions with my complete

  [Read more...]
Optimizing MIN and MAX MySQL Functions
+1 Vote Up -0Vote Down

MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below:

CREATE TABLE `history` (
   `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `u_id` int(10) unsigned NOT NULL,
   `cn_id` int(10) unsigned NOT NULL,
   `f_id` int(10) unsigned NOT NULL
   PRIMARY KEY (`h_id`)
 ) ENGINE=InnoDB

If I want to get the MAX value for cn_id, I’d to a query like this which will be a full table scan:

mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history \G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: history
  [Read more...]
Shard-Query 2.0 Beta 1 released
+4 Vote Up -0Vote Down

It is finally here.  After three years of development, the new version of Shard-Query is finally available for broad testing.

This new version of Shard-Query is vastly improved over previous versions in many ways.  This is in large part due to the fact that the previous version of Shard-Query (version 1.1) entered into production at a large company.  Their feedback during implementation was invaluable in building the new Shard-Query features.   The great thing is that this means that many of the new 2.0 features have already been tested in at least one production environment.

This post is intended to highlight the new features in Shard-Query 2.0.  I will be making posts about individual features as well as posting benchmark results.

  [Read more...]
Webinar: SQL Query Patterns, Optimized
+2 Vote Up -0Vote Down

Next Friday, May 31 at 10 a.m. Pacific, I’ll present Percona’s next webinar, “SQL Query Patterns, Optimized.”

Based on my experiences solving tough SQL problems for Percona training and consulting, I’ll classify several common types of queries with which developers struggle. I’ll test several SQL solutions for each type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query

  [Read more...]
The saddest bug of them all (SQL is dead?)
+2 Vote Up -0Vote Down

From time to time I will observe servers wasting lots of CPU when doing batch row operations. In perf top it will look like this:

8.24% mysqld [.] Arg_comparator::compare_int_unsigned()
7.17% mysqld [.] Item_cond_and::val_int()
4.37% mysqld [.] Item_field::val_int()
4.37% mysqld [.] Item_cond_or::val_int()
2.90% mysqld [.] MYSQLparse(void*)
2.64% mysqld [.] Item::val_bool()

Essentially if you construct queries like (a=1 AND b=2) OR (a=3 AND b=4) ..., at large enough batch size evaluating the WHERE will become far more expensive than anything else (yes, more expensive than decompressing rows or doing all the InnoDB magic and what not).

MySQL has awesome syntax that makes certain batch lookups much faster: WHERE a IN (1,2,3). It constructs a tree that then each row can be compared against and one does not have to iterate through

  [Read more...]
Fixing awkward TIMESTAMP behaviors...
Employee +3 Vote Up -0Vote Down
There are great features in MySQL 5.6. But not only that. We also tried to correct some old behaviors and limitations which, over the years, have shown to irritate our Community. The behavior of TIMESTAMP columns is one of them.

My colleague Martin Hansson did most of the work and summarized it well in his blog. Thanks to him, since MySQL 5.6.5, it's possible to declare more than one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. And it's possible to have DATETIME columns with such attributes. Two limitations lifted!

But that is not the end of the story. TIMESTAMP was still special. Unlike other datatypes, if not declared with the NULL or NOT NULL attributes, it would automatically get



  [Read more...]
About MySQL 5.6
+14 Vote Up -2Vote Down

I am very excited and thrilled to use the latest release of MySQL 5.6 in production. This is probably the most notable and innovative release in many years, if not ever.

During the last year, we had the chance to work with many new features and test the fixes to old issues. To be honest, I was expecting to have MySQL 5.6 GA before now, and I even wagered with my colleague Francisco that it would be out before the end of 2012. It was nothing special, just a beer in the Santa Clara Hyatt lounge. Unfortunately for me, MySQL 5.6 is now in GA and given that it happened in 2013, I lost the bet and now have to pay for that beer. But I have also lost the full list of things that we saw as relevant, interesting, or really innovative for MySQL.

So I took a step back, took some time, and reviewed what Oracle delivered in this new MySQL release.

Short premise

Oracle developer teams

  [Read more...]
When kill flag is checked for SELECT? Part II
+2 Vote Up -0Vote Down
In the previous part I've stopped at the moment when we entered JOIN:exec() - most checks for kill flag happen somewhere there, during query execution. We know the list of functions that checks this flag during query execution:

sub_select_cache()
evaluate_join_record()
flush_cached_records()
end_write()
end_update()
end_unique_update()
end_write_group()
remove_dup_with_compare()
remove_dup_with_hash_index()


but we do not know when exactly each of them is called. So, let me try to show what happens inside JOIN::exec (some code paths and checks are not considered for simplicity, we care about SELECT, but not EXPLAIN SELECT etc). I've included











  [Read more...]
When kill flag is checked for SELECT? Part I
+2 Vote Up -0Vote Down
Manual describes this briefly:

In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

Complete, correct and useful answer is more complex though. Here is correct answer, but not very useful. So, kill flag is checked in the following functions related to SELECT statement processing:

make_join_statistics()
best_extension_by_limited_search()
find_best()
sub_select_cache()
evaluate_join_record()
flush_cached_records()
end_write()
end_update()











  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 79 10 Older Entries

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.