I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database.
At the core is a table (partitioned by month) which contains a row for each measurement point:
CREATE TABLE "tblReadings2013-10-01" ( -- Inherited from table "tblReadings_master": "sessionID" integer NOT NULL, -- Inherited from table "tblReadings_master": "fieldSerialID" integer NOT NULL, -- Inherited from table "tblReadings_master": "timeStamp" timestamp without time zone NOT NULL, -- Inherited from table "tblReadings_master": value double precision NOT NULL, CONSTRAINT "tblReadings2013-10-01_readingPK" PRIMARY KEY ("sessionID", "fieldSerialID", "timeStamp"), CONSTRAINT "tblReadings2013-10-01_fieldSerialFK" FOREIGN KEY ("fieldSerialID") REFERENCES "tblFields" ("fieldSerial") MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT "tblReadings2013-10-01_sessionFK" FOREIGN KEY ("sessionID") REFERENCES "tblSessions" ("sessionID") MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT "tblReadings2013-10-01_timeStamp_check" CHECK ("timeStamp" >= '2013-10-01 00:00:00'::timestamp without time zone AND "timeStamp" < '2013-11-01 00:00:00'::timestamp without time zone) )
We are in the process of populating the table with data that has already been collected. Each file represents a transaction of around 48,000 points and there are several thousand files. They are imported using an
INSERT INTO "tblReadings_master" VALUES (?,?,?,?);
Initially the files import at a rate of 1000+ inserts/sec but after a while (an inconsistent amount but never longer than 30mins or so) this rate plummets to 10-40 inserts/sec and the Postgres process rails a CPU. The only way to recover the original rates is to perform a full vacuum and analyze. This is ultimately going to be storing around 1,000,000,000 rows per monthly table so the vacuum takes some time.
EDIT: Here is an example where it ran for some time on smaller files, and then after larger files started it failed. The larger files look more erratic but I think it is because the transaction is only commited at the end of a file, around 40sec.
There will be a web front end selecting some items but no update or deletes and this is seen with no other active connections.
My questions are:
- How can we tell what is causing the slowdown/rail the CPU (this is on Windows)?
- What can we do to maintain the original performance?