Hi,
El 19/10/10 09:22, HJ escribió:
> If I take in Java each field (like such a String i should you above)
> as a String and compress it and write it to a file the resulting file
> size is around 10Mb for 42000 fields. If I store these fields
> uncompressed into a text file the result into a text file with a size
> of 20MB. If I store each single field compressed to a database (even
> unindexed) the resulting size of the database is 35MB. If I skip the
> compression step and store 42000 Strings to the H2 database the size
> of the database grows to around 70MB.
> So my actual question is:
>
> Why is the database so immensely larger then when storing "raw" data
> as a file?
> Ok a database always creates a little overhead but it does
> not double the data.
As was stated that size come from, database structure, page store size and free
space added to btree.
But I suspect that in this case UNICODE internal representation can be the main
culprit ; maybe Thomas can add some clarification.
I have personal interest in use cases like yours, and I make some tests to make
may own implementation decisions.
If your sample bio data represent your average case,
DATA_LEN COMP_OLEN COMP_RATIO
---------------------------------------------------
3416 bytes 1944 bytes 56.9100 %
1706 chars 972 chars 56.9800 %
for 42000 rows I get:
# FILE Size Bytes Content
-- --------------- ------------ -------------------------------
71,820,000 Calculated size in chars (1706+4)*42000
0 143,640,000 Calculated size in bytes (3416+4)*42000
1 biodata.csv 72,102,907 Raw text data as CSV file (ISO-8859-1)
biodata.utf8.csv 72,102,907 Raw text data as CSV file (UTF-8
biodata.utf16.csv 144,205,816 Raw text data as CSV file (UTF-16)
2 biodata.csv.gz 466,905 Compressed CSV file (gzip -9)
3 test.h2.db 173,873,152 Database with varchar uncompressed field
4 test-comp.h2.db 87,269,376 Database with varbinary compressed field
5 test_script_txt.gz 541,166 SCRIPT from database with varchar uncompressed
field
6 test_script_bin.gz 599,455 SCRIPT from database with varbinary compressed
field
Cases 2,5 & 6, are seriously distorted because the same data is used for all
rows, so can only be viewed as an extreme best case.
* SCRIPTS to create an populated database (5 & 6) are very close to raw data
size (compressed), and extremely compact vs. db size.
* (4) live db size w/varbinary compressed field is 20% larger than (1) raw
data csv file.
* (3) live db size w/varchar uncompressed field is 240% larger than (1) raw
data csv file.
* (3) live db size w/varchar uncompressed field is 20% larger than (0) raw
data size calculated as unicode chars.
I think that your ASCII data has many to loose over a Unicode database.
Above info generated with this sql:
-- -----------------------------------
set @BIODATA = ' sp|P08506|DACC_ECOLI PENICILLIN-BINDING PROTEIN 6 PRECURSOR\n
(D-ALANYL-D-ALANINE CARBOXYPEPTIDASE FRACTION C) (EC\n3.4.16.4)\n
(DD-PEPTIDASE) (DD-CARBOXYPEPTIDASE) (PBP-6).\n Length =
400\n\n Score = 894 (409.5 bits), Expect = 5.0e-120, P = 5.0e-120\n Identities
= 169/342 (49%), Positives = 237/3 42 (69%)\n\nQuery:
1\nMDYTTGQILTAGNEHQQRNPASLTKLMTGYVVDRAIDSHRITPDDIVTVGRDAWAKDNPV 60\n
MDY +G++L GN ++ +PASLTK+MT YVV +A+
+ +I D+VTVG+DAWA\nNP\nSbjct:
45\nMDYASGKVLAEGNADEKLDPASLTKIMTSYVVGQALKADKIKLTDMVTVGKDAWATGNPA 104\n\nQuery:
61\nFVGSSLMFLKEGDRVSVRDLSRGLIVDSGNDACVALADYIAGGQRQFVEMMNNYAEKLHL 120\n
GSS+MFLK GD+VSV DL++G+I+ SGNDAC+ALADY+AG Q F+ +MN
YA\n+KL L\nSbjct:
105\nLRGSSVMFLKPGDQVSVADLNKGVIIQSGNDACIALADYVAGSQESFIGLMNGYAKKLGL 164\n\nQuery:
121\nKDTHFETVHGLDAPGQHSSAYDLAVLSRAIIHGEPEFYHMYSEKSLTWNGITQQNRNGLL 180\n
+T F+TVHGLDAPGQ S+A D+A+L +A+IH PE Y ++ EK T+N I Q
NRN\nLL\nSbjct:
165\nTNTTFQTVHGLDAPGQFSTARDMALLGKALIHDVPEEYAIHKEKEFTFNKIRQPNRNRLL 224\n\nQuery:
181\nWDKTMNVDGLKTGHTSGAGFNLIASAVDGQRRLIAVVMGADSAKGREEEARKLLRWGQQN 240\n
W +N DG+KTG T+GAG+NL+ASA G RLI+VV+GA + + R E+ KLL WG\n+\nSbjct:
225\nWSSNLNEDGMKTGTTAGAGYNLVASATQGDMRLISVVLGAKTDRIRFNESEKLLTWGFRF 284\n\nQuery:
241\nFTTVQILHRGKKVGTERIWYGDKENIDLGTEQEFWMVLPKAEIPHIKAKYTLDGKELTAP 300\n
F TV + T+R+W+GDK ++LG + + +P+ ++ ++KA YTL\n+LTAP\nSbjct:
285\nFETVTPIKPDATFVTQRVWFGDKSEVNLGAGEAGSVTIPRGQLKNLKASYTLTEPQLTAP 344\n\nQuery:
301 ISAHQRVGEIELYDRDKQVAHWPLVTLESVGEGSMFSRLSDY 342\n + Q VG I+
K + PL+ +E+V EG F R+ D+\nSbjct: 345
LKKGQVVGTIDFQLNGKSIEQRPLIVMENVEEGGFFGRVWDF 386';
set @CBIODATA = COMPRESS(STRINGTOUTF8(@BIODATA),'DEFLATE');
select length(@BIODATA) as data_len, length(@CBIODATA) as comp_len,round(
(length(@CBIODATA)*1.0000/length(@BIODATA)) ,4)*100 as comp_ratio ;
select octet_length(@BIODATA) as data_olen, octet_length(@CBIODATA) as
comp_olen,round( (octet_length(@CBIODATA)*1.0000/octet_length(@BIODATA))
,4)*100 as comp_ratio ;
drop table if exists a;
create table a (id int auto_increment primary key, bdata varbinary );
insert into a (id,bdata) select x as id, @CBIODATA as b from
system_range(1,42000);
SCRIPT DROP TO '~/data/.h2db/test/test_script_bin.gz' COMPRESSION GZIP;
-- drop table if exists a;
-- SAME with varchar in place of varbinary
drop table if exists b;
create table b (id int auto_increment primary key, bdata varchar );
insert into b (id,bdata) select x as id, @BIODATA as b from
system_range(1,42000);
SCRIPT DROP TO '~/data/.h2db/test/test_script_txt.gz' COMPRESSION GZIP;
call csvwrite('~/data/.h2db/test/biodatatext.csv','select * from
b','ISO-8859-1');
call csvwrite('~/data/.h2db/test/biodata.utf8.csv','select * from b', 'UTF-8');
call csvwrite('~/data/.h2db/test/biodata.utf16.csv','select * from b',
'UTF-16');
drop table if exists b;
shutdown compact;
-- -----------------------
To look some more numbers , an empty new created H2 database have about 14 kb
size.
Test case:
drop table a;
create table a ( id int primary key, b clob);
insert into a (id,b) select x as id, repeat('0123456789',100) as b from
system_range(1,42000);
shutdown compact;
With only one table with 42000 rows (all lobs are null) , database size grow to
608 kb ~ 15 Bytes/row
Chars/CLob DB size cLob_Files Bytes/Row
--------- -------- ---------- ---------
0 0.6 Mb 15
10 1.3 Mb 31
100 8 Mb 194
500 84 Mb 2060
1000 84 Mb 2060
2000 84 Mb 2060
3000 168 Mb 4193
4000 168 Mb 4193
5000 1.3 Mb 330 Mb 8270
6000 1.3 Mb 330 Mb 8270
>> System properties can be set when starting the virtual machine:
>> java -Dh2.lobInDatabase=true
>> They can be set within the application, but this must be done before loading
>> any classes of this database (before loading the JDBC driver):
>> System.setProperty("lobInDatabase", "true"
> So I can do this before Class.forName(org.h2...)
Yes.
regards,
Dario
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
