Introduction to Percona Server, XtraDB and Xtrabackup Morgan Tocker morgan@percona.com
1
Who is Percona?
CONSULTING
SUPPORT
TRAINING
MAINTENANCE
.. for MySQL.
2
Who is Percona?
CONSULTING
SUPPORT
TRAINING
MAINTENANCE
.. for MySQL.
2
Who is Percona?
CONSULTING
SUPPORT
TRAINING
MAINTENANCE
.. for MySQL.
2
Who is Percona?
CONSULTING
SUPPORT
TRAINING
MAINTENANCE
.. for MySQL.
2
Who is Percona?
CONSULTING
SUPPORT
TRAINING
MAINTENANCE
.. for MySQL.
2
Today’s Agenda ★ ★ ★ ★ ★
3
Percona products at 10 000 feet. A closer look at Percona Server with XtraDB. A closer look at XtraBackup. A quick look into the future. Finding more information.
Percona Server?
4
Official Oracle Product
License
MySQL Server
GPL/Commercial
The InnoDB Storage Engine (Plugin edition)
GPL/Commercial
InnoDB Hot Backup
Commercial
Official Percona Product
License
Percona Server
GPL
The XtraDB Storage Engine
GPL
XtraBackup
GPL
Percona Server is... ★
A Percona sponsored distribution of the MySQL Server. ✦
✦
★
5
Current releases are based on MySQL 5.1. Earlier releases based on MySQL 5.0 are available.
Contains all of the great work of the MySQL engineering team + additional enhancements on top.
XtraDB is.. ★
An enhanced version of the InnoDB storage engine. ✦
✦
★
6
We ship Percona Server 5.1 with XtraDB included. You don’t have to download it separately, or do anything special to enable it.
XtraDB is based on the “InnoDB plugin”.
XtraBackup is... ★ ★
★
7
An re-implementation of InnoDB’s hot backup utility. XtraBackup can backup MyISAM, Archive and InnoDB tables. It can backup MySQL, Percona and MariaDB servers.
A fork or a spoon? ★
Percona Server and XtraDB are forks by definition. ✦
★
Don’t let the word fork scare you. ✦
✦
✦
✦
8
We change the name to help users identify our distinctively different set of features. ~30K lines changed out of millions. We rebase off newer official MySQL releases. License remains as GPL (Open Source). Compatibility is maintained.
A word on compatibility: ★
★
The file format between MySQL with InnoDB to Percona Server with XtraDB does not change.* XtraDB identifies itself as InnoDB: ✦
★
If you want to revert back, just remove the Percona Packages and install MySQL packages. ✦
9
ALTER TABLE my_table ENGINE=INNODB; or CREATE TABLE my_table (a INT) ENGINE=INNODB; still works!
Both Debian/Red Hat packages don’t remove the data directory when you remove the programs.
* By default. You must explicitly enable features for incompatibility.
Compatibility (cont.) ★
★ ★
10
When you connect from your PHP/Python/Perl/C/C++/ Ruby/.NET/<insert other language> application to Percona Server, you do not need to make any special changes. Your application just sees “MySQL” on port 3306. Really, nothing here changes.
Today’s Agenda ★ ★ ★ ★ ★
11
Percona products at 10 000 feet. A closer look at Percona Server with XtraDB. A closer look at XtraBackup. A quick look into the future. Finding more information.
Release Model Safe high value changes, made to market fast.
★
✦
✦
Short release cycle. Changes are mostly incremental enhancements.
Rebases against new MySQL releases.
★
✦
i.e. Version number should be reads as: Percona-Server-server-51-5.1.52-rel11.6.140.rhel5.x86_64.rpm
Major MySQL Version (MySQL 5.1)
XtraDB Release Version (Release 11.6) Minor MySQL Version (MySQL 5.1.52)
12
Releases History ★
Historical average of a new release every 2 months: Release-1
Dec 2008
Release-10.1
May 2010
Release-2
Dec 2008
Release-11.0*
June 2010
Release-3
Feb 2009
Release-11.1
June 2010
Release-4
April 2009
Release-11.2
July 2010
Release-5
April 2009
Release-11.3
September 2010
Release-6
July 2009
Release-12.0
September 2010
Release-7
August 2009
Release-11.4
October 2010
Release-8
Oct 2009
Release-12.1
October 2010
Release-9
Jan 2010
Release-11.5
October 2010
Release-9.1
March 2010
Release-11.6
November 2010
Release-10
April 2010
13 * Recalled Release. The 12.1 branch is now ‘Release Candidate’.
So what changes? ★
Most of the enhancements fall into two categories: ✦
✦
14
Performance Improvements Operational / Usability Features
Performance Improvements Improved Buffer Pool Scalability
Separate location of double write buffer*
Improved IO Path + adaptive checkpointing
Transaction logs larger than 4G supported.
Remove excess fcntl calls
Faster Crash Recovery Insert buffer controls Separate purge thread Completely disable the query cache. Faster page checksums*
15
Strip comments before using query cache
Data dictionary memory consumption controls Support for different page sizes*
Improved Rollback Segment Scalability*
Increased number of undo slots* Per session configuration of innodb_flush_log_at_trx_commit
* Changes on disk format. Must use mysqldump to revert to regular MySQL releases.
It’s not all raw performance ★
Many people get carried away with performance. ✦
✦
An earlier benchmark showing Performance of XtraDB 9.x versus regular MySQL releases. Higher is better. Newer benchmarks are available at http://www.percona.com/ software/benchmarks/
16
Consistency of timing is also more important than raw throughput. Without diagnostics, you won’t get what you are entitled to.
Stability of results â&#x2DC;&#x2026;
â&#x2DC;&#x2026;
Benchmarks is available at http://www.percona.com/software/benchmarks/
17
A lot of engineering work has been done to minimize variance. Sharp drops in MySQL releases are caused by checkpointing activities.
Usability Enhancements Show data dictionary Show contents of the buffer pool Save index statistics between restarts Import / export of buffer pool contents
Store buffer pool in shared memory segment
18
Bug fixes - ahead User / Index / Table statistics of time
Log connection errors Import / export of innodb_file_per_table tables
Transactional Replication
Deadlock counter Retain query response time distribution.
Disable automatic statistics regeneration
Advise in processlist when waiting on Query cache mutex.
Better handling of corrupted tables
Improved slow query log
Show Temporary Tables
The slow query log $ tail /var/log/mysql.slow .. MySQL Server # Time: 100924 13:58:47 # User@Host: root[root] @ localhost [] # Query_time: 399.563977 Lock_time: 0.000110 Rows_sent: 1 Rows_examined: 46313608 SET timestamp=1285336727; select STRAIGHT_JOIN count(*) as c, person_id FROM cast_info FORCE INDEX(person_id) INNER JOIN title ON (cast_info.movie_id=title.id) WHERE title.kind_id = 1 GROUP BY cast_info.person_id ORDER by c DESC LIMIT 1;
$ mysql -e “SET GLOBAL log_slow_verbosity = ‘full’;” Percona Server $ tail /var/log/mysql.slow .. # Time: 100924 13:58:47 # User@Host: root[root] @ localhost [] # Thread_id: 10 Schema: imdb Last_errno: 0 Killed: 0 # Query_time: 399.563977 Lock_time: 0.000110 Rows_sent: 1 Rows_examined: 46313608 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 131 Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 25194923 # InnoDB_trx_id: 1403 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes # Filesort: Yes Filesort_on_disk: Yes Merge_passes: 5 # InnoDB_IO_r_ops: 1064749 InnoDB_IO_r_bytes: 17444847616 InnoDB_IO_r_wait: 26.935662 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 65329 SET timestamp=1285336727; select STRAIGHT_JOIN count(*) as c, person_id FROM cast_info FORCE INDEX(person_id) INNER JOIN title ON (cast_info.movie_id=title.id) WHERE title.kind_id = 1 GROUP BY cast_info.person_id ORDER by c DESC LIMIT 1;
19
Slow log (cont.) ★
In XtraDB 12.1, this is even further enhanced:
$ mysql -e “SET GLOBAL profiling_server = 1”; $ tail /var/log/mysql.slow .. # Query_time: 4.555235 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 1 # Profile_starting: 4.554799 Profile_starting_cpu: 0.000000 Profile_checking_permissions: 0.000095 Profile_checking_permissions_cpu: 0.000000 Profile_Opening_tables: 0.000088 Profile_Opening_tables_cpu: 0.000000 Profile_init: 0.000056 Profile_init_cpu: 0.000000 Profile_optimizing: 0.000046 Profile_optimizing_cpu: 0.000000 Profile_executing: 0.000098 Profile_executing_cpu: 0.000000 Profile_end: 0.000049 Profile_end_cpu: 0.000000 Profile_query_end: 0.000045 Profile_query_end_cpu: 0.000000 Profile_freeing_items: 0.000084 Profile_freeing_items_cpu: 0.000000 Profile_logging_slow_query: 0.000045 Profile_logging_slow_query_cpu: 0.000000 # Profile_total: 4.555405 Profile_total_cpu: 0.000000 insert into teee4 select * from teee4 limit 10000000;
20
Find indexes not being used ( Not Possible )
MySQL Server
mysql> SET GLOBAL userstat_running = 1; Percona mysql> SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IS ON (s.TABLE_SCHEMA = IS.TABLE_SCHEMA AND s.TABLE_NAME=IS.TABLE_NAME AND s.INDEX_NAME=IS.INDEX_NAME) WHERE IS.TABLE_SCHEMA IS NULL; +--------------+---------------------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | +--------------+---------------------------+-----------------+ | art100 | article100 | ext_key | | art100 | article100 | site_id | | art100 | article100 | hash | | art100 | article100 | forum_id_2 | | art100 | article100 | published | | art100 | article100 | inserted | | art100 | article100 | site_id_2 | | art100 | author100 | PRIMARY | | art100 | author100 | site_id | ... +--------------+---------------------------+-----------------+ 1150 rows IN SET (1 min 44.23 sec)
21
Server
Usability (cont.) ★
★
Internal locking on the query cache is a major problem on multi-core servers. Wonder if you’re waiting on the Query Cache? ✦
22
We have a new SHOW PROCESSLIST state.
Show Buffer Pool Contents mysql> SELECT d.*,round(100*cnt*16384/(data_length+index_length),2) fit FROM (SELECT schema_name,table_name,count(*) cnt,sum(dirty),sum(hashed) FROM INNODB_BUFFER_POOL_PAGES_INDEX GROUP BY schema_name,table_name ORDER BY cnt DESC LIMIT 20) d JOIN TABLES ON (TABLES.table_schema=d.schema_name AND TABLES.table_name=d.table_name); +-------------+---------------------+---------+------------+-------------+--------+ | schema_name | table_name | cnt | sum(dirty) | sum(hashed) | fit | +-------------+---------------------+---------+------------+-------------+--------+ | db | table1 | 1699133 | 13296 | 385841 | 87.49 | | db | table2 | 1173272 | 17399 | 11099 | 98.42 | | db | table3 | 916641 | 7849 | 15316 | 94.77 | | db | table4 | 86999 | 1555 | 75554 | 87.42 | | db | table5 | 32701 | 7997 | 30082 | 91.61 | | db | table6 | 31990 | 4495 | 25681 | 102.97 | | db | table7 | 1 | 0 | 0 | 100.00 | +-------------+---------------------+---------+------------+-------------+--------+ 7 rows in set (26.45 sec)
23
Source: http://www.mysqlperformanceblog.com/ 2010/03/26/tables-fit-buffer-poo/
Save Buffer Pool Contents ★
Export the contents of the buffer pool to a file called ‘ib_lru_dump’ in the data directory: ✦
★
Restored ib_lru_dump: ✦
24
SELECT * FROM information_schema.XTRADB_ADMIN_COMMAND /*! XTRA_LRU_DUMP*/; SELECT * FROM information_schema.XTRADB_ADMIN_COMMAND /*! XTRA_LRU_RESTORE*/;
Note: Not the actual contents - it takes 8 bytes to remember the address of a 16K page.
To summarize ★
Many MySQL users are not getting the performance they are entitled to. ✦
✦
25
They lack visibility into internal operations of MySQL. Giving a 50% improvement through new features sounds nice. What they don’t realize is they’re losing 100% by not being aware.
Today’s Agenda ★ ★ ★ ★ ★
26
Percona products at 10 000 feet. A closer look at Percona Server with XtraDB. A closer look at XtraBackup. A quick look into the future. Finding more information.
Backup Overview in MySQL Popular for small databases.
27
Best choice when all InnoDB
Popular for databases with MyISAM
mysqldump
mysqldump
Filesystem Snapshot (LVM)
XtraBackup /InnoDB Hot Backup
XtraBackup /InnoDB Hot Backup
MySQLHotc opy
Cold Backup
Engines
InnoDB
ALL
ALL
INNODB
ALL
MyISAM
ALL
Warmth
HOT
WARM
MOSTLY HOT
HOT
WARM
WARM
COLD
Flexibility of Backup
ROW
ROW
SYSTEM
TABLE
TABLE
TABLE
SYSTEM
Flexibility of Restore
MOST
MOST
SYSTEM
TABLE
TABLE
TABLE
SYSTEM
Backup Time
MED
MED
SLOW-MED
FAST
FAST
FAST
BEST
Restore Time
WORST
WORST
BEST
BEST
BEST
BEST
BEST
Impact of Backup
HIGH
HIGH
HIGH
LOW
LOW
MED
N/A
Binary log Coordination
YES
YES
YES
YES
YES
YES
YES
What’s the difference between..? Filesystem Snapshot (LVM)
XtraBackup/ InnoDB Hot Backup
Engines
ALL
INNODB
Warmth
MOSTLY HOT
HOT
Flexibility of Backup
SYSTEM
TABLE
Flexibility of Restore
SYSTEM
TABLE
Backup Time
SLOW-MED
FAST
Restore Time
BEST
BEST
Impact of Backup
HIGH
LOW
Binary log Coordination
YES
YES
28
★
Performance when in snapshot mode with LVM on Linux is much worse. ✦
If your off-peak load is not six times less than your peak load, this may result in a need to over-provision.
See: http://www.mysqlperformanceblog.com/2009/02/05/ disaster-lvm-performance-in-snapshot-mode/
But you said...
29
mysqldump
XtraBackup /InnoDB Hot Backup
Engines
InnoDB
INNODB
Warmth
HOT
HOT
Flexibility of Backup
ROW
TABLE
Flexibility of Restore
MOST
TABLE
Backup Time
MED
FAST
Restore Time
WORST
BEST
Impact of Backup
HIGH
LOW
Binary log Coordination
YES
YES
★
★
Mysqldump may be faster in a few cases. Concern yourself more with recovery time and impact.
http://www.mysqlperformanceblog.com/2010/11/08/anargument-for-not-using-mysqldump-in-production/
XtraBackup is... ★
The best option when you have a considerable amount of data: ✦
✦
30
Restore is as simple as moving the backup to your datadir. It causes the least impact while creating the backup - and is only blocking while backing up MyISAM tables.
InnoDB Hot Backup (1) Percona XtraBackup
MySQL Enterprise Backup (InnoDB Hot Backup)
License
GPL
Proprietary
Price
Free
$5000 per server
Open source
YES
NO
Non-blocking
YES
YES
InnoDB backups
YES
YES
MyISAM backups
YES
YES
Compressed backups
YES
YES
Partial backups
YES
YES
Throttling
YES
YES
Point-in-time recovery support
YES
YES
Incremental backups
YES
YES
Feature
31
More Information: http://www.percona.com/software/percona-xtrabackup/
InnoDB Hot Backup (2) Percona XtraBackup
MySQL Enterprise Backup (InnoDB Hot Backup)
Streaming backups
YES
NO
OS buffer optimizations
YES
NO
Export individual tables
YES
NO
Restore tables to a different server
YES
NO
Analyze data & index files
YES
NO
Familiar command-line behavior
YES
NO
Feature
32
More Information: http://www.percona.com/software/percona-xtrabackup/
Advanced Features ★
XtraBackup supports some advanced backup options: ✦
✦
✦
33
Backup to a stream (can be copied straight across the network, or fed through a compression program). Throttle to only a limited number of IOPS. Incremental backup.*
* Some special restrictions apply. Data Definition must not have changed since last backup.
Advanced Features (cont.) â&#x2DC;&#x2026; â&#x2DC;&#x2026;
34
Report on fragmentation of indexes: $ xtrabackup --stats --tables=art.link* -datadir=/mnt/data/mysql/ ... table: art/link_out104, index: PRIMARY, space id: 12, root page 3 leaf pages: recs=25958413, pages=497839, data=7492026403 bytes, data/pages=91% ...
http://www.mysqlperformanceblog.com/2009/09/14/statisticsof-innodb-tables-and-indexes-available-in-xtrabackup/
Today’s Agenda ★ ★ ★ ★ ★
35
Percona products at 10 000 feet. A closer look at Percona Server with XtraDB. A closer look at XtraBackup. A quick look into the future. Finding more information.
MySQL 5.5 ★ ★
In Release Candidate now. An awesome release! ✦
✦
★
Percona Server will rebase against 5.5. ✦
✦
36
A greatly improved focus on performance. Some additional diagnostic/usability features. Work has already began porting features. We will continue to maintain our 5.1 series.
Percona Server Plans ★
★
Our Blueprints are publicly available through LaunchPad: https://blueprints.launchpad.net/percona-server Percona Server with XtraDB R12.1 is now a release candidate. ✦
✦
37
It will include the ability to store the buffer pool in shared memory and built-in response time distribution stats. It will also feature HandlerSocket.
XtraBackup Plans ★
XtraBackup 1.5 will support parallel backup: ✦
★
38
http://www.percona.com/docs/wiki/perconaxtrabackup:xtrabackup:option-and-variablereference#parallel
This is very helpful for RAID controllers and some SSDs, which require multiple threads to exploit their capacity.
Support & Maintenance ★ ★
Feature development is customer-driven. Commercial Support and Maintenance for Percona Server, XtraDB and XtraBackup are available. ✦
39
http://www.percona.com/support/mysql-supportmaintenance/
Support & Maintenance (cont.) â&#x2DC;&#x2026;
â&#x2DC;&#x2026;
40
Seeking sponsorship of parallel replication: http://www.mysqlperformanceblog.com/2010/11/08/ implementing-parallel-replication-in-mysql/ We need to get more customers involved, to share the cost of this large and ambitious project.
Support & Maintenance (cont.) ★
Provides you with: ✦
✦
✦
41
Expert technical advice. Insurance against bugs in MySQL. Customized server binaries.
Today’s Agenda ★ ★ ★ ★ ★
42
Percona products at 10 000 feet. A closer look at Percona Server with XtraDB. A closer look at XtraBackup. A quick look into the future. Finding more information.
Getting Started ★
Percona Server with XtraDB and XtraBackup are free software released under the GPL license. ✦
★
We provide YUM and APT repositories: ✦
43
More information can be found at: http://www.percona.com/software/percona-server/ http://www.percona.com/software/percona-xtrabackup/ http://www.percona.com/docs/wiki/release:start
Training Classes ★
Chicago Tuesday, 01 Feb 2011 to Friday, 04 Feb 2011
★
London Monday, 21 Feb 2011 to Thursday, 24 Feb 2011
★
Frankfurt Monday, 28 Feb 2011 to Thursday, 03 Mar 2011
★ ★
44
See www.percona.com/training to register. Private training and combined onsite consulting/training is available.
Any Questions? Slides and a recording will be made available after this presentation.
45