Long Byte Object

[Ingres Forums]

Hi All,

[I originally posted this in the Database-General forum at actian.com
but thought I may get more input from here...]

I'm trying to use LBO's (for the first time) to store some documents and
spreadsheets that are generated during our development lifecycle.

All seem to be going swimmingly until I tried to load an 8Mb word
document into the table - it took a lot longer than I was expecting.

A quick google suggested that the default 'blob_etab_page_size' of 2048
bytes would not be helping matters. I changed this to 16384 and
restarted Ingres. I re-created the table that had an LBO and the new
iietab table (iietab_110_111 if it helps) had a page size of 16384.
However, it still took 25 seconds to perform the LBO.UpdateInDb method.

The structure of the iietab_110_111 table is:

Column Information:
Key
Column Name Type Length Nulls Defaults Seq
per_key TblKeyNoSM no no 1
per_segment0 integer 4 no no 2
per_segment1 integer 4 no no 3
per_next integer 4 no no
per_value byte varyi 1978 no no

Secondary indexes: none

Is there something else I should be doing or are LBO's really that
slow?

Any help gratefully received!

Dave Clark

We're runniing:
Ingres 2006 - 9.1.1 (su9.us5/103)
OpenROAD 2006 - OR 5.0/0506 (int.w32/00) BuildNo 3201 PatchNo 13398


--
wal...@aviagen.com
------------------------------------------------------------------------
wal...@aviagen.com's Profile: http://community.actian.com/forum/member.php?userid=2141
View this thread: http://community.actian.com/forum/showthread.php?t=14434

[Laframboise, André]

LOBs in ingres are not slow at all. My LOB tables are configured as 64K pages with millions of rows.

But we do all data management via data handlers in ESQL.

Andre

_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Ingres Forums]

=?iso-8859-1?Q?Laframboise=2C_Andr=E9?=;41723 Wrote:
> LOBs in ingres are not slow at all. My LOB tables are configured as 64K
> pages with millions of rows.
>
> But we do all data management via data handlers in ESQL.
>
> Andre
>
>

Hi Andre,

Thanks for the reply.

I think there must be something else that I need to configure then as
the saves are taking way too long. I expected the 'per_value' column
length to increase when I increased the page size, thereby reducing the
number of rows required for each blob.

What other parameters I can tweak?

Dave

[Sørensen.Henrik Georg HGO]

In our system we've been using Blobs in lots of heavy stuff to replace NFS-filesystems when
Running ABF (ESQL-routines since OpenIngres 1.2) and OpenROAD-applikations (Direct).
In ABF to transfer batch-output to On-line user and in OpenROAD to view this, or as well as
Uploading documents through OpenROAD appservers. (Many huge PDF Manuals uploaded during Tests :-)).
Later versions from 9.1 and forward has some improvements in performance.

Here an extract of some of my maintainance ABF-programs (Danish comments and variables but should
Be easy to understand anyway) that relocate's the blob-tables to take Usage of available locations, and
at the same time makes sure the iietab-tables is on a specified Page_size, and also compressed with high fill-factor.

Simply writing an SQL-file (file-write routine sps_skriv not included), which is later run by the
Shell-script that calls this ABF-program.

