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
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

The mysql.start script with startup parameters for InnoDB and PBXT

and script to run whole loop of benchmarks

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:
(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

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

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

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

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

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

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

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.

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

The same comment as for previous query.

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

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

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

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

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.

Share this post

Comments (14)

  • kmike Reply

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

    April 8, 2007 at 1:58 pm
  • Yusuf Reply

    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


    April 8, 2007 at 6:19 pm
  • peter Reply

    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

    April 9, 2007 at 2:17 am
  • peter Reply


    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 🙂

    April 9, 2007 at 2:20 am
  • peter Reply

    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.

    April 9, 2007 at 2:25 am
  • Roland Bouman Reply

    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.

    April 9, 2007 at 11:57 pm
  • peter Reply

    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.

    April 10, 2007 at 1:55 am
  • Roland Bouman Reply


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

    Thanks 😉

    April 10, 2007 at 11:27 am
  • Norton Security Reply

    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?

    November 18, 2007 at 9:35 am
  • unisol Reply

    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

    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

    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 🙂

    November 29, 2007 at 10:02 am
  • unisol Reply

    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).

    November 29, 2007 at 10:25 am
  • unisol Reply

    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

    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
    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

    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

    November 29, 2007 at 11:45 am

Leave a Reply