Monday Jun 17, 2013

MySQL Camp at Bangalore - 19 June

As promised we are back with the 2nd edition of Bangalore MySQL User camp this Wednesday 19 June.

As requested we are going into the details of InnoDB and Performance Schema.

See the details here:

https://groups.google.com/forum/?fromgroups#!topic/bangalore-mysql-user-camp/gnqGPLKDB0s

Venue remains the same as last time : 

Our Kalyani Magnum Office behind Apollo hospital on the Bannerghatta road

Time - 5PM-6PM

Don't miss the chance to meet the MySQL engineering team at Bangalore and learn internals of MySQL.

Hope to see you there.

Cheers

Sanjay


Friday May 03, 2013

MEB : The journey so far 2010-2013

MySQL Enterprise Backup (MEB) was born 3 years ago as a newly branded avatar of InnoDB Hot backup. Wanted to share what has gone on so far, how we at Oracle think about backup, the milestones that we have achieved and the road ahead. The idea for this blog came to me after looking at Mikael's latest blog. While Mikael talks about MySQL, I want to talk about MEB.

When we started with InnoDB Hot backup the first challenge was to have it adhere to the development, quality and release processes for MySQL. This meant creating a quality plan, getting it into the development trees of MySQL and ensuring that each piece of new code went through architecture and code review. Though the initial implementer and architect of Hot backup continues to work with the MEB team, there were a host of new engineers to be trained. We also needed to ensure that the new (at that time) Barracuda InnoDB file format and incremental backup was supported. MEB 3.5.1 was the release which got these things along with the adherence to the development and quality model of MySQL.

The next challenge we faced was that of ensuring that MEB was on equal footing for both Linux and Windows. InnoDB hot Backup consisted of 2 programs - ibbackup and innobackup; innobackup is a Perl module. The main issue with using the program on Windows was the requirement to install Perl. With multiple Perl implementations and changing Perl versions, we did not want to check MEB compatibility for every implementation and new version of Perl when it was released. Even though the problem is the similar for Linux; the users of Linux are used to hacking around, changing paths and managing multiple versions of software like Perl. Windows users however expect things to just work. So we set about removing the Perl code altogether. This meant that the innobackup functionality had to be re-coded as a C program. Merging these 2 programs meant a major re-think on how the combined command line interface needed to look. The solution we came up with was to let the ibbackup and innobackup command line syntax remain as is, while the combined program had a similar but more logical "mysqlbackup" command line syntax. We were very happy with the new syntax because it freed us from history and MEB syntax became very much in line with the syntax of other MySQL clients. With the release of 3.6 we had a single C program, a more logical syntax , a product which was easier to install and worked exactly the same for all platforms.

We were getting to 2012 and database sizes were commonly approaching the 1 TB size. Such large databases meant the backup should ideally be streamed to tape. Interfacing with tape drives is a complicated and specialized activity. We neither had the bandwidth nor the expertize to handle tapes in MEB. The best solution was to adhere to a good common standard interface that was adopted by software which dealt with tapes. The interface we decided to support was Oracle's System Backup to Tape (SBT) . MEB was modified to be able to to stream the backup output to this interface. A common requirement for these interfaces is that they ideally want to deal with the backup as a single file. A single file can be streamed and restored by any software that speaks SBT. There is a whole ecosystem around SBT because it is the preferred way to backup the Oracle database. Changing MEB to think streaming instead of random access directory output was the challenge we overcame with the release of version 3.7 of MEB. With version 3.7, MEB could interface with Oracle Secure Backup, Symantec Net Backup and Tivoli Storage Manager and any other backup software that understood SBT.

After having resolved what we saw as the "basic" requirements for backup , our customers were demanding more performance and usability. We took up the challenge of performance for the 3.8 version of MEB. MEB was a monolithic single threaded program. We decided to internally break up MEB into 3 separate modules. The read phase, the process phase and the write phase. Each of these 3 phases could be multi-threaded. The number of threads dedicated for each phase were also made user configurable. All operations of backup including the "Applylog" and "copyback" were made multi-threaded. Read more details about this design approach and the performance gains in my blog - Truly Parallel backup. Meanwhile the new release of the MySQL Server 5.6 was also out. It was an interesting challenge to ensure that MEB understood the new MySQL 5.6 features and was able to take advantage of them. As on date MEB 3.8.1 is the only online backup solution that is compatible with the new features of MySQL 5.6.

Backup is like buying insurance. When all else fails you need to be sure that there is a working backup that is available to bring back your database. Backup is not something that can fail when it is needed. It is required that we are surefooted when dealing with such a critical activity. We take your trust in our solution very seriously. Thanks for being a part of the MEB journey (and for reading this blog) so far. The MySQL landscape is ever changing and we know that you desire more usability, performance and flexibility from MEB. We will try and ensure that we meet these expectations with the best possible quality. With every new MEB release you will see a more usable, flexible and performant MEB.