/*
** PROGRAMNAVN: saet_blobs
** TYPE.......: PROCEDURE
** KILDETEKST.: saet_blobs.osq
** AF.........: HGS 05.05.2010
** FORMÅL.....: At reorganisere blob-tabeller
** PARAMETRE..:
** RETUR......:
**
** TABELLER...: SELECT - UPDATE - INSERT - DELETE:
**
** KALDER.....:
** INCLUDES...:
*/
#include <def/define.h>
#include <sps/konstant.h>
procedure saet_blobs() =
declare
pgmnavn = varchar(24) not null,
table_name = varchar(32) not null,
page_size = integer not null,
katalog = varchar(256) not null,
filnavn = varchar(256) not null,
location_name = varchar(24) not null,
aktuel_list = varchar(256) not null,
location_list = varchar(256) not null,
sql_tekst = varchar(256) not null,
chg_jn = varchar(3),
begin
pgmnavn = 'saet_blobs'; chg_jn = NEJ;
g_svar = konv_setup();
if g_svar != OK then
return :g_svar;
endif;
/* BLOBS: Spread on ALL locations Previously Used */
select distinct location_name = location
from iifile_info
where table_name = 'b_location' and owner_name = :g_dba and
squeeze(location) != '' and location != 'ii_database'
order by location_name
begin
if location_list != '' then
location_list = location_list + ',';
endif;
location_list = location_list + location_name;
end;
commit;
message varchar(date('now')) + ' Blobs';
katalog = sps_path(ident = 'optimize');
katalog = katalog + 'chgsql/' + g_database + '/';
filnavn = katalog + 'blobs.sql';
g_dummy = sps_skriv(type = 'w', tekst = :filnavn);
g_dummy = sps_skriv(type = 'g', tekst = 'set autocommit on;\p\g');
/* BLOB Page.Size */
select table_name = table_name,
page_size = table_pagesize
from iitables i
where i.table_type = 'T' and i.table_owner = :g_dba and
i.expire_date = 0 and i.table_name like 'iietab\_%' escape '\'
order by table_name
begin
aktuel_list = '';
if page_size != 8192 then
chg_jn = JA; page_size = 8192;
sql_tekst = 'modify ' + :table_name + ' to reconstruct'
+ ' with page_size = ' + varchar(page_size) + ';\p\g\time';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
endif;
select distinct location_name = location
from iifile_info
where table_name = :table_name and owner_name = :g_dba and
squeeze(location) != ''
order by location_name
begin
if aktuel_list != '' then
aktuel_list = aktuel_list + ',';
endif;
aktuel_list = aktuel_list + location_name;
end;
if location_list != '' and location_list != aktuel_list then
chg_jn = JA;
sql_tekst = 'modify ' + table_name + ' to reorganize'
+ ' with location = (' + location_list + ');\p\g\time';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
endif;
end;
commit;
select i.table_name
from iitables i
where i.table_owner = :g_dba and i.expire_date = 0 and
i.table_name like 'iietab\_%' escape '\' and
i.storage_structure = 'BTREE' and
( i.table_ifillpct != 100
or i.table_dfillpct != 100
or i.table_lfillpct != 100
or i.is_compressed = 'N')
order by table_name
begin
chg_jn = JA;
sql_tekst = 'modify ' + :table_name + ' to reconstruct'
+ ' with compression = (data),'
+ ' nonleaffill = 100, leaffill = 100, fillfactor = 100;'
+ '\p\g\time';
g_dummy = sps_skriv(type = 'g', tekst = :sql_tekst);
end;
g_dummy = sps_skriv(type = 'c');
if chg_jn = NEJ then
/* Fjern filen igen :-) */
g_dummy = sps_skriv(type = 'd', tekst = :filnavn);
endif;
return OK;
end

/* No * More */

Kind regards

Henrik Georg Sørensen, Systemdeveloper and databaseadministrator
-----Oprindelig meddelelse-----
Fra: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] På vegne af Ingres Forums
Sendt: 17. maj 2012 10:00
Til: info-...@kettleriverconsulting.com
Emne: Re: [Info-Ingres] Long Byte Object

[nikosv]

Aside from replacing NFS-filesystems, what are the advantages of
storing files inside the database over just storing the reference to a
file that exists on the file system?

[Martin Bowes]

Hi Niko,

The advantages of storing the file as a blob inside the database are:
1. its included in the database backup.
If you only store links to a file repository then you will possibly have a syncing issue when doing recoveries. Ie.you have just restored a link in your database to a file in the repository...but does that file exist anymore? Has the event which caused the database recovery also wiped the disk holding the repository...how do you recover that repository to the same standard as the database? What happens if you can't achieve that? How can you measure the quality of the recovery?

