jmeter + posgreSQL isolation "read committed" twice less throughput than "default"

0
votes

I made a PostgreSQL db in docker and made simple JMeter test plan to load data from db.

In JDBC Connection Configuration of JMeter I'm alternating the transaction isolation level from DEFAULT to TRANSACTION_READ_COMMITTED.

In Summary Report I consistently see throughput of 3.3k when on DEFAULT at end of 100k transactions in 4 threads and 1.8k throughput when on TRANSACTION_READ_COMMITTED.

Why could be that for TRANSACTION_READ_COMMITTED throughput is twice less?

https://www.postgresql.org/docs/current/transaction-iso.html:

In PostgreSQL, you can request any of the four standard transaction isolation levels. But internally, there are only three distinct isolation levels, which correspond to the levels Read Committed, Repeatable Read, and Serializable. When you select the level Read Uncommitted you really get Read Committed... Read Committed is the default isolation level in PostgreSQL.

TRANSACTION_READ_UNCOMMITTED in JMeter results in same magnitude of throughput as TRANSACTION_READ_COMMITTED, any other than DEFAULT results in significantly less throughput than DEFAULT.

1
postgresqljmeter
Must be something in your test-setup, because the default level is read committed. - a_horse_with_no_name
Is your bottleneck network latency and/or inter-process communication? If jmeter does a separate network round trip to set the isolation level (even if just setting it to the same thing as already the default), then that extra round trip will slow down your throughput. - jjanes
@ jjanes, interesting thought about setting level on each request... db is in docker on localhost (JMeter run too on local), workstation is macbook. How do you advice to find out where latency is the most? - Alexei Martianov

1 Answers

0
votes

Isolation levels hierarchy looks like:

  1. Serializable - highest isolation level, the requests will be slowest as no other transactions can work on the data set at the given moment, they will be queuing up
  2. Repeatable reads
  3. Read committed
  4. Read uncommitted - lowest isolation level, the requests will be fastest

As per Postgres 9.5 documentation:

Read Committed is the default isolation level in PostgreSQL.

I don't know the specifics of your application and database dataset, but JMeter JDBC configuration must match your application database configuration as choosing different isolation level might lead to false-positive results.

References: