The Problem
I have installed Bugzilla on an EC2 instance, using PostgreSQL RDS as my database. After entering a few dozen bugs, Bugzilla became unbelievable slow, and I get a lot of timeouts.
Server load
The machine is pretty idle:
14:26:00 up 23 days, 4:40, 2 users, load average: 0.15, 0.16, 0.25
%Cpu(s): 0.3 us, 0.3 sy, 0.1 ni, 99.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.2 st
Log evidence
Example from Nginx log (hostname changed for security):
2014/07/16 14:18:32 [error] 6462#0: *5040 upstream timed out
(110: Connection timed out) while reading response header from
upstream, client: my.ip.my.ip, server: bugs.example.com,
request: "POST /post_bug.cgi HTTP/1.1",
upstream: "http://127.0.0.1:8081/post_bug.cgi",
host: "bugs.example.com", referrer: "https://bugs.example.com/post_bug.cgi"
DB Analysis
Naturally, I suspected the database, so I run the following:
SELECT datname,
usename,
now()-query_start AS duration,
waiting,
state,
regexp_replace(query, E'[\\t\\n\\r\\s]+',' ', 'g' ) AS query_text
FROM pg_stat_activity
WHERE usename='bugs';
Which gave:
datname | usename | duration | waiting | state | query_text
---------+---------+-----------------+---------+-------+------------------------------------------------
bugs | bugs | 00:00:39.899945 | f | idle | SELECT value FROM bug_status WHERE is_open = 1
I think this is normal - the query is not waiting. When I executed the same query in a separate client, it returned immediately.
Cry for help...
Any idea why Bugzilla is so slow on my system?
