Friday, March 11, 2011

PBMS Performance

I have been doing some performance testing with PBMS and found a few things that were kind of interesting. The main finding was that you start to see performance improvements when data sizes start to reach the 20K level. This was seen when replacing a 20 K varchar field with a longblob column in a PBMS enabled table.

The following graph shows the performance differences for 'select' and 'insert' statements using a PBMS enabled version of InnoDB on an 8 core machine.

The test compares the insert and select performance of LongBlob columns with PBMS support against that of varchar and longtext columns when using InnoDB.

The test shows that depending on if your application is more heavily weighted towards Inserts or selects it may be beneficial to replace columns containing more than 10K of data with longblob columns with PBMS support. In all cases the performance of both 'selects' and 'inserts' was improved for columns containing more than 20K when using longblob columns and PBMS. As the data size in the column increased the performance gain by using PBMS also increased, at 10 M there was %580 performance improvement for selects.

The testing showed some performance irregularities with PBMS which when fixed should result in even better performance.

If anyone would like to try this themselves please contact me and I can give you a copy of the test tool I used as well as a PBMS enabled version of InnoDB.



hingo said...

Interesting test. I was just discussing using PBMS engine with someone and we were trying to guess where the cutoff point is where you should give up on vanilla MySQL/InnoDB and look into something like PBMS. Seems my guess was mostly right, but I was surprised that the difference is first seen in inserts rather than selects, I was guessing the opposite.

It would also be interesting to know what happens between 40K and 1000K for the selects. I suspect there is a cutoff point somewhere where the advantage for PBMS becomes dramatically obvious. It would be interesting to know if that is at 50K or 500K or somewhere between?

Barry Leslie said...

Since you asked I ran a few more tests.

Inserts = 123% Improvement
Selects = 55% Improvement

Inserts = 128% Improvement
Selects = 97% Improvement

Inserts = 197% Improvement
Selects = 282% Improvement