Thread: Curious case of huge simple btree indexes bloat.
Hi,
On the one of databases under my support I found very curious case of the almost endless index bloat (index size stabilises around 100x of the original size).
Graph of one index size history attached (other indexes have an similar time/size graphs).
The table have 5 indexes and they all have the same bloating behaviour (growth to almost 100x and stabilisation around that amount). An original index size 4-8Mb (after manual reindex), over time of the 5 days they all monotonically growth to 300-900MB. In the same time table size staying pretty constant at 30-50Mb (and amount of rows in the same don't vary widely and stays between 200k and 500k).
The table have 5 indexes and they all have the same bloating behaviour (growth to almost 100x and stabilisation around that amount). An original index size 4-8Mb (after manual reindex), over time of the 5 days they all monotonically growth to 300-900MB. In the same time table size staying pretty constant at 30-50Mb (and amount of rows in the same don't vary widely and stays between 200k and 500k).
The table have large amount of the inserts/update/deletes, but autovacuum tuned to be pretty aggressive and I sure that there are no long transactions (longer then few minutes). Also there are no standby replica with hot_standby=on and no prepared transactions used, and not batch deletes/inserts/updates used. The server have plenty of RAM (database fit into shared buffers), IO and CPU available so there are no visible resource starvation.
Background information:
The PostgreSQL version 9.4.2 64 bit on Linux.
Table structure:
\d+ clientsession
Table "public.clientsession"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+--------------------------+-------------------------------------------------------------------------+----------+--------------+-------------
globalsessionid | bigint | not null default nextval('clientsession_globalsessionid_seq'::regclass) | plain | |
deviceuid | text | | extended | |
localsessionid | bigint | | plain | |
createddate | timestamp with time zone | | plain | |
lastmodified | timestamp with time zone | | plain | |
keypairid | bigint | | plain | |
sessiondataid | bigint | | plain | |
Indexes:
"clientsession_pkey" PRIMARY KEY, btree (globalsessionid) CLUSTER
"clientsession_ukey" UNIQUE CONSTRAINT, btree (deviceuid, localsessionid)
"clientsession_keypairid_key" btree (keypairid)
"clientsession_sessiondataid_key" btree (sessiondataid)
"clientsession_uduid_localid_idx" btree (upper(deviceuid), localsessionid)
Foreign-key constraints:
"clientsession_keypair_fkey" FOREIGN KEY (keypairid) REFERENCES keypair(id) ON DELETE CASCADE
"clientsession_sessiondata_id" FOREIGN KEY (sessiondataid) REFERENCES sessiondata(id) ON DELETE CASCADE
Referenced by:
TABLE "remotecommand" CONSTRAINT "remotecommand_clientsessionid_fkey" FOREIGN KEY (clientsessionid) REFERENCES clientsession(globalsessionid) ON DELETE CASCADE
Options: fillfactor=50, autovacuum_vacuum_scale_factor=0.01
\d+ clientsession
Table "public.clientsession"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+--------------------------+-------------------------------------------------------------------------+----------+--------------+-------------
globalsessionid | bigint | not null default nextval('clientsession_globalsessionid_seq'::regclass) | plain | |
deviceuid | text | | extended | |
localsessionid | bigint | | plain | |
createddate | timestamp with time zone | | plain | |
lastmodified | timestamp with time zone | | plain | |
keypairid | bigint | | plain | |
sessiondataid | bigint | | plain | |
Indexes:
"clientsession_pkey" PRIMARY KEY, btree (globalsessionid) CLUSTER
"clientsession_ukey" UNIQUE CONSTRAINT, btree (deviceuid, localsessionid)
"clientsession_keypairid_key" btree (keypairid)
"clientsession_sessiondataid_key" btree (sessiondataid)
"clientsession_uduid_localid_idx" btree (upper(deviceuid), localsessionid)
Foreign-key constraints:
"clientsession_keypair_fkey" FOREIGN KEY (keypairid) REFERENCES keypair(id) ON DELETE CASCADE
"clientsession_sessiondata_id" FOREIGN KEY (sessiondataid) REFERENCES sessiondata(id) ON DELETE CASCADE
Referenced by:
TABLE "remotecommand" CONSTRAINT "remotecommand_clientsessionid_fkey" FOREIGN KEY (clientsessionid) REFERENCES clientsession(globalsessionid) ON DELETE CASCADE
Options: fillfactor=50, autovacuum_vacuum_scale_factor=0.01
Results of pgstatindex for one of bloated indexes:
select * from pgstatindex('clientsession_pkey');
-[ RECORD 1 ]------+----------
version | 2
tree_level | 2
index_size | 552640512
root_block_no | 290
internal_pages | 207
leaf_pages | 67224
empty_pages | 0
deleted_pages | 29
avg_leaf_density | 1.08
leaf_fragmentation | 3.02
-[ RECORD 1 ]------+----------
version | 2
tree_level | 2
index_size | 552640512
root_block_no | 290
internal_pages | 207
leaf_pages | 67224
empty_pages | 0
deleted_pages | 29
avg_leaf_density | 1.08
leaf_fragmentation | 3.02
List of current index sizes (they stabilized 1 day ago):
\di+ clientsession*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------------------+-------+---------+---------------+--------+-------------
public | clientsession_keypairid_key | index | phoenix | clientsession | 545 MB |
public | clientsession_pkey | index | phoenix | clientsession | 527 MB |
public | clientsession_sessiondataid_key | index | phoenix | clientsession | 900 MB |
public | clientsession_uduid_localid_idx | index | phoenix | clientsession | 254 MB |
public | clientsession_ukey | index | phoenix | clientsession | 254 MB |
\di+ clientsession*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------------------+-------+---------+---------------+--------+-------------
public | clientsession_keypairid_key | index | phoenix | clientsession | 545 MB |
public | clientsession_pkey | index | phoenix | clientsession | 527 MB |
public | clientsession_sessiondataid_key | index | phoenix | clientsession | 900 MB |
public | clientsession_uduid_localid_idx | index | phoenix | clientsession | 254 MB |
public | clientsession_ukey | index | phoenix | clientsession | 254 MB |
I never seen such behaviour on other databases and all my attempts to get this index bloat under control have no effect.
If anyone have any ideas (even crazy ones) - welcome.
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
Attachment
Maxim Boguk <maxim.boguk@gmail.com> writes:
> On the one of databases under my support I found very curious case of the
> almost endless index bloat (index size stabilises around 100x of the
> original size).
> The table have 5 indexes and they all have the same bloating behaviour
> (growth to almost 100x and stabilisation around that amount). An original
> index size 4-8Mb (after manual reindex), over time of the 5 days they all
> monotonically growth to 300-900MB. In the same time table size staying
> pretty constant at 30-50Mb (and amount of rows in the same don't vary
> widely and stays between 200k and 500k).
At least for the index you gave stats for, it seems like it's stabilizing
at one index entry per page. This is a known possible pathological
behavior if the application's usage involves heavy decimation of original
entries; say, you insert sequential timestamps and then later remove all
but every one-thousandth one, leaving at most one live entry on every
index page. Btree can recover the totally-empty leaf pages but it has no
provision for merging non-empty leaf pages, so those all stay as they are
indefinitely.
It would be pretty unusual for all the indexes on a table to be used like
that, though.
regards, tom lane
On Mon, Jun 1, 2015 at 3:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Maxim Boguk <maxim.boguk@gmail.com> writes:
> On the one of databases under my support I found very curious case of the
> almost endless index bloat (index size stabilises around 100x of the
> original size).
> The table have 5 indexes and they all have the same bloating behaviour
> (growth to almost 100x and stabilisation around that amount). An original
> index size 4-8Mb (after manual reindex), over time of the 5 days they all
> monotonically growth to 300-900MB. In the same time table size staying
> pretty constant at 30-50Mb (and amount of rows in the same don't vary
> widely and stays between 200k and 500k).
At least for the index you gave stats for, it seems like it's stabilizing
at one index entry per page. This is a known possible pathological
behavior if the application's usage involves heavy decimation of original
entries; say, you insert sequential timestamps and then later remove all
but every one-thousandth one, leaving at most one live entry on every
index page. Btree can recover the totally-empty leaf pages but it has no
provision for merging non-empty leaf pages, so those all stay as they are
indefinitely.
It would be pretty unusual for all the indexes on a table to be used like
that, though.
regards, tom lane
Thank you very much for an explanation.
This table are part of the complicated 3-tables session info structure with a lot of short living sessions and some very long living.
And most used id's are bigserials. So yet every index field on that table have the same bad behaviour.
--