SSD + PBXT = Crazy Suspicious!

Yesterday, HarrisonF noted in one of my comments that PBXT uses a log structure internally to represent its on disk format.

This prompted me to test it out to verify if it would boost performance of MySQL on SSD.

I’m somewhat impressed. Not blown away but this is very interesting.

Bulk inserting 1M rows into the DB was about 2x faster than InnoDB. What’s interesting is that PBXT did NOT saturate the SSD drive. PBXT was CPU bound and only using the SSD at about 50% utilization.

This is a quad core box and PBXT was using about 1 core to perform INSERTs as I only launched one process to create the database. If they can boost the CPU performance I imagine they could just flat out crush InnoDB for insertion speed (possibly by 4x).

In fact… it seems that if they boost it 2x more it would saturate the drive (which makes sense for an INSERT only log structured database).

I wonder if this CPU hit is due to performing checksums on the data.

Once sysbench kicked into concurrent INSERT/UPDATE/DELETE/SELECT tests the OLTP performance really started to fall – to the point where it was on par with InnoDB in terms of performance.

There seems to be a lot of opportunity here for PBXT. If they could focus on SSD markets they could really make a STRONG case for using their database as the raw transactions per second / price would be outstanding.

They’d probably need to focus on SSD exclusively though as I’m not sure they’d be able to get the same performance profile on HDD.

Update:

MyISAM is CPU bound on inserts too… I think this might be the plugin interaction layer. Apparently it’s not very efficient for high performance storage engines.

I wonder how InnoDB gets around this problem.

Installation

The only other note I wanted to make was regarding PBXT’s installation.

What a joke. PBXT took me 5 hours to install.

The MySQL 4.1.x source snapshot would dump core on install.

Compiling PBXT with MySQL 5.1.x is a comedy of errors. PBXT does not list build requirements. The recommended ./configure command line arguments were insufficient.

I could go on. Trial and error (and 5 hours later) resulted in a working PBXT install but it wasn’t exactly a fun process.

If they can’t get their installation routine down it doesn’t give me confidence to believe that deploying this into production would be a good idea.


  1. brianaker

    Could you document what you had to do to make it work? I would be curious to know what the issues were.

    Cheers,
    -Brian

  2. To be honest there were SO many issues I stopped taking notes.

    Off hand..

    The 4.1.x sources compiled JUST fine but when I tried to create a table in PBXT the mysqld dumped core.

    Then I tried to compile PBXT from source. This resulted in compilation errors. I wasn’t able to track down any list of requirements. I could have debugged this issue more but I decided to punt and install a binary build.

    Then I tried to compile 5.1.16 which is not hosted on MySQL’s site. Then I had to fetch it from Dorsal Source.

    Then…. the PBXT sources neglected to mention that you need to compile MySQL with:

    –with-pthread \
    –with-fast-mutexes \

    or it won’t work.

    Each attempted compilation is about 45 minutes so this can quickly burn a lot of time.

    Anyway. Not a very fun process.

    Kevin

  3. harrisonf

    Hi Kevin,

    Thanks for trying it out, interesting to see the results!

    I wonder why it was about the same in the oltp was about the same. Sysbench does all queries based on the PK, so I wonder if the lack of the clustered index hurt PBXT significantly compared to InnoDB in this case. With the low-seek time of SSD it should be minimized I imagine, but that is the only thing I can think of off hand.

    Still neat to see it better than InnoDB in one case, and even in the other.

  4. tgabi

    SELECTs should be faster on InnoDB because of the row buffer – and consuming less CPU as well. Try varying the percentage of SELECTs in the benchmarks, see if this is the cause.

  5. paulmccullagh

    Hi Kevin,

    Well, what can I say! Thanks for taking all that to time test PBXT! I am impressed with your perseverance and ingenuity in getting it to work!

    Sorry for making things so difficult :( Of course, I should have included my MySQL configure parameters with the binary release.

    And I am embarrassed to admit that I have not kept http://www.primebase.com/xt up-to-date while working on http://www.blobstreaming.org :(

    But I am now back to working on PBXT, and will update http://www.primebase.com/xt shortly.

    I did suspect that the original design of PBXT favored solid state drives, your tests are encouraging in this respect.

    The next version of PBXT will include full durability. This has meant using a write-ahead log, which should not be a problem for the SSD drive. So I think I can maintain the good insert performance, and maybe even do a bit better on the random updates.

    We will see…

    I hope you will test PBXT again then. Of course I will make sure it compiles this time! :)

    Best regards,

    Paul

  6. Hey Paul.

    Thanks for the commentary.

    My suggestion would be to buy a couple of SSDs and tune PBXT for them.

    It’s looking like we’re going to switch to MyISAM on SSD. We’d be able to 6x our infrastructure capacity by this switch.

    This is also without any major benefit due to the sheer performance of the SSD.

    I’m STILL not sure why PBXT isn’t faster than it already is… Maybe I didn’t tune it correctly. I gave it 500M for the index buffer and another 500M for the data buffer.

    Anyway. Grab a couple cheap SSDs. The cheaper Mtron’s are $300 and they’re just using SATA interfaces.

    If you can get PBXT to perform with SSDs you’ll be able to build a DB for $1500 that a MyISAM / InnoDB box couldn’t beat for $10-15k.

    Kevin






Follow

Get every new post delivered to your Inbox.

%d bloggers like this: