Performance tuning the MySQL persistence store

[Travis Bear]

Hi,

In my test Voldemort cluster (running version 0.81) I've been
experimenting with getting better Voldemort performance when using
MySQL for persistence, and a couple of questions have come up:

Is there any way to configure the number of JDBC connections between
Voldemort and MySQL? We have 16 CPU's available, but our MySQL
monitoring tools only show 8 active connections. I've searched the
documentation and Google for information, but found no information on
how to increase this.

I'd also like to know if there are any options for Voldemort to cache
or re-use precompiled SQL statements? Our MySQL monitoring currently
doesn't show this happening.

Thanks!


-Travis

[Travis Bear]

Just to answer my own question: I resolved this issue by making
changes to voldemort.store.mysql.MysqlStorageConfiguration, where I
was able to set the specific parameters to the Apache commons
Datasource that I wanted. By customizing these settings I was able to
coax 50% more throughput out of Voldemort.

If there is interest in these changes, I can submit a patch that
exposes these settings as config options.

-Travis

[Liam Slusser]

I am interested in your configuration changes you made...why don't you
post them here? How does the performance compare with bdb?

thanks,
liam

> --
> You received this message because you are subscribed to the Google Groups "project-voldemort" group.
> To post to this group, send email to project-...@googlegroups.com.
> To unsubscribe from this group, send email to project-voldem...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/project-voldemort?hl=en.
>
>

[Travis Bear]

Liam,

I put a custom version of Voldemort on GitHub that exposes several of
the Apache Commons DBCP settings as config options in Voldemort's
server.properties file.

https://github.com/travis-bear/voldemort/tree/mysql/dbcpParams

(note that the branch is 'mysql/dbcpParams', not 'master')

Here is a list of the new properties that are settable, plus some
sample values that seem to work well on our hardware.

mysql.ds.initialPoolSize=16
mysql.ds.poolPreparedStatements=true
mysql.ds.maxActiveConnections=16
mysql.ds.minIdleConnections=8

Some performance numbers from our own environment:

standard Voldemort on the Percona version of MySQL: 12k max TPS
custom Voldemort on Percona/MySQL with the config settings above: 20k
max TPS
standard Voldemort on BDB: 30k max TPS

Any questions, let me know.


-Travis

[Sunny Gleason]

Hi Travis,

Great stuff -- one question, how far out did you test? (In
terms of time, records inserted, size of DB in GB esp. in
relation to size of available RAM?)

The reason I ask is that when I was benchmarking bdb, I
found that performance fell to 25% of initial performance
within 900 seconds of continuous insertion. (Once the
data didn't fit in RAM anymore...)

(see slide 32+ here for graphs of the performance)...
http://slideshare.net/sunnygleason/accelerating-nosql

Thank you for sharing! This is very interesting work--

-Sunny