Redmine

Hi all,

I'm experiencing performance issues with our redmine installation. The server is dedicated to run redmine only (database runs on the same server).

Whenever we try to view issues list or a specific issue, overall it's very slow and I can see that CPU utilization is quite high (as high as 99%):

25559 www-data  20   0 86624  65m 1804 R 99.6  6.5   0:09.13 ruby    

A bit from the log file, an attempt to view an issue

Processing IssuesController#show (for 175.139.229.97 at 2011-09-21 09:36:58) [GET]
  Parameters: {"action"=>"show", "id"=>"398", "controller"=>"issues"}
Settings cache cleared.
Rendering template within layouts/base
Rendering issues/show.rhtml
Completed in 19893ms (View: 19277, DB: 335) | 200 OK [http://redmine.example.com/issues/398]

I've taken one of the SQL and tried it via psql to see the response from database:

explain analyze SELECT "members"."id" AS t0_r0, "members"."user_id" AS t0_r1, "members"."project_id" AS t0_r2, "members"."created_on" AS t0_r3, "members"."mail_notification" AS t0_r4, "projects"."id" AS t1_r0, "projects"."name" AS t1_r1, "projects"."description" AS t1_r2, "projects"."homepage" AS t1_r3, "projects"."is_public" AS t1_r4, "projects"."parent_id" AS t1_r5, "projects"."created_on" AS t1_r6, "projects"."updated_on" AS t1_r7, "projects"."identifier" AS t1_r8, "projects"."status" AS t1_r9, "projects"."lft" AS t1_r10, "projects"."rgt" AS t1_r11, "roles"."id" AS t2_r0, "roles"."name" AS t2_r1, "roles"."position" AS t2_r2, "roles"."assignable" AS t2_r3, "roles"."builtin" AS t2_r4, "roles"."permissions" AS t2_r5, "roles"."issues_visibility" AS t2_r6 FROM "members"  LEFT OUTER JOIN "projects" ON "projects".id = "members".project_id  LEFT OUTER JOIN "member_roles" ON ("members"."id" = "member_roles"."member_id")  LEFT OUTER JOIN "roles" ON ("roles"."id" = "member_roles"."role_id") WHERE ("members".user_id = 8 AND (projects.status=1))  ORDER BY projects.name;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Sort  (cost=5.93..5.93 rows=2 width=1839) (actual time=1.289..1.301 rows=15 loops=1)
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   Sort Key: projects.name
-[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   Sort Method:  quicksort  Memory: 37kB
-[ RECORD 4 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Nested Loop Left Join  (cost=0.00..5.92 rows=2 width=1839) (actual time=0.087..1.147 rows=15 loops=1)
-[ RECORD 5 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Nested Loop Left Join  (cost=0.00..4.99 rows=2 width=1642) (actual time=0.065..0.963 rows=15 loops=1)
-[ RECORD 6 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Join Filter: (members.id = member_roles.member_id)
-[ RECORD 7 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               ->  Nested Loop  (cost=0.00..2.53 rows=1 width=1638) (actual time=0.044..0.204 rows=6 loops=1)
-[ RECORD 8 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |                     Join Filter: (members.project_id = projects.id)
-[ RECORD 9 ]-------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |                     ->  Seq Scan on members  (cost=0.00..1.41 rows=1 width=21) (actual time=0.026..0.041 rows=6 loops=1)
-[ RECORD 10 ]------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |                           Filter: (user_id = 8)
-[ RECORD 11 ]------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |                     ->  Seq Scan on projects  (cost=0.00..1.10 rows=1 width=1617) (actual time=0.004..0.015 rows=8 loops=6)
-[ RECORD 12 ]------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |                           Filter: (projects.status = 1)
-[ RECORD 13 ]------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               ->  Seq Scan on member_roles  (cost=0.00..1.65 rows=65 width=8) (actual time=0.004..0.061 rows=65 loops=6)
-[ RECORD 14 ]------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Index Scan using roles_pkey on roles  (cost=0.00..0.45 rows=1 width=201) (actual time=0.005..0.007 rows=1 loops=15)
-[ RECORD 15 ]------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Index Cond: (roles.id = member_roles.role_id)
-[ RECORD 16 ]------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Total runtime: 1.487 ms

The details are as below

Redmine details (we access redmine via http://redmine.example.com), port 80:

root@redmine:~# ls -lrt /opt/
total 4
lrwxrwxrwx  1 root    root      13 2011-08-03 13:49 redmine -> redmine-1.2.1
drwxr-xr-x 15 support support 4096 2011-08-03 14:21 redmine-1.2.1

Database (default installation via apt-get)

PostgreSQL 8.4.8 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit

Operating System

Ubuntu Lucid (32-bit)
Linux redmine 2.6.32-33-generic-pae #72-Ubuntu SMP Fri Jul 29 22:06:29 UTC 2011 i686 GNU/Linux

Hardware details

root@redmine:~# cat /proc/meminfo 
MemTotal:        1026052 kB

root@redmine:~# cat /proc/cpuinfo 
processor    : 0
vendor_id    : GenuineIntel
cpu family    : 15
model        : 2
model name    : Intel(R) Pentium(R) 4 CPU 2.40GHz

root@redmine:~# fdisk -l

Disk /dev/sda: 40.0 GB, 40000000000 bytes
255 heads, 63 sectors/track, 4863 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000080

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        2432    19530752   83  Linux
/dev/sda2            2432        2554      976896   82  Linux swap / Solaris
/dev/sda3            2554        4863    18552832   83  Linux

root@redmine:~# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              19G  1.2G   17G   7% /
none                  497M  156K  497M   1% /dev
none                  501M     0  501M   0% /dev/shm
none                  501M   48K  501M   1% /var/run
none                  501M     0  501M   0% /var/lock
none                  501M     0  501M   0% /lib/init/rw
/dev/sda3              18G  1.6G   15G  10% /var