[rt-users] Tables, database size, backups

[Alex Howells]

Hi All,

First post to the list, although I've been an avid reader for months;
we've a reasonably sized RT3 installation at work, probably doing around
15,000 tickets per annum at the moment, with 5-6 staff working with it.
It's just being moved onto new hardware, which brings me to:

   *  Our 'sessions' table is rather huge, at 1.5GB
      and most of the timestamps contained within are
      literally 1-3 years old.  Do I need to keep this
      data for any reason, or can it be purged to save
      space on the DB host + time when backing up?

   *  Ditto for our attachments table, although I'm able
      to see more of a reason here why the data is needed!
      Anyone have any hints / tips on this?

   *  I've been doing backups with `mysqldump` or similar
      for a while now, and came across mk-parallel-dump [1]
      plus mylvmbackup[2]; both look nice, does anyone have
      "real world" experience using either with RT?
      Obvious downside to mysqldump is it's rather slow,
      and you may as well go for a cuppa when it runs.

   *  We had a 'problem' where a lot of connections
      to MySQL would remain open and idle for ~6 hours
      despite nobody using the application.  Reducing
      wait_timeout seemed to make this go away, but
      *seems* to have the unexpected side-effect of RT
      getting its panties in a bunch and not sending out
      correspondance + comments via e-mail properly. Is
      there a "safe" limit for wait_timeout with RT?
      Furthermore, is there anything I should be looking
      out for in particular when my install stops mailing?
      It seems to stop silently too, which is frustrating.

   *  We have a dedicated box for 'web applications' and
      another for running the databases.  I've made some
      attempts to tune MySQL but my experience there mostly
      lies with MyISAM tables; our RT3 install seems to be
      exclusively InnoDB. Anyone got some home remedies?

         innodb_additional_mem_pool_size     = 64M
         innodb_buffer_pool_size             = 1G

         innodb_flush_method                 = O_DIRECT

         innodb_log_buffer_size              = 8M

         innodb_thread_concurrency           = 8

      webapps is a Opteron 1218HE with 8GB RAM, plus
      2 x 320GB 7200rpm SATA HDDs on a 3ware RAID controller;
      database server is a Opteron 170HE with 4GB RAM, and
      2 x 150GB 10000rpm SATA HDDs on a 3ware controller.
      Happy to post other bits of my config, if that'd help :-)

Sorry for being a bit vague with all this *grin*  If my InnoDB settings
make anyone lose their lunch, can I profusely apologise in advance? ;)

Alex

[1] http://maatkit.sourceforge.net/
[2] http://lenz.homelinux.org/mylvmbackup/