[freeside-users] 1.5.8 -> 1.7.2 now slow performance on database

[Timothy Butkiewicz]

FS 1.7.2
PG 8.1.9
Latest CPAN mods except DBIx::DBSchema which was installed as v 0.32
Fedora Core 3 ProLiant PIII 1.4GHz

When using 1.5.8, access to database and reports generally ran pretty
quick. Was using PG 7.4.11. Upgraded Freeside to 1.7.2 and while
customer and account access is pretty quick, and most reports run
quickly, *but* agent screens and the AR report take hours to load.
Postgres eats CPU. Memory consumption stays about the same, disk
consumption stays about the same, but CPU spikes and stays there.
Originally this occurred on PG 7.4.11 so I decided to upgrade to 8.1 and
that doesn't appear to have changed performance. I did tweak the
postgres.conf file and upped shared_buffers etc.

Using the same database as previous versions so it dates back to 1.4x
and maybe earlier.

Any ideas? Thanks in advance.


Example TOP line:
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
26206 postgres  25   0  143m  14m  12m R 99.4  1.7  70:00.76 postmaster

The AR report that is currently chomping CPU is (from select * from
pg_stat_activity;):

16385 | freeside |   26206 |    16384 | freeside | SELECT *, coalesce( (
select sum( charged
     - coalesce(
         ( select sum(amount) from cust_bill_pay
           where cust_bill.invnum = cust_bill_pay.invnum )
         ,0
       )
     - coalesce(
         ( select sum(amount) from cust_credit_bill
           where cust_bill.invnum = cust_credit_bill.invnum )
         ,0
       )

   )
 from cust_bill  WHERE cust_bill._date > extract(epoch from now()) -
2592000 AND cust_main.custnum = cust_bill.custnum ) ,0 ) as
owed_0_30,coalesce( ( select sum( charged
     - coalesce(
         ( select sum(amount) from cust_bill_pay
           where cust_bill.invnum = cust_bill_pay.invnum )
         ,0
       )
     - coalesce(
         ( select sum(amount) from cust_credit_bill
           where cust_bill.invnum = cust_credit_bill.invnum )
         ,0
       )

   )
 from cust_bill  WHERE cust_bill._date <= extract(epoch from
now())-2592000 AND cust_bill._date > extract(epoch from now()) - 5184000
AND cust_main.custnum = cust | 2007-09-11 22:36:29.453533-04 |
2007-09-11 21:37:32.398113-04 |             |          -1