buffers_backend climbing during data importing, bad thing or no biggie?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all,

I have a database that we've been doing some load testing on. The performance isn't quite where we want it to be, and while I don't believe it's a database issue, I'm doing what I can to help speed things up.

We started off with an empty database, and started off our benchmarking test. After 30 minutes, the database size grew to around 400MB, not very large at all. While we did the test I grabbed some statistics and found some insteresting numbers in pg_stat_bgwriter.

Over the 30 minute test, there were 0 buffers written by checkpoints (I have my checkpoint timeout set to 30 minutes and checkpoint segments to 64, no checkpoints occurred during our test), 0 buffers written by the background writer, and 44,000 "buffers written by backends (that is, not by the background writer)"

From my understanding, this means that during the insert, the data is being inserted directly to disk immediately vs being taken care of later by the background writer and checkpoints. Is this just expected and unavoidable behavior when inserting data?

Again, this isn't a ton of data and the database is performing fast from what I've observed. But if there is a way that during a large data import process we can have buffers not be written directly to disk during these large inserts and instead be taken care of later, that it would be ideal.

The application we tested basically has thousands of tasks to do and can only do 80 or so of them at any given time, so any milliseconds I can save per transaction will help timing out in the end.

My setup:
OS: CentOS 6 Linux 64 bit
PostgreSQL Version: 9.1.2 Installed from source.
14 GB system memory

work_mem =50MB
shared_buffers = 4GB
checkpoint_timeout = 30 minutes
checkpoint_completion_target = 0.5 (default)
bgwriter_delay = 200ms (default)
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 2 (default)



--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux