OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Size of DB
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
sreejanair
General User
General User


Joined: 15 Jul 2010
Posts: 12

PostPosted: Fri Jul 16, 2010 4:58 am    Post subject: Size of DB Reply with quote

Why is it that i get an error while trying to upoad a document that is 32MB in my table.
It crashes and gives me an out of memory exception. A database should be able to hold more right???
Back to top
View user's profile Send private message
Corfy
Moderator
Moderator


Joined: 14 Jun 2005
Posts: 1117
Location: Near Indianapolis, IN, USA

PostPosted: Fri Jul 16, 2010 5:48 am    Post subject: Reply with quote

First off, let me clarify by saying that I don't know a whole lot about databases. I took a 20-hour class on Microsoft Access five years ago, and haven't done much with databases since then. And I've certainly never imported a file into a table before.

However, one thing I do remember is, when creating your table, you need to make your fields large enough to handle all of the data you want to put into them. If you maike a field that can hold six characters, and you later decide you want to hold seven characters, you will have some work to do.

So the only thing that occurs to me (with my admittedly limited experience in these matters) is that maybe the field you are trying to import your file into isn't big enough to hold 32 MB of data. However, as I have never imported a file into a database, I don't know how to go about checking the size or increasing it.

I apologize if I am completely off base (no pun intended) on the problem, or if what I'm saying makes absolutely no sense. I'm just trying to help.
_________________
Laugh at life or life will laugh at you.
Back to top
View user's profile Send private message Visit poster's website
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Fri Jul 16, 2010 8:28 am    Post subject: Re: Size of DB Reply with quote

sreejanair wrote:
Why is it that i get an error while trying to upoad a document that is 32MB in my table.
It crashes and gives me an out of memory exception. A database should be able to hold more right???

By default you work with embedded HSQLDB which provides tables as placed only in Ram (not cached). You can replace backend DRBMS by HSQLDB in standalone mode or any orher RDBMS backend (better).
Back to top
View user's profile Send private message
sreejanair
General User
General User


Joined: 15 Jul 2010
Posts: 12

PostPosted: Fri Jul 16, 2010 8:58 am    Post subject: Reply with quote

How can I do that??
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Fri Jul 16, 2010 9:21 am    Post subject: Reply with quote

http://www.oooforum.org/forum/viewtopic.phtml?t=102514
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Jul 16, 2010 10:34 pm    Post subject: Re: Size of DB Reply with quote

sreejanair wrote:
...error while trying to upoad a document that is 32MB in my table...A database should be able to hold more right???

Absolutely, but you've entered the world of 'CLOB' text (or 'BLOB' binary) support in the database feature set. Basically, you'll want to setup a separate table for CLOB/BLOB as described in the HSQLDB 1.8.0 Users Guide, page 40 (pdf). But follow BigAndy's advice first to run HSQLDB in a multi-file configuration for good measure.

