CODEWITHSUNDEEP
postgresqlcentoscentos7postgresql-9.5
Duy Huynh
Duy Huynh

Reputation: 291

Increase data insert speed of PostgreSQL

I am stuck in a problem that PostgreSQL data writes are very slow.

I developed my application in Java (using JDBC) to insert data into a PostgreSQL DB. It works well on our remote development server. However, after I deploy it to the production server, it causes a problem.

The insert speed of PostgreSQL on the production server is only ~150 records/s for 200000K records, while it is ~1000 records/s for the same data set on the development server.

Firstly, I tried to change the configuration in postgresql.conf as follows:

effective_cache_size = 4GB
max_wal_size = 2GB
work_mem = 128MB
shared buffers = 512MB

After I changed the configuration and restarted, it only affects the query speed, while the insert speed does not change (~150 records/s).

I have checked my server memory info, there is a lot of free memory ~4GB. The inserter only uses 0.5% of 8GB (~40MB).

So my questions are:

Is this a problem of a storage disk, such as SSD and HDD or virtual and physical etc.? Why is the insert speed still very slow, although I have changed the configuration? Is there any way for increasing the insert speed?

Note: the problem does not relate to the insert query structure. I have used the same query in the same condition elsewhere (I set up an environment in 2 servers in the same way). I do not know why the DEVELOPMENT server (4GB) works better than the PRODUCTION server (8GB).

Upvotes: 3

Views: 7672

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

The only one of your parameters that has an influence on INSERT performance is max_wal_size. High values prevent frequent checkpoints.

Use iostat -x 1 on the database server to see how busy your disks are. If they are quite busy, you are probably I/O bottlenecked. Maybe the I/O subsystem on your test server is better?

If you are running the INSERTs in many small transactions, you may be bottlenecked by fsync to the WAL. The symptom is a busy disk with not much I/O being performed.

In that case batch the INSERTs in larger transactions. The difference you observe could then be due to different configuration: Maybe you set synchronous_commit or (horribile dictu!) fsync to off on the test server.

Upvotes: 7

Related Questions