Thanks for the info Josh. Let me know if you need any help refactoring. Thanks. Junaid.
On Thu, Oct 20, 2016 at 8:18 AM, Josh Thompson <[email protected]> wrote: > -----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----- > >
