Tuning Django and Postgress for performance

[plexoos]

On 5/10/22 4:27 PM, Mashinistov, Ruslan wrote:

Dear Dmitri,

Today I played around the setting go the Postgres DB backend at http://npdb-test-test.apps.rcf.bnl.gov/.
Previously I observed the problems submitting over 100 concurrent requests to the Service.
I found that the PostgreSQL settings actually limited the MAX_CONNECTIONS to default 100.
Now I extended it to 1000. Also increased SHARED_BUFFERS to 500MB and WAL_SIZE to 2GB.
I also set CONN_MAX_AGE in the Django app settings (for the DB connection section) to 50

All this seems to improve performance when I run apache benchmark tool.

Could you please run some similar test against npdb-test-test that you did before to prepare nice histograms with response time distribution?

[plexoos]

On 10 May 2022, at 22:40, Dmitri Smirnov wrote:

Can you share the before and after results from the apache bench?
Do you have a script to populate the database with a large number of entries?

[plexoos]

On May 11, 2022, at 9:43 AM, Laycock, Paul wrote:

I think it would be interesting to run tests against a small GT to see what you get, we have hardly started to look at which parameters are relevant for performance, improving the deployment etc. so this is anyway not a number that we should use to quote as a definitive performance measurement, it’s more of an FYI for the review. Chris might comment, but Ruslan already changed four parameters and we don’t know how/if they interact. We’re a long way from testing something realistic imho (maybe I’m a pessimist!).

[plexoos]

On 11 May 2022, at 14:50, Mashinistov, Ruslan wrote:

Please have a look at the table. I have 1 pod for Django+apache and 1 pod for the DB. Old VS Tuned
https://docs.google.com/presentation/d/1Ws2ldOqtX4XE38G6PDMXurIaQtq2Z9Myp78-qisiMqQ/edit?usp=sharing

[plexoos]

On 5/11/22 8:56 AM, Laycock, Paul wrote:

Thanks Ruslan,
The failure rate improvement is obviously welcome and I think meaningful. The response time variations suggest there’s another factor (overall NFS load ?) that comes into play, although variations of +- 100% maybe aren’t terrible for shared resources, i.e. I don’t think there’s a meaningful improvement in response time looking at those results.

[plexoos]

Thank you for the results Ruslan.

Which endpoint(s) do you test? Do you always send the same parameter values in the requests (if any)?
ab also prints the number of requests per second and the number of bytes transferred. It would be good to see those as well.

Can you create separate test servers with the default and modified Django and DB parameters?

[plexoos]

On 5/12/22 11:06 AM, Mashinistov, Ruslan wrote:

I’m using only read endpoint:
/api/cdb_rest/payloadiovs/?gtName=sPHENIX_ExampleGT_1&majorIOV=1&minorIOV=1
Always same parameters.

Second slide contains more fields in the table

https://docs.google.com/presentation/d/1Ws2ldOqtX4XE38G6PDMXurIaQtq2Z9Myp78-qisiMqQ/edit?usp=sharing

So the basic change in parameters is about MAX_CONNECTIONS from 100 to 1000.
And this was obvious that after increasing concurrent requests number more than 100 leads to the big % of failures and checking the logs because of limit for connections reached.

Playing with other parameters I’ve got some improvement for now but planning to investigate more options for speedup.
Then second test instance may have sense.

[plexoos]

On Jun 2, 2022, at 1:32 AM, @plexoos wrote:

Hi Paul, Ruslan,

Did you have a chance to set up a reference server with default parameters? Currently, with my test I don't see any improvement when I ran against npdb-test-test.apps.rcf.bnl.gov but I am comparing to my old results and that is not an apples-to-apples comparison...

[plexoos]

On 6/3/22 12:17 PM, Mashinistov, Ruslan wrote:

I deployed test reference server at npdb-ref-test.apps.rcf.bnl.gov
Trying both original test and reference test indeed I saw similar results on stress tests.
But checking the logs I found that the reason is different:

New reference server is failing due to reaching the db connections limit (as it was before with original test server)
While the original test server shows the following error:

[Fri Jun 03 18:00:36.766817 2022] [mpm_prefork:error] [pid 1] AH00161: server reached MaxRequestWorkers setting, consider raising the MaxRequestWorkers setting

Looks like I need to adjust apache configuration (currently they’re default - see httpd.conf in attachment
)

I restarted the CDB pod first but this didn’t removed the error. So I restarted postgres pod and this error seems to disappear for now.

So please feel free to run tests against both:

1)npdb-test-test.apps.rcf.bnl.gov
2)npdb-ref-test.apps.rcf.bnl.gov

Also very welcome sharing your experience with apache configuration or the other http server you’ve used in your local tests

[plexoos]

On 6/4/22 1:13 PM, @plexoos wrote:

Hi Ruslan,

Please fill the test databases with the entries from the attached file. All you need to do is to copy the file to the pods running postgres and issue the following command:

zcat entries.sql.gz | psql dbname -U login

This should take a couple of minutes.

Thanks,

[plexoos]

On 6/6/22 12:22 PM, Mashinistov, Ruslan wrote:

Done!
Let me know if something didn’t working as expected

[plexoos]

The test databases seem to be filled with 1M of random interval values now. I use test_xpload_rand executable from https://github.com/BNLNPPS/xpload/tree/main/test to submit 10,000 jobs to the cluster with 10 fetches each (with nominal 1 second interval between calls) and record the time of each call relative to the first one.

I assume npdb-ref-test.apps.rcf.bnl.gov is set up with default configuration parameters and npdb-ref-test.apps.rcf.bnl.gov is set up with experimental settings (which ones exactly? MAX_CONNECTIONS in Postgres changed from 100 default to 1000?)

Below are the distributions of response codes for all fetch calls for random payload intervals. The response codes through 200 are considered as successful and above 200 as unsuccessful.

npdb-ref-test.apps.rcf.bnl.gov ⬇️

npdb-test-test.apps.rcf.bnl.gov ⬇️

The significantly larger fraction of failed attempts and the longer tail in the last distribution may indicate that the proposed configuration settings do not perform as expected.

[plexoos]

On 6/16/22 7:45 AM, Mashinistov, Ruslan wrote:

Dear Dmitri,

I would like to ask you to run your tests again against test and reference .
For the test instance I installed and minimally configured pgBouncer db pooler and first results are look very promising for me.

Cheers

[plexoos]

New results from the same test as above

npdb-ref-test.apps.rcf.bnl.gov ⬇️

npdb-test-test.apps.rcf.bnl.gov ⬇️