My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Share Next Entry
Archive vs Percona XtraDB vs Tokutek TokuDB LOAD DATA performance
swanhart
Stewart blogged about the archive storage engine and he asked the Internet to test how fast ARCHIVE is compared with other MySQL storage engines like Percona XtraDB and Tokutek TokuDB. Since I've been curious about this subject as well, I decided to give it a quick test.

Not very compressible data


I took a star schema benchmark "lineorder" file and grabbed the first 15M rows. To each row I added a TEXT field called "extra_data". This field contains 10 random words from /usr/share/dict/words separated by space. This adds up to just about 3GB of raw input data.

Insert performance with no indexes (best to worst)


TokuDB: 187K rows/sec
Query OK, 15000000 rows affected (1 min 20.25 sec)


XtraDB (uncompressed): 119K rows/sec:
Query OK, 15000000 rows affected (2 min 5.40 sec)


Archive: 61K rows/sec:
Query OK, 15000000 rows affected (4 min 3.15 sec)


XtraDB Compressed key_block_size=8: 6K row/sec:
I cancelled after 6 mins, that is 2.2M rows of 15M rows.
---TRANSACTION 514, ACTIVE 365 sec inserting
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 2233162


Test with indexes


I added the following keys to the table:
alter table insert_test add key(LO_CustKey), add key(LO_OrderDateKey),
add key(LO_SuppKey), add key(LO_PartKey);


I then loaded the table as before to see how multiple indexes affect insert performance.

TokuDB: 84K rows/sec:
Query OK, 15000000 rows affected (2 min 58.42 sec)


InnoDB: 53K rows/sec:
Query OK, 15000000 rows affected (4 min 40.05 sec)


Insert performance with more highly compressible data


This time I took the first 5M rows, but instead of just adding 10 random words, I added the words in a repeating "phrase". For example: "word1 word2 ... word10 word1 word2 ... word10 word1 ...". This generated about the same amount of data.

TokuDB (no indexes): 92K rows/sec:
Query OK, 5000000 rows affected (45.34 sec)


TokuDB (with indexes): 66K rows/sec:
Query OK, 5000000 rows affected (1 min 14.89 sec)


XtraDB (no indexes): 65K rows/sec:
Query OK, 5000000 rows affected (1 min 16.75 sec)


Archive: 43K rows/sec:
Query OK, 5000000 rows affected (1 min 55.24 sec)


XtraDB (with indexes): 36.6K rows/sec:
Query OK, 5000000 rows affected (2 min 16.23 sec)


XtraDB Compressed key_block_size=8 (no indexes): 19.25K rows/sec:
Query OK, 5000000 rows affected (4 min 20.12 sec)


XtraDB Compressed key_block_size=8 (with indexes): 7.59K rows/sec:
Query OK, 5000000 rows affected (10 min 58.43 sec)


Building index on copy of the 15M row table (best to worst)


alter table sortbuild_test  add key(LO_CustKey), add key(LO_OrderDateKey),  add key(LO_SuppKey), add key(LO_PartKey);

[edit: Fixed order to put TokuDB first]
TokuDB:
Query OK, 0 rows affected (1 min 40.55 sec)


XtraDB:
Query OK, 0 rows affected (2 min 21.93 sec)




Building index on copy of the 5M row table (InnoDB 8k only)



XtraDB Compressed key_block_size=8:
Query OK, 0 rows affected (1 min 14.05 sec)



Data size comparison (smallest disk footprint to largest)


[edit]
These numbers were calculated using INFORMATION_SCHEMA.TABLES which reports the UNCOMPRESSED size for TokuDB tables. I hope they fix this in a new release. See the comments for a look at the tokudb data directory contents.

