public inbox for [email protected]
help / color / mirror / Atom feed[Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
7+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
@ 2014-04-13 11:16
2014-04-13 14:47 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
0 siblings, 1 reply; 7+ messages in thread
From: @ 2014-04-13 11:16 UTC (permalink / raw)
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 11:16 [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
@ 2014-04-13 14:47 `
2014-04-13 22:28 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
0 siblings, 1 reply; 7+ messages in thread
From: @ 2014-04-13 14:47 UTC (permalink / raw)
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:
> 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
>
>
> _______________________________________________
> 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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 11:16 [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 14:47 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
@ 2014-04-13 22:28 `
2014-04-14 09:46 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
0 siblings, 1 reply; 7+ messages in thread
From: @ 2014-04-13 22:28 UTC (permalink / raw)
From: "Thomas Hallgren" <thomas at tada.se>
> Interesting. What does your call into PL/Java (i.e. the function) look
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 11:16 [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 14:47 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 22:28 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
@ 2014-04-14 09:46 `
2014-04-14 11:54 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
0 siblings, 1 reply; 7+ messages in thread
From: @ 2014-04-14 09:46 UTC (permalink / raw)
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
On 2014-04-14 00:28, MauMau wrote:
> From: "Thomas Hallgren" <thomas at tada.se>
>> Interesting. What does your call into PL/Java (i.e. the function) look
>> 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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 11:16 [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 14:47 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 22:28 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-14 09:46 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
@ 2014-04-14 11:54 `
2014-04-14 12:18 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-14 19:32 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
0 siblings, 2 replies; 7+ messages in thread
From: @ 2014-04-14 11:54 UTC (permalink / raw)
From: "Thomas Hallgren" <thomas at tada.se>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 11:16 [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 14:47 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 22:28 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-14 09:46 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-14 11:54 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
@ 2014-04-14 12:18 `
1 sibling, 0 replies; 7+ messages in thread
From: @ 2014-04-14 12:18 UTC (permalink / raw)
On Mon, 14 Apr 2014 20:54:51 +0900
"MauMau" <maumau307 at gmail.com> wrote:
> Anyway, in-process and out-of-process execution doesn't seem to make a big
> 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>
^ permalink raw reply [nested|flat] 7+ messages in thread
* [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 11:16 [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 14:47 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 22:28 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-14 09:46 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-14 11:54 ` [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
@ 2014-04-14 19:32 `
1 sibling, 0 replies; 7+ messages in thread
From: @ 2014-04-14 19:32 UTC (permalink / raw)
On 2014-04-14 13:54, MauMau wrote:
> From: "Thomas Hallgren" <thomas at tada.se>
>> 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.
>
>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2014-04-14 19:32 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-13 11:16 [Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?
2014-04-13 14:47 `
2014-04-13 22:28 `
2014-04-14 09:46 `
2014-04-14 11:54 `
2014-04-14 12:18 `
2014-04-14 19:32 `
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox