[rt-users] Optimize system specifically for RT

[Paul Hirose]

In short, tips/hints on creating and maintaining a system optimized solely for RT?

In more detail, Gateway E4100 desktop with Pentium4/2.8GHz w/Hyperthreading enabled, 4x256MB DDR-266, 80GB drive.  OpenBSD 4.4-Stable, with BSD.MP Generic kernel.  RT-3.8.2, MySQL-5.0.67, Apache-2.2.11, Mod_Perl-2.0.4.  Most "fixdeps" are all current, except for whatever may have already been a part of the base OpenBSD 4.4-Stable install.  I don't know how to get the specific version of all the various Perl modules, if anybody can tell me, I'll be happy to get that too.  Also Mod_auth_cas-1.0.8, and OpenSSL-0.98i (things I had to also build to get my setup working.)  Normal built-in sendmail, with procmail 3.22.  I couldn't get MySQL-5.1.30 to pass the "make test" and I could not get OpenSSL-0.98j to create the "fips" stuff.

I figured HT-enabled would be good, since I'm told most database queries can be threaded, and it's mostly integer-based so there's no competing for the sole FP unit on the CPU itself.  I disabled HT, rebooted with just BSD and I didn't notice any significant changes, so I'm chancing keeping it enabled with BSD.MP.

I've not specifically tuned mod_perl (nor anything else yet.)

./mysqltuner.pl --nocolor --forcemem 1000 -forceswap 1500 --noinfo --nogood says
-------- General Statistics --------------------------------------------------
-------- Storage Engine Statistics -------------------------------------------
-Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[!!] Total fragmented tables: 1
-------- Performance Metrics -------------------------------------------------
[!!] Maximum possible memory usage: 1.2G (120% of installed RAM)
[!!] Key buffer hit rate: 92.9% (14K cached / 1K reads)
[!!] Temporary tables created on disk: 29% (1K on disk / 6K total)
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)

Checking MySQL for
	mysql> show variables like 'tmp_table_size';
	| tmp_table_size | 33554432 |
	mysql> show variables like 'max_heap_table_size';
	| max_heap_table_size | 16777216 |

I can't add RAM the box is full.  I figure 4x256MB of slow RAM is better than 2x256MB of DDR400 (1GB slow is better than 512MB of faster RAM.)

I'm not sure if it's telling me to set tmp_table_size and max_heap_table_size both to less than 16M.  Since it says they should be the same, and the heap is being flagged as being >16M, I was thinking of setting them both to 12M.

The /etc/my.cnf is mostly, along w/stuff about password and whatnot that I don't think matter for this particular thread.  Mostly pulled from "my-large.cnf" example, disabling federated and bdb, and enabling innodb by uncommenting the lines.

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2

innodb_data_home_dir = /opt/mysql/var/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/var/
innodb_log_arch_dir = /opt/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash


I've not done the OPTIMIZE yet.  Is that cron-able for once per month/quarter/year, and does anyone have suggestion on frequency?

The RT setup is new and the database is empty.  I'm perfectly happy clearing it out, starting it over, enabling/disabling/configuring things specifically for RT's sole use.  MySQL talks via Unix sockets rather than TCP.

We expect less than a dozen support staff to use it daily, although the number of incoming email request ticket creation ebbs and flows.  Only the support staff have access to the web interface, requestors mostly prefer all email around here for correspondence.  Incoming email is passed from sendmail/spamassasin, then procmail (which drops everything SpamAssassin score > 12), and then passes it to rt-mailgate.

I'm not sure if perceived "slowness" is due to webserver or MySQL or something else.  And partly, it's just me trying to start with a clean, fast, optimized system, since I have the chance to do so.  Not necessarily, a "it IS slow, and thus I must fix something."  And yah, the hardware is the hardware (especially w/only 1GB RAM) but alas, nothing I can do on that part (sigh.)  I'm scrabbling through my "junk box" for bigger sticks of memory :)

So any tips, hints, suggestions, comments, criticisms, etc - all welcome.  It's a brand-new install, so I figure now's the time to make any changes (or start over!) as needed :)

Thank you,
PH