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 30 of 80 Next 30 Older Entries

Displaying posts with tag: 5.6 (reset)

The Road to MySQL 5.6 -- A DBA Perspective
+4 Vote Up -0Vote Down
We've all heard the hype.  MySQL 5.6 is packed with amazing new features that address all our database problems.  5.6 deals with replication and HA and performance and monitoring and security and features.  It just may cure cancer.

In fact it's been out for ages.  It went GA 

  [Read more...]
TIMESTAMP Columns, Amazon RDS 5.6, and You
+1 Vote Up -0Vote Down

This comes from an issue that I worked on recently, wherein a customer reported that their application was working fine under stock MySQL 5.6 but producing erroneous results when they tried running it on Amazon RDS 5.6. They had a table which, on the working server, contained two TIMESTAMP columns, one which defaulted to CURRENT_TIMESTAMP and the other which defaulted to ’0000-00-00 00:00:00′, like so:

CREATE TABLE mysql56 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
);

However, under Amazon RDS, the same table looked like this:

CREATE TABLE rds56 ( 
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT NULL, 
);

They

  [Read more...]
MySQL defaults evolution
+1 Vote Up -0Vote Down

MySQL, the original brand, the one developed by the MySQL team at Oracle, is steadily evolving. You can feel it if you try every new release that comes out of the milestone release cycle. Or even if you don’t try all of them, just testing a release once in a while gives you something to think about.

The engineers at Oracle are trying hard to improve the defaults. If you are the out-of-the-box type, and just install the new version on top of the previous one, leaving the same setup in place, you may be up for a for a few surprises. It’s the marketing, see? They tell you that just by replacing your old MySQL (5.1 or 5.5) with MySQL 5.6 you get 30% to 70% performance improvement. Which happens to be true, not only because the server is better, but also because they have changed the defaults. However, this change in

  [Read more...]
Fun with Bugs #31 - what's new in MySQL 5.6.17
+1 Vote Up -0Vote Down
MySQL 5.6.17 will probably be announced loudly at or immediately before Percona Live MySQL Conference & Expo next week. But official release announcement via email was made on March 28, release notes and binaries to download are already available, so why not to check them carefully to find out what to expect from this 8th minor release of MySQL 5.6 GA...

First of all, it seems Oracle still does not hesitate to introduce new features and behavior in the process. Just check these major changes:
  • Starting with 5.6.17, MySQL now supports rebuilding regular and partitioned InnoDB tables using



  [Read more...]
MySQL 5.6 GTIDs: Evaluation and Online Migration
+0 Vote Up -0Vote Down

A colleague and I have been looking at GTID on MySQL recently and you may be interested in the blog post that results from that. You can see it here. http://blog.booking.com/mysql-5.6-gtids-evaluation-and-online-migration.html.

 

MySQL 5.6 GA one year – What is next?
+3 Vote Up -0Vote Down

MySQL 5.6 has been GA for just over a year now. See MySQL 5.6.10 Release Notes.  Congratulations on your birthday! That is quite a long time. I was using it earlier in production because it worked and could do things that 5.5 could not do, but earlier versions were to use at your own risk, and indeed if prodded incorrectly would fall on the floor. That is fair enough because they were work in progress, yet if you poked them the right way they did a very good job.  Those dev versions have been long since upgraded which is good so they do not need quite as much care and attention.

So from where I see 5.6 it works very well. One big change that has made a large difference but which I think a lot of people may not really understand or use is the

  [Read more...]
