|[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
[Postgresql General] [Postgresql Performance] [Postgresql PHP] [Postgresql Jobs] [PHP Users] [PHP Databases] [PHP Home] [PHP on Windows] [Kernel Newbies] [PHP Classes] [PHP Databases] [Yosemite]