On 4/16/15 4:39 PM, Andomar wrote:
> Thanks for your reply.
>> This issue has been complained several times, and here is the most
>> recent one:
>> http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name
>>
> That post is about a server with huge shared_buffers, but ours is just
> 8GB. Total memory 48GB memory on a dedicated server. Checkpoints write
> around 2% of the buffers.
Yeah, I suspect the OP in that thread was seeing something different
than you are, but that's just a guess.
>> PG 9.4.1 shall have much alleviated it by relaxing buffer pool related
>> locks. PG 9.4.1 shall be actually better in relation extension
>> handling - a possible explanation is that your 9.3.5 database has been
>> used for a while thus there are holes in pages, so not many extensions
>> are required.
> The 9.3.5 version went live as an upgrade from 9.1.x in the same way. So
> it started from an SQL dump. The load has not changed much since the
> 9.3.5 upgrade.
Yes, but did you have the same workload when you upgraded to 9.3 as you
do today?
> With holes in pages, I suppose you mean the fill factor?
The fill factor plays a role in whether a page has free space, yes,
*especially* during an initial load (like restoring from pg_dump). Keep
in mind that as the system runs you're going to be creating free space
in pages as data is updated or deleted. So the amount of free space per
page on the 9.3 database you moved from wouldn't be anything like what
it was when you moved to 9.3.
> Is there a way
> to see the current fillfactor of a table and its indices?
Possibly the bloat query
(https://wiki.postgresql.org/wiki/Show_database_bloat) would. The page
inspect utility certainly would tell you. Possibly more useful would be
to see what the pg_freespacemap contrib module shows in the free space
map, since that's what the system will actually use to see where it can
find a page to insert data on.
BTW, something else to be aware of: because you essentially re-loaded
all your data in a single transaction, that means that a: you'll be
doing a lot of hint bit updates until all data has been read a second
time, and b: a lot of this data will come up for freezing at around the
same time, creating a big chunk of work for autovacuum. That's caused
problems for me in the past, though that was on a database that had a
pretty high workload.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
