Performance on PostgreSQL

We have an install of RT running on Linux/PostgreSQL, and it seems slow
to me.

For example, if I just bring up a ticket:

http://xxx/index.html?q=58431

the little Time to display can be anywhere between 3.6 and 8. It tends
to the longer side of things.

My overall question is: what’s good? What should I expect?

Now, the Too Much Information part of my post:

First things first: the Fedora4Install page mentions a missing index.
I’ve created that, but it doesn’t seem to have changed things much.

The production server is running Centos 4 (RHEL 4) on a machine that
really doesn’t have enough RAM (1GB) and only has IDE disks, running as
a software RAID1. It IS an Opteron, and it is running 64-bit. rt 3.4.1
and postgresql 8.0.3.

Suspicions: disk I/O, and a suboptimal Apache/mod_perl install.
However, when things are happening slowing, what I see in top is postmaster.

So, I have a newer box. I decided to copy rt and my database over to it
to see how it acts. It is a faster Athlon 64 and 2GB, but still has IDE
disks running in a software RAID 1. It is running Fedora Core 5, so it
has a better apache/mod_perl chain, and postgresql 8.1.4. After finally
getting rt (still 3.4.1) running on this new box, the performance wasn’t
markedly better…sometimes, it could be worse. I followed the tips in
the FedoraCore4 install instructions to increase shmem and the
shared_buffers, to no real benefit.

So, I’m kind of looking for data points as to how good this can be, to
see how much more work I should put into this. Do I need 2 tiers? Do I
need to get rid of the software RAID? (Although, the performance I see
out of Dell’s SCSI hardware RAID stuff is abysmal…)

I have another Athlon 64 box I can try that doesn’t have software RAID
on it…I’m just trying to decide if it is worth it, or if I’m getting
the best I can hope for.

I’ve even considered seeing if I can move to MySQL, but the info on the
Wiki seems to hint that both can perform well.

Any hints as to what I can expect for performance, or what I should be
looking at to see if I’m actually having problems, or how to tell where
the hang-ups might be, are welcome.

Thanks,

Kevin

The production server is running Centos 4 (RHEL 4) on a machine that
really doesn’t have enough RAM (1GB) and only has IDE disks, running as
a software RAID1. It IS an Opteron, and it is running 64-bit. rt 3.4.1
and postgresql 8.0.3.

Strongly recommend:
* newer RT
* newer Pg (8.x before 8.1.4 had some specialness)
* vacuum analyzing your postgres instance.

Jesse Vincent wrote:

The production server is running Centos 4 (RHEL 4) on a machine that
really doesn’t have enough RAM (1GB) and only has IDE disks, running as
a software RAID1. It IS an Opteron, and it is running 64-bit. rt 3.4.1
and postgresql 8.0.3.

Strongly recommend:

  • newer RT
  • newer Pg (8.x before 8.1.4 had some specialness)
  • vacuum analyzing your postgres instance.

Thanks Jesse…when you say newer RT, do you mean newer than 3.4.5?

My test box has Pg 8.1.4, and my next step will be attempting to take
that to RT 3.4.5, then 3.6.1 (.2 by then, probably). I have some small
customizations I need to carry forward.

I do vacuum analyze, 3 times a day actually, so I do that. :slight_smile:

Thanks for the advice. If I find anything else, I’ll post (and
contribute to the Wiki).

Kevin

Jesse Vincent wrote:

The production server is running Centos 4 (RHEL 4) on a machine that
really doesn’t have enough RAM (1GB) and only has IDE disks, running as
a software RAID1. It IS an Opteron, and it is running 64-bit. rt 3.4.1
and postgresql 8.0.3.

Strongly recommend:

  • newer RT
  • newer Pg (8.x before 8.1.4 had some specialness)
  • vacuum analyzing your postgres instance.

Thanks Jesse…when you say newer RT, do you mean newer than 3.4.5?

I recall that we made a major pg-related fix around 3.4.2 or so. If you
suspect it’s postgres-side stuff that’s slowing you down, turning on the
query log and seeing if you can reindex a bit is worthwhile.

I recall that we made a major pg-related fix around 3.4.2 or so. If you
suspect it’s postgres-side stuff that’s slowing you down, turning on the
query log and seeing if you can reindex a bit is worthwhile.

I’ve moved to 3.4.5 but it still seems slow to me.

You’re talking about the postgresql query log, right? I’ll look into
that next.

I’ve been trying desperately to turn on DBI::ProfileDumper::Apache. My
perl-status shows:

DBI_PROFILE = 2/DBI::ProfileDumper::Apache
DBI_PROFILE_APACHE_LOG_DIR = /var/log/httpd

but I get no dbi.prof.* files…anywhere on my filesystem.

Thanks for all the help.

Here’s my rt.conf:

ServerName xxx DocumentRoot /var/www/html/rt3/share/html AddDefaultCharset UTF-8
PerlSetEnv DBI_PROFILE 2/DBI::ProfileDumper::Apache
PerlSetEnv DBI_PROFILE_APACHE_LOG_DIR /var/log/httpd

PerlOptions +GlobalRequest