+------------------------------+-------------------+---------+---------+----------+
| table_schema                 | table_name        | data_mb | idx_mb  | sum_size |
+------------------------------+-------------------+---------+---------+----------+
| archive_phrase               | insert_test       |  529.78 |    0.00 |   529.78 |
| archive                      | insert_test       | 1386.51 |    0.00 |  1386.51 |
| innodb_compressed_phrase     | insert_test_8k    | 1601.00 |    0.00 |  1601.00 |
| innodb_compressed_phrase_idx | sortbuild_test_8k | 1601.00 |  151.30 |  1752.30 |
| innodb_compressed_phrase_idx | insert_test_8k    | 1601.00 |  433.83 |  2034.83 |
| tokudb                       | insert_test       |    X    |    X    |     X    |
| tokudb_phrase                | insert_test       |    X    |    X    |     X    |
| tokudb_phrase_idx            | insert_test       |    X    |    X    |     X    |
| innodb                       | insert_test       | 3112.00 |    0.00 |  3112.00 |
| innodb_phrase                | insert_test       | 3202.00 |    0.00 |  3202.00 |
| tokudb_idx                   | insert_test       |    X    |    X    |     X    |
| tokudb_idx                   | sortbuild_test    |    X    |    X    |     X    |
| innodb_phrase_idx            | insert_test       | 3202.00 |  565.03 |  3767.03 |
| innodb_idx                   | sortbuild_test    | 3112.00 |  899.19 |  4011.19 |
| innodb_idx                   | insert_test       | 3112.00 | 1519.00 |  4631.00 |
+------------------------------+-------------------+---------+---------+----------+


[edit]
The TokuDB test table is 1800MB (15M row table, 3G orig size)
The TokuDB test2 table is 2171MB (keys built with ALTER)
The tokudb_idx database is 2202MB (keys built with INSERT)
The tokudb_phrase database is 681MB (5M row, 3G orig size)
The tokudb_phrase_idx database is 806MB (5M row, keys built with INSERT)
[/edit]

Here is the CREATE TABLE for the data:
CREATE TABLE IF NOT EXISTS insert_test
(
LO_OrderKey bigint not null,
LO_LineNumber tinyint not null,
LO_CustKey int not null,
LO_PartKey int not null,
LO_SuppKey int not null,
LO_OrderDateKey int not null,
LO_OrderPriority varchar(15),
LO_ShipPriority char(1),
LO_Quantity tinyint,
LO_ExtendedPrice decimal,
LO_OrdTotalPrice decimal,
LO_Discount decimal,
LO_Revenue decimal,
LO_SupplyCost decimal,
LO_Tax tinyint,
LO_CommitDateKey int not null,
LO_ShipMode varchar(10),
extra_data TEXT
);

For compression:
ROW_FORMAT=compressed, KEY_BLOCK_SIZE=8


Here is the my.cnf for Percona XtraDB (not tuning for tokudb):
[mysqld]
#datadir=/data/datadirs/tokudb_55
datadir=/data/datadirs/percona_55
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_buffer_pool_size=16G
innodb_log_file_size=4G
innodb_flush_neighbor_pages=cont
innodb_fast_checksum
innodb_file_per_table
innodb_file_format=barracuda
innodb_buffer_pool_instances=6
innodb_write_io_threads=12
innodb_read_io_threads=12
innodb_flush_method=O_DIRECT
innodb_io_capacity=10000

read_buffer_size=2M
key_buffer_size=32M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


[edit]
Test Hardware:
Intel i970-3.2GHz 6 core w/HT (12 threads)
24GB RAM
LSI 9211-8i IT HBA
DB on software RAID 0 - Two Intel 520 SSD, one OCZ vertex.  300GB total.

Have you tried other TokuDB compression types?

ext_1800768

2013-05-14 10:53 am (UTC)

Justin, good stuff. One small detail I think you have the order reversed in "Building index on copy of the 15M row table (best to worst)", as TokuDB was faster. Also, you should try the lzma and zlib compression options of TokuDB, I assume this analysis was just for quicklz.

Order fixed.

