Currently we are migrating a high throughput application from Oracle to PostgreSQL 11 database in AWS Aurora. We have completed the migration of SQL queries for some modules and currently performing load test on PostgreSQL database.
The module under load test performs following activities for each transactions
Approximately 5 insert/update operations in 5 different partition enabled tables. Each table will contain nearly 90 partitioned tables. Approximately 8 read operations in different tables inclusive of single partition enabled tables and rest all non-partitioned config tables.
We are having an PostgreSQL cluster with one write node[16 CPU, 128 GB RAM] and one reader node [8 CPU, 64 GB]. We are using AWS RDS Performance monitoring as well as PGADMIN tools for monitoring PostgreSQL.
We have tried for 30 tps in PostgreSQL but the cluster is not able to withstand this load and reached high CPU nearly 99% of utilization for 30 tps load. We have implemented dual data-source connections to reader as well writer nodes from our Java client application and distributed select queries to reader nodes. We are able to able to achieve 75 tps with both reader and writer node having 40% CPU utilization. We have doubled the load i.e 150 tps, then the cpu boosted to 99% within 3 mins of increased load.
The average execution time of write operations[insert/update] as well as select operations in reader node are less than 5 ms. If the no of calls increased then CPU boost to its maximum value. In all our cases we could find LWLocks::Lock_manager comes in to picture in monitoring and immediately CPU boosts to its maximum value. If the load is normal the LWLocks::Lock_manager will not be available in monitoring insights graph.
Also we have analysed the Explain Plan of all the queries. The select queries are directly landing to the particular partitioned table and fetches the result but the update queries are scanning all the partitioned tables for the same table.
We are having difficulty in finding the root cause of High CPU utilization as well as trigger of LWLocks::Lock_manager. We are looking for an advice and help from PostgreSQL Experts on DB side evaluations and performance tuning.