Skip to Content.
Sympa Menu

grouper-users - Re: [grouper-users] Maintaining Grouper database size

Subject: Grouper Users - Open Discussion List

List archive

Re: [grouper-users] Maintaining Grouper database size


Chronological Thread 
  • From: Shilen Patel <>
  • To: Rory Larson <>
  • Cc: "" <>
  • Subject: Re: [grouper-users] Maintaining Grouper database size
  • Date: Mon, 29 Jan 2018 18:34:04 +0000
  • Accept-language: en-US
  • Authentication-results: oit.duke.edu; spf=none
  • Ironport-phdr: 9a23:lBf3vhHCgJ4KLYdu3uw6t51GYnF86YWxBRYc798ds5kLTJ7zo86wAkXT6L1XgUPTWs2DsrQY07OQ6/iocFdDyK7JiGoFfp1IWk1NouQttCtkPvS4D1bmJuXhdS0wEZcKflZk+3amLRodQ56mNBXdrXKo8DEdBAj0OxZrKeTpAI7SiNm82/yv95HJbAhEmCexbaluIBmqsA7cqtQYjYx+J6gr1xDHuGFIe+NYxWNpIVKcgRPx7dqu8ZBg7ipdpesv+9ZPXqvmcas4S6dYDCk9PGAu+MLrrxjDQhCR6XYaT24bjwBHAwnB7BH9Q5fxri73vfdz1SWGIcH7S60/VC+85Kl3VhDnlCYHNyY48G7JjMxwkLlbqw+lqxBm3oLYfJ2ZOP94c6jAf90VWHBBU95RWSJfH428c4UBAekPMuZZs4bzqFQBoACiBQa3AePj1iNEi2X00KA8zu8vERvG3AslH98WrXrUrNT1NKMMXuCz0aLG1y/Mb+9I1jf59YPFdQshofGNXbJ0b8XRz1IiGB7YjlWTt4PpJS2a2fkQs2WC6edrSOyhi2kiqw5rozivwN8hiojPhoIJ1F/E7yN5z5gpJdGmUkJ0f8OrEIZWuiqHNIV2WtsvT3xotSok0LELt4S3cDUJxZkj3RLTdfKKf5WQ7h7+VOudPy10iXx/dL+xiBu+60atxvXiWsWp0VtGtCRInsfWunwQ1BHf99KLRuZ/80qnxD2BzRrc6vteLkAxjafbK4Auwro3lpcLtETMBC72mEHvgK+ZeEQo4/Sk5uT9brXhp5+cK5V4hRzjMqQthMOzG/o3PRUTX2SD+OS80qPs/VHhTblXjvA6jLPVvZ7AKcgGpaO0BxVZ3pgt5hqiFzum1c4XnXgDLFJLYhKHiI3pNknVIPD4DPe/h0qjkDFwyv/bI73hAYvCImLdn7j8YLZx81RcxxYrzdBD+5JUDakMIP3pWk/2qdzYFgE2Mxatz+b6E9VyyJkeWXmUD6+dMaPSqkOI5vkxL+WWZY8Vvir9JOY/5/7ok3A5hUERcbO30pQKdXDrVshhdn+ee2ukpt4bCmAM9l4kSOXwk1qECBZOYXL0Uq4hsGIVEoWjWLzDR4SghvS51Tu2GtUCfWBHDl2KOXv1dIjCVvsRPnHBavR9myAJAODyA7Qq0guj4Uqjk+Jq
  • Spamdiagnosticmetadata: NSPM
  • Spamdiagnosticoutput: 1:99

If you’re going to trim the audit based on time, I’d suggest using the gsh
command since it’ll delete the records from the tables in the right order
(taking into account foreign keys). But as long as you delete them in the
right order via sql directly, that should be fine as well. Also, recently, I
documented how much space was being taken up at Duke (we use Oracle).
https://spaces.internet2.edu/display/Grouper/Duke+Disk+Space+Usage I was
surprised to see so much of the space being used by indexes. We could
possibly look at reducing the number of indexes to help.

- Shilen

On 1/29/18, 9:13 AM,
"
on behalf of Rory Larson"
<
on behalf of
>
wrote:

Hello,

We are encountering an issue of our Grouper database growing to a size
that threatens to use up the entire hard drive space available. The database
server is a physical machine with no room to expand, running a MySQL-type
database (5.5.56-MariaDB MariaDB Server).

The problem is not so much the size of the group data itself, but with
point-in-time tables that record what that data was back to the beginning of
the Grouper installation. Some of these have grown to huge sizes, on the
order of 20 GB or so. A suggestion has been made that we could delete a lot
of this prior to some reasonable point in time, especially groups or
memberships that no longer exist. This would be fine, but I'm wondering if
this can be done from the database command line on a per-table basis, or
whether there are dependencies that require doing this through gsh function
calls?

Also, because this is a MySQL-type database, shrinking the database
physical size after deletion of unnecessary data is not trivial. OPTIMIZE
TABLE ... might work, or it might make a table bigger. ALTER TABLE ... ROW
FORMAT=COMPRESSED is promising as tried on our test database, but takes about
four times as long as OPTIMIZE TABLE. In either case, updating has to be
shut down while running them, and they may require duplicating the table
during the operation. We are at 90% now, and can't afford any more big
tables. The ultimate MySQL solution seems to be to dump the database, delete
the whole thing, and reload it, which would mean complete downtime for
Grouper.

Does anyone else using a MySQL-type database have a system for handling
this problem and maintaining a reasonable database size?

Thanks,
Rory







Archive powered by MHonArc 2.6.19.

Top of Page