Performance Comparison between MySQL and PostgreSQL

1
Performance Comparison between MySQL and
PostgreSQL
May/2006

• Fábio Ávila
• avila_at_itautec.cin.ufpe.br
• http//itautec.cin.ufpe.br/

2
Agenda

• Introduction
• Benchmarks used
• Emphasis on DBT-2
• Results
• Conclusions and future work
• Questions

3
Objective

• Compare performance between MySQL and PostgreSQL
  in Linux
• Using DBT-2, OSDB and PolePosition benchmarks
• Stimulate improvements in open source DBMS
  development
• Were NOT promoting winners or losers

4
Who we are

• RD accord between Itautec and CIn/UFPE
• Itautec Performance Lab
• Started in January, 2003
• Primary objectives
• Performance analysis in mission-critical servers
• TPC-C publications in Itautec servers
• Hardware Compatibility Testing for Librix
• Results
• 8 TPC-C publications
• Publications in events
• Releases of white papers and HCT
• http//itautec.cin.ufpe.br

5
TPC Team
Carlos Eduardo Pires
Rilson Nascimento
Fábio Ávila
Francisco Carvalho
Marcelo Rodrigues
6
Benchmarks

• Definition
• A standard to measure and evaluate
• Generates performance metrics
• Allows for comparisons
• Shows opportunities for improvement
• Performance / Feature benchmark
• Desirable characteristics
• Detailed and open specification
• Produced by neutral organizations
• Scalable
• Portable
• Reproducible results

7
Brief Benchmark History

• Wisconsin Benchmark - David DeWitt (1983)
• Caused the creation of the DeWitt clause
• Anon et Al paper - Jim Gray (1985)
• AS3AP (1987) - Carolyn Turbyfill
• Implementation OSDB (2001)
• TPC (1988)
• SPEC (1988)
• BAPCo (1991)
• TPC-C (1993)
• SPC (1997)
• TPC-App (2004)

8
Transaction Processing Performance Council (TPC)

• Created in 1988
• Reality similar to Formula-1
• Big investments in attempt to overcome
  competitors
• 18 full members
• HP, IBM, Oracle, Microsoft, Unisys, Sun, Intel,
  AMD, Dell, Fujitsu, NEC, Teradata, Novell,
  Sybase, Bull, Netezza
• 4 associate members
• OSDL, CIn/UFPE, Ideas, ITOM

9
The Itautec Company

• Servers and Desktops
• Investments in RD
• R 50 million a year
• UFPE, Unicamp, UFRJ, USP, Unicap
• SPEC member
• TPC-C world records
• 2006 1P 1st place
• 2004 3.06 GHz 1P 1st place

10
Benchmarks used

• DBT-2
• TPC-C implementation by OSDL
• OSDB
• AS3AP open source implementation
• PolePosition
• Persistent object instances in Java

11
Hardware used

• Itautec Server
• 2 Pentium III Xeon 1.0 GHz processors, L2 256KB
  cache
• 2GB RAM
• 1 Seagate 10Krpm SCSI internal disk
• 1 storage enclosure with 14 Seagate 15Krpm 36GB
  SCSI disks in one RAID-0 volume
• 1 Mylex Extreme RAID 2000 Controller, 4 channels
  (1 used)
• 1 Switch 1Gbps

12
Software used

• Linux Fedora Core 4, Kernel 2.6.11-1.1369_FC4smp,
  filesystem ext3
• PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled
  by GCC gcc (GCC) 4.0.0 20050519 (Red Hat 4.0.0-8)
• MySQL 5.0.12-beta-standard-log
• dbt2-0.37, November 2005

13
Test Environment
System Under Test
Switch
DBT-2 e OSDB VNC Console PolePosition
Workload Generator Eclipse
Storage
Itautec Server
Linux DBT-2 SGBD
Database
14
Database Test 2 (DBT-2)

• TPC-C incomplete implementation
• Not comparable to the TPC-C official results
• Open Source
• Simulates an OLTP environment
• Operations model the 5 main activities of a
  wholesale supplier company
• Explores queries, short transactions and
  concurrency
• Non-uniform data access
• Multiuser environment

