COPY命令运行缓慢

Question

• HDD,
• Default postgresql config file
• No other connections.

I have a table:

CREATE TABLE "public"."ParamValueBlock" (
  "ParameterId" int2 NOT NULL,
  "DeviceId" int2 NOT NULL,
  "CompressedData" bytea,
  "StartDate" int4 NOT NULL,
  "UncompressedDataBits" int4 NOT NULL
)

This is my code for batch copy to database:

connectionString := fmt.Sprintf("host=%s port=%d user=%s "+
        "password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)
db, err := sql.Open("postgres", connectionString)
if err != nil {
    return err
}
defer db.Close()

tx, err := db.BeginTx(context.Background(), &sql.TxOptions{Isolation: sql.LevelReadCommitted, ReadOnly: false})
if err != nil {
    return err
}

stmt, err := tx.Prepare(`COPY "ParamValueBlock" ("ParameterId", "DeviceId", "CompressedData", "StartDate", "UncompressedDataBits") FROM STDIN;`)
if err != nil {
    return err
}

for _, item := range items{
    _, err := stmt.Exec(
        int16(item.paramID),
        1,
        item.dataBuffer,
        item.secondsSince2015,
        int32(item.uncompressedBitsSize))
    if err != nil {
        tx.Rollback()
        return err
    }
}

err = stmt.Close()
if err != nil {
    tx.Rollback()
    return err
}

err = tx.Commit()
if err != nil {
    return err
}
return nil

I am writing 1000 items (1 item = 28KB) and this is 7 seconds.

Why so slow and how can i optimize this?

If do it is from file:

COPY "ParamValueBlock" FROM 'C:\Temp\x.txt' (FORMAT text);

time is 0.7 seconds

Profile cpu:

      flat  flat%   sum%        cum   cum%
     1.82s 31.06% 31.06%      1.83s 31.23%  runtime.cgocall
     0.35s  5.97% 37.03%      0.36s  6.14%  ...pq.appendEscapedText
     0.30s  5.12% 42.15%      0.76s 12.97%  fmt.(*fmt).fmtInteger
     0.30s  5.12% 47.27%      1.53s 26.11%  fmt.(*pp).doPrintf
     0.14s  2.39% 67.92%      2.98s 50.85%  fmt.Sprintf
     0.11s  1.88% 76.11%      0.97s 16.55%  fmt.(*pp).printArg
     0.10s  1.71% 77.82%      0.19s  3.24%  fmt.(*buffer).Write (inline)
     0.09s  1.54% 79.35%      0.85s 14.51%  fmt.(*pp).fmtInteger
     0.09s  1.54% 80.89%      3.26s 55.63%  github.com/lib/pq.encodeBytea
     0.01s  0.17% 94.37%      5.20s 88.74%  github.com/lib/pq.(*copyin).Exec

</div>

Answer 1

This is pg driver bug: https://github.com/lib/pq/pull/784

I was replace github.com/lib/pq to github.com/jackc/pgx/stdlib and set this server configuration in postgresql.conf:

autovacuum_vacuum_scale_factor = 0.0  
autovacuum_vacuum_threshold = 5000
autovacuum_analyze_scale_factor = 0.0  
autovacuum_analyze_threshold = 5000  
synchronous_commit = off
wal_level = 'minimal'
archive_mode = off
max_wal_senders = 0

And this increase insert speed by 60%

Additional information

1. use PREPARE with INSERT or COPY
2. Stmt must be closed after transaction
3. pgx COPY is only supported through the native(no stdlib) interface. You can use AcquireConn and ReleaseConn to get a *pgx.Conn from the database/sql pool.

My sample for inserting many rows:

func InsertParamValueBlocks(params []model.ParamValueBlock) error {
    var dbWrk db.Worker
    var stmt *sql.Stmt
    var err error
    result := dbWrk.DoInTransaction(sql.LevelDefault, func() error {
        stmt, err = dbWrk.Context.Prepare(`INSERT INTO "ParamValueBlock" ("ParameterId", "DeviceId", "CompressedData", "StartDate", "UncompressedDataBits")VALUES ($1, $2, $3, $4, $5);`)
        if err != nil {
            return err
        }

        for _, param := range params {
            _, err := stmt.Exec(
                param.ParameterID,
                param.DeviceID,
                param.CompressedData,
                param.StartDate,
                param.UncompressedDataBits)
            if err != nil {
                return err
            }
        }

        return nil
    })
    //stmt close after transaction
    err = stmt.Close()
    if err != nil {
        return err
    }
    return result
}

</div>