Thursday Mar 28, 2013

Report : Bangalore MySQL User Camp (BMUC) 22 Mar

We had a great kickoff to the Bangalore MySQL User camp on 22 March 2013 at the Oracle Kalyani Magnum Office.

There were 17 attendees from the MySQL community besides more than 15 MySQL engineers who attended the meet. Of these 3 community members were from groups inside Oracle. There was a mix of new and veteran MySQL users. The MySQL India teams gave a brief introduction about themselves, followed by a quick overdrew of MySQL and how it is different from other databases. The presentation about 5.6 features was presented last and the one that was most liked. There was a great networking over juice and Donuts after the meeting. There were lots of questions by the community on technical details of 5.6 features. Everyone interacted and there was help offered for ensuring that the next meeting on 19th June 2013 is an even greater success.

Thanks to all the attendees, and organizers. Hope to see you in person again in 3 months – on 19 June.

Meanwhile do keep writing and keep connected via linked-in(MySQL India) , Facebook (MySQL User Camp) and Google group(bangalore-mysql-user-camp).


Tuesday Mar 19, 2013

Bangalore MySQL User Camp - 22 Mar

We are planning the kickoff meeting of the Bangalore MySQL User Camp:
We would like to invite all MySQL enthusiasts at Bangalore to join.
Plan is to have at-least one meeting every quarter and give an opportunity to all to participate, attend and speak.

Agenda for 22 March Meeting:
1700-1715: Introductions
1715-1730 : Meet the MySQL Development team @ Bangalore
1730-1815 : Introduction to MySQL 5.6
1815-1830 : Q&A & Plan for the next meeting
1830-1900 : Tea and informal networking

Venue:
Conf Rom 0C001,
Ground Floor, B Wing, Oracle,
Kalyani Magnum Software technology Park
1st Main, J P Nagar 7th Phase,
Bangalore 560076

You can also join the google group "bangalore-mysql-user-camp" where we will be announcing further meetings etc.


Friday Feb 08, 2013

Truly Parallel backup (MySQL Enterprise Backup 3.8 and later)

How do you implement a parallel algorithm for a software which needs to be streamed to tapes?
How do you ensure that you have the capability to be able to tune the level of parallelism for varying input and output devices and varying levels of load?
These were some of the questions that we needed to answer when we were trying to implement multi-threading capability for MySQL Enterprise Backup (MEB).
The trivial way of achieving parallelism is by having the multiple threads pick up the different files (in a file per table) scenario. But this did not seem adequate because:
a) The sizes of these files (corresponding to the tables) could be different and then one large file would limit the level of parallelism since it would be processed by a single thread.
b) If you have to stream the backup how do you reconcile these multiple files being streamed by separate threads? Large backups are streamed directly to tape so it is better to have a single file being output and not multiple files.
c) If you buffer each file and wait for a file to be completely processed and then push it to tape then it is not true streaming because you are using intermediate disk space to save the incomplete portions of all the files.
The answer that we found was to implement the parallel algorithm using a horizontal strategy instead of a vertical strategy.


In the vertical strategy, each thread acts on a separate file. This limits streaming since the file sizes can vary.
In the horizontal strategy, each file is broken into a sections (denoted by multiple colors). A separate thread is assigned to operate on a single section.
Parallel operations are then possible for reading , processing and writing of these file subsections because no two threads will be operating on the same section of the file.
This setup is especially useful when using compression since there can be multiple threads performing compression while the read and write continues in parallel.
There may be additional overhead of ensuring that the buffers are in the correct order when they are written out, but since most of the buffers of the same size and having similar operations being performed, the overhead is minimal.
You get truly serialized output that is streamed to tape as it gets processed. If you are streaming to a remote host or to tape, there is almost no additional space required on your main server. We call this new mechanism parallel backup because we are achieving parallelism thereby making the backup faster. Indeed, using parallel backup may see up to 10 times the speed of a normal backup in certain scenarios.
The graph below shows the time it took for backup for MEB 3.7.1 v/s MEB 3.8 using varying number of threads.



Note : This is a 16 GB, 2 x 2000 MHz, 2 RAID DISKS (1027 GB,733.9GB) machine running Oracle Linux.

As you can see above; MEB 3.8 provides options to configure the number of threads you use for reading, writing and processing. Lets denote RT, PT and WT as number of Read, Process and Write threads respectively. Default values for MEB 3.8 is RT=3,PT=3, WT=3 which is changing in MEB 3.8.1 to RT=1, PT=6, WT=1.

This is close to the fastest backup we get in the graph above. The reason for not choosing RT=1, PT=12, WT=1 (which is the fastest) is because the CPU gets very highly utilized in the 1,12,1 configuration.

Remember, the read write throughput depends on your input and output devices. It is possible that multiple threads do not give you a better performance for read or write v/s a single thread.

