Sometimes MySQL just doesn't choose the most efficient way to execute a query. GROUP BY is a good example. A customer recently wanted to add a unique key over some columns of a large (~50GB) table, and they first had to find duplicates. In this case, no suitable index was available that would help with the query. That means "Using temporary". And of course MySQL's GROUP BY also sorts by default, which means "Using filesort".

The goal here is just to find duplicates, or more specifically to find the lowest primary key value out of some group of rows with identical values for a subset of columns in the table. I think there is a much more efficient way to solve this problem.

I proposed a temporary table with the primary key column and a hash of the concatenated columns to be used for the grouping. This ended up allowing the customer to finish in 10 minutes an operation that originally resulted in a 30GB …

Disclaimer
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Introduction
There are database usage patterns, where tables grow big at times and many rows are deleted from them later. InnoDB does never shrink a table space. In these cases we can end up with big data files, which contain a lot of unused pages. It is a waste of disk- and I/O- resources to back them up.

Users have manifold requested that MySQL Enterprise Backup does not back up unused InnoDB data pages. Some want smaller backups, some want less I/O, some want shrinked table spaces.

MySQL Enterprise Backup 3.9.0 can help with smaller backups. The effect on I/O is not that remarkable. InnoDB data files must be expanded to their original size when they are restored. Backup cannot accomplish a shrinkage of InnoDB table spaces.

In the following I will try to explain, how …

Early this year Oracle released  MySQL 5.6 – Best MySQL Release Ever. This release delivered not only quality, but also quantity in terms of number of features. See a comprehensive list here . The blogs below also refer to the massive changes introduced in 5.6
http://www.mysqlperformanceblog.com/2013/01/27/mysql-5-6-improvements-in-the-nutshell/
http://www.flamingspork.com/blog/2013/03/05/mysql-code-size/

It is no mean task to deliver so many features with high quality that too for a feature rich product like MySQL. This was made …

Reblogged this on Sutoprise Avenue, A SutoCom Source.

Here’s a look at using MySQL Enterprise Backup in a specific example:

Consider a Backup Policy – Full Backup of the environment. – Complemental Incremental backups & online BinLogs. And the Restore: – Logical Restore. – Online, Zero impact. – Partial, single database, group of tables. The Backup A working environment, with 4 databases, of which 2 will require restoration. Full backup with MySQL Enterprise Backup:

mysqlbackup --user=root --socket=/tmp/mysql.sock \
  --backup-dir=/home/mysql/voju5/backup/ \
  --with-timestamp backup

Test preparation Create 4 different databases, where the structure & content is the same.

create database v5_1; use v5_1; create table `voju5` (
  `ID` int(7) NOT NULL AUTO_INCREMENT,
  `Name` char(20) NOT NULL DEFAULT '‘,
   PRIMARY KEY (`ID`) ) ENGINE=InnoDB;
create database v5_2; use v5_2; create table `voju5` (..);
create database v5_3; use v5_3; create table …

Here’s a look at using MySQL Enterprise Backup in a specific example:

Consider a Backup Policy – Full Backup of the environment. – Complemental Incremental backups & online BinLogs. And the Restore: – Logical Restore. – Online, Zero impact. – Partial, single database, group of tables. The Backup A working environment, with 4 databases, of which 2 will require restoration. Full backup with MySQL Enterprise Backup:

mysqlbackup --user=root --socket=/tmp/mysql.sock \
  --backup-dir=/home/mysql/voju5/backup/ \
  --with-timestamp backup

Test preparation Create 4 different databases, where the structure & content is the same.

create database v5_1; use v5_1; create table `voju5` (
  `ID` int(7) NOT NULL AUTO_INCREMENT,
  `Name` char(20) NOT NULL DEFAULT '‘,
   PRIMARY KEY (`ID`) ) ENGINE=InnoDB;
create database v5_2; use v5_2; create table `voju5` (..);
create database v5_3; use v5_3; create table …

With the close of call for papers earlier this month, the Percona Live London conference committee was in full swing this past week reviewing all of the many submissions for November’s Percona Live London MySQL Conference.

The submissions are far ranging and cover some really interesting topics, making the lineup for Percona Live London really strong! What the committee looks for in a submission is how much “value” a talk will bring to the conference – this is to say it needs to be far more that a product demo. As such, real-world experiences are receiving much more favorable reviews, along with talks that cover methodologies the attendees will …

I will be in Prato (Firenze), Italy Thursday and Friday (September 19th and 20th) to deliver 2 presentations at OSS4B:

• Installing and managing MySQL like a real devops: automation in production
• Introduction to Percona XtraDB Cluster

If you want to learn about these projects or discuss anything related to MySQL and Percona, join the conference and don’t hesitate to come to talk to me. I will be very happy to answer your questions.

I  also want to share a list of tracks I would like to attend (there is also another one but it’s at the same time as mine so I won’t tell it )

Learn MySQL in a virtual self-study group for free with MySQL Marinate. The Boston MySQL Users Group is continuing this popular series where you cover a chapter a week in O’Reilly’s Learning MySQL, upload your homework, and learn MySQL. If you have co-workers or friends looking to add MySQL to their skill set, please let them know.

And that’s a wrap! Check out the recording:

Hello!

Coming up this Wednesday, September 18th, 2013 at 12:00pm pacific (19:00 GMT) come and join #DBHangOps to discuss:

• Data warehousing in MySQL
  • Shipping large tables from production to your Data warehouse
  • Do you use Federated engine?
  • What other solutions do you use?
• Answer some questions about MySQL-isms (requested by Tim Callaghan)
  • Why does MySQL have FRM files instead of storing them in a data dictionary table?
  • Why doesn’t InnoDB support other page sizes?
  • Why do we store data in a master.info file?
• What are you excited to see at MySQLConnect?

As always, take …