October 26, 2014

Pretty-formatted index fragmentation with xtrabackup

The xtrabackup compiled C binary (as distinct from XtraBackup, which is the combination of the C binary and the Perl script) has support for printing out stats on InnoDB tables and indexes. This can be useful to examine whether you’d benefit from “defragmenting” your MySQL database with OPTIMIZE TABLE, although I have not determined firm […]

MySQL Indexing Best Practices: Webinar Questions Followup

I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with […]

Improved InnoDB fast index creation

One of the serious limitations in the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter has already blogged about it before, here I’ll just briefly reiterate other cases that might benefit from that feature: when ALTER TABLE […]

InnoDB: look after fragmentation

One problem made me puzzled for couple hours, but it was really interesting to figure out what’s going on. So let me introduce problem at first. The table is

Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump, the query that scans data […]

Learning about MySQL Table Fragmentation

Recently I was working with the customer who need quick warmup – to get Innodb table fetched in memory as fast as possible to get good in memory access performance. To do it I run the query: “SELECT count(*) FROM tbl WHERE non_idx_col=0″ I use this particular form of query because it will do full […]

MySQL File System Fragmentation Benchmarks

Few days ago I wrote about testing writing to many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables so I’ve got the script and ran tests for MyISAM and Innodb tables on ext3 filesystem. Here is what I found:

MySQL upgrade best practices

MySQL upgrades are necessary tasks and we field a variety of questions here at Percona Support regarding MySQL upgrade best practices. This post highlights recommended ways to upgrade MySQL in different scenarios. Why are MySQL upgrades needed? The reasons are many and include: Access to new features, performance benefits, bug fixes…. However, MySQL upgrades can […]

Using Apache Hadoop and Impala together with MySQL for data analysis

Apache Hadoop is commonly used for data analysis. It is fast for data loads and scalable. In a previous post I showed how to integrate MySQL with Hadoop. In this post I will show how to export a table from  MySQL to Hadoop, load the data to Cloudera Impala (columnar format) and run a reporting […]

Engineer duo from Google, LinkedIn join again for InnoDB talks

Google senior systems engineer Jeremy Cole is once again teaming with LinkedIn senior software engineer Davi Arnaut for two InnoDB-focused sessions at the upcoming Percona Live MySQL Conference and Expo 2014 this April 1-4 in Santa Clara, California. The duo will present “InnoDB: A journey to the core II” on April 2 and “InnoDB: A […]

How to monitor ALTER TABLE progress in MySQL

While working on a recent support issue as a Percona Support Engineer,  I got one question from a customer asking how to monitor ALTER TABLE progress. Actually, for MySQL 5.5 and prior versions, it’s quite difficult to ALTER the table in a running production environment especially for large tables (with millions records). Because it will rebuild and lock the table […]