For long time as main backup solution for MySQL on Linux we have been using LVM snapshots. Performance concerns from performance critical envinronment caused us to do LVM performance research which showed horrible results forcing us to look at more performing alternatives.

Innodb Hot Backup is a good working solution but it is not Open Source and so we can't ensure it will support all XtraDB features - extra undo slots, rollback segments etc.

After considering all alternatives we decided to develop Percona Xtrabackup tool, which will use same backup approach as InnoDB Hot Backup, that is taking online copy of InnoDB table files and transactional logs. This is going to be fully OpenSource, GPL licensed backup solution for MySQL (MyISAM and InnoDB for …

I don’t want to post the link to this (perhaps, it was left public unintentional?) but here is what I stumbled upon recently. This is a log of production maintenance of IT systems in Perth, Western Australia (as far as I could say):

Good idea but shouldn’t companies keep this sort of information private?

Here is nice gotcha which I've seen many times and which can cause just a minefield for many reasons.
Lets say you had a system storing articles and you use article_id as unsigned int. As the time goes and you see you may get over 4 billions of articles you change the type for article_id to bigint unsigned but forget linked tables.

PLAIN TEXT SQL:

1. mysql> CREATE TABLE article_comment(article_id int UNSIGNED NOT NULL, comment_id int UNSIGNED NOT NULL, KEY(article_id));
2. Query OK, 0 rows affected (0.11 sec)
3.  
4. mysql> INSERT INTO article_comment VALUES(4300000000,1);
5. Query OK, 1 row affected, 1 warning (0.00 sec)
6.  
7. mysql> INSERT INTO article_comment VALUES(4300000001,1); …

There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?

I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users - you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.

Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits, for example there is a dedicated slave available for all long reporting queries. In case …

Today we announce release 1.0.2-2 of our XtraDB storage engine.

Here is a list of enhancements:

• split-buffer_pool_mutex

The patch splits global InnoDB buffer_pool mutex into several and eliminates waitings on flush IO and mutex when there is no enough free buffers. It helps if you have performance drops when data does not fit in memory.

• innodb_extra_undoslots

InnoDB has a concurrent transaction limit of 1024 because in the standard InnoDB the number of undo slots is fixed value. This patch expands the maximum number of undo slots to 4072 and allows better utilizing modern hardware. (Thank …

When your goal is to optimize application performance it is very important to understand what goal do you really have. If you do not have a good understanding of the goal your performance optimization effort may well still bring its results but you may waste a lot of time before you reach same results as you would reach much sooner with focused approach.

The time is critical for many performance optimization tasks not only because of labor associated expenses but also because of the suffering - slow web site means your marketing budget is wasted, customer not completing purchases, users are leaving to competitors, all of this making the time truly critical matter.

So what can be the goal ? Generally I see there are 2 types of goals seen in practice. One is capacity goal this is when the system is generally overloaded so everything is slow, when you're just looking to see how you can get most out of your existing …

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

Recovering from .frm for Innodb Table

If we simply copy .frm file back to the database we will see the following MySQL creative error message:

PLAIN TEXT SQL:

1. mysql> SHOW TABLES;
…

Today we officially announce our new storage engine, "Percona XtraDB", which is based on the InnoDB storage engine. It's 100% backwards-compatible with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to scale better on modern hardware, and includes a variety of other features useful in high performance environments.

Percona XtraDB includes all of InnoDB's ACID-compliant design and advanced MVCC architecture, and adds features, more tunability, more metrics, more scalability on many cores, and better memory usage. We choose features and fixes based on customer requests and on our best judgment of real-world needs. We have not included all the InnoDB patches available. For example Google's well-known InnoDB patch set is omitted (at least for now).

The first version of our new storage engine is 1.0.2-1, which is forked from InnoDB-plugin-1.0.2. …

One command, which few people realize exists is SHOW OPEN TABLES - it allows you to examine what tables do you have open right now:

PLAIN TEXT SQL:

1. mysql> SHOW open TABLES FROM test;
2. +----------+-------+--------+-------------+
3. | DATABASE | TABLE | In_use | Name_locked |
4. +----------+-------+--------+-------------+
5. | test     | a     |      3 |           0 |
6. +----------+-------+--------+-------------+
7. 1 row IN SET (0.00 sec)

This command lists all non-temporary tables in the table-cache, showing each of them only once (even if table is opened more than ones)

In_use show how many threads are …

Well, actually it is not reading future, but just mysql-5.1.30.rhel4.spec file from MySQL RedHat 4 SRPM.

I found there few MySQL Enterprise Editions, namely:

MySQL Enterprise Server - Advanced Edition
MySQL Enterprise Server - Pro Edition
MySQL Enterprise Server - Classic Edition

What is difference ? Let's see.

For MySQL Enterprise Server - Advanced Edition:
%define PARTITION_BUILD 1
%define INNODB_BUILD 1

For MySQL Enterprise Server - Pro Edition:
%define PARTITION_BUILD 0
%define INNODB_BUILD 1
So this one comes without Partitions.

And MySQL Enterprise Server - Classic Edition
%define PARTITION_BUILD 1
%define INNODB_BUILD 0
Which I am finding most interesting ... with Partitioning but without InnoDB ?

Entry posted by Vadim | …