serial benchmarks in mysql oracle and postgresql l.
Download
Skip this Video
Download Presentation
Serial benchmarks in Mysql, Oracle and PostgreSQL

Loading in 2 Seconds...

play fullscreen
1 / 11

Serial benchmarks in Mysql, Oracle and PostgreSQL - PowerPoint PPT Presentation


  • 170 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Serial benchmarks in Mysql, Oracle and PostgreSQL' - chuck


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
serial benchmarks in mysql oracle and postgresql

Serial benchmarksinMysql, Oracle and PostgreSQL

Test objectives

Test setup

Test result and highlights

Carlos Jesus cjesus@sim.fc.ul.pt

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.

Test objectives

Carlos Jesus cjesus@sim.fc.ul.pt

test setup
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

test setup4
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

test setup5
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

slide6

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

slide7

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

slide8

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

slide9
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

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.

Overview

Carlos Jesus cjesus@sim.fc.ul.pt

slide11
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

ad