<Location /perl-status>
   SetHandler perl-script
   PerlResponseHandler Apache2::Status
</Location>

PerlModule Apache::DBI

PerlRequire /var/www/html/rt3/bin/webmux.pl

<Location /NoAuth/images>
    SetHandler default
</Location>

<Directory /var/www/html/rt3/share/html>
    SetHandler perl-script
    PerlResponseHandler RT::Mason
</Directory>

Jesse Vincent wrote:

The production server is running Centos 4 (RHEL 4) on a machine that
really doesn’t have enough RAM (1GB) and only has IDE disks, running as
a software RAID1. It IS an Opteron, and it is running 64-bit. rt 3.4.1
and postgresql 8.0.3.

Strongly recommend:

  • newer RT
  • newer Pg (8.x before 8.1.4 had some specialness)
  • vacuum analyzing your postgres instance.

Thanks Jesse…when you say newer RT, do you mean newer than 3.4.5?

My test box has Pg 8.1.4, and my next step will be attempting to take
that to RT 3.4.5, then 3.6.1 (.2 by then, probably). I have some small
customizations I need to carry forward.

I do vacuum analyze, 3 times a day actually, so I do that. :slight_smile:

Thanks for the advice. If I find anything else, I’ll post (and
contribute to the Wiki).

Kevin

Kevin,

I had meant to pass this on to the list for possible inclusion in the
PostgreSQL related FAQ/schema. When we first rolled RT out, performance
was reasonable. As the number of tickets and users grew, the ticket
update, creation, and display processes continued to degrade. Finally,
the slowdown was enough to cause the slowing queries to cross the query
log threshold on the DB server. The slow queries all involved lookups
based on OID. I added indexes on OID for all of the tables involved
and the overall responsiveness is back to normal in all areas.

Here are the indexes that needed to be added to the DB to enable fast
OID based queries. Hopefully, they can be included in 3.6.2+.

CREATE INDEX attachmentsoid ON attachments USING btree ( oid );
CREATE INDEX cachedgroupmembersoid ON cachedgroupmembers USING btree ( oid );
CREATE INDEX objectcustomfieldvaluesoid ON objectcustomfieldvalues USING btree ( oid );
CREATE INDEX transactionsoid ON transactions USING btree ( oid );

Ken

CREATE INDEX attachmentsoid ON attachments USING btree ( oid );
CREATE INDEX cachedgroupmembersoid ON cachedgroupmembers USING btree ( oid );
CREATE INDEX objectcustomfieldvaluesoid ON objectcustomfieldvalues USING btree ( oid );
CREATE INDEX transactionsoid ON transactions USING btree ( oid );

Here’s also a post from two years ago that still applies to 3.4.5:

http://lists.bestpractical.com/pipermail/rt-devel/2004-September/006273.html

  • Dmitri.

Here are the indexes that needed to be added to the DB to enable fast
OID based queries. Hopefully, they can be included in 3.6.2+.

CREATE INDEX attachmentsoid ON attachments USING btree ( oid );
CREATE INDEX cachedgroupmembersoid ON cachedgroupmembers USING
btree ( oid );
CREATE INDEX objectcustomfieldvaluesoid ON objectcustomfieldvalues
USING btree ( oid );

WTF is it trying to use OID’s in queries? OID’s are off by default
on postgres 8.1 so most tables won’t even have them. RT’s schema.Pg
file certainly doesn’t request them.

I never saw RT trying to use OIDs for anything when I analyzed the
queries. Is this something new in searchbuilder?

smime.p7s (2.47 KB)

Here’s also a post from two years ago that still applies to 3.4.5:

The Rt-devel September 2004 Archive by thread
006273.html

I still use them in RT 3.6, too :slight_smile:

smime.p7s (2.47 KB)

Strongly recommend:

  • newer RT
  • newer Pg (8.x before 8.1.4 had some specialness)
  • vacuum analyzing your postgres instance.

