View Issue Details

IDProjectCategoryView StatusLast Update
0001656SymmetricDSImprovementpublic2014-06-16 20:27
ReportercronjefourieAssigned Toelong 
PriorityurgentSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version3.5.17 
Target Version3.6.0Fixed in Version3.6.0 
Summary0001656: PostgreSQL server side prepared statements causing slow query processing
DescriptionThe 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 ReproduceInstall 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 :(
TagsNo tags attached.

Activities

cronjefourie

2014-03-28 02:55

reporter   ~0000503

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.

cronjefourie

2014-03-28 02:55

reporter  

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
issue-1656.patch (3,031 bytes)

Related Changesets

SymmetricDS: master 2c7e6646

2014-03-28 16:04:27

erilong

Details Diff
0001656: PostgreSQL server side prepared statements causing slow query processing
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

Issue History

Date Modified Username Field Change
2014-03-28 02:45 cronjefourie New Issue
2014-03-28 02:55 cronjefourie Note Added: 0000503
2014-03-28 02:55 cronjefourie File Added: issue-1656.patch
2014-03-28 17:00 Changeset attached => SymmetricDS trunk r8119
2014-03-28 17:00 elong Assigned To => elong
2014-03-28 17:00 elong Status new => resolved
2014-03-28 17:00 elong Resolution open => fixed
2014-03-28 17:00 elong Fixed in Version => 3.6.0
2014-03-28 17:00 elong Target Version => 3.6.0
2014-06-16 20:27 chenson Status resolved => closed
2015-07-30 21:49 Changeset attached => SymmetricDS master 2c7e6646