Running LinkBench against PostgreSQL

[swkim86]

Hi.

I'm trying to run LinkBench against PostgreSQL.

But, the sample configuration file is provided only for MySQL.

Could anyone explain how to run LinkBench especially for PostgreSQL?

Thanks in advance.

[apavlo]

You can change the configuration to make it use Postgres. It should be pretty easy to figure out.

On Wednesday, January 06, 2016 12:07:17 AM swkim86 wrote:

Hi

I'm trying to run LinkBench against PostgreSQL

But, the sample configuration file is provided only for MySQL

Could anyone explain how to run LinkBench especially for PostgreSQL?

Thanks in advance

---

Reply to this email directly or view it on GitHub: https://github.com/oltpbenchmark/oltpbench/issues/112

Andy Pavlo [email protected]

[mdcallag]

Someone needs to write a Postgres version of LinkStoreMySQL. I know the use of INSERT ... ON DUPLICATE KEY UPDATE is specific to MySQL.

https://github.com/mdcallag/linkbench/blob/master/src/main/java/com/facebook/LinkBench/LinkStoreMysql.java

[woonhak]

I think that Mark is right. There are some MySQL specific queries in LInkBench. I tried to run LinkBench against other DBMS such as Oracle. Actually, I already have one for Oracle, but it became pretty different from what it was. So I'm not sure whether it is correct and it is reflected LinkBench's original behavior. Nonetheless, if you want it, I will make a pull request.

[mdcallag]

If you make a pull request to linkbench, my fork might be the best repo as the upstream repo has been archived for lack of activity: https://github.com/mdcallag/linkbench https://github.com/facebookarchive/linkbench

[woonhak]

Dear Mark, I will make a pull request for your repo, after revised it for PostgreSQL.

[jberkus]

Now that 9.5 has UPSERT, might be interesting to finish this port.

[mdcallag]

The suggested schema works well for InnoDB. I am curious how it works for other DBMS. I am mostly talking about the covering secondary index on the Link table. That exists to make a frequent query index only. A few years back PG was enhanced to make such queries index only, but I don't know enough about PG and the visibility map to have a good opinion.

We also run the benchmark with InnoDB in repeatable read mode. Repeatable read in PG is not the same as in InnoDB. There might be more conflict errors detected with PG. Some details are at https://github.com/MariaDB/webscalesql-5.6/wiki/Cursor-Isolation

[jberkus]

Why is it run in repeatable read?

[mdcallag]

Probably because RR is the default for InnoDB. I have never gone through the work to figure out what might happen with InnoDB-style RC, PG-style RR/RC/SSI or Oracle-style RC.

At work, but not in linkbench, there have been a few apps that needed gap-locking as done by InnoDB RR.

[jberkus]

Yeah, with Postgres you have choices of isolation which are either higher or lower than MySQL RR. Either way, it's not directly comparable. If you need predicate locking in Postgres, most of the time you use Serializable.

[mdcallag]

Slightly off topic, but we are implementing a MySQL storage engine for RocksDB which will do RR and RC. RR provides PG-style semantics rather than InnoDB.

On Fri, Jan 8, 2016 at 9:35 AM, Josh Berkus [email protected] wrote:

Yeah, with Postgres you have choices of isolation which are either higher or lower than MySQL RR. Either way, it's not directly comparable. If you need predicate locking in Postgres, most of the time you use Serializable.

— Reply to this email directly or view it on GitHub https://github.com/oltpbenchmark/oltpbench/issues/112#issuecomment-170067901 .

Mark Callaghan [email protected]

[jberkus]

Postgres Serializable semantics, or our RR ones?

[mdcallag]

RR

On Fri, Jan 8, 2016 at 10:46 AM, Josh Berkus [email protected] wrote:

Postgres Serializable semantics, or our RR ones?

— Reply to this email directly or view it on GitHub https://github.com/oltpbenchmark/oltpbench/issues/112#issuecomment-170087824 .

Mark Callaghan [email protected]