Yes, I tested default settings with no tuning, so if it defaults to quicklz then that is what it used. What option do I set to get the other compression types? I'll read the manual soon, I promise :)

TokuDB compression types

ext_1800768

2013-05-14 06:14 pm (UTC)

Justin,

It's simple enough to use a particular compression algorithm for a table and it's secondary indexes. Consider the following create table:

create table foo
(a bigint not null auto_increment primary key,
b bigint not null,
c varchar(20) not null)
engine=tokudb row_format=tokudb_quicklz;

valid row formats are:
tokudb_quicklz
tokudb_lzma
tokudb_zlib
tokudb_uncompressed

if you don't include row_format=, you'll get the default (which comes from the server/session variable tokudb_row_format). And the default default is quicklz.

-Tim

how did you measure compressed tokudb size?

ext_1801036

2013-05-14 02:58 pm (UTC)

Did you look at the actual data size (in the directory). As I understand it, TokuDB reports uncompressed size in show table status, which I'll admit is confusing.
-Bradley

Re: how did you measure compressed tokudb size?

(Anonymous)

2013-05-14 04:18 pm (UTC)

Very good question, a common use case for ARCHIVE is not speed of insert but rather on disk footprint/savings. In these results, innodb and tokudb are clear losers on that front. It remains to be seen if this caveat is the culprit.. I was surprised to see that tokudb was only marginally smaller than uncompressed innodb.

Re: how did you measure compressed tokudb size?

swanhart

2013-05-14 08:18 pm (UTC)

I'll test with various compression options and report proper sizes now that I know the size reported in I_S is wrong (see edit above and comments with sizes).

It looks like TokuDB was 1.8GB after compression with no indexes with quicklz.

The TokuDB test table is 1800MB (15M row table, 3G orig size)
The TokuDB test2 table is 2171MB (keys built with ALTER)
The tokudb_idx database is 2202MB (keys built with INSERT)
The tokudb_phrase database is 681MB (5M row, 3G orig size)
The tokudb_phrase_idx database is 806MB (5M row, keys built with INSERT)

Re: how did you measure compressed tokudb size?

swanhart

2013-05-14 04:20 pm (UTC)