15
Database Logical Model
16
Example PostgreSQL, 115w
Total (including indexes) 11.7 GB
17
DBT-2 Transactions

• Write and read
• New-Order
• Entering a new order
• Payment
• Records the payment of an order recently ordered
• Delivery
• Process delivery of a 10-order batch
• Read-only
• Order-Status
• Returns the status of the last order made by a
  client
• Stock-Level
• Returns itens with stock level below a specific
  threshold

18
Execution Distribution

• Payment 43
• Order-Status 4
• Delivery 4
• Stock Level 4
• New-Order 45 (remaining)

19
Metrics

• NOTPM
• Number of executed new-order transactions per
  minute
• TPC-C scalability rule
• 1 warehouse ? 10 users
• Example for 1,000 users
• BD 100 warehouses
• Minimum performance 900 NOTPM
• Maximum performance 1286 NOTPM

20
User emulation
21
Tuning DBT-2

• PostgreSQL 8.1.3 (postgresql.conf)
• fsync off on
• shared_buffers 20.000 (156 MB) 1.000
• checkpoint_segments 256 6
• checkpoint_timeout 1800 s 300
• stats_ off on
• work_mem 1024K 512
• MySQL 5.0.12 Beta (my-huge.cnf)
• innodb_buffer_pool_size 1228 MB 384
• innodb_log_file_size 307 MB 100
• innodb_flush_log_at_trx_commit 0 1
• thread_concurrency 4 8

22
Number of new-order transactions per minute
(NOTPM)
23
CPU
24
Context Switches
25
I/O
26
Benchmark AS3AP

• Academic work
• Carolyn Turbyfill / Cyril Orji / Dina Bitton
• Open and neutral
• Evolution of the well-accepted Wisconsin
  Benchmark
• Normalization
• BD size gt physical memory
• Metric
• Maximum database size (under 12 hours)
• Good DBMS feature coverage
• Access methods, data types, indexes
• Joins, projections, aggregates
• Updates
• Bulk load, output modes
• Multiuser tests

27
OSDB

• Open Source Database Benchmark
• Last version 0.21, jan/2006
• Open source, available on SourceForge
• Stability problems
• AS3AP implementation
• Its more a Feature Benchmark
• Some functionalities are not so relevant
• We showed a performance comparison work in
  fisl6.0 using OSDB only
• Older versions and in greater detail

28
OSDB

• Our tests
• Database size 1 GB
• 2.500.000 records per table indexes
• 4 tables uniques, hundred, tenpct, updates
• 100-byte record
• Our team didnt do any tuning
• Ideal 2GB database minimum
• Problems to stabilize test
• Credibility
• AS3AP is very respected, but OSDB is not
  referenced and used too much
• I wish youd stop using it
• Josh Berkus, PostgreSQL Lead Developer

29
OSDB results (1GB)

• 93 operations executed
• 23 steps for table creation and population
• 46 one-user tests
• 24 multi-user tests
• The table shows the number of times each DMBS had
  better performance in a certain category
• Tie criteria performance difference lower than
  10

30
OSDB Anomalies

• bulk_modify
• update updates set keykey-100000 where key
  between 5000 and 5999
• Execution time
• 33 minutes in PostgreSQL
• Less than 1 second in MySQL
• bulk_delete
• delete updates where key lt 0
• Execution time
• 34 minutes in PostgreSQL
• Less than 1 second in MySQL
• 0 milisecond execution time
• MySQL 12 times
• PostgreSQL 9 times
• Bug, implementation or tuning?

31
PolePosition Benchmark

• Java open source implementation
• Neutral for DMBS
• DBMS object persistence performance
• Measures both pure relational and O-R storage
• Reading, writing, and manipulating an object tree
• Circuits
• Represent a series of tests
• Lap
• Represent an individual test in a circuit
• Ex Melbourne delete, read, read_hot, write
• Configurable numbers of objects

32
PolePosition Benchmark

• Bahrain
• Individually writes, reads, updates and deletes
  objects without any hierarchy
• Barcelona
• writes, reads, queries and deletes objects in a
  five-level structure
• Imola
• Reads objects by primary key
• Melbourne
• Writes, reads and deletes non-structures objects
  of one type in bulk mode
