LinuxWorld MySQL Postgres performance en

50 %
50 %
Information about LinuxWorld MySQL Postgres performance en
Entertainment

Published on October 2, 2007

Author: Tatlises

Source: authorstream.com

Performance Comparison between MySQL and PostgreSQL:  Performance Comparison between MySQL and PostgreSQL Fábio Ávila avila@itautec.cin.ufpe.br http://itautec.cin.ufpe.br/ May/2006 Agenda:  Agenda Introduction Benchmarks used Emphasis on DBT-2 Results Conclusions and future work Questions Objective:  Objective Compare performance between MySQL and PostgreSQL in Linux Using DBT-2, OSDB and PolePosition benchmarks Stimulate improvements in open source DBMS development We’re NOT promoting winners or losers Who we are:  Who we are R&D 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 TPC Team:  TPC Team Rilson Nascimento Francisco Carvalho Fábio Ávila Carlos Eduardo Pires Marcelo Rodrigues Benchmarks:  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 Brief Benchmark History:  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) Transaction Processing Performance Council (TPC):  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 The Itautec Company:  The Itautec Company Servers and Desktops Investments in R&D 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 Benchmarks used:  Benchmarks used DBT-2 TPC-C implementation by OSDL OSDB AS3AP open source implementation PolePosition Persistent object instances in Java Hardware used:  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 Software used:  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 Test Environment:  Test Environment DBT-2 e OSDB: VNC Console PolePosition: Workload Generator Eclipse Storage Itautec Server System Under Test Switch Linux + DBT-2 + SGBD Database Database Test 2 (DBT-2):  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 Database Logical Model:  Database Logical Model Example: PostgreSQL, 115w:  Example: PostgreSQL, 115w Total (including indexes): 11.7 GB DBT-2 Transactions:  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 Execution Distribution:  Execution Distribution Payment 43% Order-Status 4% Delivery 4% Stock Level 4% New-Order ≡ 45% (remaining) Metrics:  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 User emulation:  User emulation Tuning – DBT-2:  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] Number of new-order transactions per minute (NOTPM):  Number of new-order transactions per minute (NOTPM) CPU:  CPU Context Switches:  Context Switches I/O:  I/O Benchmark AS3AP:  Benchmark AS3AP Academic work Carolyn Turbyfill / Cyril Orji / Dina Bitton Open and neutral Evolution of the well-accepted “Wisconsin Benchmark” Normalization BD size >= 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 OSDB:  OSDB Open Source Database Benchmark Last version: 0.21, jan/2006 Open source, available on SourceForge Stability problems AS3AP implementation It’s 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 OSDB:  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 didn’t 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 you’d stop using it” Josh Berkus, PostgreSQL Lead Developer OSDB results (1GB):  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% OSDB – Anomalies:  OSDB – Anomalies bulk_modify update updates set key=key-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 < 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? PolePosition Benchmark :  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 PolePosition Benchmark :  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 PolePosition Benchmark :  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 PolePosition Results:  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% PolePosition - Anomalies:  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? Conclusions:  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 Conclusions:  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 Future Work:  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 DBMS’es Use Itautec’s distro: Librix Server Special thanks to... :  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 Performance Comparison between MySQL and PostgreSQL:  Performance Comparison between MySQL and PostgreSQL Fábio Ávila avila@itautec.cin.ufpe.br http://itautec.cin.ufpe.br/ May/2006 References:  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 Benchmark http://www.polepos.org/ References:  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/

Add a comment

Related presentations

Related pages

PostgreSQL: Awards

Awards PostgreSQL, ... configure and relatively easy to tune for performance." ... 1999 LinuxWorld Editor's Choice Award for Best Database:
Read more

PostgreSQL: The world's most advanced open source database

PostgreSQL 9.5.2, 9.4.7, 9.3.12, 9.2 ... Zalando relies on the stability and performance of hundreds of PostgreSQL database servers to continuously serve ...
Read more

Count Distinct Compared on Top 4 SQL Databases

Count Distinct Compared on Top 4 SQL Databases. ... We ran the queries on Postgres 9.3, MySQL 5.6, ... Yet performance was excellent compared to MySQL and ...
Read more

MySQL to Postgres Converter - Database Converters for data ...

MySQL to Postgres is a program to migrate MySQL databases to Postgres server. ... The program has high performance due to direct connection to data ...
Read more

MySQL

MySQL Engineering Blogs. Take the new MySQL Shell for a Spin … in Docker! Ubuntu 16.04 first stable distro with MySQL 5.7; Thanks for stopping by!
Read more

MySQL

Adobe vertraut auf MySQL um gestalterische Arbeiten produktiver ausführen zu können; Weitere ...
Read more

PostgreSQL – Wikipedia

September 2012 veröffentlicht und beinhaltet in erster Linie neue Funktionalitäten zur Verbesserung der Performance. ... Ein MySQL ... PostgreSQL 9 ...
Read more

PostgreSQL - Wikipedia, the free encyclopedia

Many informal performance studies of PostgreSQL have ... Matloob Khushi performed benchmarking between Postgresql 9.0 and MySQL 5.6.15 for their ability ...
Read more

PostgreSQL System Properties - DB-Engines - Knowledge Base ...

MySQL, PostgreSQL and Redis are the winners of the March ranking 2 March 2016, Paul Andlinger. Big gains for Relational Database Management Systems in DB ...
Read more

Navicat for PostgreSQL

Navicat for MySQL. Product Info Free Trial Buy Now. Navicat for Oracle. Product Info Free Trial Buy Now. Navicat Data Modeler. ... Navicat for PostgreSQL ...
Read more