Specify fixed age of queries until they get deleted and remove VACUUM…

… instructions.
BuloZB · Jun 19, 2017 · 685f4f4 · 685f4f4
1 parent 52e0845
commit 685f4f4
Show file tree

Hide file tree

Showing 5 changed files with 51 additions and 81 deletions.
diff --git a/FTL.h b/FTL.h
@@ -119,7 +119,7 @@ typedef struct {
 	bool rolling_24h;
 	bool query_display;
 	bool analyze_AAAA;
-	int maxDBfilesize;
+	int maxDBdays;
 } ConfigStruct;
 
 // Dynamic structs

diff --git a/README.md b/README.md
@@ -103,7 +103,7 @@ Possible settings (**the option shown first is the default**):
 - `TIMEFRAME=rolling24h|yesterday|today` (Rolling data window, up to 48h (today + yesterday), or up to 24h (only today, as in Pi-hole `v2.x` ))
 - `QUERY_DISPLAY=yes|no` (Display all queries? Set to `no` to hide query display)
 - `AAAA_QUERY_ANALYSIS=yes|no` (Allow `FTL` to analyze AAAA queries from pihole.log?)
-- `MAXDBFILESIZE=100` (How large do we want the FTL database to grow at max (given in MB), setting this to `0` disables the database altogether)
+- `MAXDBDAYS=365` (How long should queries be stored in the database? Setting this to `0` disables the database altogether)
 
 ### Implemented keywords (starting with `>`, subject to change):
 

diff --git a/config.c b/config.c
@@ -95,21 +95,21 @@ void read_FTLconf(void)
 	else
 		logg("   AAAA_QUERY_ANALYSIS: Hide AAAA queries");
 
-	// MAXDBFILESIZE
-	// defaults to: 100 MB
-	config.maxDBfilesize = 100;
-	buffer = parse_FTLconf(fp, "MAXDBFILESIZE");
+	// MAXDBDAYS
+	// defaults to: 365 days
+	config.maxDBdays = 365;
+	buffer = parse_FTLconf(fp, "MAXDBDAYS");
 	if(buffer != NULL)
 	{
 		int value = 0;
 		if(sscanf(buffer, "%i", &value))
 			if(value >= 0)
-				config.maxDBfilesize = value;
+				config.maxDBdays = value;
 	}
-	if(config.maxDBfilesize == 0)
-		logg("   MAXDBFILESIZE: --- (DB disabled)", config.maxDBfilesize);
+	if(config.maxDBdays == 0)
+		logg("   MAXDBDAYS: --- (DB disabled)", config.maxDBdays);
 	else
-		logg("   MAXDBFILESIZE: %i MB", config.maxDBfilesize);
+		logg("   MAXDBDAYS: max age for stored queries is %i days", config.maxDBdays);
 
 	logg("Finished config file parsing");
 

diff --git a/database.c b/database.c
@@ -347,52 +347,33 @@ void *DB_GC_thread(void *val)
 	// Need no lock on FTL's data structure, so this can work
 	// in parallel w/o affecting FTL's core responsibilities
 
-	// Disable any other DB accesses while doing this
-	database = false;
-
 	if(!dbopen())
 	{
 		logg("Failed to open DB in GC thread");
 		database = true;
 		return NULL;
 	}
 
-	float factor = 1.0;
-	while(get_db_filesize() > factor*config.maxDBfilesize)
+	int timestamp = time(NULL) - config.maxDBdays * 86400;
+
+	if(!dbquery("DELETE FROM queries WHERE timestamp <= %i", timestamp))
 	{
-		// If we run the database size reduction, make sure we remove a sufficient number
-		// of queries to go below 90% of the set maximum database file size
-		factor = 0.9;
-		logg("Notice: DB filesize is %.2f MB (%i rows), limit is %i.00 MB", get_db_filesize(), number_of_queries_in_DB(), config.maxDBfilesize);
+		dbclose();
+		logg("DB-GC error: Deleting queries due to age of entries failed!");
+		database = true;
+		return NULL;
+	}
 
-		if(!dbquery("DELETE FROM queries WHERE id in ( SELECT id FROM queries ORDER BY timestamp ASC LIMIT 10000);"))
-		{
-			dbclose();
-			logg("ERROR: Deleting queries due to exceeded filesize of database failed!");
-			database = true;
-			return NULL;
-		}
+	// Get how many rows have been affected (deleted)
+	int affected = sqlite3_changes(db);
 
