xTuple.com xTupleU Blog & News Customer Support

Account search slow after exactly 5 searches

Good Afternoon and Happy New

We have been working on a rather elusive performance bug.

When are using our provider_search function, a complex function that involves 30 tables. We are able to run the function and return our results using xtuple, psql, or pgadmin, rather quickly in about 1 to 2 secs. However, once we have ran the function 5 times, on the 6th time the function will begin to perform very poorly, causing the results to return in about 3 minutes. This continues until the session is restarted or xtuple is closed.

Any thought about what might be causing our inconsistent performance?

xTuple 5.1.0
Ubuntu 18.04.5 LTS
PostgreSQL 10.15-1.pgdg18.04+1
CPU: 16 cores 8 threads
MEM: 42GB
31GB effective cache
10GB shared buffers on 1GB large pages
512MB temp buffers
68812kB work_mem

Thanks and Regards,
Brian Orange

Here is a quick test of the timing of both 5 and 6 runs of the same funciton.

edit: Updated the example image

From a purely programming standpoint, To m it would seem that you are not releasing whatever resources you use when you call your function. I think I would need to see the psql function.

Also you mentioned pgadmin. does pgadmin also need to be restarted after the 5’th function call?

Check the resource monitor also after every call. See if it seems that the computer is hung up on anything still.

Yes that’s correct, closing the session is enough to restore performance, like closing pgadmin or xtuple would do for us this in case. Logging back in and retrying with give you full performance.

It seems like the query plan is taking a turn for the worst after being executed in succession.

I had a similar suspicion and I attempted to use DISCARD ALL; but it did not have any effect over the performance of the function.

I have attached the function that is leading me down the rabbit hole. It’s a bit complex, even more so for sysadmins like me if you interested in viewing it.

Aside from DISCARD ALL; Might there be something I am missing in regards to cleaning up after a session?

network_search.txt (12.9 KB)

It is an interesting problem. The function is quite complex. The only two things that come to my mind without some more time to think are.

  1. If you did DISCARD ALL inside your function
    PostgreSQL: Documentation: 9.4: DISCARD
    image

  2. I remember some time back I think in PostgreSQL 9.4 there was a problem with array_agg and a memory leak, but I believe that was fixed before PostgreSQL 10.

Other then That you might could use ANALYZE to see if on the 5’th and 6’th run there is a different output.

I would think DISCARD ALL would do anything that you needed it to.

Another thing that makes me curious. If you do this same statment 5 times in one day. Will it lock up on you the first time the next day?

Along with DISCARD ALL, restarting the pg service or server does not have an affect on the problem.

If you are in for some light reading I convinced auto_explain to log the query plans showing the both the fifth (fast) and sixth (slow) executions of our function.

explain-slowdown.txt (208.3 KB)

Well I can confidently Say that it is out of my depth. It is interesting the significant differences in the query plan between run 5 and 6, but I don’t know what would be causing it

It appears that this is the result PostgreSQL falling back to a generic query plan after executing the function 5 times.

I am also going to test plan_cache_mode parameter and post the results in my other forum post about query plan slowdowns after 5 executions.

I will post the results tonight.

This was exactly the fix that we needed, by testing on PostgreSQL v12 and setting the plan_cache_mode parameter to force_custom_plan (for this function only) we can now run our network search function at full speed beyond the 5 times threshold and the performance will remain to scale.

This is due to PostgreSQL reverting to a generic query plan after the 5th function call.

1m18sec is down to 15sec

Thank you u/CrackerJackKittyCat and u/mwdb.

Just let us know when PostgreSQL v12 is validated for xTuple we are itching to move forward.