• Sepang
• Writes, reads and then deletes an object tree

33
PolePosition Benchmark

• Easy to run, very stable and reproducible
• Tuning opportunities
• In the car
• Access technology db4o, Hibernate, JDBC, etc.
• In the pilot
• DBMS MySQL, PostgreSQL, HSQLDB, Derby
• Our tests
• One car JDBC
• DBMS with no tuning
• Numbers of objects
• Barhain and Barcelona 1.000, 3.000, 5.000,
  7.000, 9.000
• Melbourne 25.000, 50.000, 75.000, 100.000
• Imola 10.000, 30.000, 100.000, 300.000
• Sepang 36, 55, 78, 105

34
PolePosition Results

• 86 operations operações realizadas pelo benchmark
• A tabela mostra o número de vezes que cada SGBD
  teve melhor desempenho em determinado circuito
• Critério de empate Diferença inferior a 10

35
PolePosition - Anomalies

• Barcelona write
• MySQL 5x to 39x faster than PostgreSQL
• Barcelona delete
• 1.000 objects ? PostgreSQL 5x faster than MySQL
• 3.000, 5.000, 7.000 and 9.000 objects ? MySQL 5x
  to 40x faster than PostgreSQL
• Melbourne read_hot
• PostgreSQL is faster on read, but shows bad
  performance in read_hot
• JDBC caching is a known issue with the current
  JDBC driver. JDBC lead Dave Cramer is currently
  working on a fix, sponsored by Sun Microsystems
  Josh Berkus
• Bugs, implementation or tuning?

36
Conclusions

• Tuning significantly increases performance
• MySQL with DBT-2 75
• PostgreSQL with DBT-2 15
• For a good assessment, one benchmark is not
  enough
• Fundamental input from specialists
• PostgreSQL Josh Berkus (DBT-2)
• MySQL Peter Zaitsev (DBT-2)
• DBT-2 Mark Wong

37
Conclusions

• On DBT-2, performance was equivalent
• PostgreSQL showed a small advantage
• MySQL had room for improvement
• The difference between the procedure and plain
  statements version is some 30 in our tests
  Peter Zaitsev
• In OSDB, MySQL was faster in most tests
• PostgreSQL showed some performance anomalies
• In PolePosition, MySQL was faster in most tests
• Both showed some performance anomalies

38
Future Work

• Poleposition Interlagos circuit
• Improved benchmark implementations
• More tuning
• Include other benchmarks
• 2-tier or 3-tier environment
• Use more powerful servers
• Testing other filesystems
• Include other DBMSes
• Use Itautecs distro Librix Server

39
Special thanks to...

• Peter Zaitsev, Senior Support Engineer, Lead of
  Performance Group
• Josh Berkus, Lead Developer
• Mark Wong, DBT-2 creator
• Isabel Cristina Lopes, Maria Antonieta
  Lucianetti, Edmundo Dotta, Attila Nagy, Ébion
  Miranda

40
Performance Comparison between MySQL and
PostgreSQL
May/2006

• Fábio Ávila
• avila_at_itautec.cin.ufpe.br
• http//itautec.cin.ufpe.br/

41
References

• Gray, J. Benchmark Handbook For Database and
  Transaction Processing Systems, Morgan Kaufmann
  Publishers Inc. San Francisco, CA, USA, 1992.
• OSDL Database Test 2 (DBT-2TM)
• http//www.osdl.org/lab_activities/kernel_testing
  /osdl_database_test_suite/osdl_dbt-2/
• OSDL Database Test Suite
• http//www.osdl.org/
• Sourceforce, 2006, "The Open Source Database
  Benchmark" - http//osdb.sourceforge.net/
• PolePosition The Open Source Database
  Benchmarkhttp//www.polepos.org/

42
References

• Itautec S/A
• http//www.itautec.com
• Fedora Project
• http//www.fedora.redhat.com
• PostgreSQL
• http//www.postgresql.org
• Zaitsev, P., Asplund T. Advanced Innodb
  Optimization, MySQL Users Conference 2005.
• http//www.mysqluc.com
• Power PostgreSQL
• http//www.powerpostgresql.com/