[rt-users] clean up user table

[Todd Chapman]

Are you having preformance problems related to the users? If not why
clean it up? Do you like to play with fire?

On 11/26/07, Gordon Messmer <gmessmer at u.washington.edu> wrote:
> A while back I inherited an RT3 installation that used the mail gateway
> without any filtering.  For several years, the users manually "deleted"
> tickets from their queues every day.  Naturally, this let to some very
> bloated tables in the SQL database.  I was able to use RTx-Shredder to
> remove all of the "deleted" tickets, which pruned the tickets,
> attachments, and transactions tables.  However, even with the newest
> RTx-shredder and RT3, the "users" module won't help me delete users who
> aren't associated with tickets.
>
> Under ideal conditions, RT3 performance is still acceptable, but I'm
> tired of trying to match up the magical versions.  3.6.5 works with
> MySQL 5.0, but whichever 3.6.x we used previously was awfully slow.
> With MySQL 4.0, the older RT3 was quick.  I'm currently running on
> PostgreSQL 8.1, which is not quite as quick as MySQL 5.0 was, but
> doesn't seem to need the "right" release of RT3 to work correctly.  *BLAH*
>
> To restore sanity permanently, I need to clean out the
> CachedGroupMembers (254475 rows), GroupMembers (87456 rows), Groups
> (95138 rows), Principals (110714 rows), and Users (15574 rows) tables.
> (ACLs, too?)
>
> I presume that people need to do this, and its been done before, but I
> can't find instructions on how to do so.  Is there something that I need
> to fix in order to use RTx-Shredder?  Is there a set of SQL commands
> that will delete users who aren't associated with tickets?  The farthest
> I've yet come is this query, which gives me the list of users who don't
> own tickets.  However, it doesn't include users who opened existing
> tickets, which it needs to, or other valid relations which should be
> preserved:
>
> SELECT DISTINCT
>   users.id,
>   users.name,
>   users.password,
>   users.comments,
>   users.emailaddress,
>   users.realname
> FROM users
> INNER JOIN principals ON users.id = principals.id
> LEFT JOIN tickets ON principals.id = tickets.Owner
> WHERE Tickets.id IS NULL;
>
> Any suggestions?
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:
>
> If you sign up for a new RT support contract before December 31, we'll take
> up to 20 percent off the price. This sale won't last long, so get in touch today.
>     Email us at sales at bestpractical.com or call us at +1 617 812 0745.
>
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>