Optimize disk space consumption for BLOB concatenation operations [CORE3948]

[firebird-automations]

Submitted by: Jörg Fröber (j_froeber)

Relate to CORE1658
Relate to CORE3838
Relate to CORE1273

Votes: 7

When a blob is being concatenated mulitple times, the disk space for the database file grows massively. The only way to get the file size back to normal is a backup and restore of the database.

The issue can be reproduced as described below:

- Create an empty database
- Create the following procedure:
SET TERM ^ ;
CREATE OR ALTER PROCEDURE P_TEST (
TEST VARCHAR(200),
ITERATIONS INTEGER)
RETURNS (
BLOBTEXT BLOB SUB_TYPE 0 SEGMENT SIZE 80)
AS
DECLARE COUNTER INTEGER;
BEGIN
COUNTER = 0;
BLOBTEXT = '';

WHILE (COUNTER < ITERATIONS)
DO BEGIN
BLOBTEXT = BLOBTEXT || ' ' || TEST;
COUNTER = COUNTER + 1;
END

SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE ON PROCEDURE P_TEST TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE P_TEST TO SYSDBA;

- run the procedure:
SELECT BLOBTEXT FROM P_TEST('This is a test sentence, which is going to be concatenated.', 4000);

After the procedure has been executed, the disk space used by the database file increases to over 1 GB. It makes no difference, if the transaction is committed or not.
When you run the procedure another time using the SQL-command EXECUTE PROCEDURE P_TEST('This is a test sentence, which is going to be concatenated.', 4000); the file size increases again.

[firebird-automations]

Commented by: @asfernandes

If blob concatenation is moved to temporary space, then the cost of concatenate followed be its write to database will double.

After commit/rollback, this wasted database space will be filled again.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Based on Adriano's comments, it seems the current functionality is "as designed/won't fix".

It is a temporary condition, the un-needed space/pages will be released to the engine for re-use for other purposes.

So, this case should be closed.

Objections?

[firebird-automations]

Commented by: @asfernandes

Well, there is room for improvements IMO. But this involves a kind of garbage colletor for blobs in PSQL. It's far from be easy!

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

priority: Major [ 3 ] => Minor [ 4 ]

issuetype: Bug [ 1 ] => New Feature [ 2 ]

summary: Excessive disk space consumption on BLOB concatenation => Optimize disk space consumption for BLOB concatenation operations

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

I have edited the case description to better reflect the nautre of the case, changes required as well as to turn the case into a feature request.

[firebird-automations]

Commented by: André Schwabe (a.schwabe)

In the case that we noticed this with a customer database went from 2,2Gb to 128Gb until it crashed because there was no more space. Even adding 500 Characters to a Blob 5000 times increases the database size by 2 Gb permanently. Surely there is room for improvement.

[firebird-automations]

Commented by: @mrotteveel

How can concatenating 4000 * 59 characters = 236000 characters - best case 236kb, worst case 944kb - result in a 1 GB increase. More importantly, how can that kind of excessive allocation not be considered a bug?

[firebird-automations]

Commented by: Hilmar Brodner (syn)

I agree with Mark that this surely cannot be a feature request. What feature would be requested here? "Add characters to a blob without using 1000 times the necessary space"? Sorry, Sean, not marking this as a bug but as a feature request would need some more explanation. As I understand Adriano this is also not "as designed" and we should see about "won't fix".

I understand Adriano that evidently all of the blob operations are done not in memory but on disc. One cannot do that in memory because of the huge size a blob could have. So optimum solution imho would be to do it on disc like with the temp files that are created when the sort buffer runs out of space. As I take it changing that is a rather large chunk of work so that we are going to have to live a while longer with on disc operations.
But what about the point that Mark brought up. Doing the maths it seems that the blob takes more and more space during the operation to arrive at 1Gb. Let's say that we are just concatenating the letter "a". So what seems to be written to disc is not "a" -> "aa" -> "aaa" -> "4000 a" using the same pages but rather using a separate and new page (or several depending on size) each time it is added: First page blocked with "a", next/second page blocked with "aa" and so on. I haven't found any documents regarding how the on disc handling on blobs is done so this is just a guess, please correct me or show me a link to a document. Should I be right however, maybe it would be less work concatenating like a varchar would do instead of writing down the whole thing anew with each operation.

[firebird-automations]

Commented by: @asfernandes

I'll recheck it, as I implemented solution for CORE1658, which is a related problem.

