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 |
|  8 | test08 |
|  1 | test01 |
|  5 | test05 …

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. …

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  and checked again the host_cache-table:

The server was now in the …

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. I updated bin/run_query, which is the example application.

I …

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 typical case:

Session1> ALTER TABLE revision ADD COLUMN mycol tinyint;
Query OK, 1611193 rows affected (1 min 5.74 sec)
Records: 1611193  Duplicates: 0 …

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.

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 performance schema, online DDL and …

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 …

MySQL 5.6 @ Facebook development tree

Steaphan is a hero (well, everyone else on database engineering team are too) and he is driving efforts to publish MySQL 5.6 changes we’re making to the open. Now they’re on the github (yet not in production, we’re in active testing though with our workloads).

MySQL 5.6 includes a host of enhancements to replication, enabling DevOps teams to reliably scale-out their MySQL infrastructure across commodity hardware, on-premise or in the cloud.

One of the most significant enhancements is the introduction of Global Transaction Identifiers (GTIDs) where the primary development motivation was:

- enabling seamless failover or switchover from a replication master to slave

- promoting that slave to the new master

- without manual intervention and with minimal service disruption.

You can download the new MySQL Replication High Availability Guide to learn more. …