7

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. CPU and Insert trace of problem

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:

  1. How can we tell what is causing the slowdown/rail the CPU (this is on Windows)?
  2. What can we do to maintain the original performance?
  • The transaction/file size may be important. It is currently running through a series of files with around 4000 INSERT's and has been importing these successfully for 1hr30 so far. – jamesmc86 Aug 27 '14 at 13:48
  • I'm no Postgres expert too, but if this was showing up with MySQL I'd try a couple of things: see if committing from time to time would help; check if there's a better way to load data from files (in MySQL there's a LOAD DATA INFILE); maybe the slowdown is cause by index population / organization after each insert, see if your data lets you disable some (or all) indexes, INSERT everything and then reactivate the indexes; I don't think that it could really help, but locking the table could be another option. – watery Aug 27 '14 at 20:53
  • "full vacuum" is ambiguous. It may mean VACUUM FULL on a specific table or simple-VACUUM on the whole database or VACUUM FULL on the whole database. Anyway the fact that it helps with performance is suspicious. VACUUM reclaims dead rows resulting from UPDATEs and DELETEs, it's not needed in an INSERT-only scenario. – Daniel Vérité Aug 30 '14 at 13:26
  • 1
    It was a VACUUM FULL that we were doing. With help from the guys on the IRC channel it's looking pretty likely that the problem is with the index getting fragmented – jamesmc86 Sep 1 '14 at 8:18
5

There are a few things that might be causing this issue, but I can't be sure any of them are the real problem. The troubleshooting all involves turning on extra logging in the database, then seeing if the slow parts line up with messages there. Make sure you put a timestamp in the log_line_prefix setting to have useful logs to look at. See my tuning intro to get started here: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Postgres does all of its writes to the operating system cache, then later they head to disk. You can watch that by turning on log_checkpoints and reading the messages. When things slow down, it may simply be that all the caches are now full, and all writes are stuck waiting for the slowest part of I/O. You might improve this by changing the Postgres checkpoint settings.

There's an internal issue with the database people hit sometimes where heavy inserts get stuck waiting for a lock in the database. Turn on log_lock_waits to see if you're hitting that one.

Sometimes the rate you can do burst inserts at is higher than you can sustain once the system autovacuum process kicks in. Turn on log_autovacuum to see if the problems are concurrent with when it's happening.

We know that large amount of memory in the database's private shared_buffers cache doesn't work as well on Windows as it does on other operating systems. There isn't as much visibility into what goes wrong when it happens either. I would not try to host something that's doing 1000+ inserts a second to a Windows PostgreSQL database. It's just not a good platform for really heavy writes yet.

0

I'm no Postgres expert so this might be wrong! Your primary key has 3 columns, sessionID as the first field. Does the file contain a decent spread of timestamps? you might consider making that the first field in the primary key or using a surrogate key as currently this is fairly wide.

From your script I dont think you have a cluster. Different to SQL Server but I think you have to specify with the physical ordering of the table in Postgres with the 'Cluster' command. The link talks about this :

https://stackoverflow.com/questions/4796548/about-clustered-index-in-postgres

  • Postgres does not have clustered indexes, so the "fairly wide" for the PK isn't really a problem as it would be with SQL Server. – a_horse_with_no_name Aug 27 '14 at 12:37
  • Thanks for replying, none of those columns are unique individually which is why we made it in this way – jamesmc86 Aug 27 '14 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.