ZFS for MySQL

1 .ZFS for MySQL March 28th, 2018 Yves Trudeau 

2 . Who I am? • Principal architect at Percona since 2009 2 

3 . Who I am? • Principal architect at Percona since 2009 • With Sun and MySQL before 2 

4 . Who I am? • Principal architect at Percona since 2009 • With Sun and MySQL before • 10+y in the MySQL field 2 

5 . Who I am? • Principal architect at Percona since 2009 • With Sun and MySQL before • 10+y in the MySQL field • Focus on HA, distributed systems, storage 2 

6 . Plan • An introduction to ZFS 3 

7 . Plan • An introduction to ZFS • InnoDB file access patterns 3 

8 . Plan • An introduction to ZFS • InnoDB file access patterns • ZFS configuration for MySQL 3 

9 . Plan • An introduction to ZFS • InnoDB file access patterns • ZFS configuration for MySQL • MySQL configuration for ZFS 3 

10 . Plan • An introduction to ZFS • InnoDB file access patterns • ZFS configuration for MySQL • MySQL configuration for ZFS • Host configuration 3 

11 . Plan • An introduction to ZFS • InnoDB file access patterns • ZFS configuration for MySQL • MySQL configuration for ZFS • Host configuration • Basic operations 3 

12 . An introduction to ZFS • Developed by Sun Micro, released in 2005 4 

13 . An introduction to ZFS • Developed by Sun Micro, released in 2005 • Very advanced filesystem 4 

14 . An introduction to ZFS • Developed by Sun Micro, released in 2005 • Very advanced filesystem • Many interesting features for databases 4 

15 . An introduction to ZFS • Developed by Sun Micro, released in 2005 • Very advanced filesystem • Many interesting features for databases • Mix of a regular filesystem, LVM, md 4 

16 . An introduction to ZFS • Developed by Sun Micro, released in 2005 • Very advanced filesystem • Many interesting features for databases • Mix of a regular filesystem, LVM, md • On Linux since 2012 (first beta) 4 

17 . ZFS features: records • ZFS process data by records 5 

18 . ZFS features: records • ZFS process data by records • Records are up to a given size 5 

19 . ZFS features: records • ZFS process data by records • Records are up to a given size • Record sizes may vary between filesystems 5 

20 . ZFS features: records • ZFS process data by records • Records are up to a given size • Record sizes may vary between filesystems • Operations on records are transactional 5 

21 . ZFS features: records • ZFS process data by records • Records are up to a given size • Record sizes may vary between filesystems • Operations on records are transactional • Records have meta-data like: trx #, checksum, compressed, etc. • Records are in a btree (fanout of 3!) 5 

22 . ZFS features: CoW • CoW = Copy-on-Write 6 

23 . ZFS features: CoW • CoW = Copy-on-Write • When a record is updated, it is not overwritten 6 

24 . ZFS features: CoW • CoW = Copy-on-Write • When a record is updated, it is not overwritten • A new record is written, then pointers adjusted 6 

25 . ZFS features: CoW • CoW = Copy-on-Write • When a record is updated, it is not overwritten • A new record is written, then pointers adjusted • View depends the version you are looking for 6 

26 . ZFS features: CoW • CoW = Copy-on-Write • When a record is updated, it is not overwritten • A new record is written, then pointers adjusted • View depends the version you are looking for • Very similar to InnoDB MVCC with the undo entries 6 

27 . ZFS features: CoW(2) A file consisting of 3 records is written at transaction #1 Rec #1 Rec #2 Rec #3 trx #1 trx #1 trx #1 7 

28 . ZFS features: CoW(3) Record #2 is updated at transaction #2, a new copy is created Rec #1 Rec #2 Rec #3 trx #1 trx #1 trx #1 Rec #2 trx > 1 trx #2 8 

29 . ZFS features: CoW(4) Garbage collector removed the old record Rec #1 Rec #3 trx #1 trx #1 Rec #2 trx #2 9