Forum OpenACS Q&A: 4.5 ticket tracker submission slowing down

Collapse
1: 4.5 ticket tracker submission slowing down
Posted by Vince T on 03/11/03 07:27 AM
Hi,

I'm using OACS 4.5, postgresql 7.1.3, redhat 7.2. I only have ticket-tracker and required components installed. Basically using it as a service order system.

It was working fine for awhile but the submission of tickets has been slowing down terribly. It started at about 15 seconds per ticket, now I clock it at about a minute and a half. I've been trying to trace where it slows down by commenting out parts of the function, but have had no luck. i tried checking the logs but it slows down only on the ticket__add function.

any ideas on how i can speed things up? i'm going to throw more hardware at it but i'm afraid it wont help because everything else is pretty quick.

i'd like to update to 4.6 but i've got already a few hundred tickets in there and migration seems a pain.

please help.

vincent

Collapse
2: Re: 4.5 ticket tracker submission slowing down (response to 1)
Posted by Jeff Davis on 03/11/03 08:38 AM
Do you vacuum the db periodically? If not try 
vacuumdb -f -z dbname
and see if that helps. Note that under pg 7.1 there are some states the tables get in such that vacuum will fail (you will get a message like "ERROR: No one parent tuple was found"), If that's happened I think your only option is a dump/restore.
Collapse
3: Re: 4.5 ticket tracker submission slowing down (response to 1)
Posted by Vince T on 03/11/03 10:09 AM
Hi Jeff,

Thanks for replying but yeah, I vacuum the db nightly. I use the scripts included in the docs.

Everything else in the system is pretty fast. Even on ticket tracker itself. It's just the initial ticket submisssion. Editing and resolving through the workflow works pretty well.

Vince

Collapse
4: Re: 4.5 ticket tracker submission slowing down (response to 1)
Posted by Don Baccus on 03/11/03 07:53 PM
Take a look at the RAISE command and consider sticking a couple of RAISE Notice ''Here I am!'' statements inside the ticket add routine to see exactly which step is taking a long time.

Also if you haven't started and stopped AOLserver in a long time you might try that.  Execution plans for PL/pgSQL functions are created once per backend lifetime, and if you follow our advice on linux and keep your backends alive forever (to avoid the zombie process issue on Linux), vacuuming may analyze the tables but the function plan won't change.

Stopping AOLserver will release those backends and when they restart a new execution plan will be made for the various PL/pgSQL procs involved.

My own site stays up literally for months on end ... if yours is like that and if you started out with very few tickets your functions may be executing sequential scan plans rather than index scans.

And of course there may just be some missing indexes in the datamodel, too ... (the RAISE hack should help you find the exact step which is taking a long time)

The add function shouldn't be doing an permissions checks so I doubt upgrading to new permissions would help this.  However, you should be able to run the permissions upgrades for 4.6.1 on your 4.5 database with no problem - on a test version first, of course, not your live data!

Collapse
5: Re: 4.5 ticket tracker submission slowing down (response to 4)
Posted by Vince T on 03/12/03 12:57 AM
Hi Don,

Great tip. I found that it slows down when it attempts to start the workflow case. Everything else, including creating the case is automatic. this the part where it hangs for about a minute :

        -- start the case
    perform workflow_case__start_case (
                v_case_id,                              -- case_id
                p_creation_user,                -- creation_user
                p_creation_ip,                  -- creation_ip
                null                                    -- msg
        );

I will look through that function and do the same RAISE hack. But if anybody has any ideas or if there is a known bug, it would be most appreciated.

Thanks again.

Vincent