Performance degradation with rt-3.4.1

RT Users
1

For the last three days, I am having serious performance problems with
my RT installation.
The first thing that I noticed had something to do with table locking.
I have a script that makes a backup (mysqldump --opt --force) of my
RT database every night. It is what gave me the 1st sign of a problem
when it reported the following error:

/usr/local/bin/mysqldump: Got error: 1015: Can’t lock file
(errno: 149) when using LOCK TABLES

Looking at my top output, this is what mysqld is doing on my server:

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
86774 mysql 20 0 673M 542M kserel 0 19.8H 103.37% 103.37% mysqld

Now I decided to use mytop as well just to see what queries are being
run on the database and here is the output:

MySQL on localhost (4.0.23a)

Queries: 778.1k qps: 18 Slow: 29.3k Se/In/Up/De(%): 73/18/06/00
qps now: 10 Slow qps: 0.0 Threads: 7 ( 4/ 6) 63/24/08/00
Cache Hits: 105.0k Hits/s: 2.4 Hits now: 0.2 Ratio: 18.4% Ratio now: 3.1%
Key Efficiency: 97.9% Bps in/out: 1.5k/13.1k Now in/out: 780.6/ 2.6k

  Id   User   Host/IP   DB   Time    Cmd Query or State
  --   ----   -------   --   ----    --- ----------

23497 root localhost test 0 Query show full processlist
23307 rt3 mail rt3 2 Query SELECT main.* FROM CachedGroupMembers main WHERE ((main.MemberId = 13592
22989 rt3 mail rt3 4 Query SELECT main.* FROM CachedGroupMembers main WHERE ((main.MemberId = 13591
23274 rt3 mail rt3 4 Query SELECT main.* FROM CachedGroupMembers main WHERE ((main.MemberId = 13592

So many of queries like that one are showing.
My DB is on a remote box different than the host that runs the web UI.

I am suspecting something database related and was wondering if
someone has an idea/clue on what I should be checking on as a
first step towards the diagnostics.

    cheers
   - wash

Odhiambo Washington . WANANCHI ONLINE LTD (Nairobi, KE) |
wash at wananchi _ dot _ com . 1ere Etage, Loita Hse, Loita St., |
GSM: (+254) 722 743 223 . # 10286, 00100 NAIROBI |
GSM: (+254) 733 744 121 . (+254) 020 313 985 - 9 |
“Oh My God! They killed init! You Bastards!”
–from a /. post

2

So many of queries like that one are showing.
My DB is on a remote box different than the host that runs the web UI.

I am suspecting something database related and was wondering if
someone has an idea/clue on what I should be checking on as a
first step towards the diagnostics.

Wash,
Are your tables innodb or myisam?

Jesse

3

So many of queries like that one are showing.
My DB is on a remote box different than the host that runs the web UI.

I am suspecting something database related and was wondering if
someone has an idea/clue on what I should be checking on as a
first step towards the diagnostics.

Wash,
Are your tables innodb or myisam?

InnoDB tables.

    cheers
   - wash

Odhiambo Washington . WANANCHI ONLINE LTD (Nairobi, KE) |
wash at wananchi _ dot _ com . 1ere Etage, Loita Hse, Loita St., |
GSM: (+254) 722 743 223 . # 10286, 00100 NAIROBI |
GSM: (+254) 733 744 121 . (+254) 020 313 985 - 9 |
“Oh My God! They killed init! You Bastards!”
–from a /. post

4

InnoDB tables.

Ok. Have you set up mysql to use the my_large.cnf configuration file and
explicitly enabled mysql’s query cache? How many records do you have in
each of RT’s tables?

5

InnoDB tables.

Ok. Have you set up mysql to use the my_large.cnf configuration file and
explicitly enabled mysql’s query cache? How many records do you have in
each of RT’s tables?

Yes, I do use my_large.cnf and yes, I explicitly enabled mysql’s query
cache and doubled the value. The only other significant changes I made
to my.cnf are those of InnoDB parameters, which I configured according
to the recommendations on that file. The box has a 2.4GHz HTT processor
with 1GB RAM.

I have put some data about my MySQL server at the following URL:

http://ns2.wananchi.com/~wash/MySQL

username/password = guest/guest

Comments and welcome.

    cheers
   - wash

Odhiambo Washington . WANANCHI ONLINE LTD (Nairobi, KE) |
wash at wananchi _ dot _ com . 1ere Etage, Loita Hse, Loita St., |
GSM: (+254) 722 743 223 . # 10286, 00100 NAIROBI |
GSM: (+254) 733 744 121 . (+254) 020 313 985 - 9 |
“Oh My God! They killed init! You Bastards!”
–from a /. post