Insert Very Slow

This message was edited 3 times. Last update was at 9 Nov 2010 02:47:02

Hi all,

I have installed two postgres instances on one server with different ports. And I set up the gridsql with these two nodes.

I inserted some data into the gridsql server, but it was very slow, about 100rows per second. I tried to insert directly into the node database, it was much faster, about 20,000 rows per second. I was using a java application, jdbc, batchexcute for both two tests.

I also tried select from the node and the gridsql. The gridsql is faster as we expected.

Could you please help me to figure out the insert problem? Thanks.

Regards,
David

Part of the setting:
xdb.port=6666
xdb.maxconnections=10

xdb.default.dbusername=gridsql
xdb.default.dbpassword=gridsql
xdb.default.dbport=5432

xdb.default.threads.pool.initsize=10
xdb.default.threads.pool.maxsize=100

xdb.metadata.database=XDBSYS
xdb.metadata.dbhost=172.20.13.192

xdb.nodecount=2

xdb.node.1.dbhost=172.20.13.192
xdb.node.1.dbport=5432
xdb.node.2.threads.pool.initsize=10
xdb.node.1.threads.pool.maxsize=50

xdb.node.2.dbhost=172.20.13.192
xdb.node.2.dbport=5431
xdb.node.2.threads.pool.initsize=10
xdb.node.2.threads.pool.maxsize=50

xdb.coordinator.node=1

xdb.longQuerySeconds=300

xdb.sqlfunction.subdate.template=DATE({arg1})-INTERVAL '{arg2} days'
xdb.sqlfunction.subdate.returntype=DATE
xdb.sqlfunction.subdate.paramcount=2
xdb.sqlfunction.subdate.arg1=DATE
xdb.sqlfunction.subdate.arg2=ANYNUMBER

xdb.sqlfunction.adddate.template=DATE({arg1})+INTERVAL '{arg2} days'
xdb.sqlfunction.adddate.returntype=DATE
xdb.sqlfunction.adddate.paramcount=2
xdb.sqlfunction.adddate.arg1=ANYDATE
xdb.sqlfunction.adddate.arg2=ANYNUMBER

xdb.default.jdbcdriver=org.postgresql.Driver
xdb.default.jdbcstring=jdbc:postgresql://{dbhost}:{dbport}/{database}

GridSQL adds a layer of latency, so it is going to be slower. It was not designed for OLTP, but for parallelizing large queries that need to examine a lot of data.

Instead of INSERT, it is recommended that you use COPY in order to bulk load. You will get much better performance.

You may be able to get better insert performance with multiple threads from your application, but again, COPY is the better way to go.

Regards,

Mason

Thanks.