[root@workstation tokudb_55]# ls -lh
total 7.5G
-rw-rw----. 1 mysql mysql 18M May 14 01:59 ibdata1
-rw-rw----. 1 mysql mysql 5.0M May 14 02:56 ib_logfile0
-rw-rw----. 1 mysql mysql 5.0M May 13 23:28 ib_logfile1
-rwx------. 1 mysql mysql 806K May 14 08:57 log000000000036.tokulog24
drwx------. 2 mysql root 4.0K May 13 23:28 mysql
drwx------. 2 mysql mysql 4.0K May 13 23:28 performance_schema
-rw-rw----. 1 mysql mysql 24K May 14 02:56 tc.log
drwx------. 2 mysql root 4.0K May 13 23:28 test
drwx------. 2 mysql mysql 4.0K May 14 00:55 tokudb
-rwxrwx--x. 1 mysql mysql 32K May 14 01:43 tokudb.directory
-rwxrwx--x. 1 mysql mysql 16K May 13 23:30 tokudb.environment
drwx------. 2 mysql mysql 4.0K May 14 01:45 tokudb_idx
-rwxrwx--x. 1 mysql mysql 73M May 14 01:44 _tokudb_idx_sortbuild_test_key_LO_CustKey_75_9_18_B_0.tokudb
-rwxrwx--x. 1 mysql mysql 66M May 14 01:44 _tokudb_idx_sortbuild_test_key_LO_OrderDateKey_75_9_18_B_1.tokudb
-rwxrwx--x. 1 mysql mysql 103M May 14 01:45 _tokudb_idx_sortbuild_test_key_LO_PartKey_75_9_18_B_3.tokudb
-rwxrwx--x. 1 mysql mysql 86M May 14 01:45 _tokudb_idx_sortbuild_test_key_LO_SuppKey_75_9_18_B_2.tokudb
-rwxrwx--x. 1 mysql mysql 1.8G May 14 01:42 _tokudb_idx_sortbuild_test_main_71_2_18.tokudb
-rwxrwx--x. 1 mysql mysql 64K May 14 01:45 _tokudb_idx_sortbuild_test_status_71_1_18.tokudb
-rwxrwx--x. 1 mysql mysql 85M May 13 23:41 _tokudb_insert_test2_key_LO_CustKey_1e_1_18_B_0.tokudb
-rwxrwx--x. 1 mysql mysql 81M May 13 23:41 _tokudb_insert_test2_key_LO_OrderDateKey_1e_1_18_B_1.tokudb
-rwxrwx--x. 1 mysql mysql 108M May 13 23:41 _tokudb_insert_test2_key_LO_PartKey_1e_1_18_B_3.tokudb
-rwxrwx--x. 1 mysql mysql 97M May 13 23:41 _tokudb_insert_test2_key_LO_SuppKey_1e_1_18_B_2.tokudb
-rwxrwx--x. 1 mysql mysql 1.8G May 13 23:41 _tokudb_insert_test2_main_1e_1_18_B_4.tokudb
-rwxrwx--x. 1 mysql mysql 64K May 13 23:38 _tokudb_insert_test2_status_11_1_18.tokudb
-rwxrwx--x. 1 mysql mysql 1.8G May 13 23:39 _tokudb_insert_test_main_e_1_18_B_0.tokudb
-rwxrwx--x. 1 mysql mysql 16K May 13 23:32 _tokudb_insert_test_status_9_1_18.tokudb
-rw-------. 1 mysql mysql 0 May 13 23:30 __tokudb_lock_dont_delete_me_data
-rw-------. 1 mysql mysql 0 May 13 23:30 __tokudb_lock_dont_delete_me_environment
-rw-------. 1 mysql mysql 0 May 13 23:30 __tokudb_lock_dont_delete_me_logs
-rw-------. 1 mysql mysql 0 May 13 23:30 __tokudb_lock_dont_delete_me_temp
-rwxrwx--x. 1 mysql mysql 32K May 14 01:39 tokudb_meta_3_0_18.tokudb
drwx------. 2 mysql mysql 4.0K May 14 00:55 tokudb_phrase
drwx------. 2 mysql mysql 4.0K May 14 00:58 tokudb_phrase_idx
-rwxrwx--x. 1 mysql mysql 29M May 14 00:59 _tokudb_phrase_idx_insert_test_key_LO_CustKey_5d_1_18_B_0.tokudb
-rwxrwx--x. 1 mysql mysql 27M May 14 00:59 _tokudb_phrase_idx_insert_test_key_LO_OrderDateKey_5d_1_18_B_1.tokudb
-rwxrwx--x. 1 mysql mysql 36M May 14 00:59 _tokudb_phrase_idx_insert_test_key_LO_PartKey_5d_1_18_B_3.tokudb
-rwxrwx--x. 1 mysql mysql 33M May 14 00:59 _tokudb_phrase_idx_insert_test_key_LO_SuppKey_5d_1_18_B_2.tokudb
-rwxrwx--x. 1 mysql mysql 681M May 14 00:59 _tokudb_phrase_idx_insert_test_main_5d_1_18_B_4.tokudb
-rwxrwx--x. 1 mysql mysql 64K May 14 00:58 _tokudb_phrase_idx_insert_test_status_52_1_18.tokudb
-rwxrwx--x. 1 mysql mysql 681M May 14 00:56 _tokudb_phrase_insert_test_main_4f_1_18_B_0.tokudb
-rwxrwx--x. 1 mysql mysql 16K May 14 00:55 _tokudb_phrase_insert_test_status_4a_1_18.tokudb
-rw-------. 1 mysql mysql 0 May 13 23:30 __tokudb_recoverylock_dont_delete_me
-rwxrwx--x. 1 mysql mysql 32M May 14 01:45 tokudb.rollback
-rw-rw----. 1 mysql mysql 6 May 14 02:56 workstation.pid

