Amazon recently announced support for 5.6, unfortunately, direct upgrade from lower versions is not yet supported. On a recent migration work – running mysqldump flat out would’ve meant 6+hrs of downtime. How did we cut it off to 1h45m? Simple, run dump per table and pipe it directly to the new 5.6 instance in parallel using Percona Server’s mysqldump utility to take advantage of –innodb-optimize-keys.

Here’s the base script we used – of course, YMMV and make sure to optimize the destination instance as well!

#!/bin/bash
# export-run.sh
# This is the wrapper script which builds up the list of tables to split into $parallel parts and calls export-tables.sh

parallel=6
dblist="db1 db2 db3"
smysql="mysql -hsource-55.us-east-1.rds.amazonaws.com"
dmysql="mysql -hdest-56.us-east-1.rds.amazonaws.com" …

Everyone who works with MySQL (or MariaDB) query optimizer has to spend a lot of time looking at EXPLAIN outputs. You typically first look at the tabular form, because it is easier to read. You can immediately see what the join order is, what numbers of records will be read, etc:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey;
+------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+
| id   | select_type | table    | type | possible_keys | key         | key_len | ref                        | rows   | Extra |
+------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+
|    1 | SIMPLE      | customer | ALL  | PRIMARY       | NULL        | NULL    | NULL                       | 150303 |       |
|    1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | …

Now that Percona XtraDB Cluster 5.6 is out in beta, I wanted to start a series talking about new features in Galera 3 and PXC 5.6.  On the surface, Galera 3 doesn’t reveal a lot of new features yet, but there has been a lot of refactoring of the system in preparation for great new features in the future.

Galera vs MySQL options

wsrep_provider_options is a semi-colon separated list of key => value configurations that set low-level Galera library configuration.  These tweak the actual cluster communication and replication in the group communication system.  By contrast, other PXC global variables (like ‘wsrep%’) are set like other mysqld options and generally have more to do with MySQL/Galera …

What do swaying palms, turquoise water, white sandy beaches and absolutely pristine fauna remind you of? Correct! It’s Log Buffer. This Log Buffer brings you beads of blog posts related to data dexterity crafted by leading bloggers across the planet.

Oracle:

When are Exadata’s storage indexes used?

Oracle 12c has increased the maximum length of character-based columns to 32K bytes.

Oracle has extended the maximum length of varchar2, nvarchar and raw columns to 32K, but this comes with some challenges when it comes to indexing such columns.

…

How about MySQL 5.7 innodb memcached!

The MySQL development team is seeking feedback on how you use mysqldump!

Here is some of the feedback I have to pass along:

• With InnoDB now the default (since MySQL 5.5) I would love to see mysqldump change to take advantage of this. For example:

  • mysqldump can do a hot backup with all InnoDB tables, all you need to do is include --single-transaction. We need to find a way to make this the default behaviour so that applications are not blocked waiting.
  • Adding indexes should be delayed until after all data has been inserted into the table to take advantage of the InnoDB fast-index creation feature (InnoDB Plugin/5.5+).
  • Option to dump as MyISAM, restore as InnoDB.

• An option to have progress reports while running. Even in the form 34/60 tables backed …

Time MySQL Server Started

From a mysql client just execute the following to see the date and time the server was started.
select now() - interval (variable_value/60/60/24) day -- \ from information_schema.global_status -- \ where variable_name='Uptime';
From the shell, this will provide the same information.

mysql -uroot -p -A -s  -e"select now() - interval (information_schema.global_status.VARIABLE_VALUE/60/60/24) day -- \"from information_schema.global_status where variable_name='Uptime';"

Sometime you may run into having to restart the mysqld under pressure. And it will not come up.  
One cause is the my.cnf file is modified some time after startup with a bad setting. You could use the results from the commands above and compare that to
stat /etc/my.cnf
to see if any changes were made to my.cnf that might prevent the server from …

Last week, during Tomas' keynote at MySQL Percona Live Conference in London we announced as one of "previews" of the following MySQL 5.7 release(s) -- an over 1,000,000 Query/sec result obtained with InnoDB Memcached plugin on a Read-Only workload. This article here is just to confirm the announced results without going too much in details..

In fact we have no idea yet for today what are exactly the scalability and performance limits for this solution.. The huge gain in performance was possible here due initial overall speed-up made recently in MySQL 5.7 and letting us reach 500K …

Part of an article originally published in the article MySQL Forks: Which one is right for me?, published in the June edition of php[architect]. The preceding portions of the article are available: A Brief History of MySQL Oracle MySQL Percona Server Having left Sun in September 2008, Monty Widenius started Monty Program AB to continue [...]

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.34. This is a Stable (GA) release. See the Release Notes and Changelog for detailed information on this release and the What is MariaDB 5.5? page in the MariaDB Knowledge Base for general information about the MariaDB 5.5 series.

Download MariaDB 5.5.34

Release Notes Changelog …