Also make sure your Pg is tuned properly for the size of your DB.
See recent discussion in the postgres general mailing list (some tips
came flying by earlier this week. Also see http://www.varlena.com/
GeneralBits/Tidbits/annotated_conf_e.html.

I personally bump up the shared buffers, work mem, sort mem,
checkpoint segments, and appropriatly set the effective cache size.

If you’re still running slow after bumping these up (and applying my
indexes, mentioned earlier), you should profile your overall system
to see where the bottleneck is. I’ll vote it is your disk drives.
At least use SATA…

Our primary RT install is running on a Dell PE 1850 with hardware
RAID1 on a pair of SCSI disks which can support 80Mb/s sustained read/
write speed. With several tens of thousand tickets, it is quite
snappy. We even run it over SSL.

Our configuration of software is FreeBSD 6.1 + apache + fastcgi for
RT. RT makes zero use of mod_perl’s advanced features, and fastcgi
is much easier to get working right.

smime.p7s (2.47 KB)

Here are the indexes that needed to be added to the DB to enable fast
OID based queries. Hopefully, they can be included in 3.6.2+.

CREATE INDEX attachmentsoid ON attachments USING btree ( oid );
CREATE INDEX cachedgroupmembersoid ON cachedgroupmembers USING
btree ( oid );
CREATE INDEX objectcustomfieldvaluesoid ON objectcustomfieldvalues
USING btree ( oid );

WTF is it trying to use OID’s in queries? OID’s are off by default
on postgres 8.1 so most tables won’t even have them. RT’s schema.Pg
file certainly doesn’t request them.

I never saw RT trying to use OIDs for anything when I analyzed the
queries. Is this something new in searchbuilder?

Vivek,

I was as surprised as you. It may have been the result of migrations
from older versions of RT to newer versions. We started at 3.2, are
currently running 3.4.5, and are preparing to migrate to 3.6.x. If
the OIDs are not needed, I would like to drop them in our migration
to 3.6.x. This is with an older version of searchbuilder (1.36) on
the 3.4.5 system. We could not upgrade to a newer version because it
caused our DB queries to fail. Do you think that it is safe to remove
the oid column?

Ken

to 3.6.x. This is with an older version of searchbuilder (1.36) on
the 3.4.5 system. We could not upgrade to a newer version because it
caused our DB queries to fail. Do you think that it is safe to remove
the oid column?

If you do a fresh new install of RT, you won’t have them, so I
suspect it is safe.

I never bothered, though. I don’t see how searchbuilder would
construct a query that uses oids, though, since the string "oid"
doesn’t exist at all in version 1.43.

smime.p7s (2.47 KB)

Thank you everybody for the suggestions.

I will look into adding the suggested indexes, and look at my RAM usage.
I think I need to get more than 1GB of RAM on the box, so I have room
to play. Those httpd processes end up taking a lot of RAM.

I’ll also work on moving up to Pg 8.1.4, and getting production up to RT
3.4.5.

Thanks again for the suggestions.

Kevin

OIDs were used to get the id of the last insert. The was abandoned
because of pgsql 8.1, but it was a horrible way to get the last id
anyway. Current versions of SB insert the row and then get the current
value of the sequence associated with that table. This is much faster
and should be safe.

I’ve patched our SB (1.36) to work like most of the other db object
abstraction systems I’ve used – get the next val of the sequence and
then use the value in the insert, this will be correct everytime and in
all setups. It is also faster than the default SB-1.36.

Joby Walker
C&C SSG, University of Washington

Kenneth Marshall wrote:

OIDs were used to get the id of the last insert. The was abandoned
because of pgsql 8.1, but it was a horrible way to get the last id
anyway. Current versions of SB insert the row and then get the
current value of the sequence associated with that table. This is
much faster and should be safe.

Does it actually query the sequence or does it just pull the last
insert id from the connection’s return status? if the former, then
it is not multi-thread safe, if the latter then it is faster than
your method of pulling a sequence then doing insert (one trip to DB
vs. two).

smime.p7s (2.47 KB)

OIDs were used to get the id of the last insert. The was abandoned
because of pgsql 8.1, but it was a horrible way to get the last id
anyway. Current versions of SB insert the row and then get the
current value of the sequence associated with that table. This is
much faster and should be safe.

Does it actually query the sequence or does it just pull the last
insert id from the connection’s return status? if the former, then
it is not multi-thread safe, if the latter then it is faster than
your method of pulling a sequence then doing insert (one trip to DB
vs. two).

my $sequence_name = $self->IdSequenceName($table);
unless ($sequence_name) { return ($sequence_name) }   # Class::ReturnValue
my $seqsth = $self->dbh->prepare( qq{SELECT CURRVAL('} . $sequence_name . qq{')} );
$seqsth->execute;
$self->{'id'} = $seqsth->fetchrow_array();

A dozen people assured me that this was considered the postgres-blessed way to do this these days. Is it not?

Vivek Khera wrote:

OIDs were used to get the id of the last insert. The was abandoned
because of pgsql 8.1, but it was a horrible way to get the last id
anyway. Current versions of SB insert the row and then get the
current value of the sequence associated with that table. This is
much faster and should be safe.

Does it actually query the sequence or does it just pull the last insert
id from the connection’s return status? if the former, then it is not
multi-thread safe, if the latter then it is faster than your method of
pulling a sequence then doing insert (one trip to DB vs. two).

As was pointed out to me the current value function returns the last
value used for that connection, so from the pgsql side it is multithread
safe – but I don’t know how using a connection pooling system like
SQLRelay impacts things (because we might use a sqlrelay in the future
is one of the reasons I modified our instance of SB).

Pgsql returns the number of rows inserted or if the count is one and the
table has oids it returns the oid of the row – it doesn’t return the PK
of the table. This is why SB previously made the OID query.

jbw

As was pointed out to me the current value function returns the
last value used for that connection, so from the pgsql side it is
multithread safe – but I don’t know how using a connection pooling
system like SQLRelay impacts things (because we might use a
sqlrelay in the future is one of the reasons I modified our
instance of SB).

Well, you’re not gonna multiplex the connection during the same
transaction, so it will be safe to call the last_insert_id() method
from DBD::Pg to query the sequence number of the last insert.

smime.p7s (2.47 KB)