1

I am trying to improve the performance of Postgresql (version 9.2.3) inserts for a simple table with 1 bigint, 1 varchar, 1 float and 2 time stamps.

A simple replication of my JDBC program is attached. Here are the important points I want to mention:

  1. I am running this program on the same system which hosts the PostgreSQL DB. (64 GB RAM and 8 CPUs.)
  2. I am using INSERT statements AND I DO NOT want to use COPY statement. I have read and understand the COPY performs better but I am tuning the insert performance here.
  3. I am using PreparedStatement.addbatch() and executeBatch() to insert in batches of 1000's

The performance of the insert scales well when I increase the batch size but flattens out at around a batch size of 8000. What I notice is that the postgresql thread on the system is CPU saturated as observed by the "top" command. The CPU usage of the postgres thread steadily increases and tops out at 95% when the batch size reaches 8K. The other interesting thing I notice is that it is using only up to 200MB of RAM per thread.

In comparison an Oracle DB scales much better and the the same number of insets with comparable batch sizes finish 3 to 4 times faster. I logged on to the Oracle DB machine (Sun Solaris machine) and noticed that the CPU utilization peaks out at a much bigger batch size and also each Oracle thread is using 6 to 8 GB of memory.

Given that I have memory to space is there a way to increase the memory usage for a postgres thread for better performance?

Here are my current postgresql settings:

temp_buffers = 256MB                
bgwriter_delay = 100ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4
maintenance_work_mem = 2GB              
shared_buffers = 8GB                    
vacuum_cost_limit = 800
work_mem = 2GB
max_connections = 100

checkpoint_completion_target = 0.9
checkpoint_segments = 32
checkpoint_timeout =10min
checkpoint_warning =1min

wal_buffers = 32MB
wal_level = archive

cpu_tuple_cost = 0.03
effective_cache_size = 48GB
random_page_cost = 2

autovacuum = on
autovacuum_vacuum_cost_delay = 10ms
autovacuum_max_workers = 6
autovacuum_naptime = 5
autovacuum_vacuum_threshold = 100
autovacuum_analyze_threshold = 100
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_limit = -1

Here are the measurements:

Time to insert 2 million rows in postgreql.

batch size - Execute batch time (sec)
1K - 73
2K - 64
4K - 60
8K - 59
10K - 59
20K - 59
40K - 59

Time to insert 4 million rows in Oracle.

batch size - Execute batch time (sec)
1K - 14
2K - 12
4K - 10
8K - 8.9
10K - 8.4

As you can see Oracle is inserting a 4 million row table much faster than Postgresql.

Here is the snippet of the program I am using for insertion.

conn.setAutoCommit(false);
stmt.executeUpdate("CREATE TABLE "
                + tableName
                + " (P_PARTKEY bigint not null, "
                + " P_NAME varchar(55) not null, "
                + " P_RETAILPRICE float not null, "
                + " P_TIMESTAMP Timestamp not null, "
                + " P_TS2 Timestamp not null)");

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO " + tableName + " VALUES (?, ?, ?, ?, ? )");

        for (int i = start; i <= end; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, "Magic Maker " + i);
            pstmt.setFloat(3, i);
            pstmt.setTimestamp(4, new Timestamp(1273017600000L));
            pstmt.setTimestamp(5, new Timestamp(1273017600000L));

            pstmt.addBatch();
            if (i % batchSize == 0) {
                pstmt.executeBatch();
            }
        }

Here is the disk info: Western Digital WD6000HLHX 600GB 10000 RPM 32MB Cache SATA 6.0Gb/s 3.5" Internal Hard Drive

Here is the iostat output when the test is running.

 avg-cpu:  %user   %nice %system %iowait  %steal   %idle
 12.78    0.00    0.38    0.25    0.00   86.59

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00  1182.00    0.00   28.00     0.00  9624.00   343.71     0.12    4.43   4.11  11.50

As you can see from the %idle, %util and the avgqu-sz the system is not IO bound.

  • 1
    So you are comparing apples to oranges hardware-wise, right? Nonetheless, you can try parallelizing the batches (using threads, I guess). BTW, may I ask why you don't want to use COPY? – dezso Jul 27 '15 at 19:36
  • "AND I DO NOT want to use COPY statement" translates (for me) to: "I want it to be faster but I don't want to use the method that is guaranteed to be faster" – a_horse_with_no_name Jul 27 '15 at 19:46
  • I also don't think the memory usage is any indicator of a good or bad resource consumption. As matter of fact I'm surprised that the Oracle processes (Oracle doesn't use threads on non-windows systems) use that much memory for plain inserts. Why would an insert need that much memory, there is no sorting or similar things going on. One thing you could try is to use batched multi-row inserts (insert into .. values (..), (...), (...)) in Postgres. – a_horse_with_no_name Jul 27 '15 at 19:53
  • Our server product uses different methods for copying data - insert is one of them and copy is another one. We are happy with the copy performance and it is faster than insert. We are trying to tune the insert performance here. – Maddy Jul 27 '15 at 19:54
  • 1
    You might also want to try out Postgres 9.4 - there were several changes that might give you better insert performance. wal_buffers = 32MB seems also on the high side. Is there any reason you changed that away from the auto-tuning value of -1. work_mem = 2GB seems too high as well (although irrelevant for inserts). Remember that work_mem can be request several times by each statement that is running. I have also seen discussions where reducing shared_buffers lead to a better write performance. – a_horse_with_no_name Jul 27 '15 at 20:58

Your Answer

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

Browse other questions tagged or ask your own question.