corruption in indexes under heavy load

Started by Russell Keaneover 9 years ago3 messagesgeneral
Jump to latest
#1Russell Keane
Russell.Keane@inps.co.uk

We're writing a large amount of data to a number of tables in PG 9.3 on Windows Server 2012 R2 and then, immediately after, creating a number of indexes (there are no indexes during the initial data write). The data we're writing exists in files on the same drive as PG's data.
During the index creation we're seeing write latency on the storage up to 1500ms (during the data write it may go up to 200ms, possibly due to reading the data from the same storage). The write latency is being investigated separately as there's no way it should ever get that high.

There is no indication in the PG log that anything is amiss while creating the indexes and there are no clients accessing or updating the data at this time, although there are a number of instances of "pgstat wait timeout" around the time of the high write latency. Therefore we must assume that the indexes were created successfully.

We're fairly convinced the issue lies with the actual storage but I was wondering if there is anything within PG that would be affected by the high latency and result in corrupt indexes.

Regards,
Russell

________________________________
Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
The information in this internet email is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is not authorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of INPS or any of its affiliates. If you are not the intended recipient please contact is.helpdesk@inps.co.uk

#2Simon Riggs
simon@2ndQuadrant.com
In reply to: Russell Keane (#1)
Re: corruption in indexes under heavy load

On 25 August 2016 at 09:50, Russell Keane <Russell.Keane@inps.co.uk> wrote:

We’re fairly convinced the issue lies with the actual storage but I was
wondering if there is anything within PG that would be affected by the high
latency and result in corrupt indexes.

Nothing we know of, at this time.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

#3John R Pierce
pierce@hogranch.com
In reply to: Russell Keane (#1)
Re: corruption in indexes under heavy load

On 8/25/2016 1:50 AM, Russell Keane wrote:

We’re writing a large amount of data to a number of tables in PG 9.3
on Windows Server 2012 R2 and then, immediately after, creating a
number of indexes (there are no indexes during the initial data
write). The data we’re writing exists in files on the same drive as
PG’s data.

During the index creation we’re seeing write latency on the storage up
to 1500ms (during the data write it may go up to 200ms, possibly due
to reading the data from the same storage). The write latency is being
investigated separately as there’s no way it should ever get that high.

assuming this server has lots of ram, you might try setting
maintenance_work_mem to 1gb and trying those index creations again.
this hugely speeds up the sorting process that create index has to do
(it also speeds up vacuuming large tables).

--
john r pierce, recycling bits in santa cruz