Subject: [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?

From: maumau307 at gmail.com (MauMau)
Date: Sun, 13 Apr 2014 20:16:34 +0900

Hello all,

it seems most likely that I will fail to convince my colleagues to use 
PL/Java.  The performance was not as good as I expected compared to running 
Java functions in a process different from postgres.  On RHEL6 with 
PostgreSQL 9.2.8, I got the following results when I ran 10000 UPDATE 
statements in a single transaction, excluding the time of BEGIN and COMMIT:

* PL/pgSQL: 20 seconds
* PL/Java: 22 seconds (see the attached BatchPL.java for processing)
* Standalone client running on the same server as the database server: 23 
seconds (see Batch.java, which does the same job as BatchPL.java)

I avoided the influence of I/O by setting:

wal_buffers = 16MB
checkpoint_segments = 100
checkpoint_timeout = 1h

So I couldn't insist that PL/Java is required to greatly reduce the overhead 
of interprocess communication and process context switches.  Any ideas?

Regards
MauMau
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Batch.java
Type: application/octet-stream
Size: 1237 bytes
Desc: not available
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20140413/61dea8a8/attachment-0002.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: BatchPL.java
Type: application/octet-stream
Size: 1204 bytes
Desc: not available
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20140413/61dea8a8/attachment-0003.obj>

---

From: thomas at tada.se (Thomas Hallgren)
Date: Sun, 13 Apr 2014 16:47:54 +0200

Interesting. What does your call into PL/Java (i.e. the function) look 
like? How often do you call it (I assume just once)?

- thomas

On 2014-04-13 13:16, MauMau wrote:
> PL/Java.  The performance was not as good as I expected compared to 
> running Java functions in a process different from postgres.  On RHEL6 
> with PostgreSQL 9.2.8, I got the following results when I ran 10000 
> UPDATE statements in a single transaction, excluding the time of BEGIN 
> and COMMIT:
>
> * PL/pgSQL: 20 seconds
> * Standalone client running on the same server as the database server: 
> 23 seconds (see Batch.java, which does the same job as BatchPL.java)
>
> So I couldn't insist that PL/Java is required to greatly reduce the 
> overhead of interprocess communication and process context switches.  
> Any ideas?
>
> Regards
> MauMau
>
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at lists.pgfoundry.org
> http://lists.pgfoundry.org/mailman/listinfo/pljava-dev

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20140413/586f1635/attachment.html>

---

From: maumau307 at gmail.com (MauMau)
Date: Mon, 14 Apr 2014 07:28:58 +0900

From: "Thomas Hallgren" <thomas at tada.se>
> like? How often do you call it (I assume just once)?

Thank you for your response.  I did:

1. Compiled BatchPL.java with JDK6, then ran "jar cf batch.jar
BatchPL.class".

2. Register the jar file with:
SELECT sqlj.install_jar('file:///some/dir/batch.jar', 'batch', false);

3. Create a Java stored function with:
CREATE FUNCTION batch(integer) RETURNS void
AS 'BatchPL'
LANGUAGE java;

4. Run "SELECT batch(10000);" from psql.

So, I understand that the number of calls from the Java function to PL/Java
is 10000.  I hoped PL/Java reduces the function execution time dramatically
(by half or more) to make my colleagues get interested in PL/Java.

Regards
MauMau

---

From: thomas at tada.se (Thomas Hallgren)
Date: Mon, 14 Apr 2014 11:46:21 +0200

In some cases the time spent in the actual call overhead is dramatically reduced but far from always. It does depend on 
several factors. Here are a few:

1. A machine with multiple cores may well be able to run the client and server processes more or less 100% in parallel. 
The flow of execution is of course sequential but time spent in context switches will never become an issue.

2. If network latency is very low, then the benefit of using stored procedures for the purpose of minimizing the number 
of calls between client and server is minimal. In some cases it's even better to avoid such procedures since you get a 
better load balance by executing the logic in the client. Context switching is not an issue here either since the 
processes run on different CPUs.

3. For a test like yours, the time it takes to execute the actual statement is important. The execution may be so slow 
that the overhead imposed by the network call becomes negligible. A test using a lightweight "SELECT 1" instead of the 
heavier "UPDATE testtable" would reveal such issues.

4. The complexity of the function. Let's assume that you implement a new custom datatype. The code associated with that 
datatype may potentially be complex and evaluate millions of times when you use it in large tables. Data types affect 
sorting and indexing and may also play a role in the data integrity rules on the database and moving that type of 
responsibility to the client code is often less than ideal. Complex functions written in Java have very good performance.

Items 1-3 are not specific to PL/Java in any way. They apply regardless of procedure language.

- thomas

---

From: maumau307 at gmail.com (MauMau)
Date: Mon, 14 Apr 2014 20:54:51 +0900

> 3. For a test like yours, the time it takes to execute the actual 
> statement is important. The execution may be so slow that the overhead 
> imposed by the network call becomes negligible. A test using a lightweight 
> "SELECT 1" instead of the heavier "UPDATE testtable" would reveal such 
> issues.

Yeah, I thought this is the most likely.  We are trying to speed up batch 
jobs dramatically, and I hoped PL/Java's in-process architecture would 
contribute to that.

But I wonder why in-process execution didn't make a big difference.  I 
expected several times of difference because I saw the PollPosition 
benchmark results at http://hsqldb.org/.  It compares HSQLDB's in-process 
and client-server execution models.  I assume the client and the server ran 
on the same host.

Anyway, in-process and out-of-process execution doesn't seem to make a big 
difference for batch jobs which issue many SQL statements.

Regards
MauMau

---

From: alberto at unex.es (Alberto Cabello Sánchez)
Date: Mon, 14 Apr 2014 14:18:27 +0200

On Mon, 14 Apr 2014 20:54:51 +0900
> difference for batch jobs which issue many SQL statements.

That reminds me of a MOSIX tip... Could an out-of-process execution to be able to
get better performances from a multi processor setup?

-- 
Alberto Cabello S?nchez
<alberto at unex.es>

---

From: thomas at tada.se (Thomas Hallgren)
Date: Mon, 14 Apr 2014 21:32:35 +0200

On 2014-04-14 13:54, MauMau wrote:
>> statement is important. The execution may be so slow that the 
>> overhead imposed by the network call becomes negligible. A test using 
>> a lightweight "SELECT 1" instead of the heavier "UPDATE testtable" 
>> would reveal such issues.
>
>
> But I wonder why in-process execution didn't make a big difference.

But that's my point. It may well make a big difference but you don't see 
it because your tests are using an update statement. The update will do 
index lookups and physical writes to disk! This means that the update 
consumes perhaps 90% or more of the overall time, regardless of what 
calling mechanism you use. Its not a good choice if you want to measure 
calling mechanism differences.

- thomas