Jesse Vincent wrote:
Hi All,
I was recently tasked with upgrading RT from 3.0.11 to 3.4.5 for my
company, basicaly we use RT to handle all incoming requests from
customers as well as internal requests between branches, so the database
is very large 650k + tickets from a large number of individual users -
pretty much anyone that emails the company on the specified support
address.We have a front end box with SearchBuilder 1.38, Apache 2.0.54, mod_perl
2,RT 3.4.5 (p4 2.8 ghz 1.5 gig ram) and a backend box, running the mysql
RT database Fedora Core 4, Mysql 4.1.16 (p4 3 ghz 4 gig ram 10k rpm WD
raptor drive. The upgrade process was all very smooth ( I dumped the old
DB - transferred it to the new server, configured the appropriate my.cnf
settings, imported the database and then ran the schema upgrades) and in
initial testing everything seemed fine, however when all the support
staff started logging on this particular query grinds the server to a
halt by maxing out the CPU on the rt-sql server.Can you tell us how you’ve tuned and optimized your mysql instance?
Ok, basically the only extra index that we have that seems to speed
things along is this one “create index mbowe_MemberId on
CachedGroupMembers(MemberId)”
An analyze was run on all tables.
Interestingly the query that I posted previously seems to be attached to
the “25 Highest priority tickets that I have requested”. By disabling
this element from the At a Glance page performance is now quite snappy
with only a small delay when loading the At a Glance page.
Below is the mysqld section of the my.cnf that was used on the new
server before the database import
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 128M
Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 4
Default to using old password format for compatibility with mysql 3.x
clients (those using the mysqlclient10 compatibility package).
old_passwords=1
Don’t listen on a TCP/IP port at all. This can be a security enhancement,
if all processes that need to connect to mysqld run on the same host.
All interaction with mysqld must be made via Unix sockets or named pipes.
Note that using this option without enabling named pipes on Windows
(via the “enable-named-pipe” option) will render mysqld useless!
#skip-networking
Replication Master Server (default)
binary logging is required for replication
#log-bin
required unique id between 1 and 2^32 - 1
defaults to 1 if master-host is not set
but will not function as a master if omitted
server-id = 1
Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/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=1G
innodb_additional_mem_pool_size=20M
Set …_log_file_size to 25 % of buffer pool size
innodb_log_file_size=250M
innodb_log_buffer_size=12M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
Uncomment the next lines if you want to use them
#set-variable = innodb_thread_concurrency=5
Any suggestions are most welcome =]