On Sun, Aug 21, 2016 at 11:53 PM, Павел Филонов <filonovpv@xxxxxxxxx> 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
