Showing entries 31 to 40 of 82
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Backups (reset)
How fast is FLUSH TABLES WITH READ LOCK?

A week or so ago at the MySQL conference, I visited one of the backup vendors in the Expo Hall. I started to chat with them about their MySQL backup product. One of the representatives told me that their backup product uses FLUSH TABLES WITH READ LOCK, which he admitted takes a global lock on the whole database server. However, he proudly told me that it only takes a lock for "a couple of milliseconds." This is a harmful misconception that many backup vendors seem to hold dear.

The truth is, this command can take a lock for an indeterminate amount of time. It might complete in milliseconds on a test system in a laboratory, but I have seen it take an extremely long time on production systems, measured in many minutes, or potentially even hours. And during this time, the server will get completely blocked (not just read-only!) To understand why, let's look at what this command actually does. There are several important parts of processing …

[Read more]
Ignite MySQL at the MySQL Conference and Expo

Sarah Novotny speaking at MySQL Conference 2010

I’ll be giving a talk tonight at the Ignite MySQL! For those of you who don’t know about this format, I suggest that you check out Ignite!.  The basic premise is:

Fast-paced, fun, thought-provoking, social, local, global—Ignite is all of these and more. It’s a high-energy evening of 5-minute talks by people who have an idea—and the guts to get onstage and share it with their hometown crowd.

My talk will be called Backups Don’t Make Me Money

Backups aren’t exciting or glamorous. They don’t make you money and there’s no such thing as a valid backup. But, understanding your restores is exciting! Let me draw you a few pictures of database restores at their most ironic, entertaining, and even thrilling. Five minutes, 20 …

[Read more]
Ignite MySQL at the MySQL Conference and Expo

I’ll be giving a talk tonight at the Ignite MySQL! For those of you who don’t know about this format, I suggest that you check out Ignite!.  The basic premise is:

Fast-paced, fun, thought-provoking, social, local, global—Ignite is all of these and more. It’s a high-energy evening of 5-minute talks by people who have an idea—and the guts to get onstage and share it with their hometown crowd.

My talk will be called Backups Don’t Make Me Money

Backups aren’t exciting or glamorous. They don’t make you money and there’s no such thing as a valid backup. But, understanding your restores is exciting! Let me draw you a few pictures of database restores at their most ironic, entertaining, and even thrilling. Five minutes, 20 slides auto advancing every 15 seconds.  It will be …

[Read more]
xtrabackup-1.2

Dear Community,

The notice is hereby given that next version 1.2 of XtraBackup software is released.

The list of changes in this version includes:
Changelog:

  • XtraBackup supports now XtraDB 10
  • tar4ibd supports variable page size and fast_checksum of XtraDB
  • Supported 32bit platform for Centos 5, Centos 4, Debian lenny and etch, Ubuntu Karmic, Jaunty, Intrepid, Hardy, FreeBSD

Fixeb bugs in this release:

The binary packages for as well as source code of the XtraBackup is available on http://www.percona.com/percona-builds/XtraBackup/XtraBackup-1.2/.

[Read more]
Kontrollkit – new backup script is partition space aware

I’ve been wanting to write a backup script for a while now that does the following: reads the partition information for the directory that you are backing up into and computes the used/available/percentage-available space. Then it reads the total data size from the MySQL tables and ensures that you have enough space on disk (for [...]

Statistics of InnoDB tables and indexes available in xtrabackup

If you ever wondered how big is that or another index in InnoDB ... you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR - since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The thanks for this feature goes to a well known Social Network who sponsored the development.

We chose to put this into xtrabackup for a couple of reasons - the first is that running statistics on your backup database does not need to hurt production servers, and the second reason is that running statistic on a stopped database is more accurate than with online (although online is also supported, but you may have inexact results).

Let's see how it works. I have one table with size 13Gb what was filled during about 2.5 years.

[Read more]
MySQL 5.1 and InnoDB Hot Backup Gotcha

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:

mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed

The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are …

[Read more]
Just how useful are binary logs for incremental backups?

We've written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

  • We have 2 Servers in a Master-Slave topology.
  • The database size is 100 GB (same tables on each).
  • The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)
  • The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn't take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary logs  it would take an additional 20 hours ((12 * …

[Read more]
Improving InnoDB recovery time

Speed of InnoDB recovery is known and quite annoying problem. It was discussed many times, see:

http://bugs.mysql.com/bug.php?id=29847

http://dammit.lt/2008/10/26/innodb-crash-recovery/

This is problem when your InnoDB crashes, it may takes long time to start. Also it affects restoring from backup (both LVM and xtrabackup / innobackup)

In this is simple test, I do crash mysql during in-memory tpcc-mysql benchmark with 16 running threads.
MySQL params are:

PLAIN TEXT CODE:

  1. innodb_buffer_pool_size=16G
  2. innodb_log_files_in_group=3
  3. innodb_log_file_size=512M

So let's take standard MySQL …

[Read more]
xtrabackup-0.8

Dear community,

The release 0.8 of the opensource backup tool for InnoDB and XtraDB is available for download.

Key features:

tar4ibd is made to be sure that read of InnoDB page is consistent. Before we had some complains what in stream mode some pages are getting corrupted, and we suspect tar can do read of pages in time when they changed. So we patches libtar to make read consistent.

Export is added to support moving .ibd tablespaces …

[Read more]
Showing entries 31 to 40 of 82
« 10 Newer Entries | 10 Older Entries »