Re: vcl database cleanup

[Junaid Ali]

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 
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-
>
>

vcl database cleanup

[Junaid Ali]

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