On Tue, Sep 16, 2008 at 2:31 AM, Mauri Sahlberg <Mauri.Sahlberg@xxxxxxxxxxxxxxxx> wrote: > Hi, > > We just upgraded Best Practical's RT from 3.6 to 3.81 and gave the > database completely own machine. And the users still complain that it is > dog slow. Moved up from below: > Version : 8.1.11 Vendor: CentOS So, you built it its own machine, but you didn't upgrade to at least 8.2? Last place I worked we ran rt 3.6.1 and got a noticeable performance boost from switching to 8.2 but the only thing that was ever really slow was viewing the rather large approval queue. > :-( I installed pg_top and it seems that at the beginning of > the ticket display RT-issues a query that eats everything the database > has. Query is as follows: > > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN > Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN > CachedGroupMembers CachedGroupMembers_3 ON > ( CachedGroupMembers_3.MemberId = Principals_1.id ) WHERE > (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId = > CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND > (ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType = > 'User') AND (ACL_2.RightName = 'OwnTicket') AND ((ACL_2.ObjectType = > 'RT::Queue' AND ACL_2.ObjectId = 18) OR (ACL_2.ObjectType = > 'RT::System')) ORDER BY main.Name ASC Please post the output of explain analyze as an attachment. explain is only half the answer. > Is there something I can do to improve performance with tuning something > on postgresql.conf? Or adding/dropping indexes? What I read from that > query plan is that the single most expensive thing is sequential scan on > Principals. Principals already has indexes for both id and object.id! Possibly. explain analyze will help you identify where stats are wrong. sometimes just cranking the stats target on a few columns and re-analyzing gets you a noticeable performance boost. It's cheap and easy. When the estimated and actual number of rows are fairly close, then look for the slowest thing and see if an index can help. What have to already done to tune the install? shared_buffers, work_mem, random_page_cost, effective_cache_size. Is your db bloating during the day? Why no try 8.3 on this? Are you running on a single SATA hard drive? How big's the database directory? I'm guessing from your top output that the db is about 500 meg or so. it should all fit in memory.
