Thread: Why insertion throughput can be reduced with an increase of batch size?
My greetings to everybody!
I recently faced with the observation which I can not explain. Why insertion throughput can be reduced with an increase of batch size?Brief description of the experiment.
- PostgreSQL 9.5.4 as server
- https://github.com/sfackler/rust-postgres library as client driver
- one relation with two indices (scheme in attach)
Experiment steps:
- populate DB with 259200000 random records
- start insertion for 60 seconds with one client thread and batch size = m
- record insertions per second (ips) in clients code
Plot median ips from m for m in [2^0, 2^1, ..., 2^15] (in attachment).
On figure with can see that from m = 128 to m = 256 throughput have been reduced from 13 000 ips to 5000.
I hope someone can help me understand what is the reason for such behavior?
--
Best regards
Filonov Pavel
Attachment
On Sun, Aug 21, 2016 at 11:53 PM, Павел Филонов <filonovpv@gmail.com> wrote:
My greetings to everybody!I recently faced with the observation which I can not explain. Why insertion throughput can be reduced with an increase of batch size?Brief description of the experiment.
- PostgreSQL 9.5.4 as server
- https://github.com/sfackler/
rust-postgres library as client driver - one relation with two indices (scheme in attach)
Experiment steps:
- populate DB with 259200000 random records
How is populating the database you do in this step different from the insertions you do in the next step? Is it just that the goal is to measure insertions into an already-very-large table?
- start insertion for 60 seconds with one client thread and batch size = m
- record insertions per second (ips) in clients code
Plot median ips from m for m in [2^0, 2^1, ..., 2^15] (in attachment).
The median of how many points? Please plot all points, as well as the median. In what order did you cycle through the list of m? Are you logging checkpoints? how many checkpoints occur during the run for each batch size? Have you tuned your database to be targeted at mass insertions? e.g.. what are max_wal_size, archive_mode, wal_level, wal_buffers, shared_buffers, and checkpoint_completion_target? Are you issuing manual checkpoints between runs?
60 seconds is usually not nearly enough time to benchmark a write-heavy workload. The chances are pretty good that what you are seeing is nothing but statistical artefacts, caused by checkpoints happening to line up with certain values of batch size.
Cheers,
Jeff
Re: Why insertion throughput can be reduced with an increase of batch size?
From
Adrian Klaver
Date:
On 08/21/2016 11:53 PM, Павел Филонов wrote: > My greetings to everybody! > > I recently faced with the observation which I can not explain. Why > insertion throughput can be reduced with an increase of batch size? > > Brief description of the experiment. > > * PostgreSQL 9.5.4 as server > * https://github.com/sfackler/rust-postgres library as client driver > * one relation with two indices (scheme in attach) > > Experiment steps: > > * populate DB with 259200000 random records > * start insertion for 60 seconds with one client thread and batch size = m > * record insertions per second (ips) in clients code > > Plot median ips from m for m in [2^0, 2^1, ..., 2^15] (in attachment). > > > On figure with can see that from m = 128 to m = 256 throughput have been > reduced from 13 000 ips to 5000. > > I hope someone can help me understand what is the reason for such behavior? To add to Jeff's questions: You say you are measuring the IPS in the clients code. Where is the client, on the same machine, same network or remote network? > > -- > Best regards > Filonov Pavel > > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: Why insertion throughput can be reduced with an increase of batch size?
From
Adrian Klaver
Date:
On 08/22/2016 11:49 PM, Павел Филонов wrote: Please reply to list also. Ccing list > > > 2016-08-23 4:02 GMT+03:00 Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>: > > On 08/21/2016 11:53 PM, Павел Филонов wrote: > > My greetings to everybody! > > I recently faced with the observation which I can not explain. Why > insertion throughput can be reduced with an increase of batch size? > > Brief description of the experiment. > > * PostgreSQL 9.5.4 as server > * https://github.com/sfackler/rust-postgres > <https://github.com/sfackler/rust-postgres> library as client driver > * one relation with two indices (scheme in attach) > > Experiment steps: > > * populate DB with 259200000 random records > * start insertion for 60 seconds with one client thread and > batch size = m > * record insertions per second (ips) in clients code > > Plot median ips from m for m in [2^0, 2^1, ..., 2^15] (in > attachment). > > > On figure with can see that from m = 128 to m = 256 throughput > have been > reduced from 13 000 ips to 5000. > > I hope someone can help me understand what is the reason for > such behavior? > > > To add to Jeff's questions: > > You say you are measuring the IPS in the clients code. > > Where is the client, on the same machine, same network or remote > network? > > > Postgres runs in docker and client runs on the host machine. Client > connects to localhost interface. I don't use Docker so I have no insights here. > > > -- > Best regards > Filonov Pavel > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > > -- > С наилучшими пожеланиями, > Филонов Павел -- Adrian Klaver adrian.klaver@aklaver.com