[firebird-automations]

Modified by: @asfernandes

Link: This issue relate to CORE1658 [ CORE1658 ]

[firebird-automations]

Modified by: @asfernandes

Link: This issue relate to CORE3838 [ CORE3838 ]

[firebird-automations]

Modified by: @asfernandes

Link: This issue relate to CORE1273 [ CORE1273 ]

[firebird-automations]

Commented by: @hvlad

> Even adding 500 Characters to a Blob 5000 times increases the database size by 2 Gb permanently
You created 5000 blob's. And this is really "as designed".
And increase in size shouldn't be pemanent - after transaction end all 5000-1 blobs will be released and space will be available again.

The real solution, as Adriano said, includes kind of garbage collector for blobs, also it probably includes reference counting for temporary blobs and ability to append same instance of temporary blob without creating new one.

PS Hilmar, you guesses are correct

[firebird-automations]

Commented by: Hilmar Brodner (syn)

We played around with this a bit more. In order to get around this problem we though we might actually write the concatenation to disc. So instead of doing this in "memory" we are doing updates on a table (terribly slow, but at least without taking 120 Gb of disc space). We however need to return more than one record so we have the following constellation:

CREATE TABLE BBLOB (
ID D_ID,
BLOBBY D_BLOBTEXT
);

SET TERM ^ ;
CREATE OR ALTER PROCEDURE P_TEST2
RETURNS (
BLOB_TEXT BLOB SUB_TYPE 1 SEGMENT SIZE 80)
AS
DECLARE COUNTER SMALLINT;
BEGIN
DELETE FROM BBLOB;

INSERT INTO BBLOB( ID, BLOBBY) VALUES ( 1,'');

COUNTER = 3;
WHILE (COUNTER > 0)
DO BEGIN
UPDATE BBLOB SET BLOBBY = BLOBBY || 'a' WHERE ID = 1;

SELECT BLOBBY FROM BBLOB WHERE ID = 1 INTO :BLOB\_TEXT;

SUSPEND;
COUNTER = COUNTER \- 1;

END
END^
SET TERM ; ^

GRANT SELECT,INSERT,DELETE,UPDATE ON BBLOB TO PROCEDURE P_TEST2;
GRANT EXECUTE ON PROCEDURE P_TEST2 TO SYSDBA;

Now if you do a "SELECT * FROM P_TEST2" you get a "BLOB not found" error message. Why? Well, seemingly the blob is not in memory if you do a SELECT (we recall: it sort of works if we do this in "memory").

So we went a bit further and added a single line that shouldn't do anything:

BLOB\_TEXT = BLOB\_TEXT \|\| '';

ending up with

SET TERM ^ ;
CREATE OR ALTER PROCEDURE P_TEST2
RETURNS (
BLOB_TEXT BLOB SUB_TYPE 1 SEGMENT SIZE 80)
AS
DECLARE COUNTER SMALLINT;
BEGIN
DELETE FROM BBLOB;

INSERT INTO BBLOB( ID, BLOBBY) VALUES ( 1,'');

COUNTER = 3;
WHILE (COUNTER > 0)
DO BEGIN
UPDATE BBLOB SET BLOBBY = BLOBBY || 'a' WHERE ID = 1;

SELECT BLOBBY FROM BBLOB WHERE ID = 1 INTO :BLOB\_TEXT;

BLOB\_TEXT = BLOB\_TEXT \|\| '';

SUSPEND;
COUNTER = COUNTER - 1;

END
END^
SET TERM ; ^

GRANT SELECT,INSERT,DELETE,UPDATE ON BBLOB TO PROCEDURE P_TEST2;
GRANT EXECUTE ON PROCEDURE P_TEST2 TO SYSDBA;

If we do our "SELECT * FROM P_TEST2" again, everything works fine.
So evidently doing a SELECT on a Blob and suspending it doesn't put it into memory, leading to problems, but accessing (modifying it without really doing anything) the same blob leads to it being put into memory and everything works like a charm?

I'm unsure if I should open a new thread with this as it might be a related issue. This should qualify as a bug however, shouldn't it?

[firebird-automations]

Commented by: @hvlad

> Now if you do a "SELECT * FROM P_TEST2" you get a "BLOB not found" error message. Why?
Because SUSPEND returns to you blob_id of blob but update on next iteration removed this blob and replaced it by new one.

This is definitely support question.

PS Blob's is not varchar's, its was not designed for manipulation at server side.