Extreme bloating of intarray GiST indexes
Hackers,
I'm currently looking at a database which has some extreme bloating of
intarray GiST indexes. As in 1000% bloating in only a few months. This
is not a particularly high-transaction-rate database, so the bloating is
a little surprising; I can only explain it if vacuum wasn't cleaning the
indexes at all, and maybe not even then.
We're currently instrumenting the database so that we can collect a bit
more data on update activity, but in the meantime, has anyone seen
anything like this?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
I'm currently looking at a database which has some extreme bloating of
intarray GiST indexes. As in 1000% bloating in only a few months. This
is not a particularly high-transaction-rate database, so the bloating is
a little surprising; I can only explain it if vacuum wasn't cleaning the
indexes at all, and maybe not even then.
We're currently instrumenting the database so that we can collect a bit
more data on update activity, but in the meantime, has anyone seen
anything like this?
1. What PG version?
2. If new enough to have contrib/pgstattuple, what does pgstattuple()
have to say about the index?
I'm suspicious that this might be bloat caused by a bad picksplit function,
not from having a lot of dead entries in the index. We've fixed several
other bogus picksplit functions in contrib in the past.
regards, tom lane
1. What PG version?
8.4.4, so it has the broken picksplit.
2. If new enough to have contrib/pgstattuple, what does pgstattuple()
have to say about the index?
Will check.
I'm suspicious that this might be bloat caused by a bad picksplit function,
not from having a lot of dead entries in the index. We've fixed several
other bogus picksplit functions in contrib in the past.
Yeah, I'll test updating to 8.4.8.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
1. What PG version?
8.4.4, so it has the broken picksplit.
...
Yeah, I'll test updating to 8.4.8.
Uh, no, the picksplit bugs we fixed were in cube and seg --- there's
no reason to think that updating will help this. But 8.4's pgstattuple
does appear to support gist indexes, so please run that and see what
you get.
regards, tom lane
On Thu, Apr 28, 2011 at 11:11 PM, Josh Berkus <josh@agliodbs.com> wrote:
I'm currently looking at a database which has some extreme bloating of
intarray GiST indexes. As in 1000% bloating in only a few months. This
is not a particularly high-transaction-rate database, so the bloating is
a little surprising; I can only explain it if vacuum wasn't cleaning the
indexes at all, and maybe not even then.We're currently instrumenting the database so that we can collect a bit
more data on update activity, but in the meantime, has anyone seen
anything like this?
What opclass is used for GiST index: gist__int_ops or gist__intbig_ops?
Do you take into account that gist__int_ops is very inefficient for large
datasets?
----
With best regards,
Alexander Korotkov.
Alexander Korotkov <aekorotkov@gmail.com> writes:
What opclass is used for GiST index: gist__int_ops or gist__intbig_ops?
Do you take into account that gist__int_ops is very inefficient for large
datasets?
I seem to recall some discussion recently about documenting where you
should cut over to using "gist__intbig_ops" --- IIRC, it wasn't all that
"big" by modern standards. But it doesn't look like any such change made
it into the docs. Should we reopen that discussion?
regards, tom lane
On Fri, Apr 29, 2011 at 1:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I seem to recall some discussion recently about documenting where you
should cut over to using "gist__intbig_ops" --- IIRC, it wasn't all that
"big" by modern standards. But it doesn't look like any such change made
it into the docs. Should we reopen that discussion?
Actually, I don't see a reason to make decision between gist__int_ops
and gist__intbig_ops. Because we can choose between full enumeration and
lossy bitmap on the fly on the base of array length (when some length
threshold achived array is converted to bitmap). If this problem is urgent,
I can write a patch with opclass that would seem more suitable to be default
to me, when I'll have a time for it.
----
With best regards,
Alexander Korotkov.
Tom Lane <tgl@sss.pgh.pa.us> writes:
Uh, no, the picksplit bugs we fixed were in cube and seg --- there's
no reason to think that updating will help this. But 8.4's pgstattuple
does appear to support gist indexes, so please run that and see what
you get.
There's also gevel that I used to inspect in development GiST index, and
I found it pretty useful. Don't know yet how it compares to pgstattuple.
http://www.sai.msu.su/~megera/wiki/Gevel
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Tom, Alexander,
So we are using gist_intbig_ops, so that's not the issue.
Using pgstattuple might be a bit of a challenge. The client doesn't
have it installed, and I can't pull it from Yum without also upgrading
PostgreSQL, since Yum doesn't stock old versions AFAIK.
Maybe we should consider making diagnostic utilities like this standard
with PostgreSQL?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
Tom, Alexander,
So we are using gist_intbig_ops, so that's not the issue.
Using pgstattuple might be a bit of a challenge. The client doesn't
have it installed, and I can't pull it from Yum without also upgrading
PostgreSQL, since Yum doesn't stock old versions AFAIK.
And updating Postgres to latest minor release is a bad thing why?
I can't believe you're not holding your client's feet to the fire
about running an old version, quite independently of the fact that
they need that contrib module.
But having said that, what you say makes no sense at all. They have
intarray installed, so they have postgresql-contrib. I know of no
Yum-accessible distributions in which intarray and pgstattuple wouldn't
be delivered in the same RPM.
regards, tom lane
Tom, Alexander,
So, some data:
corp=# select indexname,
pg_size_pretty(pg_relation_size(indexname::text)) as indexsize,
pg_size_pretty(pg_relation_size(tablename::text)) as tablesize
from pg_indexes where indexname like '%__listings_features' order by
pg_relation_size(indexname::text) desc;
indexname | indexsize | tablesize
---------------------------------------+------------+------------
idx__listings_features | 52 MB | 20 MB
corp=# select * from pg_indexes where indexname = 'idx__listings_features';
-[ RECORD 1
]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | boards
tablename | listings
indexname | idx__listings_features
tablespace |
indexdef | CREATE INDEX idx__listings_features ON listings USING gist
(features public.gist__intbig_ops) WHERE ((deleted_at IS NULL) AND
(status_id = 1))
corp=# select * from public.pgstattuple('idx__listings_features');
-[ RECORD 1 ]------+---------
table_len | 54190080
tuple_count | 7786
tuple_len | 2117792
tuple_percent | 3.91
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 49297536
free_percent | 90.97
^^^^^^^^^
Well, that explains the bloating. Why all that free space, though?
Maybe autovac isn't running?
Nope:
corp=# select * from pg_stat_user_tables where relname = 'listings';
-[ RECORD 1 ]----+------------------------------
relid | 110919
schemaname | boards
relname | listings
seq_scan | 37492
seq_tup_read | 328794009
idx_scan | 33982523
idx_tup_fetch | 302782765
n_tup_ins | 19490
n_tup_upd | 668445
n_tup_del | 9826
n_tup_hot_upd | 266661
n_live_tup | 9664
n_dead_tup | 776
last_vacuum | 2010-07-25 19:46:45.922861+00
last_autovacuum | 2011-04-30 17:30:40.555311+00
last_analyze | 2010-07-25 19:46:45.922861+00
last_autoanalyze | 2011-04-28 23:49:54.968689+00
I don't know when stats were last reset (see, this is why we need a
reset timestamp!) so not sure how long those have been accumulating.
(note: object names changed for confidentiality)
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
All,
Some trending data, since there's a lot of bloated indexes here:
select 'index_' || ( row_number() over ( order by free_percent desc ) )
as "index", *
from (
select (public.pgstattuple(indexname::text)).free_percent,
round(( n_tup_upd )::numeric / n_tup_ins, 2) as update_ratio,
round(( n_tup_hot_upd )::numeric / n_tup_ins, 2) as hot_update_ratio,
round(( n_tup_del * 100 )::numeric / n_tup_ins) as delete_percent,
extract('days' from ( now() - last_autovacuum )) as days_since_vac,
n_live_tup / 1000 as "1K_tuples"
from pg_indexes join pg_stat_user_tables as tables
ON pg_indexes.schemaname = tables.schemaname
AND pg_indexes.tablename = tables.relname
where indexname like '%__listings_features'
) as idxstats
order by free_percent desc;
index|free_percent|update_ratio|hot_update_ratio|delete_percent|days_since_vac|1K_tuples
index_1|90.97|34.30|13.68|50|3|9
index_2|87.14|15.54|2.99|41|1|2
index_3|85.08|10.86|1.42|35|5|77
index_4|84.28|22.27|5.47|18|4|370
index_5|82.4|13.65|3.89|24|49|82
index_6|82.2|11.32|2.22|29|3|54
index_7|80.97|14.38|2.95|6|14|17
index_8|80.59|15.64|2.73|48|1|29
index_9|78.43|12.81|2.97|21|37|42
index_10|77.91|11.24|2.33|57|1|21
index_11|77.26|12.73|2.00|18|11|55
index_12|77.07|16.62|2.71|15|7|7
index_13|76.56|12.20|3.20|11|11|18
index_14|75.94|14.52|2.00|23|13|15
index_15|74.73|14.94|2.68|17|11|34
index_16|73.78|15.94|3.77|25|5|2
index_17|73.54|50.19|4.26|14|14|10
index_18|73.11|15.07|6.70|20|20|7
index_19|72.82|10.26|4.63|19|11|7
index_20|72.55|15.59|5.14|22|3|13
index_21|68.52|19.69|5.49|13|11|3
index_22|61.47|14.00|4.61|27|47|2
index_23|45.06|18.10|11.65|19|96|2
index_24|37.75|6.04|1.32|36|96|15
index_25|36.87|15.32|3.71|10|96|17
index_26|32.32|7.07|2.15|18|96|15
index_27|0|6.28|0.74|10|316|48
This makes a pretty graph, but the only thing it tells me is that the
handful of non-bloated tables are the ones which weren't vacuumed
recently, and either have very few rows or haven't gotten a lot of
updates. This is not a surprise.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
So, some data:
corp=# select * from public.pgstattuple('idx__listings_features');
-[ RECORD 1 ]------+---------
table_len | 54190080
tuple_count | 7786
tuple_len | 2117792
tuple_percent | 3.91
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 49297536
free_percent | 90.97
^^^^^^^^^
Well, that explains the bloating. Why all that free space, though?
Maybe autovac isn't running?
No, because you have under 10% dead tuples in the main table.
I think this is sufficient proof of the crummy-page-splits theory.
Can you provide the data in the column that's indexed?
regards, tom lane
I have another hypothesis about index bloat cause. AFAIK, vaccum procedure
on GiST don't have any storage utilization guarantee. For example, if only
one live item is in some page, then only one item will be left in this page.
I.e. there is no index reroganization during vacuum. If there wouldn't be
many inserts into such pages in future then they will be stay bloat.
----
With best regards,
Alexander Korotkov.
Alexander Korotkov <aekorotkov@gmail.com> writes:
I have another hypothesis about index bloat cause. AFAIK, vaccum procedure
on GiST don't have any storage utilization guarantee. For example, if only
one live item is in some page, then only one item will be left in this page.
I.e. there is no index reroganization during vacuum. If there wouldn't be
many inserts into such pages in future then they will be stay bloat.
Possibly, but the same is true of btree indexes, and we very seldom see
cases where that's a serious issue. In any case, this is all just
speculation without evidence --- we need to see actual data to figure
out what's going on.
regards, tom lane
No, because you have under 10% dead tuples in the main table.
I think this is sufficient proof of the crummy-page-splits theory.
Can you provide the data in the column that's indexed?
Yes, I can. Fortunately, none of it's identifiable.
Attached. This is for the index which is 90% free space.
So, some other characteristics of this index:
* If you didn't notice earlier, it's a partial index. The two columns
which determine the partial index change more often than the intarray
column.
* We've also determined some other unusual patterns from watching the
application:
(a) the "listings" table is a very wide table, with about 60 columns
(b) whenever the table gets updated, the application code updates these
60 columns in 4 sections. So there's 4 updates to the same row, in a
single transaction.
(c) we *think* that other columns of the table, including other indexed
columns, are changed much more frequently than the intarray column is.
Currently doing analysis on that.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
Attachments:
bloated_intarray.csv.zipapplication/x-zip-compressed; name=bloated_intarray.csv.zipDownload
PK (`�>i|q#�q H bloated_intarray.csvUT [��M[��MUx �������$���g�v4$��o����J"�4L#@�����2��d�#\�| ��?������������?�����������h�����=������l�i�����:o��������{n�w��s������?��������c�����:-��=��a����u���~}�>^�-����e���2�����}������y{������4��\��x��`O��h?�,�:��u{��;�W~l�<�N����m��j����OwZ����F����0�w�w0>����i?_���
�o�;���w���'[[<��O0M��I�w�$���������d�kk����3>��w ^O;�7{���X�r�2��X�v���e{]��.�s{)���xn+����_
�=]�pf_����ik�����{.�P�Zl7�``�@`-}ak����>��t?;>��;}����w��g��������>>� �C�4�������ey?��2�/�f��0���NO�:7{��9�������r��`&��C�����Y+������������#�};���aCq���v>���������n+r�]�p�7�+���#������x��������/f[��o��^���8�H����ccFc�Ml1W,��{3����m��Z�F�K��K��o7������K���H>G�K�Ak{N����� ����bi������7
t~��bK`
>l���\�a{��|��l���4�����������F�d�v5~�h� ���_��� W��0�v�]�v����7�^�1�e6��lk3�ZO����\���6Wb�&n5 @�������G�� ����g������| ��l���Sp�%�i�e��// ����C������e�pmw�MM���w����������sI�>�=a��a�Ig���N�$��^��y[�6�� `����i��
�k;3������������;L��q0��(V;i�v����������������h(�����p�c|��s7~�W����^s<y��A�������2�N��H�G��=}���9���\������I���]Q��p�,�����r�|��q��zl����g��hd+��M���{�6�l�����$��@�
��<<C�Qu���`����T���s���>��i>#���R<�6���(��h<������~o���D�&��q��8������$������Nt�����Tv������e\���8_}x��3b������1��[�-g����j�29 a�0-��3#�C~_�y �9y�_����e|������l��������9�b_�^�g����O<m�n�Y�s�6�����2��G8@�A����Y��C+}��Y[��=�����k�m�^.�nO������t�����t0'�n�{Nb ���n��vpn0D���pL����@���J�����mF�h��m��1�{[�2����$�
-����RE�v�����������y���* �YC��}����q'&�S@]8�k����H~��a�vA�����gNK���b=zMIo����3=����2
�sHy4����}z�B�e�h�>�����3;���������\#_�� h,kH���=����nY�q�W�����!�G|C��s(B����[��C �A��7?$_���������
��,�������f� E ��3�H���4s%����m�|GR_X�&�F ��u��'aj���;��r�@-2O��u���@-1���A�P����DP�
����R^ �*9p�yT@����WHw����_M�0<G4O�I��B�$��,v�'< ������'w��E$QJ�y}m=U�F�J��;���������
����O���g��V��\&������|�f�(��Am�PH'�|G����KD|�&$M2����E���N.d�xM/�4-qv0QX��]�c'�l
���#���
�DP�`c�SX�ba����Q�M%8��X��BXq��� �c/>;0�L��q����(dvg"������m����rvs�XR���,A��D$$�D"�tk>B���'�d5
E8*SO���b�9��,���% ����>�&��[�4���:���� ��� l<��*m{����e������sD������1��A|�/p\:�T^�|��%A�<K�~��C\�e�K7��������]��2a*cS�\��8�K�_���ut��C8��%��S`�E�3�1�|Q�t#go�O����� ^V!"����Y$^>��W������2(5�k|��9/?_$vv*�'���+hL���0~��M����[�<��p?7$�CA�H�
�62$L���d($�a���=� J`o��}��Hn�����+G��`:������F�O�,N�
���`M����Kg����� ���NaT�����r]�;�g���-�rqU�G�6��"�L�9���EZ��u����V�O��J�:2��(Z�c�ZI�u���BUq~Ez�_�ks�3jp��v�K��<���[�����Z���w.���T���t� |��g�`�!-�b_s�%���@@�)���%�����������Q�"�{sDvb'�I4s��C�EB�;p��e�r�f���i!�� 7<��G����;J�dZCG6Ha��(?�"��x^�{���F��(�U`�'����^�E�����K"������ �����$ ��aC��
��OH9�b�E )K��R�s�ad=�@[�o����+��7��R ���a��u)b��4PU�*!7q�D�
hV8rY �������!,8���� J]�0�J��iZ����'�n [�^!D{�i�*'p<�Q+"�nN�P�W����|`gZv<�f��������`���Tv)!L���T'���������(��EJ�o�*fs*�s�B�7_�?����g���<<�GW��T�J:��"�&�c�n�$N��+@5�'�)�Az#Q������5N�����S��h$@�������b�X�\�����t4��yj�7�.��X1��BJ,~���&�py9[�����v�A=�ve'$����]z����@���[������>����eBCV�v{�[���R�(�,/ew��|T��q�p�z��!*��|C������0>� �g�-��EX��W����%F��
��D!�� =�dU-��zY��d���DB/�?j�Z����/^6�i�Ul`���"N��b�d"
��E�;U���W]"�~%0�!�KR��s��|��_���������AsVLRv�%��d�96_YT��[bYI���L�
Y�q����x�w������������U���b��pya)Xu������;Y,����!VbN)�.������ ���*�K�~�y�������*�c{�U��T����9%�V�.1�c��?�����`��,9&��0b�;Y@w�x��qf�EP���62cc�9�cI��P����q��L���L�cG�%����?�u�M�x ��������:�1���VO^/�d�G�2�{;q2T� ��-0��B�"����e�>'��3��� �������
�=�����pGs{��6+ZS�,����I�����������u�oy3>J;({��s��2�q����k1c.������.(�����b{�Z$s��s�I�xF��O2���rz
g��O|l�����J�Gg��o�T��2�KP��p��4���,s|�`��[�J���]Nn��H3\1��"����E,���r�~G��Tw�@�I@�X����X"a+e�rlb�DO (C�����h�;h�Y��)bT�b��}�����+(���C����.�]�3b:�S�'�y��qZ�Z�`wC�d��~�(���n����/X�����E�� ��adYe��a�E���H������OSoj0k�������m��n�#�<a�,��_�����GhVWY�Xw'�rP��S��8d�_6?����>� !i�il��#K���b��;_l_���/K1%8t�{`,���:��'2���~,�� �-�9M?�`-��1`��&��8h����j��8��y�"�|�f������S��������������q�?^Q;1���&?��~���������������D���M-Xf�
8�i�������
����s����,Z�pQN F�H 7'I �,�C( �T��1q��w��/��g���e��w��c�]�h���n5�V"%�\�Y�J�Uq��?�=s�������
�������g�k9�������\�dd��
�*t��$�2��i��E T��������V������W����`bT��&�_$0'"tn&9�st`f3K���R���\���D�3?���PH`�5�.���/c>`��&i_�,c(*���,�l)��f�X<E��}:D��;���L�Ce+Z�S���l��u�����2BpA
���`�3�����1�,r���h�I����,��K���>��R�.gw ���c��G��RC�Di:���Fk�V��#=�=:��oF��@��ifn�(� .��]���Q�#��2(�3���������#�m��a8g��5m�R1M�T�AlS��OM���k��'�v9�JD��:��
3���ZP�U�|#:�����F���-P�>�/zR�t��w����9��~��m���8
&)�ck����%=.�9�C���\.�~e�.w[}��5D�7Q`��.����BU�E�0�[�|�2��{������
��������X;��$E�K�a��������Y�w�~�����?��z9����O���S2���w��H]�c�X����]�8�$���s�_ZQ���e��T���RB����U�����K�����V5�����h�C5��SN�����h��0B��S�����W��~���(/���/��T��'����/���~���G���3 F���[)$��EEB���*��Bv\��@��1m6n�8f5L����"���\k���E��F�5h(�F���3�$+���)Z+�bg��LsB1�u����3/�����[p�[������;%��?�{���y{� �*{�s���y��8J�uP4E�(�&_�J!�<2�i\1�+������5C�a�{Wg�9�b�!�n��%uL�qe���j��������������
PP=�S���k6�s?�N��)/�{��m\�^����A����u����o�j��f���u�'�<'�d�D.Ix��wV�����I+��%J���@<����)�N� ��_�<{�g����sU��������&�$���g�a�21���
���>7�Okb�
I�O'�����K�Z�RK���gt4��W��XW�W�����W���O�q=
���Pv��U<�8�E�>���E<