pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpt_toast table seems to be
7+ messages / 4 participants
[nested] [flat]
* pt_toast table seems to be
@ 2018-01-03 15:25 Jorge Daniel <elgaita@hotmail.com>
2018-01-03 15:36 ` Re: pt_toast table seems to be Tom Lane <tgl@sss.pgh.pa.us>
2018-01-03 19:23 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
0 siblings, 2 replies; 7+ messages in thread
From: Jorge Daniel @ 2018-01-03 15:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi guys
This is my first post in the comunity so my apologies in advance about the formalities.
In a production db , and after several OOM killer events on the postgres running on a docker (now fixed) we remain with a kind of corrupted pg_toast đ:
2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:ERROR: unexpected chunk number 0 (expected 1) for toast value 17143928 in pg_toast_77809
2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:STATEMENT: SELECT "id", "needs_pumping", "needs_lucene", "surveybatchid", "ip_address", "is_open_proxy", "is_partial", "cookies", "social_media_source", "social_media_review_id", "social_media_pull_id", "social_media_pull_id_long", "source_config_unitid", "source_config_source_id", "updated", "external_id_printable_coder", "unique_reusable_id", "invite_errmsg", "reminder_errmsg", "last_seen_pagename", "last_submitted_pagename", "has_content_fields", "logid", "alertid", "language_name", "e_status", "e_reminder_status", "e_lastupdated", "e_creationdate", "e_sampleddate", "e_responsedate", "e_invitationdate", "reminderdate", "e_delivereddate", "e_remindeddate", "e_accepteddate", "e_initialfinishdate", "e_ta_completed_date", "e_expirationdate", "e_survey_method", "e_survey_source", "e_survey_type", "parse_status", "tagging_attempts", "e_optout", "e_bounce_category", "feed_fileid", "feed_file_recordid", "startdate", "e_lastname", "e_firstname", "e_address", "e_address2", "e_city", "e_state", "e_postalcode", "e_phone", "e_email", "is_mobile", "is_mobile_first", "is_mobile_finished", "is_cookie_confirmation_needed", "exclude_from_problem_tracker", "is_invitation_error", "page_number_seen_last", "page_number_seen_highest", "pages_submitted", "pages_validation_failed", "last_submit_date", "user_agent", "jsonb_fields", "thrift", "episodeid", "e_unitid", "e_committed_survey_specid", "customer_veonid", "xmin" FROM "survey_prior" WHERE survey_veon.surveyid = ANY($1)
The usual fix is :
REINDEX TABLE pg_toast_77809;
After this we can select * from survery_prior with no problem, and we can dump the entire table if we wish it.
Then the jobs resumes and we're happy until we hit again with the same error , this occurs randomly , we suspect on the UPDATE on certain row is creating the corruption.
We can't reproduce the ERROR because we can't do a full scan of the table while the error is still alive () on-call guys applies the fix inmediatly to resume production ).
So how can we discover the bad-rows and expose them, or how do we check the consistency of the pt_toast table?
We need this evidence to ask for window and a complete dump/restore that will wipe out this problem.
Thx in advance
Jorge Daniel Fernandez
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pt_toast table seems to be
2018-01-03 15:25 pt_toast table seems to be Jorge Daniel <elgaita@hotmail.com>
@ 2018-01-03 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-01-03 16:59 ` Re: pt_toast table seems to be Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-01-03 15:36 UTC (permalink / raw)
To: Jorge Daniel <elgaita@hotmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Jorge Daniel <elgaita@hotmail.com> writes:
> [ repeated corruption of a pg_toast index ]
That's pretty interesting. What PG version is this exactly?
If you could put together a self-contained test case based on your
environment, I'm sure we'd be happy to try to isolate how the
corruption is occurring. Even with random OOM kills, PG is not
supposed to leave you with a corrupt index, so I'd say there's
a bug in there somewhere.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pt_toast table seems to be
2018-01-03 15:25 pt_toast table seems to be Jorge Daniel <elgaita@hotmail.com>
2018-01-03 15:36 ` Re: pt_toast table seems to be Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-01-03 16:59 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2018-01-03 16:59 UTC (permalink / raw)
To: Jorge Daniel <elgaita@hotmail.com>; +Cc: pgsql-admin@lists.postgresql.org
[ please keep the list cc'd, for the archives' sake ]
Jorge Daniel <elgaita@hotmail.com> writes:
> We're running PostgreSQL 9.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit.
OK. That's a bit older than I'd like --- if you read the release notes
you will notice there are a bunch of corner-case-data-corruption fixes
since then. However, I do not think that any of the known issues would
manifest in the way you're describing, so whatever you're seeing is
probably still a live bug. Please see if you can make a test case.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pt_toast table seems to be
2018-01-03 15:25 pt_toast table seems to be Jorge Daniel <elgaita@hotmail.com>
@ 2018-01-03 19:23 ` Rui DeSousa <rui.desousa@icloud.com>
2018-01-03 21:42 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
1 sibling, 1 reply; 7+ messages in thread
From: Rui DeSousa @ 2018-01-03 19:23 UTC (permalink / raw)
To: Jorge Daniel <elgaita@hotmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Do you mean the Postgres instance is killed or the docker instance is killed? For OOM, whatâs the memory configuration and how much swap is allocated?
I would look at your disk subsystem from the prospected of Postgres; when it issues a sync() does your setup honor it? If you docker instance is crashing then it seems like sync() might not honored which would lead to corruption issues youâre describing.
> On Jan 3, 2018, at 10:25 AM, Jorge Daniel <elgaita@hotmail.com> wrote:
>
> Hi guys
> This is my first post in the comunity so my apologies in advance about the formalities.
>
>
> In a production db , and after several OOM killer events on the postgres running on a docker (now fixed) we remain with a kind of corrupted pg_toast đ:
>
>
> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:ERROR: unexpected chunk number 0 (expected 1) for toast value 17143928 in pg_toast_77809
> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:STATEMENT: SELECT "id", "needs_pumping", "needs_lucene", "surveybatchid", "ip_address", "is_open_proxy", "is_partial", "cookies", "social_media_source", "social_media_review_id", "social_media_pull_id", "social_media_pull_id_long", "source_config_unitid", "source_config_source_id", "updated", "external_id_printable_coder", "unique_reusable_id", "invite_errmsg", "reminder_errmsg", "last_seen_pagename", "last_submitted_pagename", "has_content_fields", "logid", "alertid", "language_name", "e_status", "e_reminder_status", "e_lastupdated", "e_creationdate", "e_sampleddate", "e_responsedate", "e_invitationdate", "reminderdate", "e_delivereddate", "e_remindeddate", "e_accepteddate", "e_initialfinishdate", "e_ta_completed_date", "e_expirationdate", "e_survey_method", "e_survey_source", "e_survey_type", "parse_status", "tagging_attempts", "e_optout", "e_bounce_category", "feed_fileid", "feed_file_recordid", "startdate", "e_lastname", "e_firstname", "e_address", "e_address2", "e_city", "e_state", "e_postalcode", "e_phone", "e_email", "is_mobile", "is_mobile_first", "is_mobile_finished", "is_cookie_confirmation_needed", "exclude_from_problem_tracker", "is_invitation_error", "page_number_seen_last", "page_number_seen_highest", "pages_submitted", "pages_validation_failed", "last_submit_date", "user_agent", "jsonb_fields", "thrift", "episodeid", "e_unitid", "e_committed_survey_specid", "customer_veonid", "xmin" FROM "survey_prior" WHERE survey_veon.surveyid = ANY($1)
>
> The usual fix is :
>
> REINDEX TABLE pg_toast_77809;
>
> After this we can select * from survery_prior with no problem, and we can dump the entire table if we wish it.
>
> Then the jobs resumes and we're happy until we hit again with the same error , this occurs randomly , we suspect on the UPDATE on certain row is creating the corruption.
>
> We can't reproduce the ERROR because we can't do a full scan of the table while the error is still alive () on-call guys applies the fix inmediatly to resume production ).
>
> So how can we discover the bad-rows and expose them, or how do we check the consistency of the pt_toast table?
>
> We need this evidence to ask for window and a complete dump/restore that will wipe out this problem.
>
> Thx in advance
>
> Jorge Daniel Fernandez
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pt_toast table seems to be
2018-01-03 15:25 pt_toast table seems to be Jorge Daniel <elgaita@hotmail.com>
2018-01-03 19:23 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
@ 2018-01-03 21:42 ` Rui DeSousa <rui.desousa@icloud.com>
2018-01-04 01:37 ` Re: pt_toast table seems to be Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-04 15:33 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rui DeSousa @ 2018-01-03 21:42 UTC (permalink / raw)
To: Jorge Daniel <elgaita@hotmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Interesting setup. What is your ceph settings? Are you using caching and is it set to write-back or write-through?
If youâre using caching it should be using write-through with the max dirty set to zero.
rbd cache max dirty to 0
http://docs.ceph.com/docs/master/rbd/rbd-config-ref/
> On Jan 3, 2018, at 3:38 PM, Jorge Daniel <elgaita@hotmail.com> wrote:
>
> We're using ceph, ext4 volumes where resides our PGDATA , it's mounted like this:
> /dev/rbd5 on /pg_data type ext4 (rw,relatime,discard,stripe=1024,data=ordered)
>
> Jorge Daniel Fernandez
>
>
> From: Rui DeSousa <rui.desousa@icloud.com>
> Sent: Wednesday, January 3, 2018 5:20 PM
> To: Jorge Daniel
> Subject: Re: pt_toast table seems to be
>
> Iâm not a docker expert nor have I ever run Postgres in a docker container; but my thought is docker is not handle sync() truly to form â i.e. buffering for performance improvements instead. If it buffers it but then crashes before the data is actual saved it will lead to corruption. Iâve seen similar issues with RAID controllers that buffer the sync() call but eventual fail to actual save the data for whatever reason; buffer overrun, system crashed, etc.
>
> How is the Postgres data stored In the docker container? What file system is it using? Is using a docker data volume, directory mount, or storage plug in?
>
> Doing a quick google search it does seem that docker has itâs own storage model which introduces itâs own COW semantics, plugins, etc; although, I canât find anything on if it guarantees sync() calls.
>
>> On Jan 3, 2018, at 2:43 PM, Jorge Daniel <elgaita@hotmail.com <mailto:elgaita@hotmail.com>> wrote:
>>
>> Hi Rui, every time autoAnalyze ran against that table , after a long time running, the OOM killer act up and killed the autovacuum , in some cases the engine got in recovery and other cases it was causing the crash of the whole container. We're pretty sure that this last ones were the ones that lead to this issue.
>>
>> We were able to reproduce the OOM killing and our findings lead us to a strange Docker setting for the SHMMAX:
>>
>> Linux 434d18e30d83 4.10.0-42-generic #46~16.04.1-Ubuntu SMP Mon Dec 4 15:57:59 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
>> postgres@434d18e30d83:~$ cat /proc/sys/kernel/shmmax
>> 18446744073692774399
>>
>> after we a fixed that number, we were able to run the ANALYZE and Vacuum to its end without any OOM killing.
>>
>> This is related to
>> https://github.com/moby/moby/issues/16315 <https://github.com/moby/moby/issues/16315;
>>
>> I don't have now that syslog to examine if the sync() has worked properly or not.
>>
>> <https://github.com/moby/moby/issues/16315;
>> <https://github.com/moby/moby/issues/16315;
>> Container is reporting incorrect /proc/sys/kernel/shmmax value ¡ Issue #16315 ¡ moby/moby <https://github.com/moby/moby/issues/16315;
>> github.com <http://github.com/;
>> BUG REPORT INFORMATION My host is Ubuntu 15.04 (kernel 3.19.0-28-generic) docker version: Client version: 1.5.0 Client API version: 1.17 Go version (client): go1.3.3 Git commit (client): a8a31ef OS...
>>
>>
>>
>> Jorge Daniel Fernandez
>>
>>
>> From: Rui DeSousa <rui.desousa@icloud.com <mailto:rui.desousa@icloud.com>>
>> Sent: Wednesday, January 3, 2018 4:23 PM
>> To: Jorge Daniel
>> Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
>> Subject: Re: pt_toast table seems to be
>>
>> Do you mean the Postgres instance is killed or the docker instance is killed? For OOM, whatâs the memory configuration and how much swap is allocated?
>>
>> I would look at your disk subsystem from the prospected of Postgres; when it issues a sync() does your setup honor it? If you docker instance is crashing then it seems like sync() might not honored which would lead to corruption issues youâre describing.
>>
>>> On Jan 3, 2018, at 10:25 AM, Jorge Daniel <elgaita@hotmail.com <mailto:elgaita@hotmail.com>> wrote:
>>>
>>> Hi guys
>>> This is my first post in the comunity so my apologies in advance about the formalities.
>>>
>>>
>>> In a production db , and after several OOM killer events on the postgres running on a docker (now fixed) we remain with a kind of corrupted pg_toast đ:
>>>
>>>
>>> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:ERROR: unexpected chunk number 0 (expected 1) for toast value 17143928 in pg_toast_77809
>>> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:STATEMENT: SELECT "id", "needs_pumping", "needs_lucene", "surveybatchid", "ip_address", "is_open_proxy", "is_partial", "cookies", "social_media_source", "social_media_review_id", "social_media_pull_id", "social_media_pull_id_long", "source_config_unitid", "source_config_source_id", "updated", "external_id_printable_coder", "unique_reusable_id", "invite_errmsg", "reminder_errmsg", "last_seen_pagename", "last_submitted_pagename", "has_content_fields", "logid", "alertid", "language_name", "e_status", "e_reminder_status", "e_lastupdated", "e_creationdate", "e_sampleddate", "e_responsedate", "e_invitationdate", "reminderdate", "e_delivereddate", "e_remindeddate", "e_accepteddate", "e_initialfinishdate", "e_ta_completed_date", "e_expirationdate", "e_survey_method", "e_survey_source", "e_survey_type", "parse_status", "tagging_attempts", "e_optout", "e_bounce_category", "feed_fileid", "feed_file_recordid", "startdate", "e_lastname", "e_firstname", "e_address", "e_address2", "e_city", "e_state", "e_postalcode", "e_phone", "e_email", "is_mobile", "is_mobile_first", "is_mobile_finished", "is_cookie_confirmation_needed", "exclude_from_problem_tracker", "is_invitation_error", "page_number_seen_last", "page_number_seen_highest", "pages_submitted", "pages_validation_failed", "last_submit_date", "user_agent", "jsonb_fields", "thrift", "episodeid", "e_unitid", "e_committed_survey_specid", "customer_veonid", "xmin" FROM "survey_prior" WHERE survey_veon.surveyid = ANY($1)
>>>
>>> The usual fix is :
>>>
>>> REINDEX TABLE pg_toast_77809;
>>>
>>> After this we can select * from survery_prior with no problem, and we can dump the entire table if we wish it.
>>>
>>> Then the jobs resumes and we're happy until we hit again with the same error , this occurs randomly , we suspect on the UPDATE on certain row is creating the corruption.
>>>
>>> We can't reproduce the ERROR because we can't do a full scan of the table while the error is still alive () on-call guys applies the fix inmediatly to resume production ).
>>>
>>> So how can we discover the bad-rows and expose them, or how do we check the consistency of the pt_toast table?
>>>
>>> We need this evidence to ask for window and a complete dump/restore that will wipe out this problem.
>>>
>>> Thx in advance
>>>
>>> Jorge Daniel Fernandez
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pt_toast table seems to be
2018-01-03 15:25 pt_toast table seems to be Jorge Daniel <elgaita@hotmail.com>
2018-01-03 19:23 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
2018-01-03 21:42 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
@ 2018-01-04 01:37 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
1 sibling, 0 replies; 7+ messages in thread
From: Mark Kirkwood @ 2018-01-04 01:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Also the Ceph version might be important (if you are running pre Jewel
then there was a data corruption bug with db type workloads for 0.94.9
and earlier).
regards
Mark
On 04/01/18 10:42, Rui DeSousa wrote:
>
> Interesting setup. Â What is your ceph settings? Are you using caching
> and is it set to write-back or write-through?
>
> If youâre using caching it should be using write-through with the max
> dirty set to zero.
>
> |rbd cache max dirty|Â to 0
>
> http://docs.ceph.com/docs/master/rbd/rbd-config-ref/
>
>
>
>> On Jan 3, 2018, at 3:38 PM, Jorge Daniel <elgaita@hotmail.com
>> <mailto:elgaita@hotmail.com>> wrote:
>>
>> We're using ceph, ext4Â volumes where resides our PGDATA , it's
>> mounted like this:
>> /dev/rbd5 on /pg_data type ext4
>> (rw,relatime,discard,stripe=1024,data=ordered)
>>
>> Jorge Daniel Fernandez
>>
>>
>> ------------------------------------------------------------------------
>> *From:*Rui DeSousa <rui.desousa@icloud.com
>> <mailto:rui.desousa@icloud.com>>
>> *Sent:*Wednesday, January 3, 2018 5:20 PM
>> *To:*Jorge Daniel
>> *Subject:*Re: pt_toast table seems to be
>> Iâm not a docker expert nor have I ever run Postgres in a docker
>> container; but my thought is docker is not handle sync() truly to
>> form â i.e. buffering for performance improvements instead. Â If it
>> buffers it but then crashes before the data is actual saved it will
>> lead to corruption. Â Iâve seen similar issues with RAID controllers
>> that buffer the sync() call but eventual fail to actual save the data
>> for whatever reason; buffer overrun, system crashed, etc.
>>
>> How is the Postgres data stored In the docker container? What file
>> system is it using? Is using a docker data volume, directory mount,
>> or storage plug in?
>>
>> Doing a quick google search it does seem that docker has itâs own
>> storage model which introduces itâs own COW semantics, plugins, etc;
>> although, I canât find anything on if it guarantees sync() calls.
>>
>>> On Jan 3, 2018, at 2:43 PM, Jorge Daniel <elgaita@hotmail.com
>>> <mailto:elgaita@hotmail.com>> wrote:
>>>
>>> Hi Rui, every time autoAnalyze ran against that table , after a long
>>> time running, the OOM killer act up and killed the autovacuum , in
>>> some cases the engine got in recovery and other cases it was causing
>>> the crash of the whole container. We're pretty sure that this last
>>> ones were the ones that lead to this issue.
>>>
>>> We were able to reproduce the OOM killing and our findings lead us
>>> to a strange Docker setting for the SHMMAX:
>>>
>>> Linux 434d18e30d83 4.10.0-42-generic #46~16.04.1-Ubuntu SMP Mon Dec
>>> 4 15:57:59 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
>>> postgres@434d18e30d83:~$ cat /proc/sys/kernel/shmmax
>>> 18446744073692774399
>>>
>>> after we a fixed that  number, we were able to run the ANALYZE and
>>> Vacuum to its end without any OOM killing.
>>>
>>> This is related to
>>> https://github.com/moby/moby/issues/16315
>>>
>>> I don't have now that syslog to examine if the sync() has worked
>>> properly or not.
>>>
>>> <https://github.com/moby/moby/issues/16315;
>>>
>>> Container is reporting incorrect /proc/sys/kernel/shmmax value ¡
>>> Issue #16315 ¡ moby/moby <https://github.com/moby/moby/issues/16315;
>>> github.com <http://github.com/;
>>> BUG REPORT INFORMATION My host is Ubuntu 15.04 (kernel
>>> 3.19.0-28-generic) docker version: Client version: 1.5.0 Client API
>>> version: 1.17 Go version (client): go1.3.3 Git commit (client):
>>> a8a31ef OS...
>>>
>>>
>>>
>>>
>>> Jorge Daniel Fernandez
>>>
>>>
>>> ------------------------------------------------------------------------
>>> *From:*Rui DeSousa <rui.desousa@icloud.com
>>> <mailto:rui.desousa@icloud.com>>
>>> *Sent:*Wednesday, January 3, 2018 4:23 PM
>>> *To:*Jorge Daniel
>>> *Cc:*pgsql-admin@lists.postgresql.org
>>> <mailto:pgsql-admin@lists.postgresql.org>
>>> *Subject:*Re: pt_toast table seems to be
>>> Do you mean the Postgres instance is killed or the docker instance
>>> is killed? Â For OOM, whatâs the memory configuration and how much
>>> swap is allocated?
>>>
>>> I would look at your disk subsystem from the prospected of Postgres;
>>> when it issues a sync() does your setup honor it? Â If you docker
>>> instance is crashing then it seems like sync() might not honored
>>> which would lead to corruption issues youâre describing.
>>>
>>>> On Jan 3, 2018, at 10:25 AM, Jorge Daniel <elgaita@hotmail.com
>>>> <mailto:elgaita@hotmail.com>> wrote:
>>>>
>>>> Hi guys
>>>> This is my first post in the comunity so my apologies in advance
>>>> about the formalities.
>>>>
>>>>
>>>> In a production db , and after several OOM killer events on the
>>>> postgres running on a docker (now fixed) we remain with a kind of
>>>> corrupted pg_toast đ:
>>>>
>>>>
>>>> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL
>>>> JDBC Driver:user=veon:db=veon:ERROR: unexpected chunk number 0
>>>> (expected 1) for toast value 17143928 in pg_toast_77809
>>>> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL
>>>> JDBC Driver:user=veon:db=veon:STATEMENT: SELECT "id",
>>>> "needs_pumping", "needs_lucene", "surveybatchid", "ip_address",
>>>> "is_open_proxy", "is_partial", "cookies", "social_media_source",
>>>> "social_media_review_id", "social_media_pull_id",
>>>> "social_media_pull_id_long", "source_config_unitid",
>>>> "source_config_source_id", "updated",
>>>> "external_id_printable_coder", "unique_reusable_id",
>>>> "invite_errmsg", "reminder_errmsg", "last_seen_pagename",
>>>> "last_submitted_pagename", "has_content_fields", "logid",
>>>> "alertid", "language_name", "e_status", "e_reminder_status",
>>>> "e_lastupdated", "e_creationdate", "e_sampleddate",
>>>> "e_responsedate", "e_invitationdate", "reminderdate",
>>>> "e_delivereddate", "e_remindeddate", "e_accepteddate",
>>>> "e_initialfinishdate", "e_ta_completed_date", "e_expirationdate",
>>>> "e_survey_method", "e_survey_source", "e_survey_type",
>>>> "parse_status", "tagging_attempts", "e_optout",
>>>> "e_bounce_category", "feed_fileid", "feed_file_recordid",
>>>> "startdate", "e_lastname", "e_firstname", "e_address",
>>>> "e_address2", "e_city", "e_state", "e_postalcode", "e_phone",
>>>> "e_email", "is_mobile", "is_mobile_first", "is_mobile_finished",
>>>> "is_cookie_confirmation_needed", "exclude_from_problem_tracker",
>>>> "is_invitation_error", "page_number_seen_last",
>>>> "page_number_seen_highest", "pages_submitted",
>>>> "pages_validation_failed", "last_submit_date", "user_agent",
>>>> "jsonb_fields", "thrift", "episodeid", "e_unitid",
>>>> "e_committed_survey_specid", "customer_veonid", "xmin" FROM
>>>> "survey_prior" WHERE survey_veon.surveyid = ANY($1)
>>>>
>>>> The usual fix is :
>>>>
>>>> REINDEX TABLE pg_toast_77809;
>>>>
>>>> After this we can select * from survery_prior with no problem, and
>>>> we can dump the entire table if we wish it.
>>>>
>>>> Then the jobs resumes and we're happy until we hit again with the
>>>> same error , this occurs randomly , we suspect on the UPDATEÂ on
>>>> certain row is creating the corruption.
>>>>
>>>> We can't reproduce the ERROR because we can't do a full scan of the
>>>> table while the error is still alive () on-call guys applies the
>>>> fix inmediatly to resume production ).
>>>>
>>>> So how can we discover the bad-rows and expose them, or how do we
>>>> check the consistency of the pt_toast table?
>>>>
>>>> We need this evidence to ask for window and a complete dump/restore
>>>> that will wipe out this problem.
>>>>
>>>> Thx in advance
>>>>
>>>> Jorge Daniel Fernandez
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pt_toast table seems to be
2018-01-03 15:25 pt_toast table seems to be Jorge Daniel <elgaita@hotmail.com>
2018-01-03 19:23 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
2018-01-03 21:42 ` Re: pt_toast table seems to be Rui DeSousa <rui.desousa@icloud.com>
@ 2018-01-04 15:33 ` Rui DeSousa <rui.desousa@icloud.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rui DeSousa @ 2018-01-04 15:33 UTC (permalink / raw)
To: Jorge Daniel <elgaita@hotmail.com>; +Cc: pgsql-admin
Correct. The following parameter would have to change otherwise a system crash will leave the database in a corrupted state that is unrecoverable.
rbd_cache_max_dirty = 25165824
To
rbd_cache_max_dirty = 0
> On Jan 4, 2018, at 10:15 AM, Jorge Daniel <elgaita@hotmail.com> wrote:
>
> Hi Rui:
>
> We're using Jewel and the actual parameters for rbd caching:
>
> rbd_op_threads = 1
> rbd_op_thread_timeout = 60
> rbd_non_blocking_aio = true
> rbd_cache = true
> rbd_cache_writethrough_until_flush = true
> rbd_cache_size = 33554432
> rbd_cache_max_dirty = 25165824
> rbd_cache_target_dirty = 16777216
> rbd_cache_max_dirty_age = 1
> rbd_cache_max_dirty_object = 0
> rbd_cache_block_writes_upfront = false
> rbd_concurrent_management_ops = 10
> rbd_balance_snap_reads = false
> rbd_localize_snap_reads = false
> rbd_balance_parent_reads = false
> rbd_localize_parent_reads = true
> rbd_readahead_trigger_requests = 10
> rbd_readahead_max_bytes = 524288
> rbd_readahead_disable_after_bytes = 52428800
> rbd_clone_copy_on_read = false
> rbd_blacklist_on_break_lock = true
> rbd_blacklist_expire_seconds = 0
> rbd_request_timed_out_seconds = 30
> rbd_skip_partial_discard = false
> rbd_enable_alloc_hint = true
> rbd_tracing = false
> rbd_validate_pool = true
> rbd_validate_names = true
> rbd_mirroring_resync_after_disconnect = false
> rbd_auto_exclusive_lock_until_manual_request = true
> rbd_default_format = 2
> rbd_default_order = 22
> rbd_default_stripe_count = 0
> rbd_default_stripe_unit = 0
> rbd_default_features = 1
> rbd_default_map_options =
> rbd_journal_order = 24
> rbd_journal_splay_width = 4
> rbd_journal_commit_age = 5
> rbd_journal_object_flush_interval = 0
> rbd_journal_object_flush_bytes = 0
> rbd_journal_object_flush_age = 0
> rbd_journal_pool =
> rbd_journal_max_payload_bytes = 16384
> rbd_journal_max_concurrent_object_sets = 0
> rbd_mirror_journal_commit_age = 5
> rbd_mirror_journal_poll_age = 5
> rbd_mirror_journal_max_fetch_bytes = 32768
> rbd_mirror_sync_point_update_age = 30
> rbd_mirror_concurrent_image_syncs = 5
>
> Are those the ones you were refering?
>
> Jorge Daniel Fernandez
>
>
> From: Rui DeSousa <rui.desousa@icloud.com <mailto:rui.desousa@icloud.com>>
> Sent: Wednesday, January 3, 2018 6:42 PM
> To: Jorge Daniel
> Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
> Subject: Re: pt_toast table seems to be
>
>
> Interesting setup. What is your ceph settings? Are you using caching and is it set to write-back or write-through?
>
> If youâre using caching it should be using write-through with the max dirty set to zero.
>
> rbd cache max dirty to 0
>
> http://docs.ceph.com/docs/master/rbd/rbd-config-ref/ <http://docs.ceph.com/docs/master/rbd/rbd-config-ref/;
> librbd Settings â Ceph Documentation <http://docs.ceph.com/docs/master/rbd/rbd-config-ref/;
> docs.ceph.com <http://docs.ceph.com/;
> The user space implementation of the Ceph block device (i.e., librbd) cannot take advantage of the Linux page cache, so it includes its own in-memory caching, called ...
>
>
>
>
>> On Jan 3, 2018, at 3:38 PM, Jorge Daniel <elgaita@hotmail.com <mailto:elgaita@hotmail.com>> wrote:
>>
>> We're using ceph, ext4 volumes where resides our PGDATA , it's mounted like this:
>> /dev/rbd5 on /pg_data type ext4 (rw,relatime,discard,stripe=1024,data=ordered)
>>
>> Jorge Daniel Fernandez
>>
>>
>> From: Rui DeSousa <rui.desousa@icloud.com <mailto:rui.desousa@icloud.com>>
>> Sent: Wednesday, January 3, 2018 5:20 PM
>> To: Jorge Daniel
>> Subject: Re: pt_toast table seems to be
>>
>> Iâm not a docker expert nor have I ever run Postgres in a docker container; but my thought is docker is not handle sync() truly to form â i.e. buffering for performance improvements instead. If it buffers it but then crashes before the data is actual saved it will lead to corruption. Iâve seen similar issues with RAID controllers that buffer the sync() call but eventual fail to actual save the data for whatever reason; buffer overrun, system crashed, etc.
>>
>> How is the Postgres data stored In the docker container? What file system is it using? Is using a docker data volume, directory mount, or storage plug in?
>>
>> Doing a quick google search it does seem that docker has itâs own storage model which introduces itâs own COW semantics, plugins, etc; although, I canât find anything on if it guarantees sync() calls.
>>
>>> On Jan 3, 2018, at 2:43 PM, Jorge Daniel <elgaita@hotmail.com <mailto:elgaita@hotmail.com>> wrote:
>>>
>>> Hi Rui, every time autoAnalyze ran against that table , after a long time running, the OOM killer act up and killed the autovacuum , in some cases the engine got in recovery and other cases it was causing the crash of the whole container. We're pretty sure that this last ones were the ones that lead to this issue.
>>>
>>> We were able to reproduce the OOM killing and our findings lead us to a strange Docker setting for the SHMMAX:
>>>
>>> Linux 434d18e30d83 4.10.0-42-generic #46~16.04.1-Ubuntu SMP Mon Dec 4 15:57:59 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
>>> postgres@434d18e30d83:~$ cat /proc/sys/kernel/shmmax
>>> 18446744073692774399
>>>
>>> after we a fixed that number, we were able to run the ANALYZE and Vacuum to its end without any OOM killing.
>>>
>>> This is related to
>>> https://github.com/moby/moby/issues/16315 <https://github.com/moby/moby/issues/16315;
>>>
>>> I don't have now that syslog to examine if the sync() has worked properly or not.
>>>
>>> <https://github.com/moby/moby/issues/16315;
>>> <https://github.com/moby/moby/issues/16315;
>>> Container is reporting incorrect /proc/sys/kernel/shmmax value ¡ Issue #16315 ¡ moby/moby <https://github.com/moby/moby/issues/16315;
>>> github.com <http://github.com/;
>>> BUG REPORT INFORMATION My host is Ubuntu 15.04 (kernel 3.19.0-28-generic) docker version: Client version: 1.5.0 Client API version: 1.17 Go version (client): go1.3.3 Git commit (client): a8a31ef OS...
>>>
>>>
>>>
>>> Jorge Daniel Fernandez
>>>
>>>
>>> From: Rui DeSousa <rui.desousa@icloud.com <mailto:rui.desousa@icloud.com>>
>>> Sent: Wednesday, January 3, 2018 4:23 PM
>>> To: Jorge Daniel
>>> Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
>>> Subject: Re: pt_toast table seems to be
>>>
>>> Do you mean the Postgres instance is killed or the docker instance is killed? For OOM, whatâs the memory configuration and how much swap is allocated?
>>>
>>> I would look at your disk subsystem from the prospected of Postgres; when it issues a sync() does your setup honor it? If you docker instance is crashing then it seems like sync() might not honored which would lead to corruption issues youâre describing.
>>>
>>>> On Jan 3, 2018, at 10:25 AM, Jorge Daniel <elgaita@hotmail.com <mailto:elgaita@hotmail.com>> wrote:
>>>>
>>>> Hi guys
>>>> This is my first post in the comunity so my apologies in advance about the formalities.
>>>>
>>>>
>>>> In a production db , and after several OOM killer events on the postgres running on a docker (now fixed) we remain with a kind of corrupted pg_toast đ:
>>>>
>>>>
>>>> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:ERROR: unexpected chunk number 0 (expected 1) for toast value 17143928 in pg_toast_77809
>>>> 2017-11-29 23:46:13.147 PST rhost=10.149.54.5(54750) app=PostgreSQL JDBC Driver:user=veon:db=veon:STATEMENT: SELECT "id", "needs_pumping", "needs_lucene", "surveybatchid", "ip_address", "is_open_proxy", "is_partial", "cookies", "social_media_source", "social_media_review_id", "social_media_pull_id", "social_media_pull_id_long", "source_config_unitid", "source_config_source_id", "updated", "external_id_printable_coder", "unique_reusable_id", "invite_errmsg", "reminder_errmsg", "last_seen_pagename", "last_submitted_pagename", "has_content_fields", "logid", "alertid", "language_name", "e_status", "e_reminder_status", "e_lastupdated", "e_creationdate", "e_sampleddate", "e_responsedate", "e_invitationdate", "reminderdate", "e_delivereddate", "e_remindeddate", "e_accepteddate", "e_initialfinishdate", "e_ta_completed_date", "e_expirationdate", "e_survey_method", "e_survey_source", "e_survey_type", "parse_status", "tagging_attempts", "e_optout", "e_bounce_category", "feed_fileid", "feed_file_recordid", "startdate", "e_lastname", "e_firstname", "e_address", "e_address2", "e_city", "e_state", "e_postalcode", "e_phone", "e_email", "is_mobile", "is_mobile_first", "is_mobile_finished", "is_cookie_confirmation_needed", "exclude_from_problem_tracker", "is_invitation_error", "page_number_seen_last", "page_number_seen_highest", "pages_submitted", "pages_validation_failed", "last_submit_date", "user_agent", "jsonb_fields", "thrift", "episodeid", "e_unitid", "e_committed_survey_specid", "customer_veonid", "xmin" FROM "survey_prior" WHERE survey_veon.surveyid = ANY($1)
>>>>
>>>> The usual fix is :
>>>>
>>>> REINDEX TABLE pg_toast_77809;
>>>>
>>>> After this we can select * from survery_prior with no problem, and we can dump the entire table if we wish it.
>>>>
>>>> Then the jobs resumes and we're happy until we hit again with the same error , this occurs randomly , we suspect on the UPDATE on certain row is creating the corruption.
>>>>
>>>> We can't reproduce the ERROR because we can't do a full scan of the table while the error is still alive () on-call guys applies the fix inmediatly to resume production ).
>>>>
>>>> So how can we discover the bad-rows and expose them, or how do we check the consistency of the pt_toast table?
>>>>
>>>> We need this evidence to ask for window and a complete dump/restore that will wipe out this problem.
>>>>
>>>> Thx in advance
>>>>
>>>> Jorge Daniel Fernandez
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-01-04 15:33 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-03 15:25 pt_toast table seems to be Jorge Daniel <elgaita@hotmail.com>
2018-01-03 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-01-03 16:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-01-03 19:23 ` Rui DeSousa <rui.desousa@icloud.com>
2018-01-03 21:42 ` Rui DeSousa <rui.desousa@icloud.com>
2018-01-04 01:37 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-04 15:33 ` Rui DeSousa <rui.desousa@icloud.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox