objectivity/db determining amount of "used" space

[wallst32]

I have a collaboration application that uses objectivity/db as its backend. The databases have grown very large in size, and I was very doubtful the contents actually consumed that much space. So I ran ootidy to compact them, and to my surprise, the databases were hardly reduced in size (maybe 30-100MB of reclaimed space for 2-9GB databases).

Is there any way to determine how much "empty" space is in each database?

I know there is way to export a list of files/attachments and their size; could anyone assist me on that?

TIA

 

[SQLSister]

I'm not familar with that database becasue I'm SQL Server kind of person, but there are many reasons why a database could be using more space thatn it actually needs. In SQL server for instance the transaction log will grow until it takes up the whole hard drive unless you regularly back it up or truncate it. I don't know if your database would have these issues, but it is worth investigating.

The use of character versus variable character datatypes will also consume more space than necessary. If I uses a char datatype of 100, then 100 characters are allotted even if I use 0 characters in a record. A variable character datatype only uses the amount it needs for the information stored. So the use of character data types (exactly what they are called will vary from database to database) can easily serve to greatly expand the amount of space a database uses ewpecially if the definition is way greater thatn the actual range of data in the records (if I define a field as 255 charcters and usually only use 4 or 5, for instance). So I would look at the table structures to see if this is a problem.

Another issue is the storage of records which are no longer needed. For instance we have a table which stores date sensistive information., Once the dat ai s passed the information is no longer displayed anywhere and the data is essentially useless (not all dat information is useless after the date is past, so be wary fo deleting just for this reason.) I nightly run a procedure to delete these no longer neede records. This keeps my table sizes down to the minimum they must be to perform their job. So if you have 3 years of historical data and only the last six months is needed, yo might be able to reduce the size of the database this way. However, I will caveat this by saying you must be very sure the data is no loger necessary before deleting it. Sometimes this information is used intrend analysis or is accounting data that will be used to summarize year ensd slaes fiuges or the like. Sometimes you may need to keep the data but move it to an archive database that is only rarely accessed. That way you can have a smaller active database (and therefore faster data access) and still have the data you may need for analytical or legal purposes.

Another problem that uses much more space is the use of multiple charcter filed as primary and foreign keys. If it takes three fields of 10 character each to identify my record uniquely, then this same 30 character smust be repeated in all the child tables. Integer joins generally use less space and are usually faster when queried against. They can be somewhat more difficult to write the queries for, so some less experienced database people continue to use the inefficient keys.

A database which is not normalised can also use too much space as the dat is frequwntly repeated. There are however valid reason for denormailzing a database, so I would not recommend just arbitrarily chaging the normalization pattern unless you understand exactly how and why the database is designed.

But frankly you may find that the data is necessary. 9 Gig databases are fairly common and I would not be surprised if the data is needed. Often the database structure is much more complex than a non database person would suspect.

 

[DonQuichote]

I only know objectivity as an object database, althogh I have never used it myself. One of the problems with object databases is that lower level data that is not linked anymore to top level data is very hard to recover. This means that data corruption cannot be undone in most cases.

I guess that the ootidy program would be able to find unreferenced data. If not, there might be a program to clean up the unreachable data, or you could try to store all the data that you can reach into a new database (I don't know how much work that is).

Best regards