Обсуждение: corruption in indexes under heavy load
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
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
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