Serial benchmarks in Mysql, Oracle and PostgreSQL -Test objectives -Test setup -Test result and highlights

Carlos Jesus [email protected]

Test objectives

• Our goal was to perform a series of tests to explore and evaluate some database properties, that are relevant in the GAIA context. This database evaluation was done in different client server machines and on a single local machine.

• Explored benchmark factors: – MySQL: Rewrite prepared statements as multi-value inserts.

– PostgreSQL: Evaluate BYTEA and its specific large binary object API.

– Oracle: Minimize the compression in the network that Oracle seamed to have, by using random data.

– Use different multi-value insert size and row count, in order to have an idea how does this scale.

– Perform a the client server network traffic analysis, in order to see if this is a possible bottleneck.

Carlos Jesus [email protected]

Test setup

• The tests made use and extended the already available DbBenchmark package which was modified to serve our proposes, the changes where namely converting it to GaiaTools 3, and altering the default create statements.

• PostgreSQL: The BYTEA required a modified GaiaTools library, in order to add support for BYTEA, in version 4.x of GaiaTools the problem is solved, the alterations involved changing the JdbcGaiaTable to add the a java.sql.Types mapping.

For the PostgreSQL specific large object API, the GaiaTools (

JdbcGaiaTable

) required alterations for serializing the arrays in to the pg_largeobject system table. In this legacy API (org.postgresql.PGConnection) the object access is performed by means of a reference OID and uses specific methods non standard in JDBC.

• In MySQL was tested the multi-value insert option, which was activated by passing a parameter in the connection string.

gaia.tools.db.url=jdbc:mysql://server:3306/DbName?rewriteBatchedStatements=true • For the network analysis it was used the IPTraf tool, registering the packet count and transferred data size between client and server.

Carlos Jesus [email protected]

Test setup

The different databases have different data types so in order to produce the same results the create statements where changed in order to have a similar row size in all databases, using the following conversion: Integer Conversion Floating point number Conversion Carlos Jesus [email protected]

Test setup

• Performed operations: • Create table • Insert rows (aprox. 0,5Kb each row) • Create an index on this table • Lookup 10 rows • Retrieve all the rows • Drop table Carlos Jesus [email protected]

MySQL Benchmark

MySQL: Tests composed of 400k rows filled with random data M - MySQL B - Batch inserts R - Remote tests L - Local Tests Carlos Jesus [email protected]

PostgreSQL Benchmark

PostgreSQL: Tests composed of 400k rows PostgreSQL: Tests composed of 400k rows filled with random data P - PostgreSQL +A - PostgreSQL specific API -A - BYTEA R - Remote tests L - Local Tests Carlos Jesus [email protected]

Oracle Benchmark

Oracle: Tests composed of 400k rows Oracle: Tests composed of 400k rows filled with random data O - Oracle R - Remote tests L - Local Tests Carlos Jesus [email protected]

Network load

Traffic for tests of 400k rows Traffic records for 400k rows (Random Data) • PostgreSQL: 780 Mb • MYSQL: 580Mb • ORACLE: 275 Mb • PostgreSQL: 2072Mb • MYSQL: 1050 Mb • ORACLE: 520 Mb Carlos Jesus [email protected]

Overview

• Multi-value inserts: – Changing the bulk commit didn’t alter the test time, • Oracle – Presented it self as having good insert time,low network traffic and queries although slower scaled better form 90k rows to 400k rows.

• Mysql – In MySQL, the rewrite batch statements connection option had a big performance impact, by a factor of 3 times faster.

• PostgreSQL – Even exploring different aspects of its JAVA API it had a slower query and insert time than the other two databases, and had a very heavy network output.

Carlos Jesus [email protected]

Q&A

•For more information:

GaiaTools, Bytea and OIDs: SIM Studies at the GAIA WIKI The technical note: GAIA-C1-TN-SIM-AAB-001-01.pdf

Carlos Jesus [email protected]