Re: how did you measure compressed tokudb size?

ext_1801036

2013-05-14 04:40 pm (UTC)

This, of course, will likely lead to an echo of Mark Callaghan's complaint that TokuDB keeps all its files in the top level data directory.

We'll fix that eventually.
-Bradley

Re: how did you measure compressed tokudb size?

swanhart

2013-05-14 06:11 pm (UTC)

I don't complain for complaining sake.

Performance may suffer when there are many files in a single directory, such as when you have many tables on a single server.

Re: how did you measure compressed tokudb size?

ext_1801036

2013-05-14 06:35 pm (UTC)

I haven't actually seen this kind of performance problem, even with tens of thousands of tables. Generally ext4 and xfs both seem to achieve good performance with large numbers of files ina single directory.

Re: how did you measure compressed tokudb size?

swanhart

2013-05-14 07:57 pm (UTC)

a) There are still people who use ext3
b) ext4 is still slower than XFS at dropping files and slower when there are many files in the directory
c) XFS still takes longer to drop files in a directory with many files compared to one with fewer
d) This isn't an OLTP test, but microstalls are very bad in an OLTP environment because they lead to pileups. Dropping a table has to drop one file per index in your case, which may lead to stalls if you hold some global lock while you drop a table. Though I don't know if you do.

Re: how did you measure compressed tokudb size?

swanhart

2013-05-14 08:00 pm (UTC)

I also want to be able to do `du -sh *` to figure out how much space each DB is using on disk. I don't want to write a script to parse the files in the datadir.

Re: how did you measure compressed tokudb size?

ext_1801036

2013-05-14 05:18 pm (UTC)

It looks like:
 The TokuDB test table is 1800MB.
 The TokuDB test2 table is 2171MB
 The tokudb_idx database is 2202MB
 The tokudb_phrase database is 681MB
 The tokudb_phrase_idx database is 806MB.

Re: how did you measure compressed tokudb size?

swanhart

2013-05-14 06:07 pm (UTC)

Hmm. I wonder if there is a parameter that will make it report correct sizes?

Re: how did you measure compressed tokudb size?

ext_1801036

2013-05-14 06:35 pm (UTC)

I don't think so.

read performance and compression?

(Anonymous)

2013-05-14 02:59 pm (UTC)

Since Archive engine is used for "write once read many times" pattern IMO read performance and compression are most important.

When testing for TokuDB aggressive compression

shlominoach.wordpress.com

2013-05-15 04:14 am (UTC)

Justin,

If adding tests for TokuDB's aggressive compression, please note that merely doing

ALTER TABLE insert_test engine=tokudb row_format=tokudb_quicklz;

May NOT actually reduce footprint on disk; at least this is how I get it. Either modify to InnoDB then back to TokuDB or create table from scratch.


It would have been better if versions of each of the engines (or
their parent packages) would have been reported.

Recently I saw on tokudb's list about its fsync performance and
how one of the slaves was falling behind. They seem to be fixing
it with certain settings (to adjust interval) though.

Another thing is, the default zlib Innodb compression (which cannot be
changed though) is not on par with quicklz or any new ones (or cpu-intensive as the latter).

Also, it would be interesting to see how MySQL 5.6 fares in
these.

Mentioned MySQL 5.6 especially since there have been many improvements to InnoDB compression in it (with some of the facebook patches gone into it).

If possible, you can also build from https://github.com/facebook/mysql-5.6 to instrument all their new compression features.

Regarding compression efficiency, the table here https://code.google.com/p/lz4/ is quite illuminating.

LZ4 (r59) 2.084 330 915

v/s

zlib 1.2.5 -1 2.728 39 195

so zlib is nearly 10 times slow.


You are viewing swanhart