View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0001656 | SymmetricDS | Improvement | public | 2014-03-28 06:45 | 2014-06-17 00:27 |
Reporter | cronjefourie | Assigned To | elong | ||
Priority | urgent | ||||
Status | closed | Resolution | fixed | ||
Product Version | 3.5.17 | ||||
Target Version | 3.6.0 | Fixed in Version | 3.6.0 | ||
Summary | 0001656: PostgreSQL server side prepared statements causing slow query processing | ||||
Description | The server side prepared statement cache mechanism implemented on PostgreSQL 9.0 and up causes basic prepared statements to execute extremely slowly. In short this is because the extremely generic query plan basically always results in table scans for all data :( | ||||
Steps To Reproduce | Install SymmetricDS on any version of PostgresSQL 9.0 or higher instance. Do a basic primary key based UPDATE of approx 250k records on a master table that has around 5M records. Now watch the slave DB take 2/3 weeks to replicate the 250k updates :( | ||||
Tags | No tags attached. | ||||
|
After much googling and testing I found that setting the protocolVersion to version 2 using &protocolVersion=2 on the postgres url the problem was resolved. 250k updates went from 2/3 weeks UNCOMPLETED to COMPLETED in 20mins I've added a patch file issue-1656.patch to resolve the problem. Or you can just add "protocolVersion=2" to your connection string. |
|
issue-1656.patch (3,031 bytes)
Index: symmetric-jdbc/src/test/resources/db-test.properties =================================================================== --- symmetric-jdbc/src/test/resources/db-test.properties (revision 8112) +++ symmetric-jdbc/src/test/resources/db-test.properties (working copy) @@ -106,10 +106,12 @@ postgres.db.driver=org.postgresql.Driver postgres.db.user=postgres postgres.db.password=admin -postgres.root.db.url=jdbc:postgresql://dbdev1/symmetricroot?stringtype=unspecified -postgres.server.db.url=jdbc:postgresql://dbdev1/symmetricroot?stringtype=unspecified -postgres.client.db.url=jdbc:postgresql://dbdev1/symmetricclient?stringtype=unspecified +#protocolVersion=2 - required to avoid PostgreSQL 9 and > prepared statement caching performance bug +postgres.root.db.url=jdbc:postgresql://dbdev1/symmetricroot?protocolVersion=2&stringtype=unspecified +postgres.server.db.url=jdbc:postgresql://dbdev1/symmetricroot?protocolVersion=2&stringtype=unspecified +postgres.client.db.url=jdbc:postgresql://dbdev1/symmetricclient?protocolVersion=2&stringtype=unspecified + db2.db.driver=com.ibm.db2.jcc.DB2Driver db2.db.user=db2inst1 db2.db.password=admin Index: symmetric-server/src/main/deploy/samples/corp-000.properties =================================================================== --- symmetric-server/src/main/deploy/samples/corp-000.properties (revision 8112) +++ symmetric-server/src/main/deploy/samples/corp-000.properties (working copy) @@ -39,7 +39,9 @@ # The JDBC URL used to connect to the database #db.url=jdbc:mysql://localhost/corp?tinyInt1isBit=false #db.url=jdbc:oracle:thin:@127.0.0.1:1521:corp -#db.url=jdbc:postgresql://localhost/corp?stringtype=unspecified + +#protocolVersion=2 - required to avoid PostgreSQL 9 and > prepared statement caching performance bug +#db.url=jdbc:postgresql://localhost/corp?protocolVersion=2&stringtype=unspecified #db.url=jdbc:derby:corp;create=true #db.url=jdbc:hsqldb:file:corp;shutdown=true #db.url=jdbc:jtds:sqlserver://localhost:1433;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880 Index: symmetric-server/src/main/deploy/samples/store-001.properties =================================================================== --- symmetric-server/src/main/deploy/samples/store-001.properties (revision 8112) +++ symmetric-server/src/main/deploy/samples/store-001.properties (working copy) @@ -39,7 +39,9 @@ # The JDBC URL used to connect to the database #db.url=jdbc:mysql://localhost/store001?tinyInt1isBit=false #db.url=jdbc:oracle:thin:@127.0.0.1:1521:store001 -#db.url=jdbc:postgresql://localhost/store001?stringtype=unspecified + +#protocolVersion=2 - required to avoid PostgreSQL 9 and > prepared statement caching performance bug +#db.url=jdbc:postgresql://localhost/store001?protocolVersion=2&stringtype=unspecified #db.url=jdbc:derby:store001;create=true #db.url=jdbc:hsqldb:file:store001;shutdown=true #db.url=jdbc:jtds:sqlserver://localhost:1433/store001;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880 |
SymmetricDS: master 2c7e6646 2014-03-28 16:04:27 erilong Details Diff |
0001656: PostgreSQL server side prepared statements causing slow query processing |
Affected Issues 0001656 |
|
mod - symmetric-jdbc/src/test/resources/db-test.properties | Diff File | ||
mod - symmetric-server/src/main/deploy/samples/corp-000.properties | Diff File | ||
mod - symmetric-server/src/main/deploy/samples/store-001.properties | Diff File |
Date Modified | Username | Field | Change |
---|---|---|---|
2014-03-28 06:45 | cronjefourie | New Issue | |
2014-03-28 06:55 | cronjefourie | Note Added: 0000503 | |
2014-03-28 06:55 | cronjefourie | File Added: issue-1656.patch | |
2014-03-28 21:00 | Changeset attached | => SymmetricDS trunk r8119 | |
2014-03-28 21:00 | elong | Assigned To | => elong |
2014-03-28 21:00 | elong | Status | new => resolved |
2014-03-28 21:00 | elong | Resolution | open => fixed |
2014-03-28 21:00 | elong | Fixed in Version | => 3.6.0 |
2014-03-28 21:00 | elong | Target Version | => 3.6.0 |
2014-06-17 00:27 | chenson | Status | resolved => closed |
2015-07-31 01:49 | Changeset attached | => SymmetricDS master 2c7e6646 |