Return to Question

• When binding a multi-line prepared statement within a single transaction block (see code), it takes 390 sec to INSERT the same 11,117 rows into each of the 28 partitioned tables for total throughput of 798 rows/ sec (compared to the 22,234 rec/sec in the baseline)

• The max/avg/min values from vmstat -wt 1 during the INSERT resulting from DBI::dbBind() appear as follows:

   procs -----------------------memory---------------------- ---swap-- -----io---- --system-- --------cpu--------
   r  b         swpd         free         buff        cache   si   so    bi    bo    in    cs  us  sy  id  wa  st     
   6  1            0    112348416       200092     15661572    0    0     0 10090 12259 37796  32   3  81   4   0   # MAX
   3  0            0    111860781       199437     15608955    0    0     0  1708  5658  6371  21   1  78   0   0   # AVG
   3  0            0    111353344       199372     15572860    0    0     0   184  4115  1602  17   0  67   0   0   # MIN
  

• When binding a multi-line prepared statement within a single transaction block (see code), it takes 390 sec to INSERT the same rows into each of the 28 partitioned tables for total throughput of 9 rows/ sec (compared to the 22,234 rows/sec in the baseline)

• The max/avg/min values from vmstat -wt 1 during the INSERT resulting from DBI::dbBind() appear as follows:

   procs -----------------------memory---------------------- ---swap-- -----io---- --system-- --------cpu--------
   r  b         swpd         free         buff        cache   si   so    bi    bo    in    cs  us  sy  id  wa  st     
   6  1            0    112348416       200092     15661572    0    0     0 10090 12259 37796  32   3  81   4   0   # MAX
   3  0            0    111860781       199437     15608955    0    0     0  1708  5658  6371  21   1  78   0   0   # AVG
   3  0            0    111353344       199372     15572860    0    0     0   184  4115  1602  17   0  67   0   0   # MIN
  

• Performance unaffected when running single or multiple R child threads (ie. one or multiple database client connections).
• Have tried to use RPostgres, RPostgreSQL, and odbc R packages as database drivers (while slight performance differences exist among the drivers, no implementation-specific artifacts appear to contribute significantly to the processing time).
• Working on EXPLAIN ANALYZE of SQL, but welcome thoughts ahead of time on potential kernel settings/ database configuration/ SystemD settings/ etc.

• Performance unaffected when running single or multiple R child threads (ie. one or multiple database client connections).
• Have tried to use RPostgres, RPostgreSQL, and odbc R packages as database drivers (while slight performance differences exist among the drivers, no implementation-specific artifacts appear to contribute significantly to the processing time).
• Working on EXPLAIN ANALYZE of SQL, but welcome thoughts ahead of time on potential kernel settings/ database configuration/ SystemD settings/ etc.

##Update (2018.11.28)##

• Created an SQL flat file with the INSERT statements produced by the R script (placed the statements within a BEGIN ... COMMIT transaction block) and loaded using psql -f <file>.sql -- no performance improvement.

• Ran EXPLAIN ANALYZE VERBOSE on single INSERT, with following output:

   Insert on <tablename>  (cost=0.00..0.26 rows=1 width=1952) (actual time=0.074..0.074 rows=0 loops=1)
      ->  Result  (cost=0.00..0.26 rows=1 width=1952) (actual time=0.006..0.007 rows=1 loops=1)
            Output: <row data>
   Planning time: 0.303 ms
   Trigger RI_ConstraintTrigger_c_274329 for constraint <foreign_key_constraint_name> on <child_tablename>: time=0.071 calls=1
   Execution time: 13.345 ms