2. The blob moves with the database.
If you need to move the database then everything is included in that database move, no need to separately move the file repository.

The disadvantages are:
1. Its included in the database backup.
And it can make huge backups real quick. If the blobs are images then they won't compress well so your backups become huge. This is a real problem if the files are effectively dormant. Ie don't change a lot. You waste an awful lot of backup time and space on data that has not changed a lot (if at all) since the last backup....which you are forced to do due to the volatility of the normal non blob data. You can try table level checkpoints but God help you in a recovery if you need to apply those!

One way around this problem is to put the blobs in a dedicated database away from the rest of the data. Then use STAR to create a database with links to your real database and to your blob database. The trouble with this approach is that STAR sucks! You can easily find a lot of problems using STAR as it will disallow some queries and hosts of useful things. If you are forced to not use STAR and want to go down the separate database route then you may be forced to write applications with multiple database connections and enjoy the full horror of that.

So I would say you have to look at the volatility of the data. You also need to consider how much you are going to get and how quickly. Evaluate If Ingres/STAR is going to be acceptable...I would use it if it can do what you think you'll need to do.

Hope this helps,

Marty

[Roy Hann]

Martin Bowes wrote:

> [snip]

> Then use STAR to create a database with links to your real database
> and to your blob database. The trouble with this approach is that
> STAR sucks! You can easily find a lot of problems using STAR as it
> will disallow some queries and hosts of useful things.

I think we see eye-to-eye on Star. It is a good concept and could be an
extremely slick solution to lots of problems.

Unfortunately it has never really had the work done that (I think)
it deserves so it's a bit rough around the edges. It's tantalizing and
infuriating by turns, like a brilliant but bone-lazy child.

