I have a very heavy OLTP application in the field.
We have two PostgreSQL databases (9.0.x)/FreeBSD 8.1/amd64 – one is a listener which just stores data digests, the other is the actual database.
The digests from the listener are processed by multi-threaded daemons and inserted into the main database.
Every so often, we see the queues on the listener grow and get significantly behind.
Checking the system metrics, we do not see any issue with either memory, CPU, or IO (using top and iostat), so it would appear we are being hit by contention. When data insertion rates slow down we can see a lot of the potgresql processes in a semwait state.
The data which is coming in from the daemons is inserted into a temporary table, which fires of a trigger which in turn calls a stored procedure (massive) which processes the data input.
We also have another daemon which runs in the background continuously creating a materialized view of a dashboard. If this daemon does not run, the queues do not grow (or the rate of growth decreases if some other heavy processing is going on such as a large report being generated), so it appears this is one of the primary causes for the contention which is causing the semwaits.
The data is inserted into 3 partitioned tables, each of which is fairly wide. The daemon which processes the dashboard looks at only 2 fields (state and last value), using the Devices and Tests tables. If I were to create a 2 more partitioned tables which only holds the columns in question (as well as the columns necessary to associate the rows), should this reduce the contention?
The partitioned tables are as such:
Devices -> Tests -> Statistical info on tests
I was thinking of adding two more partitioned tables
Altdevices -> DashboardTests
That way, when we process the dashboard we do not touch any of the 3 primary tables, which are the ones which are constantly being pounded on.
Looking at the stats on the server (Xact committed), we are processing approximately 4000 transactions per second.
On another note, this setup is using streaming replication to a secondary server which is used as a read only server for reporting. Would accessing data from the secondary server somehow cause contention on the primary server? From the patterns of behavior, it would appear so (when large reports are generated we are seeing some effect on the data insertion rates).
Thanks in advance,
Benjamin