pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_toast oid limits
7+ messages / 4 participants
[nested] [flat]
* pg_toast oid limits
@ 2016-10-26 18:58 Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 19:05 ` Re: pg_toast oid limits Joshua D. Drake <jd@commandprompt.com>
2016-10-26 19:22 ` Re: pg_toast oid limits Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Natalie Wenz @ 2016-10-26 18:58 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I am seeing some performance issues that I'm trying to track down on a large database. One of the things I'm beginning to suspect is a particularly large table with many columns, about 200 of which (type text) contain large chunks of data. Now, for a given row, maybe 10-30 of those columns contain data, so not all 200 for each row, but the data can still be pretty sizable. There are currently around 750 million records in this table (and is about 22TB in size). I was trying to learn more about toast, and I see some references in the wiki and the hackers list to performance issues when you approach the 4 billion oids for a single table (which, I gather, are used when the data is toasted). Given my rudimentary understanding of how the whole toast thing works, I was wondering if there is a way to see how many oids are used for a table, or another way to know if we're running into toast limits for a single table.
What I was reading, for reference:
https://wiki.postgresql.org/wiki/TOAST
http://osdir.com/ml/postgresql-pgsql-hackers/2015-01/msg01901.html
Also, we are running postgres 9.5.4.
Many thanks!
Natalie
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_toast oid limits
2016-10-26 18:58 pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-10-26 19:05 ` Joshua D. Drake <jd@commandprompt.com>
2016-10-26 19:18 ` Re: pg_toast oid limits David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Joshua D. Drake @ 2016-10-26 19:05 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On 10/26/2016 11:58 AM, Natalie Wenz wrote:
> Hi all,
>
> I am seeing some performance issues that I'm trying to track down on a large database. One of the things I'm beginning to suspect is a particularly large table with many columns, about 200 of which (type text) contain large chunks of data. Now, for a given row, maybe 10-30 of those columns contain data, so not all 200 for each row, but the data can still be pretty sizable. There are currently around 750 million records in this table (and is about 22TB in size). I was trying to learn more about toast, and I see some references in the wiki and the hackers list to performance issues when you approach the 4 billion oids for a single table (which, I gather, are used when the data is toasted). Given my rudimentary understanding of how the whole toast thing works, I was wondering if there is a way to see how many oids are used for a table, or another way to know if we're running into toast limits for a single table.
>
> What I was reading, for reference:
> https://wiki.postgresql.org/wiki/TOAST
> http://osdir.com/ml/postgresql-pgsql-hackers/2015-01/msg01901.html
>
> Also, we are running postgres 9.5.4.
oids are used per relation not per row.
Your problem is likely more to do with the number of rows + the width of
the table. I suggest finding a way to partition this table.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_toast oid limits
2016-10-26 18:58 pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 19:05 ` Re: pg_toast oid limits Joshua D. Drake <jd@commandprompt.com>
@ 2016-10-26 19:18 ` David G. Johnston <david.g.johnston@gmail.com>
2016-10-26 19:25 ` Re: pg_toast oid limits Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2016-10-26 19:18 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On Wed, Oct 26, 2016 at 12:05 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:
> On 10/26/2016 11:58 AM, Natalie Wenz wrote:
>
>>
>> What I was reading, for reference:
>> https://wiki.postgresql.org/wiki/TOAST
>> http://osdir.com/ml/postgresql-pgsql-hackers/2015-01/msg01901.html
>>
>> Also, we are running postgres 9.5.4.
>>
>
> oids are used per relation not per row.
>
While your conclusion about rows/widths may
be accurate I'm missing something if you are correct in stating the above.
Each TOAST table has an associated OID but within that table every VALUE
(which could mean multiple per record) has an OID and a Sequence Number
which combined comprise a PK. Natalie's line of thinking seems to have
some merit even if I don't know how to answer the question at hand.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_toast oid limits
2016-10-26 18:58 pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 19:05 ` Re: pg_toast oid limits Joshua D. Drake <jd@commandprompt.com>
2016-10-26 19:18 ` Re: pg_toast oid limits David G. Johnston <david.g.johnston@gmail.com>
@ 2016-10-26 19:25 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 7+ messages in thread
From: Joshua D. Drake @ 2016-10-26 19:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On 10/26/2016 12:18 PM, David G. Johnston wrote:
> On Wed, Oct 26, 2016 at 12:05 PM, Joshua D. Drake <jd@commandprompt.com
>
> While your conclusion about rows/widths may
>
> be accurate I'm missing something if you are correct in stating the above.
>
> Each TOAST table has an associated OID but within that table every VALUE
> (which could mean multiple per record) has an OID and a Sequence Number
> which combined comprise a PK. Natalie's line of thinking seems to have
> some merit even if I don't know how to answer the question at hand.
Oh that's interesting. I think you are right. That said, the way forward
I think is still to partition that out and possibly break up the table.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_toast oid limits
2016-10-26 18:58 pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-10-26 19:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-10-26 22:12 ` Re: pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2016-10-26 19:22 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin
Natalie Wenz <nataliewenz@ebureau.com> writes:
> I am seeing some performance issues that I'm trying to track down on a large database. One of the things I'm beginning to suspect is a particularly large table with many columns, about 200 of which (type text) contain large chunks of data. Now, for a given row, maybe 10-30 of those columns contain data, so not all 200 for each row, but the data can still be pretty sizable. There are currently around 750 million records in this table (and is about 22TB in size). I was trying to learn more about toast, and I see some references in the wiki and the hackers list to performance issues when you approach the 4 billion oids for a single table (which, I gather, are used when the data is toasted). Given my rudimentary understanding of how the whole toast thing works, I was wondering if there is a way to see how many oids are used for a table, or another way to know if we're running into toast limits for a single table.
You could do
select reltoastrelid::regclass from pg_class where relname = 'problem_table';
which will give you something like
reltoastrelid
-------------------------
pg_toast.pg_toast_78004
and then poke into the contents of that table. Probably
select count(distinct chunk_id) from pg_toast.pg_toast_78004;
would answer your immediate question.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_toast oid limits
2016-10-26 18:58 pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 19:22 ` Re: pg_toast oid limits Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-10-26 22:12 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 22:35 ` Re: pg_toast oid limits Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Natalie Wenz @ 2016-10-26 22:12 UTC (permalink / raw)
To: pgsql-admin; Tom Lane <tgl@sss.pgh.pa.us>; Joshua D. Drake <jd@commandprompt.com>; david.g.johnston@gmail.com
Thanks, I'm running that now. (/twiddles thumbs) The disks are pretty much saturated at the moment, so everything is taking a long time.
I'm a little confused about the limit. Is it 4 billion oids for the whole database, or just per table? I keep coming back to this line, from https://wiki.postgresql.org/wiki/TOAST :
"If you exceed 4 billion of these rows across all tables (remember this is a global shared wrapping counter), then the OID counter "wraps" which might cause significant slowdown as you approach the 4B row limit."
We are actually in the process of attempting to create new tables (one for each of these large columns in this large table), and this process may have actually accelerated our use of oids, if it's a global pool.
Thanks for your help!
Natalie
> On Oct 26, 2016, at 2:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Natalie Wenz <nataliewenz@ebureau.com> writes:
>> I am seeing some performance issues that I'm trying to track down on a large database. One of the things I'm beginning to suspect is a particularly large table with many columns, about 200 of which (type text) contain large chunks of data. Now, for a given row, maybe 10-30 of those columns contain data, so not all 200 for each row, but the data can still be pretty sizable. There are currently around 750 million records in this table (and is about 22TB in size). I was trying to learn more about toast, and I see some references in the wiki and the hackers list to performance issues when you approach the 4 billion oids for a single table (which, I gather, are used when the data is toasted). Given my rudimentary understanding of how the whole toast thing works, I was wondering if there is a way to see how many oids are used for a table, or another way to know if we're running into toast limits for a single table.
>
> You could do
>
> select reltoastrelid::regclass from pg_class where relname = 'problem_table';
>
> which will give you something like
>
> reltoastrelid
> -------------------------
> pg_toast.pg_toast_78004
>
> and then poke into the contents of that table. Probably
>
> select count(distinct chunk_id) from pg_toast.pg_toast_78004;
>
> would answer your immediate question.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_toast oid limits
2016-10-26 18:58 pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 19:22 ` Re: pg_toast oid limits Tom Lane <tgl@sss.pgh.pa.us>
2016-10-26 22:12 ` Re: pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
@ 2016-10-26 22:35 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2016-10-26 22:35 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin; Joshua D. Drake <jd@commandprompt.com>; david.g.johnston@gmail.com
Natalie Wenz <nataliewenz@ebureau.com> writes:
> I'm a little confused about the limit. Is it 4 billion oids for the whole database, or just per table? I keep coming back to this line, from https://wiki.postgresql.org/wiki/TOAST :
Per table --- the problem is you need a unique 4-byte chunk_id for each
toasted field value.
> "If you exceed 4 billion of these rows across all tables (remember this is a global shared wrapping counter), then the OID counter "wraps" which might cause significant slowdown as you approach the 4B row limit."
Um. That could use improvement, couldn't it. The numbers are sourced
from a global counter, but as the counter wraps around it's possible for
the same number to be re-used in different toast tables. You only start
to see a problem when the density of already-used numbers in a particular
toast table gets too high, so that the system has to spend a long time
searching for the next free number.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2016-10-26 22:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-26 18:58 pg_toast oid limits Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 19:05 ` Joshua D. Drake <jd@commandprompt.com>
2016-10-26 19:18 ` David G. Johnston <david.g.johnston@gmail.com>
2016-10-26 19:25 ` Joshua D. Drake <jd@commandprompt.com>
2016-10-26 19:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-10-26 22:12 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-10-26 22:35 ` Tom Lane <tgl@sss.pgh.pa.us>
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