EXTREMALLY SLOW Large Object operations :(

Forum related to PostgreSQL

Moderators: gto, cipto_kh, EgonHugeist, olehs

bulba
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 12.06.2012, 20:37

EXTREMALLY SLOW Large Object operations :(

Postby bulba » 16.06.2012, 10:13

Hello,

When testing at home Large Object operations I've found that they are extremally slow! :( To configrm that and to be sure I've made following tests:

1) I've made 4MB file filled witch char(0)
2) Transferred it to server from my laptop using laptop - the speed was 2373 Kb/s
3) Inserting this 4MB file using SQL mechanism took 20 seconds!

I've added TZMonitor and checked log - no anomailes, no warings, no errors. I've also dumped the session with tcpdump and the resulting file was 4,2MB - it means thet 0.2MB was prrotocol overhead and that file was NOT enocded while transfering (eg: sent binary).
By the way: my shared_buffers and work_mem are: 512MB and 128MB so it shouldn't be server-side problem.

Please share with me your ideas, thank you in advance!

Bulba Nakcharashi, MD

bulba
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 12.06.2012, 20:37

Postby bulba » 16.06.2012, 10:46

I've made another test: sending this 4MB over the wire into the database using lo_export function of psql, it tooks only 4 seconds. So it looks like the bootlenck is somewere inside ZDBC :(

Bulba Nakcharashi, MD

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 16.06.2012, 11:21

bulba,

Open ZDbcPostgreSQLStatement.pas
goto TZPostgreSQLPreparedStatement.PrepareSQLParam(...

set a breakpoint where the stBinaryStream will be assigned.
Check where we have this speed decrese. Either it happens on WriteTempBlob.SetStream(TempStream); because here we copy the statement/rowaccessor buffer to the postgre oid blob and then the blob will be witten with:

DbcPostgreSQLResultset.pas

Code: Select all

procedure TZPostgreSQLBlob.WriteBlob;
var
  BlobHandle: Integer;
  Position: Integer;
  Size: Integer;
begin
  { Checks for empty blob. }
  if IsEmpty then
  begin
    FBlobOid := 0;
    Exit;
  end;

  { Creates a new large object. }
  if FBlobOid = 0 then
  begin
    FBlobOid := FPlainDriver.CreateLargeObject(FHandle, INV_WRITE);
    CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcOther, 'Create Large Object',nil);
  end;

  { Opens and writes a large object. }
  BlobHandle := FPlainDriver.OpenLargeObject(FHandle, FBlobOid, INV_WRITE);
  CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcOther, 'Open Large Object',nil);

  Position := 0;
  while Position < BlobSize do
  begin
    if (BlobSize - Position) < 1024 then
      Size := BlobSize - Position
    else
      Size := 1024;
    FPlainDriver.WriteLargeObject(FHandle, BlobHandle,
      Pointer(LongInt(BlobData) + Position), Size);
    CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcOther, 'Write Large Object',nil);
    Inc(Position, Size);
  end;

  FPlainDriver.CloseLargeObject(FHandle, BlobHandle);
  CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcOther, 'Close Large Object',nil);
end;

Here we took a 1024 byte heap to write the oid blob. Maybe it is better to write 4096 bytes or something like this? Here you are the expert, i only can guid you where you find the lines which executes the processes.

The heap size makes me wonering. We've tests in our testsuites which check the oid blob's and they do not show us such an trouble...

Another possiblity:
what if we upgrade the WriteTempBlob.WriteBlob; procedure and use the TempBlob.GetStream as paramter? So we do not copy the Stream and write him directly but actually i can not say how zeos do behave if we do not set the stream to the blob interface..

Waiting for repley.

Michael
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

bulba
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 12.06.2012, 20:37

Postby bulba » 16.06.2012, 11:49

Michael,

Well, I'm scientist and medicine doctor with 3rd level in oncology, not a computer science expert :) But in fact since 2 years I'm studing computer science as I have to connect my medicine knowledge with image processing and programming knowledge, we are working on big project of CACD (computer aided cancer detection/diagnosis) which will help to fast detect early stages of cancer and will allow fast, accurate and effective treatment without need of making massive surgeries or lognterm radio and chemotherapy :)
OK, I'll test your sollution in few hours and will post the answer :)

Bulba Nakcharashi, MD

bulba
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 12.06.2012, 20:37

Postby bulba » 16.06.2012, 12:30

Wow! You were right! Increasing communication buffer gived in effect much faster communication :) I've changed it to 2^16 (65536) and 4MB file transfer is now 5 second, not 20 - it means 4 times speedup :)
However it's still only half of effective posibility of throughput - I'll now play a bit with WriteBlob and sending data directly, without coping stream.

Bulba Nakcharashi, MD

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 16.06.2012, 12:49

bulba,

great news! attach the patch an i'll commit it this evening.

Btw. which branch of our repositories do you use here?

Michael
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

bulba
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 12.06.2012, 20:37

Postby bulba » 16.06.2012, 13:08

OK, after many test I've decide that on MY HOME NETWORK the best size is 2^18 (262144), however it will NOT be always true! The most effective size of the packet will vary, it depends on your network speed, server configuration and even on network access technology (for example Fiber versus WiFi). So I've decided to make it configurable options, means: property of the class :)

And because Large Object are usually LARGE in their nature I'm writing now an event which will be fired after each sucessfull chunk write, it will help you make an progress bar while sending or receiving data - if you know the size of the file AND the size of the chunk it's quite easy to calculate progress :)

I'm using now SVN repo version: 956 - is it OK?

Bulba Nakcharashi, MD

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 16.06.2012, 13:16

bulba,

you're a great mind! use the ZQuery.Properties and introduce that patch then you can ask the IZStatement interface for the property packetsize := StrToIntDef(info.values['packetsize'], 1024); Then use it insteat of the static size.

according your rev. oh, oh, oh, that's eprecated we are on 1392 actually with \testing and \testing-egonhugeist.

May i point you to: http://zeos.firmos.at/viewtopic.php?t=3427 ? this is my private branch where i'm going a new way..

I'm back this evening.

Michael
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image


Return to “PostgreSQL”

Who is online

Users browsing this forum: No registered users and 1 guest