[jberkus]

FWIW, I don't recommend running RR on Postgres. We support it because standards, but really you want either RC or Serializable.

[woonhak]

@mdcallag I've done implement new LinkStorePgsql.java for PostgreSQL on LInkBench. I will make pull request after running short term test. @swkim86 Linkbench for PostgreSQL currently supports only postgresql 9.5 because of UPSERT issue. If you want to run for other versions < 9.5, then you should change queries using plpgsql script for UPSERT.

[woonhak]

I've done also short term test. With running 1GB database with 8 requestor for 10 minutes. It was fine for me. No errors were reported.

[swkim86]

Thanks Woon-Hak! I'll test the modified version on my platform. I appreciate all the comments. Thank you all.

[jberkus]

@wookhak, thanks for doing that!

[woonhak]

@jberkus You're welcome.

[chandrabvv]

I am not able to run linkbench benchmark using oltpbench for mysql. getting following errors: Exception in thread "main" java.lang.NullPointerException at com.oltpbenchmark.util.SQLUtil.getInsertSQL(SQLUtil.java:384) at com.oltpbenchmark.util.SQLUtil.getInsertSQL(SQLUtil.java:366) at com.oltpbenchmark.util.SQLUtil.getInsertSQL(SQLUtil.java:343) at com.oltpbenchmark.benchmarks.linkbench.LinkBenchLoader.load(LinkBenchLoader.java:54) at com.oltpbenchmark.api.BenchmarkModule.loadDatabase(BenchmarkModule.java:305) at com.oltpbenchmark.api.BenchmarkModule.loadDatabase(BenchmarkModule.java:269) at com.oltpbenchmark.DBWorkload.runLoader(DBWorkload.java:783) at com.oltpbenchmark.DBWorkload.main(DBWorkload.java:539) command used to load database: ./oltpbenchmark -b linkbench -c config/sample_linkbench_config.xml --create=true --load=true

Can you please tell me the correct way to run this benchmark.

[apavlo]

@chandrabvv Please post a separate issue.

[grooverdan]

UPSERT for reference https://github.com/mdcallag/linkbench/blob/master/src/main/java/com/facebook/LinkBench/LinkStorePgsql.java#L373..L383

So it just looks like a dialects-export needs to happen.

[grooverdan]

With a few commits in #140 I've got Postgres to the same state as MySQL - (which is failing like what @chandrabvv mentioned earlier)

https://travis-ci.org/grooverdan/oltpbench/builds/213805602 - oraclejdk8 maps to MySQL-5.6.33 and openjdk8 maps to Postgres-9.6.1 (for travis ease of implementation more than any real logical reason).

Thanks @mdcallag and @jberkus for the discussion here.

[mdcallag]

If you are still pulling changes for Linkbench activity has moved to https://github.com/mdcallag/linkbench because the upstream version was archived at https://github.com/facebookarchive/linkbench

On Wed, Mar 22, 2017 at 7:06 PM, Daniel Black [email protected] wrote:

With a few commits in #140 https://github.com/oltpbenchmark/oltpbench/pull/140 I've got Postgres to the same state as MySQL - (which is failing like what @chandrabvv https://github.com/chandrabvv mentioned earlier)

https://travis-ci.org/grooverdan/oltpbench/builds/213805602 - oraclejdk8 maps to MySQL-5.6.33 and openjdk8 maps to Postgres-9.6.1 (for travis ease of implementation more than any real logical reason).

Thanks @mdcallag https://github.com/mdcallag and @jberkus https://github.com/jberkus for the discussion here.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/oltpbenchmark/oltpbench/issues/112#issuecomment-288595961, or mute the thread https://github.com/notifications/unsubscribe-auth/ABkKTeQQjx__v3fiAUErphp12Pr-PFIoks5rodOigaJpZM4G_aum .

-- Mark Callaghan [email protected]

[grooverdan]

@mdcallag, yep. noticed. thanks.

[grooverdan]

@chandrabvv - your error is reported here with a work around #47