DBMS_PIPE performance

[Richard Shuttlewood]

I am testing an alternative method for Oracle Autonomous Transactions using DBMS_PIPE

It works but I am finding the elapsed time from send to receive is 5ms - not bad but I would prefer faster

Looking at the code,  the send is simply doing a memory copy

The receive is presumably doing a wait of some sort before it copies from memory

Before I dig deeper I just wanted to ask here whether anyone has any thoughts on this?

Thanks,

[Pavel Stehule]

Hi

st 22. 3. 2023 v 18:30 odesílatel Richard Shuttlewood <erintec...@gmail.com> napsal:

This code is very old - it was designed for Postgres 8.2. From release 10, Postgres has some code for interprocess communication, so this code can be used. The slow part of implementation

are macros

#define WATCH_PRE(t, et, c) \
et = GetNowFloat() + (float8)t; c = 0; \
do \
{ \

#define WATCH_POST(t,et,c) \
if (GetNowFloat() >= et) \
PG_RETURN_INT32(RESULT_WAIT); \
if (cycle++ % 100 == 0) \
CHECK_FOR_INTERRUPTS(); \
pg_usleep(10000L); \
} while(true && t != 0);

Probably it can be replaced by using real locks.

Regards

Pavel

Thanks,

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orafce-genera...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/64a9a911-5a93-43f4-ac3d-dbd3363a74ean%40googlegroups.com.

[Richard Shuttlewood]

Hi Pavel

Thanks for the quick reply!

I took a look at Postgres listen and notify but I couldn’t work out how to set listen to wait indefinitely and then grab the message and take some action the way I can with dbms_pipe

Richard 

To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/CAFj8pRDscQpfM_mwbcmLOakRgP-ytdFyYGNEf3F2D2qC8AnN0Q%40mail.gmail.com.

[Pavel Stehule]

st 22. 3. 2023 v 21:15 odesílatel Richard Shuttlewood <erintec...@gmail.com> napsal:

Hi Pavel

Thanks for the quick reply!

I took a look at Postgres listen and notify but I couldn’t work out how to set listen to wait indefinitely and then grab the message and take some action the way I can with dbms_pipe

the LISTEN command doesn't wait indefinitely - so this is not a good example. You should to check advisory locks maybe https://www.postgresql.org/docs/current/explicit-locking.html

Regards

Pavel

 

To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/CAKBtMmH-6SYX3S5%2ByyNqZPakEMXDamAkB8VgZ6%3D5sMG0LsH1cQ%40mail.gmail.com.

[Richard Shuttlewood]

Hi Pavel

It would be great if the performance of dbms_pipe could be improved.  I've taken a look at  whether I could do something but this is outside my comfort zone!

Would there be any chance of finding an orafce contributor who would be willing to take it on?

Thanks

richard

To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/CAFj8pRBieBhGEqyUTJPwUbNSUkMzz2oK9oCiv0Ah5H-O5uHEUw%40mail.gmail.com.

[Pavel Stehule]

Hi

pá 19. 5. 2023 v 17:52 odesílatel Richard Shuttlewood <erintec...@gmail.com> napsal:

Hi Pavel

It would be great if the performance of dbms_pipe could be improved.  I've taken a look at  whether I could do something but this is outside my comfort zone!

Would there be any chance of finding an orafce contributor who would be willing to take it on?

I can look at this issue, but I don't think it can be before July. Now is the worst time for me -  It needs just a few days, but I have not. Currently I have a lot of work on contract, and I need to finish refactoring of session variables patch. Probably this change will be small, but synchronization between processes is not my cup of tea. This is not my usual work.

quick fix can be decreasing limit in pg_usleep(10000L);

There will be higher usage of CPU, but the latences should be lower

Regards

Pavel

To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/CAKBtMmGzv1NsdQTRJ-rh53SBHvPK04OyWuyX0_jV-jBsFqekJQ%40mail.gmail.com.

[Richard Shuttlewood]

Hi Pavel

Hope you are doing well.

Just wondered if you have had a chance to look at this dbms_pipe performance issue?

Many thanks,

richard

To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/CAFj8pRBctofa%3DSA5LFSKK-i2GQVp7zTVDdGEfSJYi%2BVLpgcyJA%40mail.gmail.com.

[Pavel Stehule]

Hi

st 26. 7. 2023 v 10:18 odesílatel Richard Shuttlewood <erintec...@gmail.com> napsal:

Hi Pavel

Hope you are doing well.

Just wondered if you have had a chance to look at this dbms_pipe performance issue?

Unfortunately not, I spent a lot of time on plpgsql_check - my other project. But I think I can do some optimization in August - it is a realistic estimation.

Regards

Pavel

To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/CAKBtMmH8opdg1j7Tqv7XYWTHtYqBz0UiwkkTL%2Bk6q6a9_RxaEQ%40mail.gmail.com.

[Pavel Stehule]

Hi

st 22. 3. 2023 v 18:30 odesílatel Richard Shuttlewood <erintec...@gmail.com> napsal:

I am testing an alternative method for Oracle Autonomous Transactions using DBMS_PIPE

I tested some simple example of dbms_pipe

(2023-07-29 13:19:15) postgres=# do $$
begin
  for i in 1..1000000
  loop
    perform dbms_pipe.pack_message(i); perform dbms_pipe.pack_message('extra long long string for extra long long string');
    perform dbms_pipe.send_message('testp');
  end loop;
end;
$$;
DO
Time: 201906,318 ms (03:21,906)

(2023-07-29 13:19:15) postgres=# do $$
begin
  for i in 1..1000000
  loop
    perform dbms_pipe.receive_message('testp');
    perform dbms_pipe.unpack_message_number();
  end loop;
end;
$$;
DO
Time: 210634,757 ms (03:30,635)

and you can see, the communication is about 201 us - it is around 25 times faster than your 5 ms

You can increase speed of communication by increasing size of pipe buffer (in orafce pipe items).

Please, can you send some test cases that show performance issues that you detected?

[Pavel Stehule]

Hi

st 26. 7. 2023 v 10:32 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

Hi

st 26. 7. 2023 v 10:18 odesílatel Richard Shuttlewood <erintec...@gmail.com> napsal:

Hi Pavel

Hope you are doing well.

Just wondered if you have had a chance to look at this dbms_pipe performance issue?

Unfortunately not, I spent a lot of time on plpgsql_check - my other project. But I think I can do some optimization in August - it is a realistic estimation.

I wrote some optimizations based on different synchronization. It is in the master branch and PostgreSQL 13 and higher is required. 

Please test it.

Some tests look unstable, so maybe there can be some issues, but it should be enough for testing.

[Pavel Stehule]

út 1. 8. 2023 v 22:13 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

Hi

st 26. 7. 2023 v 10:32 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

Hi

st 26. 7. 2023 v 10:18 odesílatel Richard Shuttlewood <erintec...@gmail.com> napsal:

Hi Pavel

Hope you are doing well.

Just wondered if you have had a chance to look at this dbms_pipe performance issue?

Unfortunately not, I spent a lot of time on plpgsql_check - my other project. But I think I can do some optimization in August - it is a realistic estimation.

I wrote some optimizations based on different synchronization. It is in the master branch and PostgreSQL 13 and higher is required. 

Please test it.

Some tests look unstable, so maybe there can be some issues, but it should be enough for testing.

After last changes I run 100 cycles of tests without any problem, so I think new implementation can be ok

Regards

Pavel