If you know its quirks and limits and go into it with your eyes open
it can still be very useful, but my, what it could have been... :-(

--
Roy

UK Actian User Association Conference 2012 will be on Tuesday June 19 2012.
Register now at https://www.regonline.co.uk/ukiua2012
The latest information is available from www.uk-iua.org.uk.

[nikosv]

Hi Marty,
thanks for your very detailed answer. True, the syncing issue is a pain. Maybe a file notification service that fires events and notifies the admin when there is change in the filesystem could help to coordinate.But I am thinking that storing files inside the database would not scale well for web based apps plus if compression is used for saving space there will be lag until the file contents are decompressed and finally presented

[Paul White]

> you may be forced to write applications with multiple database
connections and enjoy the full horror of that.

I've not found this a problem with OpenROAD.
I usually break data up by year and connect to prior years' tables /
database as required.
The user can choose to archive images at a different time from the
transactional information.
It makes backups and archiving straightforward.

eg
Financials_db
transtab_2010
transtab_2011
transtab_2012

blobs2010_db
docstab_2010

blobs2011_db
docstab_2011

blobs2012_db
docstab_2012

[Martin Bowes]

Hi Niko,

If you store links to the files you may be well advised to also store an md5sum of the file. It's only an extra 32 characters and can help ensure you detect any alterations in the file after its storage. This is particularly useful in a recovery.

Another way to help the recovery situation is to ensure the file is stored in two locations. This can be achieved in many ways and probably depends on how much money you can spend on the task. You may like to look at hardware or software replication agents. Alternatively write an application which simply copies the files to two locations yourself.

Marty

-----Original Message-----
From: nikosv [mailto:niko...@gmail.com]
Sent: 21 May 2012 07:08
To: info-...@kettleriverconsulting.com
Cc: Ingres and related product discussion forum; nikosv
Subject: Re: [Info-Ingres] Long Byte Object

Hi Marty,
thanks for your very detailed answer. True, the syncing issue is a pain. Maybe a file notification service that fires events and notifies the admin when there is change in the filesystem could help to coordinate.But I am thinking that storing files inside the database would not scale well for web based apps plus if compression is used for saving space there will be lag until the file contents are decompressed and finally presented _______________________________________________

[Ian Kirkham]

Perhaps an area of focus for the upcoming sprint could be to tackle some
of these present limitations.
A good one to address would be the elevation of GCA protocol level -
this is what holds up things like ANSI dates and i8s etc.

Regards,
Ian

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Roy
Hann
Sent: 20 May 2012 18:46
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Long Byte Object

[Martin Bowes]

Hi Roy,

I totally agree...It is a great concept and could be a very slick solution.

Although I tend to use the analogy that Ingres/STAR is a cock-teaser. The look promises a night of comparing hard to find birth-marks in a hot bubble bath, but all you get is a slap in the face & a cold shower. The only question you get to ask beforehand is 'How hard a slap?'

And knowing this, I still use it as often as possible.

Marty

-----Original Message-----
From: Roy Hann [mailto:spec...@processed.almost.meat]
Sent: 20 May 2012 18:46

To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Long Byte Object

[Martin Bowes]

Hi Paul,

Did you bother to organise a two-phase commit? I think this is the major problem with organising the multiple database approach and is certainly why I prefer Ingres/STAR which has the two phase commit as standard. It's probably not much of an issue if the databases are all on the same host.

Marty

[Martin Bowes]

Hi Ian,

I suspect the problems with Ingres/STAR are way too deep seated for a code sprint and need to be addressed by a thorough re-examination of the process. Not being a developer I have no idea how hard this would be to implement, but for my 2c worth....

Remove the concept of separating the databases into local, distributed and co-ordinator. Just handle all databases as if they were distributed. Hence any database could have a table, view procedure registered in it. This is just like Access and would mean that I wouldn't have to 'have that conversation' with some snot-nose kid programmer again.

With any sort of luck this would mean that queries involving registered objects wouldn't have their present restrictions. Outer joins, session temp tables etc...all would be available.

Marty

-----Original Message-----
From: Ian Kirkham [mailto:Ian.K...@actian.com]
Sent: 21 May 2012 10:12
To: Ingres and related product discussion forum

Subject: Re: [Info-Ingres] Long Byte Object

Perhaps an area of focus for the upcoming sprint could be to tackle some of these present limitations.
A good one to address would be the elevation of GCA protocol level - this is what holds up things like ANSI dates and i8s etc.

Regards,
Ian

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Roy Hann
Sent: 20 May 2012 18:46

To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Long Byte Object

[Paul White]

Hiya Marty.
My implementations were always on the same host. I allowed the application
software to use the same unique key on both tables and optionally overwrite
/ update any existing images in the second DB. The "transaction" was marked
with a complete status when both db commits were completed.
Paul

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Martin
Bowes
Sent: Monday, 21 May 2012 7:14 PM
To: Ingres and related product discussion forum

[Ingres Forums]

Thanks for all the replies,

We have decided to leave LBO's alone for the time being - instead we are
just copying the files to another file location.

[James K. Lowden]

On Fri, 18 May 2012 08:48:10 -0700 (PDT)
nikosv <niko...@gmail.com> wrote:

> what are the advantages of storing files inside the database over
> just storing the reference to a file that exists on the file system?

If you store the file in the database, the DBMS can prevent the
deletion of the row while references to it exist.

--jkl

[nikosv]

[Ingres Forums]

people appear to born to compute. The numerical skills of children
develop so early and so inexorably that it is easy to imagine an
internal clock of mathematical maturity guiding their growth.Youth means
'Los Angeles Escort' (http://www.laasianescort.com) a tempera-mental
'Los Angeles Escorts' (http://www.laasianescort.com) predominance of
courage over timidity, of the appetite for adventure over 'Los Angeles
Asian Escort' (http://www.laasianescort.com) the love of ease. This
often exists in a man of 60 more 'Los Angeles Asian Escorts'
(http://www.laasianescort.com) than a boy of 20. Nobody grows old merely
by a number of yearsNot long after learning to walk and talk, they can
se


--
wsx111
------------------------------------------------------------------------
wsx111's Profile: http://community.actian.com/forum/member.php?userid=115352