Originally Posted by
JohnM
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();
?>
Bookmarks