Hsqldb 1.8.0 Users Guide, pg 40 wrote:
Large Objects
JDBC Clobs are supported as columns of the type LONGVARCHAR. JDBC Blobs are supported as
columns of the type LONGVARBINARY. When large objects (LONGVARCHAR, LONGVARBIN-
ARY, OBJECT) are stored with table definitions that contain several normal fields, it is better to use two
tables instead. The first table to contain the normal fields and the second table to contain the large object
plus an identity field. Using this method has two benefits. (a) The first table can usually be created as a
MEMORY table while only the second table is a CACHED table. (b) The large objects can be retrieved
individually using their identity, instead of getting loaded into memory for finding the rows during query
processing. An example of two tables and a select query that exploits the separation between the two fol-
lows:
CREATE MEMORY TABLE MAINTABLE(MAINID INTEGER, ......);
CREATE CACHED TABLE LOBTABLE(LOBID INTEGER, LOBDATA LONGVARBINARY);
SELECT * FROM (SELECT * FROM MAINTABLE <join any other table> WHERE <various condit
The inner SELECT finds the required rows without reference to the LOBTABLE and when it has found
all the rows, retrieves the required large objects from the LOBTABLE.

_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
sreejanair
General User
General User


Joined: 15 Jul 2010
Posts: 12

PostPosted: Fri Jul 16, 2010 11:04 pm    Post subject: Reply with quote

Hi,

Thanks for the reply. I follwed the steps from the link in your last reply. Now i am not working with an embedded database. On the statusbar of the Database window i see JDBC and hsqldb:file:c//path and so on.

The driver loaded successfully and the connection test was also successful.
When i now try to upload a file which is 32MB ...its still gives me the out of memory exception

BASIC runtime error
An exception occured
type:com.sun.star.sdbc.SQlException
Message:out of memory

regards Sreeja
Back to top
View user's profile Send private message
sreejanair
General User
General User


Joined: 15 Jul 2010
Posts: 12

PostPosted: Fri Jul 16, 2010 11:17 pm    Post subject: Reply with quote

Sorry dacm, I did not see your post...going through it right away.. i am a little tensed as i have a deadline to deliver it by coming Monday and with my oo base skills ...looks like i will be working till the last moment Smile

Regards
Sreeja
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Jul 17, 2010 1:02 am    Post subject: Reply with quote

sreejanair wrote:
...with my oo base skills ...looks like i will be working till the last moment

Well...lets catch you up quickly on memory allocation AND adjusting table-column/field properties in a multi-file database using SQL:

First the Cached table memory considerations:

Here's the .properties file dump from a standard OOo Base ('embedded database') .odb file:
Code:
#HSQL Database Engine 1.8.0.10
#Wed Feb 10 11:21:53 CST 2010
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=true
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=false
hsqldb.cache_scale=13
version=1.8.0
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.lock_file=true
hsqldb.log_size=10
modified=no
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0


Your .properties file may be slightly different after creating a new database using HSQLDB in a multi-file/mode configuration:
Code:
#HSQL Database Engine 1.8.0.10
#Thu Jul 15 08:40:33 WGST 2010
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=no
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0


Notice from above you are using HSQLDB version 1.8.0.10 as you read on...

FAQ
Quote:
Does HSQLDB store all data in memory. Doesn't memory run out as a result?

Only if you want to. By default, CREATE TABLE results in a memory table, as this is the best type for smaller tables. For larger tables, use CREATE CACHED TABLE and adjust the hsqldb.cache_scale to suite your memory use requirements (as little as 8MB or so). See the Deployment Issues section of the Guide. There is no simple rule and no imposition on the part of HSQLDB as maximum flexibility is allowed using only a couple of settings. A popular use of HSQLDB is for OLAP, ETL, and data mining applications where huge Java memory allocations are used to hold millions of rows of data in memory.


Cache Memory Allocation
Quote:
With CACHED tables, the data is stored on disk and only up to a maximum number of rows are held in memory at any time. The default is up to 3*16384 rows. The hsqldb.cache_scale database property can be set to alter this amount. As any random subset of the rows in any of the CACHED tables can be held in the cache, the amount of memory needed by cached rows can reach the sum of the rows containing the largest field data. For example if a table with 100,000 rows contains 40,000 rows with 1,000 bytes of data in each row and 60,000 rows with 100 bytes in each, the cache can grow to contain nearly 50,000 rows, including all the 40,000 larger rows.

An additional property, hsqldb.cache_size_scale can be used in conjunction with the hsqldb.cache_scale property. This puts a limit in bytes on the total size of rows that are cached. When the default values is used for both properties, the limit on the total size of rows is approximately 50MB. (This is the size of binary images of the rows and indexes. It translates to more actual memory, typically 2-4 times, used for the cache because the data is represented by Java objects.)

If memory is limited, the hsqldb.cache_scale or hsqldb.cache_size_scale database properties can be reduced. In the example above, if the hsqldb.cache_size_scale is reduced from 10 to 8, then the total binary size limit is reduced from 50MB to 12.5 MB. This will allow the number of cached rows to reach 50,000 small rows, but only 12,500 of the larger rows.


So consider the following OOo Base settings versus the HSQLDB recommended defaults:
OOo Base:
hsqldb.cache_scale=13
which equals 3*2^13 rows or 3*8192 rows or 24576 rows
hsqldb.cache_size_scale=8
which equates to 2^8 * (cache_scale rows) Bytes or 256*24576 Bytes or ~6 MB of the .data file to be stored in the memory cache

HSQLDB default:
hsqldb.cache_scale=14
which equals 3*2^14 rows or 3*16384 rows or 49152 rows
hsqldb.cache_size_scale=10
which equates to 2^10 * (cache_scale rows) Bytes or 1024*49152 Bytes or ~48 MB of the .data file to be stored in the memory cache.


Bump those HSQLDB defaults by +1 each and you'll use 192 MB of RAM cache. But anything beyond the 48MB settings requires adjustments to the JVM memory allocation...

And then there's the JVM (Java Virtual Machine) memory allocation, but I don't think CLOB/BLOB is a consideration for JVM memory allocation.
edit: Actually, JVM memory is an issue for HSQLDB 1.8.x because CLOB/BLOB is not implemented using the standard Java CLOB/BLOB objects available today. Without getting into the details, the answer is simple, upgrade to HSQLDB 2.x. See the posts below for upgrade steps and details.
With HSQLDB 2.x, you won't need to adjust JVM memory until you reach 100,000 CLOB/BLOB objects.


Now for the table-column adjustments using SQL.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Thu Nov 10, 2011 6:26 pm; edited 10 times in total
Back to top
View user's profile Send private message
sreejanair
General User
General User


Joined: 15 Jul 2010
Posts: 12

PostPosted: Sat Jul 17, 2010 1:55 am    Post subject: Reply with quote

Thanks a lot !! I made the changes to the DB properties. I copied the tables from the old db to the new one. I cannot edit the Field properties when i right click on the table and open it in Edit mode.

Regards Sreeja
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Jul 17, 2010 9:17 am    Post subject: Reply with quote

sreejanair wrote:
I cannot edit the Field properties when i right click on the table and open it in Edit mode.

See the last link above about table-column (field) adjustments using SQL
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
sreejanair
General User
General User


Joined: 15 Jul 2010
Posts: 12

PostPosted: Sun Jul 18, 2010 11:50 pm    Post subject: Reply with quote

Hi dacm,

I did everything and at the end of the day i tried to upload a document, i got the usual java heapspace error and i increased the heap space and in the end the document uploaded but my db crashed along with it...with the message - Out of memory.

Regards
Sreeja
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Jul 19, 2010 9:17 am    Post subject: Reply with quote

What is your operating system, total RAM, and free RAM while running OpenOffice/Java?

It sounds like you're saying that increasing the Java heap eliminated that error but OpenOffice still throws an error while loading a LOB table (LONGVARCHAR column) with a 32mb file (CLOB object)?
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
sreejanair
General User
General User


Joined: 15 Jul 2010
Posts: 12

PostPosted: Tue Jul 20, 2010 12:05 am    Post subject: Reply with quote

I work with Micorsoft Windows XP, 3.25 GB RAM.
I anyway missed the deadline Smile My question is, can i use OO Base alone as a robust DB to store data and upload documents without the DB crashing ?? Is there a limit in the size and if so what is it ?
I followed the steps to separate the database and not work in embedded mode. That did not seem to help. I might have done something wrong...the problem is that even if i have i do not know what i did wrong Smile

You were so helpful in giving me all the steps... and so much of information. I will try by creating my database from the start again.

Regards
Sreeja
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Jul 20, 2010 1:13 am    Post subject: Reply with quote

sreejanair wrote:
I anyway missed the deadline Smile
Sorry Sad
Quote:
My question is, can i use OO Base alone as a robust DB to store data and upload documents without the DB crashing ??
Yes; in the configuration you're using (HSQLDB-embedded, file-mode) you are technically not using "Base alone" but with the included HSQLDB engine.
Quote:
Is there a limit in the size and if so what is it ?

HSQLDB 1.8: 2GB by default, or change to 8GB by changing a setting after running SHUTDOWN SCRIPT command. But read on (HSQLDB 2.0)...

HSQLDB 2.2.x: 2TB per CACHE table (previously 256GB per CACHE table in 2.0) and 64TB per CLOB/BLOB

Database size limits are further limited by file system limits per database file.
FAT32 = ~4GB (minus 1 byte)
NTFS = 16EB
ext3 = 16GB to 2TB
HFS Plus = ~8EB


And, available RAM limits table/query cache which is limited by the OS address space:
Windows 32-bit = generally 4GB minus overhead, video memory, etc (so typically <3 GB RAM cache limit)
Windows 64-bit = RAM cache limits up to 128 GB (Windows XP/Vista), 192 GB (Windows 7), 1 TB (Windows Server 2003), or 2 TB (Windows Server 2008).


HSQLDB 1.8.0 User Guide, page 40:
Code:
The file system must have enough spare room both for the 'permanent' and 'temporary' files. The default
maximum size of the .log file is 200MB. The .data file can grow to up to 8GB. The .backup file
can be up to 50% of the .data file. The temporary file created at the time of a SHUTDOWN COMPACT
can be equal in size to the .data file.

HSQLDB 1.8.0 User Guide, page 35:
Code:
hsqldb.cache_file_scale=1
This property can be set to 8 to increase the size limit of the .data file from 2GB to 8GB. To apply the
change to an existing database, SHUTDOWN SCRIPT should be performed first, then the property=
value line below should be added to the .properties file before reopening the database.
hsqldb.cache_file_scale=8


But with as much trouble as you're having I think it's time for you to switch to HSQLDB 2.0 using the upgrade procedure, but only after migrating to a split-database as BigAndy suggested (after migration to split-database, simply issue SHUTDOWN COMPACT using Tools > SQL... in Base, then shutdown OpenOffice and perhaps the Quickstarter, and switch-out/overwrite the engine file: hsqldb.jar with the new version of the file from the download zip-container file: hsqldb-2.0.0.zip, and restart Base and issue the command SHUTDOWN COMPACT once again to complete the upgrade). This new version handles LOB data quite differently than HSQLDB 1.8. As for database/table/LOB size is concerned, the HSQLDB 2.0 limits are increased to 256GB per CACHE table and 64TB per CLOB/BLOB.

HSQLDB 2.0 User Guide, page 10:
Code:
Lob Data

Lobs are logically stored in columns of tables. Their physical storage is a separate *.lobs file. In version 2.0 this file
is created as soon as a BLOB or CLOB is inserted into the database. The file will grow as new lobs are inserted into
the database. In version 2.0, the *.lobs file is never deleted even if all lobs are deleted from the database (In this case
you can delete the .lobs file after a SHTUDOWN).


HSQLDB 2.0 User Guide, page 164:
Code:
Lob Memory Usage

Access to lobs is always performed in chunks, so it is perfectly possible to store and access a CLOB or BLOB that is larger than the JVM memory allocation. Early versions of HyperSQL 2.0 use memory-based tables for the lob catalog (not the data). Therefore it is practical to store about 100,000 individual lobs in the database with the default JVM memory allocation. More lobs can be stored with larger JVM memory allocations. The realistic maximum number of lobs stored in the database is probably about a million. The actual total size of lobs is almost unlimited. We have tested with over 100 GB of lobs without any loss of performance.

HSQLDB 2.0 User Guide:
Code:
Data for CLOB and BLOB columns is not cached and does not affect the CACHED table memory cache.

HSQLDB 2.0 User Guide:
Code:
Large Objects

HyperSQL 2.0 supports dedicated storage and access to BLOB and CLOB objects. These objects can have huge sizes. BLOB or CLOB is specified as the type of a column of the table. Afterwards, rows can be inserted into the table using a PreparedStatement for efficient transfer of large LOB data to the database. In mem: catalogs, CLOB and BLOB data is stored in memory. In file: catalogs, this data is stored in a single separate file which has the extension *.lobs. The size of this file can grow to huge, terabyte figures.

LOB data should be store in the database using a JDBC PreparedStatement object. The streaming methods send the LOB to the database in one operation as a binary or character stream. Inside the database, the disk space is allocated as needed and the data is saved as it is being received. LOB data should be retrieved from the database using a JDBC ResultSet method. When a streaming method is used to retrieve a LOB, it is retrieved in large chunks in a transparent manner. LOB data can also be stored by calling a JDBC method with String or byte[] argument, but these methods limit the size of the LOB that can be stored or retrieved.

LOB data is not duplicated in the database when a lob is copied from one table to another. The disk space is reused when a LOB is deleted and is not contained in any table.

By using a dedicated LOB store, HyperSQL achieves consistently high speeds (usually over 20MB / s) for both storage and retrieval of LOBs.

The LOB catalog is stored in the database as a memory table. Therefore the amount of JVM memory should be increased when more than tens of thousands of LOBs are stored in the database.


Wikipedia 64-bit:
Code:
A compiled Java program can run on a 32 bit or 64 bit Java virtual machine without modification. The lengths and precision of all the built in types are specified by the standard and are not dependent on the underlying architecture. Java programs that run on a 64 bit Java virtual machine have access to a larger address space.


Quote:
I followed the steps to separate the database and not work in embedded mode.
Well technically, you're still using the embedded mode but in a split-database configuration. But don't let the "embedded" term alarm you because it has nothing to do with the issues here. I encouraged you to switch to the split-database solution for several reasons, including easy access to the .properties file.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Sat Dec 29, 2012 7:49 pm; edited 10 times in total
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group