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

[Ivan Kohler]

Upgrade to current DBIx::DBSchema and run freeside-upgrade again.

DBIx::DBSchema starting with 0.33 will make sure your indices are 
up-to-date with current code, so it doesn't seem very useful to keep 
that at 0.32 if you're having performance problems.

-- 
_ivan


On Tue, Sep 11, 2007 at 11:23:51PM -0400, Timothy Butkiewicz wrote:
> 
> 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
> 
> _______________________________________________
> freeside-users mailing list
> freeside-users at sisd.com
> http://420.am/cgi-bin/mailman/listinfo/freeside-users

-- 
_ivan