Buy Percona ServicesBuy Now!

Sysbench-tpcc Supports PostgreSQL (No, Really This Time)

 | April 19, 2018 |  Posted In: Benchmarks, Database Monitoring, Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

Sysbench-tpcc Supports PostgreSQLThis time, we really mean it when we say sysbench-tpcc supports PostgreSQL.

When I initially announced sysbench-tpcc, I mentioned it potentially could run against PostgreSQL, but it was more like wishful thinking than reality. The reality was that even though both databases speak SQL, the difference in dialects was too big and the queries written for MySQL could not run without modification on PostgreSQL.

Well, we introduced needed changes, and now you can use sysbench-tpcc with PostgreSQL. Just try the latest commit to https://github.com/Percona-Lab/sysbench-tpcc.

If you’re interested, here is a quick overview of what changes we had to make:

  1. It appears that PostgreSQL does not support the  tinyint and datetime data types. We had to use smallint and timestamp fields, even if using smallint makes the database size bigger.
  2. PostgreSQL does not have a simple equivalent for MySQL’s SHOW TABLES. The best replacement we found is select * from pg_catalog.pg_tables where schemaname != 'information_schema' and schemaname != 'pg_catalog'.
  3. PostgreSQL does not have a way to disable Foreign Key checks like MySQL: SET FOREIGN_KEY_CHECKS=0. With PostgreSQL, we needed to create and load tables in a very specific order to avoid Foreign Keys violations.
  4. PostgreSQL requires you to have a unique index name per the whole database, white MySQL requires it only per table. So instead of using:

    We need to use:
  5. PostgreSQL does not have a  STRAIGHT_JOIN hint, so we had to remove this from queries. But it is worth mentioning we use STRAIGHT_JOIN mostly as a hack to force MySQL to use a correct execution plan for one of the queries.
  6. PostgreSQL is very strict on GROUP BY queries. All fields that are not in the GROUP BY clause must use an aggregation function. So PostgreSQL complained on queries like SELECT d_w_id,sum(d_ytd)-w_ytd diff FROM district,warehouse WHERE d_w_id=w_id AND w_id=1 GROUP BY d_w_id even when we know that only single value for w_ytd is possible. We had to rewrite this query as SELECT d_w_id,SUM(d_ytd)-MAX(w_ytd) diff FROM district,warehouse WHERE d_w_id=w_id AND w_id=1 GROUP BY d_w_id.

So you can see there was some work involved when we try to migrate even a simple application from MySQL to PostgreSQL.

Hopefully, now sysbench-tpcc supports PostgreSQL, it is a useful tool to evaluate a PostgreSQL performance. If you find that we did not optimally execute some transaction, please let us know!

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

Leave a Reply