When I’m doing conventional ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even though we do not specify default value. MySQL will assign empty string as default default value for varchar column. This however does not work for pt-online-schema-change:

root@smt2:~# pt-online-schema-change --execute  --alter="add column v varchar(100) not null" D=sbtest,t=sbtest     Altering `sbtest`.`sbtest`...
Creating new table...
Created new table sbtest._sbtest_new OK.
Altering new table...
Altered `sbtest`.`_sbtest_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 10000060 rows...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
`sbtest`.`sbtest` was not altered.
        (in cleanup) Error copying rows from `sbtest`.`sbtest` to `sbtest`.`_sbtest_new`: Copying …

Having duplicate keys in our schemas can hurt the performance of our database:

• They make the optimizer phase slower because MySQL needs to examine more query plans.

• The storage engine needs to maintain, calculate and update more index statistics

• DML and even read queries can be slower because MySQL needs update fetch more data to Buffer Pool for the same load

• Our data needs more disk space so our backups will be bigger and slower

In this post I’m going to explain the different types of duplicate indexes and how to find and remove them.

Duplicate keys on the same column

This is the easiest one. You can create multiple indexes on the same column and MySQL won’t complain. Let’s see this example:

mysql> alter table t add index(name);
mysql> alter table t add index(name);
…

Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them through insertion, especially for tables much larger than memory and large uncorrelated indexes you might be looking at 10x difference or more. Yet for some reason Innodb team has chosen to use very small (just 1MB) and hard coded buffer for this operation, which means almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process.
Mark Callaghan and Facebook Team has fixed this in their tree back in early 2011 adding innodb_merge_sort_block_size variable and I was thinking this small patch will be merged to MySQL 5.5 promptly, yet it has not happen to date.

Here is example of gains you can expect (courtesy of Alexey Kopytov), using 1Mil rows Sysbench table.

Buffer Length   |  alter table sbtest add key(c)
1MB                      34 sec
8MB …

I was playing with Percona Server today and found fast_index_creation does not work quite exactly like described in the manual. The point I’m looking to make though it would be very hard to catch this problem without additional
information_schema tables we added in Percona Server.

I was doing simple ALTER TABLE such as: “alter table sbtest add z int not null;” which I expected would create table with primary key only copy the data to it and when create secondary key by sort. Looking at innodb_sys_tables and innodb_sys_indexes I discovered it is not the case:

mysql> select * from innodb_sys_tables;
+----------+--------+------------------+------+--------+-------+
| TABLE_ID | SCHEMA | NAME             | FLAG | N_COLS | SPACE | …

One of our customers asked me whether it’s safe to enable the so-called USER_STATISTICS features of Percona Server in a heavy-use production server with many tens of thousands of tables.

If you’re not familiar with this feature, it creates some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful information. It lets you run queries to see which tables get the most reads and writes, for example. Another popular use for it is to find unused indexes.

I knew that some of our customers were using the feature enabled in production all the time, and I knew that Google and Facebook and others (the original developers) did also. But I didn’t know the …

MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks for different queries and explain the results so that you have a better understanding of when using the Hash Join will be best and when not. Although Hash Joins are available since MariaDB 5.3, but I will be running my benchmarks on the newer MariaDB 5.5.

Overview

Hash Join is a new algorithm introduced in MariaDB 5.3/5.5 that can be used for joining tables that have a equijoin conditions of the form tbl1.col1 = tbl2.col1, etc. As I mentioned above that what is actually implemented is the Classic Hash Join. But its known as Block Nested Loop Hash (BNLH) Join in MariaDB.
The Classic Hash Join Algorithm …

Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this case we were not allowed to make any query or application logic changes so we chose to implement the Barracuda file format and utilize compressed rows as this appealed to me for this mostly-read application. One quick way you can see if your rows will benefit from compression would be to read Peter Zaitsev’s blog post and execute:

SELECT AVG(LENGTH((`colTextField`)) FROM `t1` WHERE `id` < 1000

compare this to:

SELECT AVG(LENGTH(COMPRESS(`colTextField`))) FROM `t1` WHERE `id` < …

If you follow the general advices to create secure password the following ones seem to be secure, right?

The answer to the question is, “it depends on how you use them”

Notice that these passwords all contain multiple exclamation points and ampersands which are normally special characters for your shell. The people tend to copy and paste them directly to the terminal but that can lead to some non-predictable behavior and therefore cause big problems depending on the character combination.

Let’s execute the previous examples:

Login to mysql:

root@debian:~# mysql -uroot -ps11P$||!sh&2
mysql -uroot -ps11P$||shutdown -r now&2
[1] 1758
-bash: 2: command not found
root@debian:~# ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using …

Whenever I learn about a new technology, I typically want to know the major administrative touch points more than I want to know exhaustive detail about every configuration option.  Give me the gist, show me enough to get started, and give me a link to the manual.

XtraDB cluster (and Galera, the technology on which it is based) has been attracting a lot of interest in the community and we want to start presenting information about both what we know is essential, but also what we suspect will become essential as more and more production experience is logged with these important new technologies.

As such, I put together a baseline talk for getting you into a working knowledge of XtraDB …

I used to think one should never look at max_binlog_size, however last year I had a couple of interesting cases which showed that sometimes it may be very important variable to tune properly. I meant to write about it earlier but never really had a chance to do it. I have it now!

One of our customers was complaining that the database would lock up at random times and then it would go back to normal in just a few seconds. This was MySQL 5.0 running MyISAM/InnoDB mix, not heavily loaded. We used pt-stalk (at that time it was aspersa stalk) trying to figure out what is happening, however all we found was a spike in writes, many queries piled up and looking at the system process list it was quite obvious that page flush daemon was acting out. I/O Pattern was rather awkward – here is an output from …