-		// When a large amount of data is deleted from the database file
-		// it leaves behind empty space, or "free" database pages. This
-		// means the database file might be larger than strictly necessary.
-		// Running VACUUM to rebuild the database reclaims this space and
-		// reduces the size of the database file.
-		// Furthermore, running VACUUM ensures that each table and index is
-		// largely stored contiguously within the database file. In some
-		// cases, VACUUM may also reduce the number of partially filled pages
-		// in the database, reducing the size of the database file further.
-		dbquery("VACUUM");
-	}
-	// Print final message
-	logg("Notice: DB filesize is %.2f MB (%i rows), limit is %i.00 MB", get_db_filesize(), number_of_queries_in_DB(), config.maxDBfilesize);
+	// Print final message only if there is a difference
+	if(debug || affected)
+		logg("Notice: Database size is %.2f MB, deleted %i rows", get_db_filesize(), affected);
 
 	// Close database
 	dbclose();
 
-	// Sleep one second so that we don't immediately re-launch the DB GC thread
-	sleepms(1000);
-
 	// Re-enable database actions
 	database = true;
 	return NULL;

diff --git a/main.c b/main.c
@@ -13,7 +13,6 @@
 
 char * username;
 bool needGC = false;
-bool needDBGC = false;
 
 int main (int argc, char* argv[]) {
 	username = getUserName();
@@ -49,7 +48,7 @@ int main (int argc, char* argv[]) {
 
 	read_gravity_files();
 
-	if(config.maxDBfilesize != 0)
+	if(config.maxDBdays != 0)
 		db_init();
 
 	logg("Starting initial log file parsing");
@@ -84,27 +83,16 @@ int main (int argc, char* argv[]) {
 	{
 		sleepms(100);
 
-		bool runGCthread = false, runDBthread = false, runDBGCthread = false;
+		bool runGCthread = false, runDBthread = false;
 
 		if(((time(NULL) - GCdelay)%GCinterval) == 0)
 			runGCthread = true;
 
-		if(database)
-		{
-			// DBGC_time == 0 at five minutes after each full even hour
-			int DBGC_time = time(NULL) % 7200;
-			if(DBGC_time == 300 || needDBGC)
-			{
-				needDBGC = false;
-				runDBGCthread = true;
-			}
-
-			else if(((time(NULL)%DBinterval) == 0) && !runDBGCthread)
-				runDBthread = true;
-		}
+		if(database && ((time(NULL)%DBinterval) == 0))
+			runDBthread = true;
 
 		// Garbadge collect in regular interval, but don't do it if the threadlocks is set
-		if(config.rolling_24h && (runGCthread || needGC))
+		if(runGCthread || needGC)
 		{
 			// Wait until we are allowed to work on the data
 			while(threadwritelock || threadreadlock)
@@ -113,11 +101,30 @@ int main (int argc, char* argv[]) {
 			needGC = false;
 			runGCthread = false;
 
-			pthread_t GCthread;
-			if(pthread_create( &GCthread, &attr, GC_thread, NULL ) != 0)
+			if(config.rolling_24h)
+			{
+				pthread_t GCthread;
+				if(pthread_create( &GCthread, &attr, GC_thread, NULL ) != 0)
+				{
+					logg("Unable to open GC thread. Exiting...");
+					killed = 1;
+				}
+			}
+
+			if(database)
 			{
-				logg("Unable to open GC thread. Exiting...");
-				killed = 1;
+				// Disable any other DB accesses while doing this and wait one second
+				// so that currently running transactions can still finish
+				database = false;
+				sleepms(1000);
+
+				// Launch DB GC thread
+				pthread_t DBGCthread;
+				if(pthread_create( &DBGCthread, &attr, DB_GC_thread, NULL ) != 0)
+				{
+					logg("Unable to open DB GC thread. Exiting...");
+					killed = 1;
+				}
 			}
 
 			// Avoid immediate re-run of GC thread
@@ -144,24 +151,6 @@ int main (int argc, char* argv[]) {
 			while(((time(NULL)%DBinterval) == 0))
 				sleepms(100);
 		}
-
-		// Clean database in regular interval
-		if(runDBGCthread)
-		{
-			runDBGCthread = false;
-
-			// Avoid any further DB activities outside of the DB_GC thread
-			database = false;
-
-			if(debug)
-				logg("Launching DB GC...");
-
-			pthread_t DBGCthread;
-			if(pthread_create( &DBGCthread, &attr, DB_GC_thread, NULL ) != 0)
-			{
-				logg("WARN: Unable to open DB GC thread.");
-			}
-		}
 	}