There are also options available to have a configurable number of buffers used by these threads.

Each buffer is of size 16MB. You should have at-least [RT+PT+WT+ (MAX(RT,PT,WT) ] number of buffers so that you get optimal parallelism.

For Example if RT=1, PT=6, WT=1 then you should configure 1+6+1+6 = 14 buffers (default in MEB 3.8.1)

If for example you configure multiple threads but configure only 1 buffer then your backup is not taking advantage of parallelism at all. The read thread reads into the single buffer, buffer is then processed, written and then freed. The read thread is waiting for a buffer to be free to read into it; so it is like a serial process.

One more thing to note is that the number of buffers is limited by the memory limit configured for backup (default 300MB). Please ensure that you configure enough memory to be able to distribute it to the buffers you have configured. If the memory limit configured is less then what is required for the configured number of buffers; MEB will automatically decrease the number of buffers to fit into the memory limit. Based on the default values, if you are configuring more than 18 buffers you will need to increase the memory limit.

Please look at the previous 3.8 blog for detailed configuration examples :

https://blogs.oracle.com/mysqlenterprisebackup/entry/parallel_backup_in_mysql_enterprise

or into our documentation of this feature at

http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/backup-capacity-options.html

Cheers 

and remember the wise DBA advise:

If you don't verify your backups periodically it is like not having backups at all


Thursday Feb 07, 2013

MySQL Enterprise Backup 3.8.1 release for 5.6 Server

The MySQL Enterprise Backup 3.8.1 release's main goal was support MySQL 5.6 server. But also beyond that primary goal MEB team added some valuable new options and features to ensure you'll get most from the new features in 5.6 as well. At a glance, here are some of the highlights,

MEB copy of InnoDB undo log tablespaces

MySQL 5.6 introduces a new feature to store undo logs in separate files called as undo tablespaces for improved performance. These undo tablespaces are logically part of system  tablespace. All the commands associated with MEB - "backup", "apply-log" and "copy-back"  now take care of the undo tablespaces in the same way as they process the system tablespace. MEB now supports innodb_undo_directory[logs][tablespace] option variables. When backup is executed, undo datafiles (up to number specified by innodb_undo_tablespaces) are stored in same directory as the datafiles of system tablespace. During copy-back, files can be stored in a location specified by the user using option --innodb-undo-directory.

MEB support for Global transaction ID's

GTID feature is newly introduced in MySQL 5.6 server. GTID's help to track the data being replicated particularly with the automatic slave promotion when a master fails.
When server is started with GTID's enabled and backup is performed on the master server, mysqlbackup produces a new file called as gtid_executed.sql in meta backup directory . This file is used after restoring the backup data on slave server and contains GTID_PURGED option. This provides information from the server at the end of the backup, thereby ensuring that replication starts from the point in time when backup was taken.

UNC Path name support

MEB now supports UNC path names by specifying a location of network resource such as shared file, directory or printer. This feature helps to start backups using windows task scheduler when shared drives cannot be mapped to a drive letter. Support for UNC path names also allows MEB to take backups when user is not logged in.
eg: ./mysqlbackup --defaults-file=/home/my/my.cnf  --backup-dir="\\mysql\\testmeb\" backup

Where testmeb is shared network directory on windows.

When the shared name is corrupt / invalid, MEB detects and then tries to access the files pointed to by the path and prints an error message.

MEB support for different page size settings for InnoDB

InnoDB page size is the server parameter that is associated with all the innodb tablespaces in the MySQL instance. By default the value of this size used to be 16K in the versions less than MySQL 5.6. But from MySQL 5.6, this option is made user configurable to 4k, 8k, 16k etc. Starting from MEB 3.8.1, Backup will work successfully when server is started with different innodb_page_size values. The innodb-page-size option can also be specified in the mysqlbackup command line but MEB will ignore the option provided the connection to server is available. If innodb_page_size option is not specified in command line or if connection to server is not available, then the value of innodb-page-size is read from the header of the innodb data files.

 InnoDB Checksum Algorithm Support

MEB 3.8.1 introduces new option support of --innodb-checksum-algorithm in MySQL 5.6. This option can also be specified on the command line. A default option is used if its not  specified in the command line and also if the connection to the server is offline. Without the support for this new option, MEB could not start the server after sequence of backup, apply-log and restore operations. One thing to be noted here is,
a. Server backed with strict_crc32, strict_innodb or strict_none checksum algorithms should be restored with the same algorithm
b. Server backed with mixed algorithms should not be restored to a server with strict_* algorithms.

Backup of system tablespace with fractional megabyte.

It sometimes happens that InnoDB engine extends datafiles of system tablespace by few megabytes. But if the disk is full, then system tablespace will actually extend to  fractional megabytes. During such cases, MEB performs a consistency check on the sizes of InnoDB datafiles and if the size does not match the size of the file on the disk, a warning is reported.That is MEB does not backup the fractional datafile in the system tablespace.

Backup restore file per table tablespaces at different locations.

In MySQL 5.6, it is possible to create new InnoDB table with per-table tablespace outside of data directory where .ibd file should be created instead of default location in the database sub directory. For each .ibd file, a .isl file is created in the database subdirectory containing absolute path name acting like a symbolic link to actual tablespace file. All the MEB operations are now able to read the .isl files to locate the .ibd files during backup. During backup, both .isl and .ibd files are copied to the backup directory but .isl is renamed as .bl file. During copy-back, .ibd files are being copied to a location specified in .bl file. But if the target location is changed where the restore of the backup should be performed, then one needs to manually edit the .bl file before doing the restore and specify the abs path name where .ibd files should go.

The above mentioned are new features added to backup code but this release also includes various bug fixes, please take a look at the MEB 3.8.1 reference manuals for more details.

The MEB team has put great deal of efforts to ensure that latest release of MEB 3.8.1 is compliant with MySQL 5.6 server. Please try this new MEB 3.8.1 version with MySQL 5.6 server and as always send us your feedback / comments here. MEB 3.8.1 is now available in My Oracle Support site and will very soon be available in Oracle's Cloud delivery site.

Once again, I would like to thank entire MEB team to deliver this release on time and with many valuable new additions.


Friday Oct 05, 2012

MySQL Connect Conference: My Experience

It was a great experience to attend the MySQL Connect Conference for the first time ever. Personally I was very much enthralled to present about "How to make MySQL Backups" besides attending different sessions to absorb more knowledge about the technical prospects of MySQL. One of the agenda items in my presentation was "MySQL Enterprise Backup" functionality and features. There were total of 40 attendees in the session, who were very much interested about the MySQL Enterprise Backup product and gave positive feedback as well as areas of improvements on our product. Some of our features brought lot of excitement and smile amongst our customers including,

1. Performance improvements in MEB 3.8.0
2. Incremental Base option from MEB 3.7.1 where there is no need to specify the directory name of the previous backup to fetch the lsn values and instead can directly fetch from backup_history table using --incremental-base=history: last_backup
3. only-innodb-with-frm option introduced in MEB 3.7 version. A true online hot backup of InnoDB tables.

I also attended a session with similar topic "MEB Best Practices" conducted by Sanjay Manwani, where he double clicked all the features and best strategies of backup & restore. I also got an opportunity to attend other sessions including,

* Enabling the new generation of web and cloud services with MySQL 5.6 replication
* Getting the most out of MySQL with MySQL Workbench
* InnoDB compression for OLTP
* Scaling for the Web and Cloud with MySQL replication.

Above all, had some special moments in the conference including meeting some of the executives / colleagues for the first time f2f. On a whole, the first MySQL Connect conference was a great success in terms of manifesting the features of our products, direct feedback from customer and team building.  We also had some applauding yahoo moments when Tomas Ulin announced different releases including MySQL 5.6 RC, Connector Python 1.0 and ODBC 5.2 release, MySQL Cluster 7.3, additions to MySQL Enterprise edition etc.

Thursday Oct 04, 2012

Optimistic non-locking copy of InnoDB .frm files


MySQL Enterprise Backup(MEB) does hot backup of innodb data and log files. Till MEB 3.6.1, the user backs up the only innodb tables in a 3 step process:

STEP 1. Take backup using --only-innodb option

STEP 2. Temporarily make the table read only by executing “FLUSH TABLES WITH READ LOCK”

STEP 3. Manually copy the .frm files of innodb tables to the destination directory where backup is stored.

 


MEB 3.7.0 has an enhancement to innodb file copying. The .frm files gets copied along with the hot backup done for innodb files. I would like to make the blog a little interactive by explaining the feature as answers:


1. What are these .frm files?


The files containing the metadata, such as the table definition, of a MySQL table.

For backups, the full set of .frm files are always required along with the backup data, to be able to restore tables that are altered or dropped after the backup.


2. Can the .frm files not be copied by MEB itself?


--only-innodb-with-frm is the new option introduced in MEB 3.7.1 to do a copy of .frm files without locking the tables during backup operation itself. This is to reduce the pain of manually copying the .frm files. The option is intended for backups where you can ensure that no ALTER TABLE, CREATE TABLE, DROP TABLE, or other DDL statements modify the .frm files for InnoDB tables during the backup operation.


3. How is data consistency ensured?


MEB does validation of the .frm files after copying by comparing with the server directory to see if the timestamps of any of the .frm files is greater than the saved system time (check .frm time).  This change in timestamp of the .frm files will show if a table is altered during the process of backup.


The total number of frm files in the server directory is also verified against the copied contents. If the number of .frm files is less compared to server directory, it shows that table/tables have been dropped during the process of backup. If the number of .frm files is more compared to server directory, it shows that new table/tables have been created during backup operation.


4. How does MEB handle data inconsistency?


MEB copies the .frm files through several iterations,  does the validation and throws a WARNING if there is any inconsistency found in .frm files at the end of backup operation. This means the user is warned of some DDL operations that had occurred during backup operation, and has to manually copy the .frm files or do a backup again.


5. What is the option and explain its usage?


The option introduced is --only-innodb-with-frm which does optimistic copy of .frm files without locking. This can be used when the user wants to backup only innodb tables along with .frm files.

The option can take one of the 2 values: all | related.


--only-innodb-with-frm=all does copy of all .frm files of all innodb tables.

--only-innodb-with-frm=related works in conjunction with --include option.This is to allow partial backup of .frm files corresponding to the tables specified in --include.


Let me show the usage with example output:


./mysqlbackup -uroot --backup-dir=/logs/backupWithFrmAll --only-innodb-with-frm=all backup

MySQL Enterprise Backup version 3.7.1 [2012/06/05]

Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.


INFO: Starting with following command line ...

./mysqlbackup -uroot --backup-dir=/logs/backupWithFrmAll

       --only-innodb-with-frm=all backup


INFO: Got some server configuration information from running server.


IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup' run mysqlbackup

           prints "mysqlbackup completed OK!".


--------------------------------------------------------------------

                      Server Repository Options:

--------------------------------------------------------------------

 datadir                          =  /mysql/trydb/

 innodb_data_home_dir             =  

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /mysql/trydb/

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


--------------------------------------------------------------------

                      Backup Config Options:

--------------------------------------------------------------------

 datadir                          =  /logs/backupWithFrmAll/datadir

 innodb_data_home_dir             =  /logs/backupWithFrmAll/datadir

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /logs/backupWithFrmAll/datadir

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


mysqlbackup: INFO: Unique generated backup id for this is 13451979804504860

mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.

mysqlbackup: INFO: System tablespace file format is Antelope.

mysqlbackup: INFO: Found checkpoint at lsn 1656792.

mysqlbackup: INFO: Starting log scan from lsn 1656320.

120817 15:36:22 mysqlbackup: INFO: Copying log...

120817 15:36:22 mysqlbackup: INFO: Log copied, lsn 1656792.

         We wait 1 second before starting copying the data files...

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/ibdata1 (Antelope file format).

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table2.ibd (Antelope file format).

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table3.ibd (Antelope file format).

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table1.ibd (Antelope file format).

mysqlbackup: INFO: Opening backup source directory '/mysql/trydb/'

120817 15:36:23 mysqlbackup: INFO: Starting to backup .frm files in the subdirectories of /mysql/trydb/

mysqlbackup: INFO: Copying innodb data and logs during final stage ...

mysqlbackup: INFO: A copied database page was modified at 1656792.

         (This is the highest lsn found on page)

         Scanned log up to lsn 1656792.

         Was able to parse the log up to lsn 1656792.

         Maximum page number for a log record 0

mysqlbackup: INFO: Copying non-innodb files took 2.000 seconds

120817 15:36:25 mysqlbackup: INFO: Full backup completed!

mysqlbackup: INFO: Backup created in directory '/logs/backupWithFrmAll'


-------------------------------------------------------------

  Parameters Summary         

-------------------------------------------------------------

  Start LSN                  : 1656320

  End LSN                    : 1656792

-------------------------------------------------------------


mysqlbackup completed OK!


bash$ ls /logs/backupWithFrmAll/datadir/innodb1/

table1.frm  table1.ibd  table2.frm  table2.ibd  table3.frm  table3.ibd


Here the backup directory contains all the .frm files of all the innodb tables.


./mysqlbackup -uroot --backup-dir=/logs/backupWithFrm --include="innodb1.table3.*" --only-innodb-with-frm=related backup


MySQL Enterprise Backup version 3.7.1 [2012/06/05]

Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.


INFO: Starting with following command line ...

./mysqlbackup -uroot --backup-dir=/logs/backup371frm

       --include=innodb1.table3.* --only-innodb-with-frm=related backup


INFO: Got some server configuration information from running server.


IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup' run mysqlbackup

           prints "mysqlbackup completed OK!".


--------------------------------------------------------------------

                      Server Repository Options:

--------------------------------------------------------------------

 datadir                          = /mysql/trydb/

 innodb_data_home_dir             =  

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /mysql/trydb

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


--------------------------------------------------------------------

                      Backup Config Options:

--------------------------------------------------------------------

 datadir                          =  /logs/backupWithFrm/datadir

 innodb_data_home_dir             =  /logs/backupWithFrm/datadir

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /logs/backupWithFrm/datadir

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


mysqlbackup: INFO: Unique generated backup id for this is 13451973458118162

mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.

mysqlbackup: INFO: The --include option specified: innodb1.table3.*

mysqlbackup: INFO: System tablespace file format is Antelope.

mysqlbackup: INFO: Found checkpoint at lsn 1656792.

mysqlbackup: INFO: Starting log scan from lsn 1656320.

120817 15:25:47 mysqlbackup: INFO: Copying log...

120817 15:25:47 mysqlbackup: INFO: Log copied, lsn 1656792.

         We wait 1 second before starting copying the data files...

120817 15:25:48 mysqlbackup: INFO: Copying /mysql/trydbibdata1 (Antelope file format).

120817 15:25:49 mysqlbackup: INFO: Copying /mysql/trydbinnodb1/table3.ibd (Antelope file format).

mysqlbackup: INFO: Opening backup source directory '/mysql/trydb'

120817 15:25:49 mysqlbackup: INFO: Starting to backup .frm files in the subdirectories of /mysql/trydb

mysqlbackup: INFO: Copying innodb data and logs during final stage ...

mysqlbackup: INFO: A copied database page was modified at 1656792.

         (This is the highest lsn found on page)

         Scanned log up to lsn 1656792.

         Was able to parse the log up to lsn 1656792.

         Maximum page number for a log record 0

mysqlbackup: INFO: Copying non-innodb files took 2.000 seconds

120817 15:25:51 mysqlbackup: INFO: Full backup completed!

mysqlbackup: INFO: Backup created in directory '/logs/backupWithFrm'


-------------------------------------------------------------

  Parameters Summary         

-------------------------------------------------------------

  Start LSN                  : 1656320

  End LSN                    : 1656792

-------------------------------------------------------------


mysqlbackup completed OK!


bash$ ls /logs/backupWithFrm/datadir/innodb1/

table3.frm table3.ibd


Thus the backup directory contains only the .frm file matching the innodb table name specified in --include option.


In a nutshell, we present our great new option --only-innodb-with-frm which is a true hot InnoDB-only backup with .frm files, but with an additional check, if any DDL happened during the backup. If a DDL has happened, the DBA can decide if to repeat the backup, or to live with the potential inconsistency. This is the ideal solution for users that have all their "real" data in InnoDB and seldom change their schemas.


You may also like:

http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/backup-partial-options.html



Monday Oct 01, 2012

Slides for MySQL Connect session "Save Your Data: How to Make MySQL Backups"

I just uploaded slides for MySQL Connect session "Save Your Data: How to Make MySQL Backups" which we did together with Hema Sridharan. Enjoy!

Saturday Sep 29, 2012

MEB Support to NetBackup MMS

In MySQL Enterprise Backup 3.6, new option was introduced to support backup to tapes via SBT interface. SBT stands for System Backup to Tape, an Oracle API that helps to perform backup and restore jobs via media management software such as Oracle's Secure Backup (OSB). There are other storage managers like IBM's Tivoli Storage Manager (TSM) and Symantec's Netbackup (NB) which are also supported by MEB but we don't guarantee that it will function as expected for every release. MEB supports SBT API version 2.0

In this blog, I am primarily going to focus the interface of MEB and Symantec's NB. If we are using tapes for backup, ensure that tape library and tape drives are compatible.

Test Setup

1. Install NB 7.5 master and media servers in Linux OS. ( NB 7.1 can also be used but for testing purpose I used NB 7.5)
2. Install MEB 3.8 also in Linux OS.
3. Install NB admin console in your windows desktop and configure the NB master server from there.

Note: Ensure that you have root user permission to install NetBackup.

Configuration Steps for MEB and NB

Once MEB and NB are installed,
  • Ensure that NB is linked to MEB by specifying the library /usr/openv/netbackup/bin/libobk.so64 in the mysqlbackup command line using --sbt-lib-path.
  • Configure the NB master server from windows console. That is configure the storage units by specifying the Storage unit name, Disk type, Media Server name etc.
  •  Create NetBackup policies that are user selectable. But please make sure that policy type is "Oracle".
  •  Define the clients where MEB will be executed. Some times this will be different host where MEB is run or some times in same Media server where NB and tapes are attached.

Now once the installation and configuration steps are performed for MEB and NB, the next part is the actual execution.

MEB should be run as single file backup using --backup-image option with prefix sbt:(it is a tag which tells MEB that it should stream the backup image through the SBT interface) which is sent to NB client via SBT interface . The resulting backup image is stored where NB stores the images that it backs up.

 The following diagram shows how MEB interacts with MMS through SBT interface.

Backup

The following parameters should also be ready for the execution,
    --sbt-lib-path : Path to SBT library specific to NetBackup MMS. SBT lib for NetBackup  is in /usr/openv/netbackup/bin/libobk.so64
    --sbt-environment: Environment variables must be defined specific to NetBackup. In our example below, we use
    NB_ORA_SERV=myserver.com,
    NB_ORA_CLIENT=myserver.com,
    NB_ORA_POLICY=NBU-MEB
    ORACLE_HOME = /export/home2/tmp/hema/mysql-server/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
./mysqlbackup --port=13000 --protocol=tcp --user=root --backup-image=sbt:bkpsbtNB --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --sbt-environment="NB_ORA_SERV=myserver.com, NB_ORA_CLIENT=myserver.com, NB_ORA_POLICY=NBU-MEB, ORACLE_HOME=/export/home2/tmp/hema/mysql-server/” --backup-dir=/export/home2/tmp/hema/MEB_bkdir/ backup-to-image

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Once backup is completed successfully, this should appear in Activity Monitor in NetBackup Console.

For restore,  image contents has to be extracted using image-to-backup-dir command and then apply-log and copy-back steps are applied.


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

./mysqlbackup --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64  --backup-dir=/export/home2/tmp/hema/NBMEB/ --backup-image=sbt:bkpsbtNB image-to-backup-dir
-----------------------------------------------------------------------------------------------------------------------------------
Now apply logs as usual, shutdown the server and perform restore, restart the server and check the data contents.

  • ./mysqlbackup   ---backup-dir=/export/home2/tmp/hema/NBMEB/  apply-log
  • ./mysqlbackup --datadir=/export/home2/tmp/hema/mysql-server/mysql-5.5-meb-repo/mysql-test/var/mysqld.1/data/  --backup-dir=/export/home2/tmp/hema/MEB_bkpdir/ innodb_log_files_in_group=2 --innodb_log_file_size=5M --user=root --port=13000 --protocol=tcp copy-back

The NB console should show 'Restore" job as done. If you don't see that there is something wrong with MEB or NetBackup.

You can also refer to more detailed steps of MEB and NB integration in whitepaper
here




Tuesday Aug 21, 2012

Parallel backup in MySQL Enterprise Backup v3.8.0

MySQL Enterprise Backup (MEB) supports backup and copyback of mysql database objects into/from different disks or tape drives. On backup, MEB reads the data from the disk, processes it in form of validation or compression(if requested by user) and writes to disk. These operations were performed in a serial fashion till 3.7.1, wherein the reading of data is completed, followed by processing, and then writing. The performance of this can be increased by pipelining the read, process and write operations to be run in parallel, and this is what exactly is done in the latest release MEB 3.8.0 so as to reduce the time taken for all kinds of backups and restore operations.

How does this pipeline parallelism work?

This pipelined parallel execution of read, process(if any) and write operations is achieved by invoking multiple threads for different operations and usage of buffers. Each operation is split into segments and threads operate on different segments from different iterations concurrently. Each segment is assigned one or more worker threads and in-queue buffers which stores the work to be processed by that stage.

Here is an example:

Each thread T1-T3 does the following repeatedly for 3 segments S1-S3, where:

S1: Read block from source directory/file
S2: Process (compress/validate)
S3: Write processed block to destination directory/file

T1 will run S1 and then insert the block in S2′s in-queue.
T2 will compress the block and enqueue it for writing by S3. 
T3 runs S3 to write the block. 
Thus, T1, T2, and T3 can be working concurrently on stages from different iterations, thereby increasing performance.

Now, let us take a look at the new options introduced in version 3.8.0 for this parallel execution using threads. The user can tune the performance of backup and restore operations for the particular configuration by specifying thread values for different options as follows:

--read-threads option controls the maximum number of READ threads to run in parallel.

--process-threads option controls the maximum number of PROCESS threads to run in parallel.

--write-threads option controls the maximum number of WRITE threads to run in parallel.

--number-of-buffers option is used to specify maximum number of buffers could be created and used for MEB operations.These buffers, each of size 16MB, are used by READER, PROCESSOR and WRITER thread pools. This value controls the level of parallelism in MEB operations. The value ‘1’ causes the threads to run in serial. If not specified by the user, number of buffers is usually calculated as the sum of number of READ, PROCESS and WRITE threads + MAX of (READ, PROCESS, WRITE) threads.

ie., Let (READ, PROCESS, WRITE) threads are (5,8,3). Then number-of-buffers needed are (5 + 8 + 3 + MAX(5,8,3)) ==> (5+8+3+8) = 24 buffers.These extra 8 buffers would be used for prefetching and pre-processing by faster thread pool.For eg., if READER has completed reading a block of data, and processing takes time to complete, then READER can use these extra buffers to read the next block of data instead of waiting on PROCESSOR, followed by WRITER, to put buffers into free buffer queue. You can read more about the thread options and number-of-buffers here

Lets look at an example to show the execution of backup operation using MEB 3.8.0:
====================================================================
bash$ ./mysqlbackup -uroot –backup-dir=/backups backup

Snippet of output:

mysqlbackup: INFO: Unique generated backup id for this is 13444082910667734
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: Creating 12 buffers each of size 16777216.
120716 08:05:55 mysqlbackup: INFO: Full Backup operation starts with following threads
3 read-threads 3 process-threads 3 write-threads
120716 08:05:55 mysqlbackup: INFO: System tablespace file format is Antelope.
120716 08:05:55 mysqlbackup: INFO: Starting to copy of all innodb files...
120716 08:05:55 mysqlbackup: (Antelope file format). INFO: Copying /data/ibdata1
mysqlbackup: Progress in MB: 200
120716 08:05:59 mysqlbackup: INFO: Found checkpoint at lsn 1600768722.
mysqlbackup: Progress in MB: 400 600 800 1000
...
...
...
120808 12:14:06 mysqlbackup: INFO: Completing the copy of all non-innodb files.
120808 12:14:06 mysqlbackup: INFO: A copied database page was modified at 1604792918.
(This is the highest lsn found on page)
Scanned log up to lsn 1604794840.
Was able to parse the log up to lsn 1604794840.
Maximum page number for a log record 16418
120808 12:14:07 mysqlbackup: INFO: All tables unlocked
120808 12:14:07 mysqlbackup: INFO: All MySQL tables were locked for 0.934 seconds.
120808 12:14:07 mysqlbackup: INFO: Full Backup operation completed successfully.
120808 12:14:07 mysqlbackup: INFO: Backup created in directory '/backups'
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 1604792832
End LSN : 1604794840
-------------------------------------------------------------
mysqlbackup completed OK!
====================================================================
As you can see, MEB 3.8 starts backup operation with 3 read, 3 process and 3 write threads by default respectively, though the thread values are not specified by the user. This is to ensure parallelism. The default values may/may not differ for various MEB operations. For eg, for apply-log, the default values are 1 read-thread and 1 process-thread.

** Default values for various operations are defined after reviewing the performance test results on different machine configurations based on the number of CPUs, speed of RAM, etc.

Here’s another example to show the complete usage of thread options introduced in MEB 3.8.0:
====================================================================
bash$ ./mysqlbackup -uroot –backup-dir=/backups --read-threads=3 --process-threads=4 --write-threads=3 backup

Snippet of output:

mysqlbackup: INFO: Unique generated backup id for this is 13444081787458390
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
120808 12:13:00 mysqlbackup: INFO: Full Backup operation starts with following threads
3 read-threads 4 process-threads 3 write-threads
120808 12:13:00 mysqlbackup: INFO: System tablespace file format is Antelope.
120808 12:13:00 mysqlbackup: INFO: Starting to copy of all innodb files...
...
...
...
120808 12:15:15 mysqlbackup: INFO: A copied database page was modified at 1604795408.
(This is the highest lsn found on page)
Scanned log up to lsn 1604797288.
Was able to parse the log up to lsn 1604797288.
Maximum page number for a log record 16418
120808 12:15:15 mysqlbackup: INFO: All tables unlocked
120808 12:15:15 mysqlbackup: INFO: All MySQL tables were locked for 0.912 seconds.
120808 12:15:15 mysqlbackup: INFO: Full Backup operation completed successfully.
120808 12:15:15 mysqlbackup: INFO: Backup created in directory '/backups'
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 1604795392
End LSN : 1604797288
-------------------------------------------------------------
mysqlbackup completed OK!
====================================================================
As you can observe in both the examples, the number of buffers is calculated according to the thread values. User’s value for --number-of-buffers option will take priority over the default calculation of number of buffers.

How to choose the thread values for optimal performance:

For the same combination of thread values, performance has increased on a system with RAID configuration with multi-core CPUs compared to a machine with non-RAID storage configuration.

I would like to give you some guidelines based on the experimental executions that has been performed on our machines:

1. Monitor the resource utilization for determining the CPU usage, number of I/O requests, etc.

2. Always have the source and target directories/locations at different drives.

3. Increase the value of read/write threads if source/destination drives can handle more I/O requests respectively

4. Note that the extra performance is at the expense of enhanced CPU utilization. Please set the number of process-threads based on the CPU cycles that can be spared for backup on your setup.

5. The combination of read-threads=1, process-threads=6, write-threads=1 have given the optimal performance on the following configuration:

1. A non-RAID configuration having:
○ 64-bit Windows 7 Operating System
○ 8 GB RAM
○ Intel Core i5-2400 CPU @3.10 GHz

2. A RAID configuration having:

○ OEL6.2/64 Operating System
○ 16 GB RAM
○ 2 x 2000 MHz CPUs
○ 2 RAID DISKS (1027 GB,733.9GB)

as compared to the previous version 3.7.1

Note : The maximum value for each thread option is 15.

For more details on performance tuning, please take a look at the user manual.

You may also like:

https://blogs.oracle.com/MySQL/entry/new_parallel_backups_in_mysql
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/meb-news-3-8-0.html
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/index.html

About

MySQL MEB Team Blog

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today