Single thread performance regression in 5.6 - Replication
+2 Vote Up -0Vote Down
 At Facebook, we have upgraded most of MySQL database tiers to 5.6, except very few tiers that have a special requirement -- very fast single threaded replication speed.

 As Oli mentioned, single threaded performance is worse in 5.6. The regression is actually not visible in most cases. For remote clients, the performance regression is almost negligible because network latency is longer than 5.1->5.6 overhead. If clients are running locally but MySQL server is disk i/o bound, the overhead is negligible too because disk i/o latency is much longer than 5.1->5.6 overhead.

 But the regression is obvious when clients run locally and queries are CPU bound. The most well known local client program for MySQL is SQL Thread (Replication



  [Read more...]
MySQL RPMS and the new yum repository
+1 Vote Up -0Vote Down

I was really pleased to see the announcement by Oracle MySQL yum repositories that they have now produced a yum repository from where the MySQL RPMs they provide can be downloaded. This makes keeping up to date much easier. Many companies setup internal yum repositories with the software they need as then updating servers is much easier and can be done with a simple command. For many people at home that means you set this up once and don’t need to check for updates and do manual downloads, but can do a quick yum update xxxx and you get the latest version. Great!  This new yum repository only covers RHEL6 did not include RHEL5 which is not yet end of life and still used by me and probably quite a lot of other people. I filed

  [Read more...]
How to recover an orphaned .ibd file with MySQL 5.6
+0 Vote Up -0Vote Down

A few years ago Yves Trudeau and Aleksandr Kuzminsky wrote posts about different ways for recovering orphaned .ibd files:

Today I want to show you how to do that in more easy and quick way. In my example I’ll restore a “payment.ibd” file (payment table) from

  [Read more...]
Persistent statistics and partitions
+1 Vote Up -0Vote Down
Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:
  • Create and utilize table partitioning
  • Obtain MySQL metadata from INFORMATION_SCHEMA tables

The first step is to create a table, partition it with a hash.
mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
-> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
-> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11');
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM pfoo;
+----+--------+
| id | name |
+----+--------+
| 4 | test04

















  [Read more...]
Making full table scan 10x faster in InnoDB
+6 Vote Up -0Vote Down
At MySQL Connect 2013, I talked about how we used MySQL 5.6 at Facebook, and explained some of new features we added to our Facebook MySQL 5.6 source tree. In this post, I'm going to talk about how we made full table scan faster in InnoDB.

Faster full table scan in InnoDB

 In general, almost all queries from applications are using indexes, and reading very few rows (0..1 on primary key lookups and 0..hundreds on range scans). But sometimes we run full table scans. Typical full table scan examples are logical backups (mysqldump) and online schema changes (SELECT ... INTO OUTFILE).

 We take logical backups by mysqldump at Facebook. As you know MySQL offers both physical and logical backup commands/utilities. Logical backup has some advantages against



  [Read more...]
New MySQL 5.6-Feature host_cache_size does not work
+1 Vote Up -0Vote Down

Today as i was learning for the new MySQL 5.6-certification (more about that in a later post) i stumbled again across the host_cache-Table that was added to MySQL in 5.6.5. Before that you had no chance to check which hosts are already known by the MySQL-server.

So thats a pretty good feature for us. And even better: you could resize the size of the host_cache now! Whoohoo, awesome! As we have a lot of servers that need to connect to one of our MySQL-server and we could not switch to ip-based-authentication we were really happy to tune the host_cache-size without recompiling MySQL.

Unfortunately i noticed that the performance_schema.host_cache table only holds 128 rows and that the content was changing everytime i checked. So i logged in to a server that wasn’t already in the host_cache-table, made a connection attempt to the mysql server

  [Read more...]
New Shard-Query features checked into SVN
+0 Vote Up -0Vote Down
I checked some updates to Shard-Query into SVN.

Partitioning support has been extended for MySQL 5.6+ to ALL partitioning types.

This includes all previously unsupported types including RANGE LIST/COLUMNS partitioned tables that are partitioned over more than one column, and HASH/KEY/LINEAR variants as well. Shard-Query now exclusively uses the PARTITION hint for partition elimination instead of WHERE clauses in MySQL 5.6. For 5.5 and previous versions, support remains limited to LIST,RANGE, and LIST/RANGE COLUMNS over a single column.

The old mysql interface DAL has been replaced completely by the PDO DAL.

There is no major difference for end users except that you have to check that the return of the query() method is an object with the is_object() function instead of checking that it is a resource with the is_resource() function.

  [Read more...]
Fun with Bugs #22 - Some Bug Reports You Should Not Miss
+0 Vote Up -0Vote Down
Yet another user installed MySQL 5.5.32 yesterday and got a system that can not start... It's really easy to help in this case - just downgrade back to 5.5.31 or upgrade to 5.5.33 if you can. Why problem happened during upgrade? Because of a regression bug #69623.

This case that was easily solved during a quick chat reminded me about the problem of bugs in production. Nobody expects any sane DBA to review every new bug report, but some of them should not be missed, at least when upgrading to any newer version. Regression bugs (I see 15

  [Read more...]
Fun with Bugs #21 - recently verified bugs in MySQL 5.6.13
+0 Vote Up -0Vote Down
Notable contribution of MySQL Community to MySQL 5.6.13 was explicitly recognized recently. But users and contributors still continue their efforts, as well as Oracle engineers. Even though MySQL 5.6.13 has been generally available just for few days, we already have several new bug reports and updates to known bugs at http://bugs.mysql.com. Let me present a short list with some comments.

  • Bug #69915 is a great example of a "new thinking" inside Oracle. Todd Farmer does not only write about new ways to use PERFORMANCE_SCHEMA in MySQL 5.6 in his blog, but also reports


  [Read more...]
Fun with Bugs #19 - waiting for MySQL 5.6.13 and some real fun?
+0 Vote Up -0Vote Down
I feel like MySQL 5.6.12 was released ages ago, while in reality it was on June 3, less than 2 months ago. No wonder I feel so, after writing several posts about bugs fixed and not fixed in it... Anyway, we still have to wait for MySQL 5.6.13 for a week or even two probably and in the meantime I decided to write new post for this series based on good old idea of making a digest of my recent bugs-related posts at Facebook. I know, it's boring and annoying (same as waiting for the release of 5.6.13).

Let's start with Bug #69846 - "ICP does not work on UNIQUE indexes". Based on my quick tests presented there I'd say that ICP (

  [Read more...]
Fun with Bugs #16 - read the fine MySQL 5.6 manual...
+1 Vote Up -2Vote Down
MySQL 5.6 has many new features and some of them change usual DBA procedures and formerly well known facts/limitations. It's important to have all these properly documented now, when MySQL 5.6 is GA for 6 months already and is supposed to be widely used in production. So, I think it's time to check what documentation problems still remain.

Here is the list of active bug reports in Server: Docs category for version 5.6, starting with recently reported:

  • Bug #69717 - "DML statements replicated via RBR are NOT logged in the general query log ". I had to report this while working on customer issue and trying to explain why there are no DML statements in slave's general query log. This feature was new in 5.1 and even somewhat documented at




  [Read more...]
Fun with Bugs #15 - Recent News and Hawthorne Effect Studies
+0 Vote Up -0Vote Down
Let me present a quick review of new and recently verified MySQL bug reports (mostly in 5.6.12, but not only). Surely I have to start with this request that many my colleagues had already mentioned in their blogs:

Bug #69558 - Put *all* know bugs into the public bug tracking system at bugs.mysql.com. We may argue on how and when this should apply to "security" bugs, but automatic bi-directional replication (even if delayed) with Oracle internals bugs database is what I was also asking for since we were forced to use it. Click on "Affects Me" button there and let's hope that some day Oracle will publish list of bugs that affect most of community users and may even try to take this into account while making decisions.

I have good news for everybody who was following MySQL 5.6



  [Read more...]
Schema changes – what’s new in MySQL 5.6?
+3 Vote Up -0Vote Down

Among many of the improvements you can enjoy in MySQL 5.6, there is one that addresses a huge operational problem that most DBAs and System Administrators encounter in their life: schema changes.

While it is usually not a problem for small tables or those in early stages of product life cycle, schema changes become a huge pain once your tables get a significant amount of data. Planning for maintenance is becoming more and more difficult, and your worldwide users want the service to be up and running 24/7, while on the other hand, your developers desire to introduce schema changes every week.

PITA

But what is the real problem here? Let me illustrate very

  [Read more...]
Fun with Bugs #14 - InnoDB in MySQL 5.6
+2 Vote Up -1Vote Down
InnoDB improvements in MySQL 5.6 are well known. One of the key reasons to upgrade to MySQL 5.6 for most users is to get the benefits of improved performance, scalability, new monitoring features and fulltext indexes support in InnoDB.

Is there anything to double check before assuming that InnoDB in MySQL 5.6 is just better than any older version for any practical purposes? Let's review known public InnoDB-specific bug reports. Here is my "Top 10" list, as of MySQL 5.6.12, starting with most recent reports:

  • Bug #69424  - maybe I miss something (I am not the only one though), but I see no way to continue using raw devices (on Linux at least) to store InnoDB data. You had working raw device in 5.5.32, then you upgrade to 5.6.12 and just can not start MySQL any more.




  •   [Read more...]
    Fun with Bugs #13 - MySQL replication and two-way communication
    +2 Vote Up -0Vote Down
    I hope you had noted this already, but in case you missed it, please, read this post by Matt Lord and check any bug at http://bugs.mysql.com. As soon as you log in to your Oracle account, you can vote for bugs and feature requests! I hope that eventually somebody will publish lists of "Top N Most Wanted" fixes based on number of users who clicked on this great "Affects Me" button.

    If you plan to use this new feature to express your needs while given a chance, why not to start with replication-related bugs in latest and greatest MySQL 5.6.12? Here is my "Top 10" list (starting with recently reported):
  • Bug #69444 -



  •   [Read more...]
    Beginning on MySQL 5.6? Take the New MySQL for Beginners Training
    Employee_Team +0 Vote Up -0Vote Down

    The MySQL for Beginners training course is a great way of for you to learn about the world's more popular open source database. During this 4 day course, epxert instructors will teach you how to use MySQL Server 5.6 and the latest tools while helping you develop deeper knowledge of using relational databases.

    You can take this live-instructor course as a:

    • Live-Virtual event: Take this course from your own desk, choosing from a selection of events on the schedule to suit different time-zones.
    • In-Class Event: Travel to an education center to follow this course. Below is a selection of events already on the schedule.

      [Read more...]
    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...]
    Fun with Bugs #10 - recently reported bugs affecting MySQL 5.6.12
    +0 Vote Up -0Vote Down
    MySQL 5.6.12 is available to community for more than a week already, so people started to test and use it. And, no wonder, new bug reports started to appear. Let's concentrate on them in this issue.

    I'd like to start with a funny one.  Bug #69413 had scared some of my Facebook readers to death, as we see kernel mutex mentioned clearly in the release notes for 5.6.12. What, kernel mutex comes back again? No, it's just a result of null merge and, probably, copy/paste from the release notes for 5.5.32.

    It seems recent bug reports for 5.6.12 are mostly related to small details



      [Read more...]
    Fun with Bugs #9 - MySQL 5.6.12, quick review
    +0 Vote Up -0Vote Down
    So, it seems we have MySQL 5.6.12 released officially. We have great Changes in MySQL 5.6.12 page already widely shared and people already blogging about a feature implemented by my dear friend Sinisa.

    Quick scroll over changes shows 130+ bugs fixed and it will surely take time to understand the impact of all these fixes. We have 2 months for this till next release, so eventually we'll find out what's good in MySQL 5.6.12 and should we immediately switch to it from all other older 5.x.y versions.

    But we have to start with something, and I'd like to start with bugs that I've mentioned in



      [Read more...]
    MySQL 5.6 Experiences - .mylogin.cnf and mysql_config_editor
    +0 Vote Up -0Vote Down
    Having  basic ideas of how I am going to describe new features explained, I can proceed with some real (and I hope useful) content. As I read this page about new features from top to bottom, let's start with security improvements...

    .mylogin.cnf and mysql_config_editor

     

    Details:

    • you can store authentication credentials encrypted in an option file named .mylogin.cnf (in user's home directory or in %APPDATA%\MySQL on Windows)
    • password is no longer stored in plain text (like in .my.cnf) and still is not exposed in


      [Read more...]
    The small improvements of MySQL 5.6: Duplicate Index Detection
    +3 Vote Up -0Vote Down

    Here at the MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5.6 brought: GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete

      [Read more...]
    Fun with bugs #7, still mostly about 5.6.11
    +1 Vote Up -0Vote Down
    It looks like now Oracle will release new 5.6.x every 2 months, so while I'd happily write about bugs fixed in 5.6.12 we still have to wait for the official release to happen. I am too impatient to wait for 5.6.12, so let me write this post of a classical kind - just a summary of my MySQL bugs' related posts on Facebook since the previous one.

    I have to start with replication-related bugs in 5.6.11 pointed out by Giuseppe Maxia:
    • Bug #69095 -  replication fails with GTID enabled and master changes from SBR to RBR. This bug is



      [Read more...]
    MySQL Sandbox supports latest MySQL releases, has more metadata and docs
    +2 Vote Up -0Vote Down

    MySQL Sandbox has been updated again. The latest version is 3.0.38, which was just released. There were four releases in the space of one week, and this last one is just a polished edition.

    Cherry-picking from the Change log:

    • Added option --bind_address to complement the effects of --remote_access;
    • The script 'enable_gtid' (for MySQL 5.6 +) now is durable. Previously the changes did not survive a restart.
    • Now you can install MariaDB with its bizarre version '10.0'
    • It also works well with MySQL 5.7. A bug prevented the creation of 'enable_gtid', but it is

      [Read more...]
    Fun with Bugs #6 - MySQL 5.6.11
    +2 Vote Up -0Vote Down
    Last time I had written about bugs here it was a beginning of February, and hardly somebody could imagine that we were going to have 2 more months of winter, 2 more months to wait for next MySQL 5.6 GA release or that in 2 months and two weeks I'll be speaking about MySQL in public for 50 minutes based on my own presentation, and speaking about bugs (surely)...

    In any case, MySQL 5.6.11 looks promising and had got a lot of bugs fixes (check at least this nice post by Todd Farmer). Time for me to get

      [Read more...]
    Showing entries 1 to 30 of 80 Next 30 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.