Optimize system specifically for RT

Paul_Hirose1 · January 30, 2009, 1:02am

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)

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

Rob_Munsch · January 30, 2009, 9:25pm

While by no means an expert, i noticed some stuff:

In more detail, Gateway E4100 desktop with Pentium4/2.8GHz w/Hyperthreading enabled, 4x256MB

-------- Performance Metrics -------------------------------------------------
[!!] Maximum possible memory usage: 1.2G (120% of installed RAM)

You have a gig of ram on the box, and the !! here is telling you your
current vars allow MySQL to grab 1.2 GB. Unless / until you increase
the box’s ram, you need to lower the various buffers significantly, as
there’s still system/OS mem to consider.

Reduce your overall MySQL memory footprint for system stability

That’s what it’s sayin’ here.

When making adjustments, make tmp_table_size/max_heap_table_size equal

This just means what it says: if you tweak one to X, make sure the
other is also exactly X.

*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***

Again talking about your lack of RAM, and saying “Don’t do what we
recommend below until you add some!”

tmp_table_size (> 32M)
max_heap_table_size (> 16M)

In this section, it means “adjust these variables HIGHER,” greater
than their current values, which are 32 and 16M respectively.
However, as per previous 2 warnings: 1) you can’t adjust anything
higher right now, until you lower the other buffer values, since
you’re asking for more total RAM than you physically have; and 2) when
you adjust these, make sure they’re the same size. So, assuming you
had more RAM, you’d want to kick tmp_table_size up above 32M; and when
you do, make sure max_heap_table_size is adjusted to the same number.

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 |

The recommendations show you this: they say “make it greater than .”

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.)

if you have 4 slots, and they have quarter-gig sticks in them, why
wouldn’t you be able to chuck them and get new sticks? Even if you
just replaced them with half-gig sticks - dirt cheap - you’d double
your RAM.

better yet, get gig sticks.

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.

It’s saying to make them both greater than 32M. The recommendation
for the larger is “make it greater than the current value of 32M,” and
“keep them both the same,” with the caveat “but not until you do
something about your total RAM usage.”

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.

key_buffer = 256M
myisam_sort_buffer_size = 64M
query_cache_size= 16M

This is your only current option to reduce usage right now, and i
don’t know how much room you have not knowing your DB’s habits. Try
gently lowering these if more physical RAM is impossible for whatever
reason, and restarting MySQL.

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

Same 'ere.

I’m not sure if perceived “slowness” is due to webserver or MySQL or something else. And partly, it’s just

It’s possible that MySQL is getting into fistfights with your system
for RAM right now, due to all of the above.

Hope this all helps in some way.

/chown -R us:us /yourbase