1 / 11

Serial benchmarks in Mysql, Oracle and PostgreSQL

Serial benchmarks in Mysql, Oracle and PostgreSQL. Test objectives Test setup Test result and highlights. Carlos Jesus cjesus@sim.fc.ul.pt.

chuck
Download Presentation

Serial benchmarks in Mysql, Oracle and PostgreSQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Serial benchmarksinMysql, Oracle and PostgreSQL Test objectives Test setup Test result and highlights Carlos Jesus cjesus@sim.fc.ul.pt

  2. 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. Test objectives Carlos Jesus cjesus@sim.fc.ul.pt

  3. 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 cjesus@sim.fc.ul.pt

  4. 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 cjesus@sim.fc.ul.pt

  5. 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 cjesus@sim.fc.ul.pt

  6. 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 cjesus@sim.fc.ul.pt

  7. 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 cjesus@sim.fc.ul.pt

  8. 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 cjesus@sim.fc.ul.pt

  9. Traffic for tests of 400k rows PostgreSQL: 780 Mb MYSQL: 580Mb ORACLE: 275 Mb Network load Traffic records for 400k rows (Random Data) • PostgreSQL: 2072Mb • MYSQL: 1050 Mb • ORACLE: 520 Mb Carlos Jesus cjesus@sim.fc.ul.pt

  10. 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. Overview Carlos Jesus cjesus@sim.fc.ul.pt

  11. Q&A • For more information:GaiaTools, Bytea and OIDs:SIM Studies at the GAIA WIKIThe technical note:GAIA-C1-TN-SIM-AAB-001-01.pdf Carlos Jesus cjesus@sim.fc.ul.pt

More Related