Page 1 of 2 12 LastLast
Results 1 to 10 of 32

Thread: Database file size

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Default Database file size

    Hello,
    I'm using Mezzmo for a few months now, mainly because it was one of the few apps able to stream external SRT files to playback devices. However I have a huge complaint to file for the developers of this UPnP server: the database file that it creates and uses, Mezzmo.db, grows to unhuman proprotions uselessly.
    Let me illustrate what I'm trying to say:
    With a library of just 400 video files, the library is taking up 3.5 GIGAbytes. That's around 3500 megabytes of space, for just a few hundred movies. Now imagine having 3000 movies, and maybe some 20.000 songs. I would need to have a dedicated HDD just for the database file Mezzmo is using, it would easily reach 1 TB or more.

    Now, being a programmer myself, I decided to take some actions about this ever since I was suspicious when it hit 1 GB in size for just 50 media files. So I did what any developer would do: started digging through the database using a SQLite database manager, so here's what I immediately detected:

    - THUMBNAILS are being cloned into the thumbnails table for each playlist that contains a file for that thumbnail, and also for any other file that has the same thumbnail. So, for example, if we have movie which has a thumbnail image that takes 300 kilobytes, and that movie is the thumbnail of 30 playlists (including automatically generated playlists), then it will eat 10 MB of space, instead of just 300 kB. This takes like 95% of the DB space.
    - ALL playlists are being stored in the database. This includes automatic playlists. This is stupid, since those are dynamic and should be created on-the-fly, in-memory, at runtime. Also these consume thumbnail storage since they require thumbnails...
    - dead entries for a lot of stuff, including playlists. I observed this in the GUI too: when I delete a playlist, I sometimes end-up with ghost entries, or sub-items that no longer have a node parent.

    So, what I did after this: wrote myself a nice PHP script to "clean" this database in the following way:

    - redundant thumbnails are being completely deleted and only the unique ones are kept (since they have a hash anyway, this is a fast operation). Ofcourse, deleted items referenced by other files and playlists will get the ID of the one and only kept thumbnail's id;
    - deleted thumbnails of inexistent or dead entries (as I said, this is a bug in Mezzmo, ghost entries remain many times after deleting stuff)
    - removed oprhaned playlists (there were A LOT of those!). Recursively doing this until no entries with inexistent parents remain.
    - vacuum - to compact the database file

    Final results: after running this cleanup script, my DB file shrank from 3.5 GB to 67 MB. That's 50 times smaller in size, with absolutely the same DB content.

    Unofrtunately, I have to run this script after each time Mezzmo starts or I add any new files, because on startup/adding files, it WILL re-duplicate all those thumbnails for the playlist entries again and again, ending up back to gigabytes in size.

    So, my request to your DEV team is: please, please, PLEASE, figure this out for your next release. My system drive is not a black-hole storage space for the Mezzmo server. DO NOT DUPLICATE thumbnails over and over again, DO NOT keep in the DB thousands and thousands of dynamic playlists (they're called dynamic for a reason), FIX those dead entries that remain after editing the library, and you'll have happier users.

    Thanks.

  2. #2
    Join Date
    Nov 2007
    Location
    Melbourne, Australia
    Posts
    11,616

    Default

    Many thanks for the detailed post, Adrian. We've addressed several of these issues in the next release. Email us at support [at] conceiva [dot] com and please send us your script. We'll check that our fixes matches what you are doing.

    Mezzmo Android: Install it on your tablet, smartphone, Android TV or Amazon Fire to browse and stream files from your Mezzmo library to all your devices. Full details at http://www.conceiva.com/products/mez...mo_android.asp
    Mezzmo for Kodi Add-on: Install it into Kodi to stream files from your Mezzmo library directly in Kodi. Full details at http://www.mezzmo.com/wiki/doku.php?...odi_user_guide
    Mezzmo for Roku App: Install it onto your Roku to stream files from your Mezzmo library. Full details at http://www.mezzmo.com/wiki/doku.php?...oku_user_guide
    Wiki: User Guides & Reference Manual at http://www.mezzmo.com/wiki
    Facebook: http://www.facebook.com/Mezzmo.DLNA.Server
    Twitter: https://twitter.com/conceiva_mezzmo
    Web: http://www.mezzmo.com

  3. #3
    Join Date
    Jul 2010
    Location
    California, USA
    Posts
    195

    Default

    I feel cheated. My db is only 995KB and I have 1958 video files and about 20000 songs. It is like I am not getting the bang for my buck.

    Oh wait, it might just be the way I have my system set up. Never mind you guys are doing great keep up the good work.

  4. #4

    Default

    Quote Originally Posted by JohnM View Post
    I feel cheated. My db is only 995KB and I have 1958 video files and about 20000 songs. It is like I am not getting the bang for my buck.

    Oh wait, it might just be the way I have my system set up. Never mind you guys are doing great keep up the good work.
    Nice one... maybe you meant 995 MB? It's mathematically impossible to store even the meta information for 21.958 files in under 995*1024 bytes since this would mean 46 bytes for each entry, including file path, record ID, thumbnail, etc.
    That's assuming you don't have any kind of playlists or any kind of any other information in the database file... I think you get my point.

    Anyway, I just updated to 3.4.3.0 and the issue is STILL here. My DB slowly grew again from 180 MB to 3.5 GB the moment the server restarted (it does this in the background until all playlists have a thumbnail, ofcourse duplicated from first file of that playlist).

    Since I assume Mezzmo developers are clueless about DB optimizations and how relational tables work in general, here's my PHP script that does the cleanup for me. It requires the PHP binary ofcourse, to run it (outside the scope of this post), with the SQLite extension enabled.
    Make sure the server does not currently use the database, but LEAVE IT RUNNING (so it doesn't start doing stupid stuff when restarted).

    Save it as a .php file, then invoke the PHP binary with this filename as the argument in a cmd console (I personally created a BAT file that runs this full command).
    Note the final step (compacting) will require much time since the journaling will first make a full database copy on disk (so you need at least that amount of additional free space), and then delete itself at the end.

    Code:
    <?php
    
    // REPLACE the path below with your account's user directory
    $db = new SQLite3 ('c:\Users\<username>\AppData\Local\Conceiva\Mezzmo\Mezzmo.db');
    if (!$db) {
    	die ("DB init failed");
    }
    //$db->busyTimeout(60000);
    
    // do everything in a atomic transaction to speed up the final commit
    if (!$db->exec("BEGIN TRANSACTION")) {
    	die ("Transaction failed!");
    }
    
    echo "Removing dead playlists...\n";
    do {
    	if (!$db->exec("delete from MGOPlaylist WHERE ParentID>0 AND ParentID NOT IN (SELECT ID FROM MGOPlaylist)")) {
    		die ("Query failed!");
    	}
    	$deleted = $db->changes();
    	echo " $deleted orphaned playlists deleted\n";
    } while ($deleted > 0);
    
    echo "Deleting all playlists thumbnails\n";
    $db->exec("update mgoplaylist set thumbnailid=null");
    
    echo "Removing unused thumbnails...";
    if (!$db->exec("DELETE from MGOThumbnailData WHERE ID NOT IN (SELECT ThumbnailID FROM MGOFile)")) {
    	die ("Query failed!");
    }
    $deleted = $db->changes();
    echo $deleted . " deleted\n";
    
    $sql = $db->query("SELECT COUNT(DISTINCT Hash) FROM MGOThumbnailData");
    if (!$sql) {
    	die ("Query failed");
    }
    $row = $sql->fetchArray();
    echo "Distinct thumbnail hashes: " . $row[0] . "\n";
    $sql->finalize();
    
    $sql = $db->query("SELECT ID,Hash FROM MGOThumbnailData GROUP BY Hash ORDER BY ID ASC");
    while ($row = $sql->fetchArray()) {
    	echo "Processing entry {$row['ID']}\n";
    	// TODO escape string - hash is Base64, so no need to escape now
    	$otherEntriesRes = $db->query("SELECT ID FROM MGOThumbnailData WHERE Hash='{$row['Hash']}' AND ID<>{$row['ID']}");
    	while ($rowOtherEntry = $otherEntriesRes->fetchArray()) {
    		echo " Removing entry {$rowOtherEntry['ID']}... ";
    		if (!$db->exec("UPDATE MGOFile SET ThumbnailID={$row['ID']} WHERE ThumbnailID={$rowOtherEntry['ID']}")) {
    			die ("Query failed!");
    		}
    		echo $db->changes() . " files, ";
    		if (!$db->exec("UPDATE MGOPlaylist SET ThumbnailID={$row['ID']} WHERE ThumbnailID={$rowOtherEntry['ID']}")) {
    			die ("Query failed!");
    		}
    		echo $db->changes() . " playlists affected\n";
    	}
    	$otherEntriesRes->finalize();
    	
    	$db->exec("DELETE FROM MGOThumbnailData WHERE Hash='{$row['Hash']}' AND ID<>{$row['ID']}");
    }
    $sql->finalize();
    
    $newId = 1;
    $sql = $db->query("SELECT ID,Hash FROM MGOThumbnailData GROUP BY Hash ORDER BY ID ASC");
    while ($row = $sql->fetchArray()) {
    	if ($row['ID'] > $newId) {
    		echo "Fixing entry {$row['ID']}... ";
    		if (!$db->exec("UPDATE MGOThumbnailData SET ID=$newId WHERE ID={$row['ID']}")) {
    			die ("Query failed!");
    		}
    		if (!$db->exec("UPDATE MGOFile SET ThumbnailID=$newId WHERE ThumbnailID={$row['ID']}")) {
    			die ("Query failed!");
    		}
    		echo $db->changes() . " files, ";
    		if (!$db->exec("UPDATE MGOPlaylist SET ThumbnailID=$newId WHERE ThumbnailID={$row['ID']}")) {
    			die ("Query failed!");
    		}
    		echo $db->changes() . " playlists affected\n";
    	}
    	$newId++;
    }
    $sql->finalize();
    
    // The section below removes any internal subtitles for all entries, since I only use external SRT file. Uncomment only if you have a similar setup.
    //echo "Fixing subtitles...\n";
    //$db->exec("DELETE FROM MGOSubtitles WHERE SubsTrack>0");
    //$db->exec("UPDATE MGOSubtitles SET SubsTitle=NULL, SubsLangID=NULL, SubsCharset=NULL");
    
    
    if (!$db->exec("COMMIT")) {
    	die ("Transaction failed!");
    }
    
    echo "Compacting DB... this might take a few minutes or more\n";
    $sql = $db->exec("PRAGMA page_size=4096");
    $sql = $db->exec("VACUUM");
    
    $db->close();
    
    ?>
    Last edited by adrian; 11-24-2013 at 06:11 AM.

  5. #5
    Join Date
    Nov 2007
    Location
    Melbourne, Australia
    Posts
    11,616

    Default

    Thanks for sharing this, Adrian. As mentioned in my other posts, the database optimizations will be included in the next version.

    Mezzmo Android: Install it on your tablet, smartphone, Android TV or Amazon Fire to browse and stream files from your Mezzmo library to all your devices. Full details at http://www.conceiva.com/products/mez...mo_android.asp
    Mezzmo for Kodi Add-on: Install it into Kodi to stream files from your Mezzmo library directly in Kodi. Full details at http://www.mezzmo.com/wiki/doku.php?...odi_user_guide
    Mezzmo for Roku App: Install it onto your Roku to stream files from your Mezzmo library. Full details at http://www.mezzmo.com/wiki/doku.php?...oku_user_guide
    Wiki: User Guides & Reference Manual at http://www.mezzmo.com/wiki
    Facebook: http://www.facebook.com/Mezzmo.DLNA.Server
    Twitter: https://twitter.com/conceiva_mezzmo
    Web: http://www.mezzmo.com

  6. #6

    Angry big bigger biggest

    Quote Originally Posted by Paul View Post
    Thanks for sharing this, Adrian. As mentioned in my other posts, the database optimizations will be included in the next version.
    Please please my database is now about 7-9GB!!!! i have now 12GB free on my system partion. Why i can't move the database to a other drive?

    KR JiKo

  7. #7
    Join Date
    Jun 2011
    Location
    USA
    Posts
    20

    Default

    Wow! I just looked at my database and was chagrined to see that it is just under 2G for about 600 movies and 4000 songs.

    I suspect that there is a balance to be struck here. Making the database fully relational will dramatically reduce its size, but may reduce performance in some cases, like multiple users browsing menus on endpoint devices. Replication may shorten menu response time but balloons the database size, especially with all the duplicated bitmaps. It also slows down the Mezzmo application; adding files and editing entries can sometimes be a frustratingly slow process.

    All that said, I think there has got to be a way to eliminate most if not all of this replicated data, particularly the thumbnails, without an excessive performance hit. At least now I know why my Mezzmo application runs so much slower than it used to. I know for a fact that MEZZMO.DB was about 65Mb with v2.x.

    Please add my voice to the chorus to clean this up. Thanks.

  8. #8
    Join Date
    Nov 2007
    Location
    Melbourne, Australia
    Posts
    11,616

    Default

    Thanks for the feedback.

    Mezzmo Android: Install it on your tablet, smartphone, Android TV or Amazon Fire to browse and stream files from your Mezzmo library to all your devices. Full details at http://www.conceiva.com/products/mez...mo_android.asp
    Mezzmo for Kodi Add-on: Install it into Kodi to stream files from your Mezzmo library directly in Kodi. Full details at http://www.mezzmo.com/wiki/doku.php?...odi_user_guide
    Mezzmo for Roku App: Install it onto your Roku to stream files from your Mezzmo library. Full details at http://www.mezzmo.com/wiki/doku.php?...oku_user_guide
    Wiki: User Guides & Reference Manual at http://www.mezzmo.com/wiki
    Facebook: http://www.facebook.com/Mezzmo.DLNA.Server
    Twitter: https://twitter.com/conceiva_mezzmo
    Web: http://www.mezzmo.com

  9. #9
    Join Date
    Sep 2013
    Posts
    25

    Default

    My DB is 7GB - and database maintenance in the new verions didn't help shrinking that. Would adrian please publish the script or you guys please fix this? The DB growed more than 5GB in the last 2 versions...

  10. #10
    Join Date
    Nov 2007
    Location
    Melbourne, Australia
    Posts
    11,616

    Default

    We're looking into this for the next major version of Mezzmo.

    Mezzmo Android: Install it on your tablet, smartphone, Android TV or Amazon Fire to browse and stream files from your Mezzmo library to all your devices. Full details at http://www.conceiva.com/products/mez...mo_android.asp
    Mezzmo for Kodi Add-on: Install it into Kodi to stream files from your Mezzmo library directly in Kodi. Full details at http://www.mezzmo.com/wiki/doku.php?...odi_user_guide
    Mezzmo for Roku App: Install it onto your Roku to stream files from your Mezzmo library. Full details at http://www.mezzmo.com/wiki/doku.php?...oku_user_guide
    Wiki: User Guides & Reference Manual at http://www.mezzmo.com/wiki
    Facebook: http://www.facebook.com/Mezzmo.DLNA.Server
    Twitter: https://twitter.com/conceiva_mezzmo
    Web: http://www.mezzmo.com

Similar Threads

  1. Changing Subtitle Color and Size
    By guhorns in forum Mezzmo Questions and Support
    Replies: 5
    Last Post: 07-13-2013, 01:35 PM
  2. TV Icon Size
    By sysv in forum Mezzmo Questions and Support
    Replies: 5
    Last Post: 01-02-2012, 04:19 AM
  3. Render to exact screen size
    By FlyingVguitarist in forum Mezzmo Questions and Support
    Replies: 2
    Last Post: 09-29-2011, 12:43 AM
  4. Video Thumbnail Size on Samsung TV
    By Gus in forum Mezzmo Questions and Support
    Replies: 10
    Last Post: 06-29-2011, 08:53 AM
  5. Size Limitation
    By MPG in forum Mezzmo Questions and Support
    Replies: 2
    Last Post: 11-17-2010, 03:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •