JDBC Backend network usage

[ldap4life]

Hi, im experimenting with the JDBC backend and using postgres as the database. Im noticing on a medium sized directory (~13,000) entries poor performance but what I think is excessive network utilization, about 52MB per second which seems high so i am wondering if this is typical, or if there are any optimizations I could look at? The entries are not excessively large, about 10 attributes each with each attribute less than 100 chars. The following is a screenshot of data transfer over a 5 minute window from postgres. In that timeframe there are about 4000 search requests, 750 add requests, and 250 modify requests. Despite the poor performance I experience at my app, etimes in the access logs look low. If I switch the same performance test to the je backend, I get much smoother results. jdbc url: db-directory jdbc:postgresql://database.com:5432/db?user=user&password=password&sslmode=require&connectTimeout=30&socketTimeout=60&tcpKeepAlive=true

[vharseko]

Thanks for the detailed report — the numbers made the cause easy to pin down.

Root cause. The JDBC backend's cursor (JDBCStorage.CursorImpl) fetches rows in eager batches of 1000 (select k,v … order by k limit 1000), and every cursor repositioning discards the buffer and re-fetches a fresh 1000-row batch — even when the requested key is already in the buffer. One-level searches enumerate children by repositioning once per child, so listing N children transfers N × 1000 rows from PostgreSQL instead of ~N. At your rate (~13 searches/s) that works out to almost exactly the ~52 MB/s you measured. The JE backend does the same repositioning as a local B-tree seek, which is why it feels smooth there.

Workaround available right now — shrink the cursor fetch size via a system property (add it to OPENDJ_JAVA_ARGS / java.properties):

-Dorg.openidentityplatform.opendj.jdbc.fetchsize=50

For your directory size this should cut database network traffic by roughly ~20×, at the cost of a few extra round-trips on long sequential scans. Values in the 20–100 range are reasonable; please share what you observe.

Proper fix (serving repositioning from the already-fetched buffer + adaptive batch sizing, expected to reduce dn2id scope-scan traffic by ~1000× for one-level searches) is tracked in #860.

[ldap4life]

tested and experienced the same results. I repeated the test with fetch size 1 and noted about half the network
utilization. Digging further, I checked active queries on postgres during the test, there were 12 queries over 2 tables. 1 of the tables
is 13MB and dispersed over ~10k rows. The other table is ~3.5MB but only has 1 row. Could this concentration on the 3.5MB table be the source of my issue?

  pid  |       usename        |        state        |                                                                    query                                                                     
-------+----------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------
 81951 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81946 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81919 | opendj | idle in transaction | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2
 81940 | opendj | idle in transaction | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2
 81917 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81948 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81920 | opendj | idle in transaction | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2
 81944 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81918 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81947 | opendj | active              | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81954 | opendj | idle in transaction | select k,v from opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 where k>$1 order by k offset $2 rows fetch next $3 rows only
 81921 | opendj | active              | select v from opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 where h=$1 and k=$2

And tables stats:

Schema |                              Name                               | Type  |        Owner         | Persistence | Access method |    Size    | Description 
--------+-----------------------------------------------------------------+-------+----------------------+-------------+---------------+------------+-------------
public | opendj_67108e419e0a2f8d03c6b56381be82971d3f5253ba33b13c865e5138 | table | opendj | permanent   | heap          | 13 MB      | 
public | opendj_89664c245635f826d983b56d437e14c284eefeea28dd3b638bc391c2 | table | opendj | permanent   | heap          | 3496 kB    |