Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

Thread: Database file size

  1. #11
    Join Date
    Jun 2012
    Posts
    365

    Default

    This thread caught my interest because after upgrading from v2.7.1 to 3.4.x my database grew from around 150meg to over 900meg. Also I noticed that whenever I made any changes to the database I would see a status message saying that 5 active playlists were being updated and it would often last for 20-30 minutes (This is on an AMD 8 core prosessor with 32GB of memory). On a whim this morning I deleted all of my active video playlists and then compacted my database. It shrunk down to 250 meg and is much quicker. I left the audio active playlists in the database. I never really used any of the video active playlists because I have everything I want in Smart playlists. All the active playlists were in the database were the ones which came with Mezzmo such as genre, by year etc. Anyway now things are much faster and no more thrashing with the active playlist update messages for 20-30 minutes. My database has around 3,000 videos and 3,300 audio files.
    Last edited by jbinkley60; 11-23-2013 at 07:36 PM.

  2. #12

    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.

  3. #13
    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

  4. #14

    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

  5. #15
    Join Date
    Sep 2013
    Posts
    25

    Default

    With the newest version my DB was compacted by almost 2GB using the builtin mezzmo database compact, did you try this?

  6. #16

    Default

    Quote Originally Posted by JMC View Post
    With the newest version my DB was compacted by almost 2GB using the builtin mezzmo database compact, did you try this?
    Yes, but i can't.. when i do this, mezzmo need more as the double space for compacting. but my drive have no diskspace for this (after 1-5 minutes after starting compacting drive c: is out of space and the pc is slowly and will near stay.. and so say the mezzmo popup "compact size the same before and after the compacting"
    Last edited by JiKo; 12-17-2013 at 10:51 PM.

  7. #17
    Join Date
    Sep 2013
    Posts
    25

    Default

    And there is a way to move the mezzmo database to a different drive (at least for compacting this would be a way and with NTFS drives): Using NTFS Junctions/Hardlinks and linking the Folder to a different folder on a different drive and keeping the database there.

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

    Default

    Quote Originally Posted by JiKo View Post
    Why i can't move the database to a other drive?
    You can move your Mezzmo database to another drive/folder. See this FAQ - http://forum.conceiva.com/showthread...t-drive-folder

    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. #19
    Join Date
    Nov 2007
    Location
    Melbourne, Australia
    Posts
    11,616

    Default

    Quote Originally Posted by JMC View Post
    And there is a way to move the mezzmo database to a different drive (at least for compacting this would be a way and with NTFS drives): Using NTFS Junctions/Hardlinks and linking the Folder to a different folder on a different drive and keeping the database there.
    Rather than using junctions or symlinks, I'd recommend following our FAQ to move to your Mezzmo database - see http://forum.conceiva.com/showthread...t-drive-folder.

    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

  10. #20
    Join Date
    Sep 2013
    Posts
    25

    Default

    You're right Paul - I didn't know about the way with the ini (since I didn't move my DB) and thought if there is no other way there is this way

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
  •