Result fetch length is dropped when the result set contains blob

[kogan69]

It's started in https://github.com/rana/ora/issues/225 As the title suggests, the traversal of result set degrades dramatically ~20 times worse, when the result set contains at lease a one blob. The code responsible for this is in: rset.go:529 switch param.typeCode { // These can consume a lot of memory. case C.SQLT_LNG, C.SQLT_BFILE, C.SQLT_BLOB, C.SQLT_CLOB, C.SQLT_LBI: fetchLen = MinFetchLen

I understand that this is done by design as an optimization for memory consumption. And it's great for some situations. However, in our workloads, we need to be optimized for speed, rather than for memory. I'd like to suggest to leave it as a default behavior, however, to add configuration mechanism to switch to a different one. A reasonable place for it, IMHO, could be a StmtCfg.

Gentlemen, your suggestions?

Cheers, LK

[tgulacsi]

Blobs can be quite big, we prefetch 1MiB for each - MinFetchLen is 8, so this limits the prefetch memory usage at 8MiB. With the MaxFetchLen=128, this would mean 128MiB - maybe that's not too much, I don't know.

Try with different values for MinFetchLen (16, 32, 64, 128) and please report which resulted the best performance, with the memory usage (for example as reported by /usr/bin/time), too!

[kogan69]

Hi there! I've created my local branch and made the changes to present to you via pull request. The problem that I'm a total newbie here and just can't manage to push this branch to the repo. (Quite embarrassing, but true :) )

This is what I do: [kogan@leonidk-lnx ora.v4]$ git branch

• force_maxfetch_len master

[kogan@leonidk-lnx ora.v4]$ git remote set-url origin https://gopkg.in/rana/ora.v4

and then: [kogan@leonidk-lnx ora.v4]$ git push --set-upstream origin force_maxfetch_len fatal: https://gopkg.in/rana/ora.v4/info/refs not valid: is this a git repository?

What am I doing wrong?

With kind regards, LK

[tgulacsi]

You should fork github.com/rana/ora on GitHub, then,

cd $GOPATH/src/gopkg.in/rana/ora.v4
git remote add fork git@github.com:kogan69/ora
git push fork force_maxfetch_len

or sth along these lines.

But a simple git show HEAD or the output of git diff origin/master could be enough.

Could you test & find the optimal MinFetchLen?

[kogan69]

Tamas, Many thanks man! I've managed to create my first pull request :))) Please take a look at: https://github.com/rana/ora/pull/227

The essence of my change was to preserve the existing behavior and provide the users with an explicit way to enforce the new one.

Regarding the testing - the performance gain was almost linear, up to the MaxFetchLen point - which is total no-brainer on the second thought :)) For our workloads - the speed is everything, so we opt to be memory hogs... Our baseline RAM setup is 32GB, so... However, for normal people, this will be probably much less, and this is why I used the word "Force" in the naming Thanks again for your help and hope to see it merged soon.

With kind regards, Leonid Kogan

[tgulacsi]

Please take a look at 51a7b25d06b3916acc7ac8615ab33cfdd6b30e85 !

[kogan69]

Already pooled the master. Your version is definitely better. There’s a bug, however. When a lob fetch size is bigger than default - it’s a problem.

Cheers, LK

On Oct 25, 2017, at 11:13 AM, Tamás Gulácsi notifications@github.com wrote:

Please take a look at 51a7b25 !

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[tgulacsi]

How can it be bigger? The SetLOBFetchLen caps it at MaxFetchLen.