The dreaded semwait on FreeBSD

From: "Benjamin Krajmalnik" <kraj(at)servoyant(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: The dreaded semwait on FreeBSD
Date: 2013-03-11 15:53:33
Message-ID: F4E6A2751A2823418A21D4A160B6898892BB30@fletch.stackdump.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message Shaun Thomas 2013-03-11 22:29:13 Slow query when used in a view
Previous Message Jeff Adams - NOAA Affiliate 2013-03-11 15:48:17 Re: Large Table - Slow Window Functions (Better Approach?)