September 16, 2014

PBXT benchmarks

The PBXT Storage Engine (http://www.primebase.com/xt/) is getting stable and we decided to benchmark it in different workloads.
This time I tested only READ queries, similar to ones in benchmark InnoDB vs MyISAM vs Falcon
(http://www.percona.com/blog/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1)
The difference is I used new sysbench with Lua scripting language, so all queries were scripted for sysbench.

New sysbench you can get from svn repository:

The used LUA script for sysbench
http://www.percona.com/blog/files/benchpbxt/phptest.lua

The mysql.start script with startup parameters for InnoDB and PBXT
http://www.percona.com/blog/files/benchpbxt/mysql.start

and script to run whole loop of benchmarks
http://www.percona.com/blog/files/benchpbxt/go.run

The special thanks to Benjamin Grant, John Glass and Paul Elliott for access to modern multiCPU servers, without them I would not be able to run benchmarks.

Description of benchmark:

We used table “normal” table structure which corresponds to typical structure you would see in OLTP or Web applications – medium size rows, auto increment primary key and couple of extra indexes.

In this benchmark we used only read (SELECT) queries with different typical data access patterns:
primary key single row lookup, primary key range lookup, same access types for primary key and full table scans.

To highlight different properties of storage engines we tested ranges with and without LIMIT clause, and tested queries which
need to read the data or can only be satisfied by reading the index.

This benchmark is so called “micro” benchmark which concentrates on particular simple storage engine functions and we use it to see performance and scalability in this simple cases. We also use CPU bound workload in this case (no disk IO) to see how efficient storage engines are in terms of CPU usage. In real life workload results are likely to be very different.

The schema and queries are described here

Used hardware

Red Hat Enterprise Linux AS release 4 (Nahant Update 4)

2 Ñ… Quad Core Intel XEON
cpu family : 15
model : 6
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 8
cpu MHz : 3192.222
cache size : 8192 KB

32GB of RAM

Fedora Core release 5 (Bordeaux)

2 Ñ… Dual Core AMD Opteron
cpu family : 15
model : 65
model name : Dual-Core AMD Opteron(tm) Processor 2210
stepping : 2
cpu MHz : 1800.163
cache size : 1024 KB

4GB of RAM

MySQL version
We used MySQL 5.1.16-beta sources for MyISAM / InnoDB
and pbxt-0.9.85-beta.tar.gz
Compilation parameters:

Method of benchmark:
1. Prepare table with 1,000,000 records (about 350Mb of data on disk)
2. Run each query for 1, 4, 16, 64, 128, 256, 512 concurrent threads.
3. For each thread perform a warm-up run (duration 180 sec), and then
run three effective runs (duration of each is 60 sec).
As the final result we get a maximal result of three runs.

The raw numbers are available here:
http://www.percona.com/blog/files/benchpbxt/res.html
(Note: This benchmark is synthetic micro benchmarks focusing on particular simple data access patterns. Results for your workload are likely to be different.)

There are interesting results I want to show graphics with comments

READ_PK_POINT
READ_PK_POINT
READ_PK_POINT
Query: SELECT name FROM $tableName WHERE id = %d

READ_KEY_POINT
READ_KEY_POINT
READ_KEY_POINT
Query: SELECT name FROM $tableName WHERE country_id = %d

READ_KEY_POINT_LIMIT
READ_KEY_POINT_LIMIT
READ_KEY_POINT_LIMIT
Query: SELECT name FROM $tableName WHERE country_id = %d LIMIT 5
The same query as previous but with LIMIT clause.

READ_KEY_POINT_NO_DATA
READ_KEY_POINT_NO_DATA
READ_KEY_POINT_NO_DATA
Query: SELECT state_id FROM $tableName WHERE country_id = %d
This query is similar to previous READ_KEY_POINT with only different the values of accessed column is stored in key.
Interesting InnoDB scalability is pretty bad for this query. The result with 4 thread is worse then with 1 thread on both 4CPU and 8CPU boxes

READ_KEY_POINT_NO_DATA_LIMIT
READ_KEY_POINT_NO_DATA_LIMIT
READ_KEY_POINT_NO_DATA_LIMIT
Query: SELECT state_id FROM $tableName WHERE country_id = %d LIMIT 5
The previous query but with LIMIT.

READ_PK_POINT_INDEX
READ_PK_POINT_INDEX
READ_PK_POINT_INDEX
Query: SELECT id FROM $tableName WHERE id = %d
Simple but very quick query to retrieve value from PK.

READ_PK_RANGE
READ_PK_RANGE
READ_PK_RANGE
Query: SELECT min(dob) FROM $tableName WHERE id between %d and %d
Access by range of PK values.

PBXT is much worse here because of InnoDB clustering by primary key. InnoDB does not need an extra lookup to access to data.

READ_PK_RANGE_INDEX
READ_PK_RANGE_INDEX
READ_PK_RANGE_INDEX
Query: SELECT count(id) FROM $tableName WHERE id between %d and %d

The same comment as for previous query.

READ_KEY_RANGE
READ_KEY_RANGE
READ_KEY_RANGE
Query: SELECT name FROM $tableName WHERE country_id = %d and state_id between %d and %d
Interesting PBXT is better here by 50-80%

READ_KEY_RANGE_LIMIT
READ_KEY_RANGE_LIMIT
READ_KEY_RANGE_LIMIT
Query: SELECT name FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50

READ_KEY_RANGE_NO_DATA
READ_KEY_RANGE_NO_DATA
READ_KEY_RANGE_NO_DATA
Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d

READ_KEY_RANGE_NO_DATA_LIMIT
READ_KEY_RANGE_NO_DATA_LIMIT
READ_KEY_RANGE_NO_DATA_LIMIT
Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50

READ_FTS
READ_FTS
READ_FTS
Query: SELECT min(dob) FROM $tableName
The hardest query performs a scan of all million rows.

General notes
In general PBXT looks pretty good. To be honest I did not expect such results. The one problem comparing to InnoDB is the access to data by Primary KEY, as I understand PBXT will not support clustering and will not able to reach InnoDB results, but I hope PBXT team will improve performance to make the difference a bit better.

Unfortunately InnoDB shows bad scalability for 8CPU box, in many cases the transition from 16 threads to 64 is too hard for InnoDB even with all scalability fixes exist in 5.1.16 tree. I think InnoDB team should solve this problem, I do not know Oracle plans about.

It is also interesting to check PBXT performance on INSERT/UPDATE queries taking into account PBXT way to handle such queries, I hope I will be able to test it soon.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. kmike says:

    Excellent, thanks for the tests! Yes, INSERT/UPDATE benchmarks would be very interesting – the way PBXT handles them is very peculiar.

  2. Yusuf says:

    Vadim, Ulrich Drepper of glibc fame started a recent thread on lkml explaining what he thought was the root cause behind the innodb scaling issues

    http://marc.info/?l=linux-kernel&m=117561753326551&w=2

  3. peter says:

    That also would be interesting to see how google memory allocator affects things which was found to cause significant difference on some workloads.

    On other hand in this tests we can see almost perfect scalability for certain queries on certain storage engines, I guess this issue with MySQL memory allocation only hurts in certain cases, like highlighted case with temporary table creation

  4. peter says:

    Yusuf,

    After reading all the resent stuff about scalability it does not look like it is related to Innodb, mainly to internal MySQL issues with sort buffer allocation and temporary table creation.

    The workload they tried has a lot of these queries.

    At least I did not see any pointers to Innodb code in particular :)

  5. peter says:

    Right. We need to run both high volume (IO Bound) and Update benchmarks but we need proper server hardware to do so. These were done on loaner boxes which readers of these blog gave us access to. It was not long enough to get proper round of IO bound benchmarks.

    Also it is better to do these on systems with several hard drives in RAID so you can see how efficiently system can use these.

  6. Great, very interesting!

    I did a few tests re. speed of INSERTs to compare InnoDB, PBXT, MyISAM (and Archive and CSV). These were all single thread tests and I don’t dare to claim a general applicability of the test, but:

    Although somewhat slower than MyISAM, PBXT was in all cases relatively very close as compared to InnoDB. Also, whereas the difference between PBXT and MyISAM remained quite constant as more rows where inserted (I went up to 4G rows) InnoDB seemed to take increasingly more time.

  7. peter says:

    One important note about PBXT write benchmarks – PBXT currently does not flushes data and logs to the disk, meaning it does not call fsync() or uses other techniques.

    These means it can hardly be compare to Innodb apples to apples. innodb_flush_logs_at_trx_commit=2 settings is kind of close, but not exactly because Innodb will still flush logs every second and flush data files when it writes to them while PBXT will relay on OS buffering so you can’t predict when data gets flushed.

  8. Peter,

    thanks for pointing that out. That seems indeed to be an important difference.

    Thanks ;)

  9. Well, sounds interesting to me. Thanks for submitting the figures showing the performance details. Do you ever use FreeBSD for MySQL benchmarks? To my dismay, I noticed that the 5.0.24 version breaks at around 100 CUC on my 1Gb RAM pentium 4 box running FreeBSD 6.2. I went through the Performance Tuning tips found at MySQL’s website, but it doesn’t seem to be helping to increase the CUC number without crashing the database engine. Any suggestions, monks?

  10. unisol says:

    I’ve encounterd completely weird results with mysql on an 8cpu server – it performed 3!! :( times slower than on the same 4cpu server:
    FreeBSD-6.2/7.0, amd64
    both sched_4bsd an sched_ule tested
    2xIntel Xeon(R) CPU E5335 @ 2.00GHz (quad core)
    4GB ram
    mysql-4.1.22/from FreeBSD ports
    apache+php

    tests done with apache benchmark on a web page that does some 20 selects (one is select count(*) as count from … where … – the slowest one), and a couple of inserts/updates as well.
    ab -c 125 -n 500 http://dumb-sql-design/dumb-sql-page.php

    results:
    2 cores (kernel params.h modified with MAXCPU set to 2 and 4 to diable extra cores):
    9-11 requests/s

    4 cores:
    18-20 requests/s

    all 8 cores:
    5-7 requests/s, whatever did I try to do – nothing really helped.

    The kernel doesn’t boot with 5 cores :)

  11. unisol says:

    6 cores gives 11-13 requests/s, which is way better
    launching screen and 2x “top” with update interval set to 0 in a couple of its windows and renicing them to -20 (which significantly loaded 2 cores) + “screen” got a huge bytes flow as well – Did Not affect performance in any way, I was still getting the same 11-13 requests/s, even after renicing of mysqld and httpd to 20. (FreeBSD-7.0, sched_ule).

  12. unisol says:

    tried running super-smack on the server – the situation was not that bad for 8 cores, neither it was much better – 8 cores gave at most 20% more transactions than 4 or didn’t give anything visible at all… So basically I hit both mysql scalability and FreeBSD scheduler issues…

    ./super-smack -d mysql update-select.smack 120 500
    4 cpus:
    Query Barrel Report for client smacker
    connect: max=8ms min=0ms avg= 1ms from 120 clients
    Query_type num_queries max_time min_time q_per_s
    select_index 60000 12 0 7253.63
    update_index 60000 23 0 7253.63

    8cpus:
    Query Barrel Report for client smacker
    connect: max=63ms min=1ms avg= 31ms from 120 clients
    Query_type num_queries max_time min_time q_per_s
    select_index 60000 15 0 7464.76
    update_index 60000 10 0 7464.76

    ./super-smack -d mysql select-key.smack 120 500
    4cpus:
    Query Barrel Report for client smacker1
    connect: max=57ms min=0ms avg= 11ms from 120 clients
    Query_type num_queries max_time min_time q_per_s
    select_index 120000 17 0 23242.07

    8cpus:
    Query Barrel Report for client smacker1
    connect: max=10ms min=0ms avg= 2ms from 120 clients
    Query_type num_queries max_time min_time q_per_s
    select_index 120000 8 0 27558.95

Speak Your Mind

*