Benchmarking 8.4 - Chapter 2/bulk loading

Bulk loading requirements can appear in various different ways. People might want to load CSV style data from external sources, would like to populate development databases with vast amounts of test data to get and idea on scalability of their application or they might simply want to restore their database after the losing their server.
8.4 ships with an enhanced pg_restore that is capable of restoring custom dumps generated with pg_dump -Fc in parallel. This - in combination with the trend to multiple cores - means that bulk loading under concurrent scenarios is getting more and more interesting and deserves some time spent on.

The testbed here is the same as the one mentioned in the first Chapter so I wont spend much time in repeating that information. All the following benchmarks use COPY which is the PostgreSQL specific way to load large amounts of data. given the fact that COPY (or \copy in psql) is not always a feasible solution I might do some further testing using multi value INSERTS and transactional batching in a later post. All the tables used here are NOT indexed - testing this is planned for a future post as well.

The data loaded here is what is generated by the dbgen utility from Mark Wongs DBT3 project. The file containing the test data contains ~60M rows and is used multiple times(coming out of the OS buffer cache). This means that in the case of say 16 processes a total of 60000000*16=960000000 rows got loaded, so the larger the process count the more amount of data PostgreSQL had to deal with in total.
This might have had an influence on the following test results(especially at higher connection counts), however given that we loaded up to 10GB/min(or 600GB per hour) in the best case this was kind of needed to get not-to-short run-times for the higher connection counts.

The schema for that table is:

              Table "public.lineitem1"
     Column      |         Type          | Modifiers 
-----------------+-----------------------+-----------
 l_orderkey      | integer               | 
 l_partkey       | integer               | 
 l_suppkey       | integer               | 
 l_linenumber    | integer               | 
 l_quantity      | real                  | 
 l_extendedprice | real                  | 
 l_discount      | real                  | 
 l_tax           | real                  | 
 l_returnflag    | character(1)          | 
 l_linestatus    | character(1)          | 
 l_shipdate      | date                  | 
 l_commitdate    | date                  | 
 l_receiptdate   | date                  | 
 l_shipinstruct  | character(25)         | 
 l_shipmode      | character(10)         | 
 l_comment       | character varying(44) | 

and for those who are curious some sample data:

3|293797|18800|4|2|3581.56|0.01|0.06|A|F|1993-12-04|1994-01-07|1994-01-01|NONE                     |TRUCK     |fluffily unusual deposits hag
3|1830941|5996|5|28|52411.8|0.04|0|R|F|1993-12-14|1994-01-10|1994-01-01|TAKE BACK RETURN         |FOB       |blithely pending platelets according
3|621426|96445|6|26|35032.1|0.1|0.02|A|F|1993-10-29|1993-12-18|1993-11-04|TAKE BACK RETURN         |RAIL      |ideas until the unusual packages c

The following graph shows three different benchmark runs each done at a connection count of 1,2,4,8,12 and 16. The one labeled "single table" was creating by loading the data concurrently into the same table. The one labeled "multiple tables" loads into one separate table per connection (ie. process one loads into table "lineitem1", the second one into "lineitem2" and so on). The last one does the same as the multiple table test except that it makes use of the fact that recent versions of PostgreSQL can bypass writing to the WAL altogether under certain circumstances. In this test the was done wrapping the COPY into a "BEGIN; TRUNCATE lineitem1; COPY FROM ... COMMIT;".

The first thing this graph tells us that the WAL overhead is HUGE. It also shows that there is not that much a difference between loading into the same table vs. loading data into separate ones.
On the other side it is already clearly visible that only the WAL-bypass case seem to be able to scale reasonably to all the available cores - the test cases logging to the WAL reach their peak performance at around half the available cores and significantly degrades afterwards.

On a closer look we can see that the scaling problem for concurrent copy with WAL logging already starts with the second concurrent process inserting data. In the following graph I ploted the theoretical scaling (based on multiplying the single connection performance with the number of available cores/threads):

The main scalability issue here seems to be internal locking in PostgreSQL - namely the XLogInsert lock which is showing up on top in a lot of other update/insert heavy workloads as well. The second process only results in a 50% performance increase and with 8 cores we are reaching the performance peak with a bit better than 3x the single core performance. At higher connection counts performance degrades again with the server showing around 70% idle time and abysmal performance at 16 connections.

The profile for the server at 8 connections looks fairly similar to the following:

Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
samples  %        symbol name
47546    15.4562  XLogInsert
39598    12.8725  DoCopy
33798    10.9870  CopyReadLine
14191     4.6132  DecodeNumber
12986     4.2215  heap_fill_tuple
12092     3.9308  pg_verify_mbstr_len
9553      3.1055  DecodeDate
9289      3.0197  InputFunctionCall
7972      2.5915  ParseDateTime
7324      2.3809  DecodeDateTime
7290      2.3698  pg_next_dst_boundary
7218      2.3464  heap_form_tuple
5385      1.7505  AllocSetAlloc
4779      1.5536  heap_compute_data_size
4367      1.4196  float4in
3903      1.2688  DetermineTimeZoneOffset
3603      1.1713  pg_mblen
3494      1.1358  pg_atoi
3461      1.1251  .plt
3428      1.1144  date2j
3416      1.1105  pg_mbstrlen_with_len

As one can see in the first graph PostgreSQL is able to avoid writing to the WAL under certain circumstances resulting in a significant performance increase.

This graph shows much better scaling behavior - it gives at near perfect scaling up to 4 cores and reaches an 8x speedup at 16 cores without the degradation at higher connection counts as seen in the former tests. This is also what the new pg_restore in 8.4 will do in most circumstances. With 16 connections the profile during that test looks like:

Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
samples  %        symbol name
2462358  15.3353  DoCopy
2319677  14.4467  CopyReadLine
806269    5.0214  pg_verify_mbstr_len
782594    4.8739  DecodeNumber
712426    4.4369  heap_fill_tuple
642893    4.0039  DecodeDate
609313    3.7947  InputFunctionCall
476887    2.9700  ParseDateTime
456569    2.8435  pg_next_dst_boundary
435812    2.7142  DecodeDateTime
429733    2.6763  heap_form_tuple
361750    2.2529  heap_compute_data_size
301193    1.8758  AllocSetAlloc
268830    1.6742  float4in
238119    1.4830  DetermineTimeZoneOffset
229598    1.4299  pg_atoi
225169    1.4023  .plt
217817    1.3565  pg_mbstrlen_with_len
207041    1.2894  PageAddItem
200024    1.2457  pg_mblen
192237    1.1972  bpchar_input
181552    1.1307  date2j

At this load rate the server is really busy with almost no idle time left but still only showing iowait in the single digit percentage range. It almost seems that there is still opportunity left to optimize here :)