-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Junaid,
Good to hear from you - we've just been working on incorporating your AD work this week. Thanks for contributing it - sorry it's taken so long to incorporate it. I'd recommend against cleaning out the user entries because they are tied to so many other table entries. If you have concerns of having old user data in there that could potentially be exposed in the event of a security breach, I'd recommend to anonymize the unityid, firstname, lastname, preferredname, and email fields for the old accounts. To help with the space usage, cleaning up the continuations and querylog tables will be the most helpful. I'd actually recommend having a maintenance window once or twice a year to clean those tables. You can safely delete any entries from the continuations table with expiretime < NOW(). The querylog table is never read from - it is only written to to allow for auditing in the event of a problem or security incident. All queries by the web frontend that are INSERT, UPDATE, or DELETE are logged to the table. You can delete as many entries from querylog as you'd like based on the timestamp. If you know you'd never look at data in the querylog table, you can disable it by setting QUERYLOGGING to 0 in conf.php (that may have been added in 2.4.2). That said, because the tables are in the innodb format, deleting entries will not decrease the amount of space consumed on disk. It will free up space for future database entries that will be added without increasing the disk usage further. It's kind of like a thin provisioned VM disk file. The only way to actually reclaim the space is to backup the database by dumping it, deleting/recreating the database, and then doing a restore. You can also reconfigure your database to use individual files per innodb table and then run an optimize query on the table (which creates a new table, transfers the data, and deletes the old table). I hope that helps! Josh On Wednesday, October 19, 2016 3:22:44 PM Junaid Ali wrote: > Hello, > We are currently using vcl version 2.3.2 in our environment. We use Active > Directory for LDAP Authentication and user accounts get added to specific > groups in VCL based on user access rights. Since its deployment, the VCL > MySQL database has not been purged of historical data. Curerntly the > querylog table is using 1.5 Gb and continuations table is using 750 Mb > storage. We are interested in cleaning the user accounts that exist in the > database and are not active (during the current academic year). Is there a > recommended procedure for purging user accounts from the VCL database? I > understand there is user data referenced in other VCL tables (e.g. log) > that needs to be deleted before the actual user account can be purged. > > Thanks > > Junaid Ali > Systems & Virtualization Engineer, > Office of Technology Services/IIT, > Chicago, IL - 60616 - -- - ------------------------------- Josh Thompson VCL Developer North Carolina State University my GPG/PGP key can be found at pgp.mit.edu All electronic mail messages in connection with State business which are sent to or received by this account are subject to the NC Public Records Law and may be disclosed to third parties. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iEYEARECAAYFAlgIxEQACgkQV/LQcNdtPQO6IQCdHsj3kLw769IFH7c6zS/cHaI0 t/8An13UtK+iT1wHCIV0NdW06Oss3Uau =Yj8G -----END PGP SIGNATURE-----
