[rt-users] clean up user table
todd at chaka.net
Mon Nov 26 17:31:58 EST 2007
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
> SELECT DISTINCT
> 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?
> 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
More information about the rt-users