Showing entries 31 to 40 of 109
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: 5.6 (reset)
TIMESTAMP Columns, Amazon RDS 5.6, and You

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 (
  ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

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


They mentioned that their schema contains TIMESTAMP column definitions without any modifiers for nullability or …

[Read more]
MySQL defaults evolution

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 defaults may come with some serious consequences for the ones who …

[Read more]
Fun with Bugs #31 - what's new in MySQL 5.6.17

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 online DDL (ALGORITHM=INPLACE) for …
[Read more]
MySQL 5.6 GTIDs: Evaluation and Online Migration

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.


MySQL 5.6 GA one year – What is next?

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

 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 Thread). Yes, 5.6 has a slower replication performance problem, if SQL thread is …

[Read more]
MySQL RPMS and the new yum repository

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 bug#70773 to ask for RHEL5 support to be …

[Read more]
How to recover an orphaned .ibd file with MySQL 5.6

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 Sakila DB on a server with MySQL 5.5 (but with …

[Read more]
Persistent statistics and partitions

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.

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 |
| 8 | test08 |
| 1 | test01 |
| 5 | test05 …
[Read more]
Making full table scan 10x faster in InnoDB

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 physical backup. For example:

  • Logical backup size is much smaller. …
[Read more]
Showing entries 31 to 40 of 109
« 10 Newer Entries | 10 Older Entries »