9193320a5f59b6e91135495311e52a3b6eb0bfa6 hiram Fri Apr 8 09:11:23 2011 -0700 adding delLostTable option to avoid any show table status diff --git src/hg/dbTrash/dbTrash.c src/hg/dbTrash/dbTrash.c index 3be26a4..feebac5 100644 --- src/hg/dbTrash/dbTrash.c +++ src/hg/dbTrash/dbTrash.c @@ -20,59 +20,64 @@ "options:\n" " -age=N - number of hours old to qualify for drop. N can be a float.\n" " -drop - actually drop the tables, default is merely to display tables.\n" " -db=<DB> - Specify a database to work with, default is " CUSTOM_TRASH ".\n" " -historyToo - also consider the table called 'history' for deletion.\n" " - default is to leave 'history' alone no matter how old.\n" " - this applies to the table 'metaInfo' also.\n" " -extFile - check extFile for lines that reference files\n" " - no longer in trash\n" " -extDel - delete lines in extFile that fail file check\n" " - otherwise just verbose(2) lines that would be deleted\n" " -topDir - directory name to prepend to file names in extFile\n" " - default is /usr/local/apache/trash\n" " - file names in extFile are typically: \"../trash/ct/...\"\n" - " -tableStatus - use tableStatus to get size data, very inefficient\n" + " -tableStatus - use 'show table status' to get size data, very inefficient\n" + " -delLostTable - delete tables that exist but are missing from metaInfo\n" + " - this operation can be even slower than -tableStatus\n" + " - if there are many tables to check.\n" " -verbose=N - 2 == show arguments, dates, and dropped tables,\n" " - 3 == show date information for all tables." ); } static struct optionSpec options[] = { {"age", OPTION_FLOAT}, {"extFile", OPTION_BOOLEAN}, {"extDel", OPTION_BOOLEAN}, {"drop", OPTION_BOOLEAN}, {"db", OPTION_STRING}, {"topDir", OPTION_STRING}, {"tableStatus", OPTION_BOOLEAN}, + {"delLostTables", OPTION_BOOLEAN}, {"historyToo", OPTION_BOOLEAN}, {NULL, 0}, }; static double ageHours = 0.0; /* must be specified */ static boolean drop = FALSE; /* optional */ static char *db = CUSTOM_TRASH; /* optional */ static boolean historyToo = FALSE; /* optional */ static time_t timeNow = 0; static time_t dropTime = 0; static boolean extFileCheck = FALSE; static boolean extDel = FALSE; static boolean tableStatus = FALSE; +static boolean delLostTable = FALSE; static char *topDir = "/usr/local/apache/trash"; void checkExtFile(struct sqlConnection *conn) /* check extFile table for files that have been removed */ { char query[256]; struct sqlResult *sr; char **row; char buffer[4 * 1024]; char *name = buffer; struct slName *list = NULL; if (! sqlTableExists(conn, CT_EXTFILE)) { verbose(2,"WARNING: -extFile option specified, extFile table does not exist\n"); @@ -152,30 +157,31 @@ /* dbTrash - drop tables from a database older than specified N hours. */ { char query[256]; struct sqlResult *sr; char **row; int updateTimeIx; int createTimeIx; int dataLengthIx; int indexLengthIx; int nameIx; int timeIxUsed; unsigned long long totalSize = 0; // expiredTableNames: table exists and is in metaInfo and subject to age limits struct slName *expiredTableNames = NULL; struct slName *lostTables = NULL; // tables existing but not in metaInfo +unsigned long long lostTableCount = 0; struct hash *expiredHash = newHash(10); // as determined by metaInfo struct hash *notExpiredHash = newHash(10); struct sqlConnection *conn = sqlConnect(db); if (extFileCheck) checkExtFile(conn); time_t ageSeconds = (time_t)(ageHours * 3600); /* age in seconds */ safef(query,sizeof(query),"select name,UNIX_TIMESTAMP(lastUse) from %s WHERE " "lastUse < DATE_SUB(NOW(), INTERVAL %ld SECOND);", CT_META_INFO,ageSeconds); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) hashAddInt(expiredHash, row[0], sqlSigned(row[1])); sqlFreeResult(&sr); safef(query,sizeof(query),"select name,UNIX_TIMESTAMP(lastUse) from %s WHERE " @@ -263,32 +269,38 @@ { time_t lastUse = (time_t)hashIntVal(notExpiredHash,row[0]); struct tm *lastUseTm = localtime(&lastUse); verbose(3,"%4d-%02d-%02d %02d:%02d:%02d %ld OK %s\n", lastUseTm->tm_year+1900, lastUseTm->tm_mon+1, lastUseTm->tm_mday, lastUseTm->tm_hour, lastUseTm->tm_min, lastUseTm->tm_sec, (unsigned long)lastUse,row[0]); } else { struct slName *el = slNameNew(row[0]); slAddHead(&lostTables, el); } } sqlFreeResult(&sr); - if (lostTables) // tables exist, but not in metaInfo, check their age - { // this happens rarely, should not be many of these, if ever + lostTableCount = slCount(lostTables); + // If tables exist, but not in metaInfo, check their age to expire them. + // It turns out even this show table status is slow too, so, only + // run thru it if asked to eliminate lost tables. It is better to + // do this operation with the stand-alone perl script on the customTrash + // database machine. + if (delLostTable && lostTables) + { struct slName *el; for (el = lostTables; el != NULL; el = el->next) { if (sameWord(el->name,"history")) continue; if (sameWord(el->name,CT_META_INFO)) continue; if (sameWord(el->name,CT_EXTFILE)) continue; boolean oneTableOnly = FALSE; // protect against multiple tables /* get table time information to see if it is expired */ safef(query,sizeof(query),"show table status like '%s'", el->name); STATUS_INIT; while ((row = sqlNextRow(sr)) != NULL) @@ -343,62 +355,68 @@ if (sameWord(db,CUSTOM_TRASH)) { sqlDisconnect(&conn); conn = sqlConnect(db); } for (el = expiredTableNames; el != NULL; el = el->next) { verbose(2,"# drop %s\n", el->name); sqlDropTable(conn, el->name); ctTouchLastUse(conn, el->name, FALSE); /* removes metaInfo row */ ++droppedCount; } /* add a comment to the history table and finish up connection */ if (tableStatus) safef(comment, sizeof(comment), "Dropped %d tables with " - "total size %llu", droppedCount, totalSize); + "total size %llu, %llu lost tables", + droppedCount, totalSize, lostTableCount); else safef(comment, sizeof(comment), - "Dropped %d tables, no size info", droppedCount); + "Dropped %d tables, no size info, %llu lost tables", + droppedCount, lostTableCount); verbose(2,"# %s\n", comment); hgHistoryComment(conn, "%s", comment); } else { safef(comment, sizeof(comment), - "Dropped no tables, none expired"); + "Dropped no tables, none expired, %llu lost tables", + lostTableCount); verbose(2,"# %s\n", comment); } } else { char comment[256]; if (expiredTableNames) { int droppedCount = slCount(expiredTableNames); if (tableStatus) safef(comment, sizeof(comment), "Would have dropped %d tables with " - "total size %llu", droppedCount, totalSize); + "total size %llu, %llu lost tables", + droppedCount, totalSize, lostTableCount); else safef(comment, sizeof(comment), - "Would have dropped %d tables, no size info", droppedCount); + "Would have dropped %d tables, no size info, %llu lost tables", + droppedCount, lostTableCount); verbose(2,"# %s\n", comment); } else { safef(comment, sizeof(comment), - "Would have dropped no tables, none expired"); + "Would have dropped no tables, none expired, %llu lost tables", + lostTableCount); verbose(2,"# %s\n", comment); } } sqlDisconnect(&conn); } int main(int argc, char *argv[]) /* Process command line. */ { struct tm *tm; if (argc < 2) usage(); optionInit(&argc, argv, options);