0

I have a simple (but large) postgres database.

I have a table defined like this….

CREATE TABLE TABLE_A (
  MessID      SERIAL UNIQUE,
  rawData     CHAR(250),               
  loadedTime  TIMESTAMP)

And a process that continuously loads millions of records (each one a variable length ASCII string) into the table. There’s an index on loadedTime. That’s it, no triggers, nothing fancy. All day records get loaded in, at night they get processed. The night-time processing only takes a few hours, and since I’ve got all night I’m not worried about the performance then.

What’s is of critical importance is the rate at which I can insert records during the day.

I’ve managed to get the time down from about 10ms/record to about 1.5m/s record. I’d like improve performance further (maybe 1 ms/record? Or quicker?)

The following changes have made improvements….

  • Partitioned my data by date/time range.
  • Kept ‘current’ data on an SSD tablespace, and ‘archive’ data on a magnetic drive
  • Tweaked both the number of records added in a single INSERT statement, and the number of INSERTs in each transaction

I’ve also tried tweaking postgres.conf using pgtune.leopard.in.ua. To be honest, none of the changes to this file have made any noticeable difference. The current settings are…

# DB Version: 10
# OS Type: windows
# DB Type: oltp
# Total Memory (RAM): 16 GB
# CPUs num: 1
# Connections num: 10
# Data Storage: ssd

max_connections = 10
shared_buffers = 512MB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 541764kB
min_wal_size = 2GB
max_wal_size = 4GB

My questions are…

  1. Why haven’t the hardware and config changes made any noticeable difference?
  2. Are there any other areas for performance tuning I should be looking at?
  3. If I were to upgrade the server the database is on, what factors should I look for in new one – bearing in mind that increasing the memory doesn’t seem to make the slightest bit of difference.

Other information that may be relevant.

  • I’m only interested in the speed at which records get inserted during the day.
  • During daytime operations there is a single connection to the database (doing the INSERTs) and no other processing.
  • The existing server is a 16 GByte Windows pro single processor with Postgres 10 installed on an SSD and archive tablespaces on a magnetic drive.
  • TABLE_A is partitioned by day. Todays data is held on a tablespace on the SSD, at night it’s archived to tablespace on magnetic disc
  • Incoming records are added using a powershell script. The INSERT statement is wrapped in a stopwatch function which allows me to accurately record how long each INSERT takes and work out the time per record.
  • Incoming records are arriving one-by-one from an external server, over which I have no control, so using COPY wouldn't help.
  • Spooling the records to a temporary file prior to loading hasn't worked either since it seems to take longer to append to a file than it does to insert into a database.
  • It currently takes 1.5ms to insert a record, so the system can cope with a maximum of between 5 and 700 hundred records/sec. The problem is that the source can, at busy periods, generate 800-900 records per second.

UPDATE in response to horse_with_no_name. Is it number of processors, or number of cores that effect the number of simultaneous INSERTS that Postgres can cope with? My processor is (I quote) "Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz, 2601 Mhz, 4 Core(s), 8 Logical Processor(s)"

Having 4 Core(s), does that mean I ought to be able support 4 simultaneous processes doing INSERTs at the same time? Could I re-write my powershell script to round-robin the incoming records across 2 (or more) connections and improve INSERT performance that way?

  • Yes, with 8 "logical" processors you should be able to do 8 concurrent inserts at the same time. Alternatively you could batch the inserts. Instead of doing single-row inserts, create one insert statement that inserts multiple rows at a time. You are also limited by the time it takes to open a connection to the database server - something that takes several milliseconds as well (definitely a lot more than the 1ms you are trying to achieve). – a_horse_with_no_name Sep 27 '18 at 8:37
  • thanks @horse_with_no_name I'm already doing the the batching of inserts (item 3 in my first list) I'll try working with multiple connections and round-robin distributing the records between the connections – ConanTheGerbil Sep 27 '18 at 8:44
  • "Incoming records are arriving one-by-one from an external server, over which I have no control, so using COPY wouldn't help." I don't see how the conclusion follows. You can COPY FROM STDIN, not just from real files. – jjanes Sep 29 '18 at 18:35
0

I think some of these suggestions may help, esp. using COPY instead of INSERT.

https://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql

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.