DB size growing

[jhb50]

Is there an easy way to get this number?

You can see the numbers (images / audio / videos) in the new 1.6 console homepage.

nice, so when can we expect this new version ?

Its in beta test now so soon.

I have 6334 songs, 1315 movies, 4617 photos and ~1000 on-line feeds.

nice, so when can we expect this new version ?

Its in beta test now so soon.

I have 6334 songs, 1315 movies, 4617 photos and ~1000 on-line feeds.

interesting, so i am wondering why my db is so much bigger if i have less files

[DenyAll]

Firstly some caveats - as stated above, I know very little about derby, and (barring below) have never programmed in Java before...... so if you elect to do this, use at own risk. Before running the code below, you must shutdown Serviio completely (ie. stop the Serviio service). Then take a copy of your Serviio folder (and all subs) just in case.

Further to my previous post, I wanted to try and use the SYSCS_UTIL.SYSCS_COMPRESS_TABLE call to see if I could compress the database.

I used the code from http://www-01.ibm.com/support/docview.w ... wg21577292 as the basis to create a new java app in Netbeans, with the following code (this is for Windows, you may have to change the location of the "C:\\Program Files\\Serviio\\library\\db" database for your OS):

  Code:

package compressserviio;

import java.sql.*;
       
public class CompressServiio {

    /**
     * Based on:
     * http://www-01.ibm.com/support/docview.wss?uid=swg21577292
     */
   
    public static void main(String[] args) throws Exception {
   Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
   Connection conn = DriverManager.getConnection("jdbc:derby:C:\\Program Files\\Serviio\\library\\db");
   Statement s = conn.createStatement();
   ResultSet rs = s.executeQuery("SELECT SCHEMANAME, TABLENAME FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid   and t.tabletype = 'T'");

   CallableStatement cs = conn.prepareCall ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");   
   while (rs.next() ) {
       String schema = rs.getString(1);
       String table = rs.getString(2);
       System.out.println("Now compressing " + schema + " " + table);
       cs.setString(1,schema);
       cs.setString(2,table);
       cs.setShort(3, (short) 1);
       cs.execute();      
   }
    }
}

You also have to add derby.jar (from the serviio\lib folder) to the library in Netbeans (I'm sure all of this can also be done using the standard javac compiler rather than Netbeans, but that's for another day - I'm still learning ).

On running the code you get the following output:

  Code:

run:
Now compressing APP DB_SCHEMA_VERSION
Now compressing APP REPOSITORY
Now compressing APP MUSIC_ALBUM
Now compressing APP SERIES
Now compressing APP GENRE
Now compressing APP FOLDER
Now compressing APP PERSON
Now compressing APP COVER_IMAGE
Now compressing APP METADATA_EXTRACTOR_CONFIG
Now compressing APP MEDIA_ITEM
Now compressing APP PERSON_ROLE
Now compressing APP METADATA_DESCRIPTOR
Now compressing APP CONFIG
Now compressing APP RENDERER
Now compressing APP ONLINE_REPOSITORY
Now compressing APP PLAYLIST
Now compressing APP PLAYLIST_ITEM
Now compressing APP ACCESS_GROUP
Now compressing APP REPOSITORY_ACCESS_GROUP
Now compressing APP ONLINE_REPOSITORY_ACCESS_GROUP
BUILD SUCCESSFUL (total time: 1 minute 1 second)

Prior to running, my library (3,639 Videos, 14,249 Photos, 51 Audio) was 598MB (the db folder and sub folders only). I do a lot of testing, often adding files to the library and removing or renaming them - so I would expect that the database would have some reclaimable space. (ps. of my video's, about 2,600 collect metadata, which I'd expect accounts for most of the db size).

After running the results showed about a 6% improvement - my \db folder reduced to 560MB.

So while there was some improvement it's arguably not worth pursuing this approach, given the time it takes to run (most of the time was in compressing the COVER_IMAGE table, as you'd expect) for the saving you get. It suggests Derby is probably managing the slack space quite well by itself. However it would be interesting to see if others get better reductions, and in any case, it was an interesting learning exercise .