Perhaps, database engines are sufficiently complex that taking the time to READ ALL the documentation, which in H2's case is quite small, (say compared to PostgreSQL) you would easily have found the DataBase File Layout table.
It states that every LOB (CLOB or BLOB) is stored in a separate file. This is fine for LARGE objects, where "large" is at least 3x or 4X greater than the minimum file allocation size for the host OS. However because LOBs are 1 file per LOB, they inherently waste space to the tune of about 1/2 the minimum file allocation space size, which is apparently what is causing all the space consumption. This approach to LOB storage is consistent with the LARGE in LOB, but it becomes a problem if you are actually just storing data that seldom is actually much larger than the OS file allocation size. Determine what your OS allocation size is compared to the typical message size you are storing and i expect you will discover that this is where all the unused space is going. Because of how H2 stores LOBs, i suggest that you should use a table with a varchar column with NO size spec'd, and maybe use an "overflow" LOB only for messages > 16K or what ever size is 3-4x your OS's min file allocation, where the "waste" space in each LOB file will be infrequent and thus acceptable. regards, john On Dec 15, 11:55 am, jjg <[email protected]> wrote: > OK, now as I read I see that *.lob.db are not temporary, so I'll keep > them. :o) I am to assume that every text field in the entire database > is stored as an individual file? Since I will probably top out at half > a million of these, in that case, should I start getting worried? Has > anyone tested this database with that many? Also, there is enormous > bloat then in the data file (currently at 224 Mb). What is in there > that occupies so much space? As I say I have entered less than 50 mb > and most of that is accounted for in the many lob.db files. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
