Обсуждение: GIN Trigram Index Size
Hi Some weeks ago I got some advice here on how to build a trigram index. Now, I have implemented this in our rather large database, which looks like this: We have one table of log events per month, used as partitions via "logs": public | logs | table | postgres public | logs_01 | table | postgres public | logs_02 | table | postgres public | logs_03 | table | postgres public | logs_04 | table | postgres public | logs_05 | table | postgres public | logs_06 | table | postgres public | logs_07 | table | postgres public | logs_08 | table | postgres public | logs_09 | table | postgres public | logs_10 | table | postgres public | logs_11 | table | postgres public | logs_12 | table | postgres and the individual tables have this definition: Table "public.logs_09" Column | Type | Modifiers --------------+-----------------------------+---------------------------------------------------- host | character varying(255) | facility | character varying(10) | priority | character varying(10) | tag | character varying(255) | log_date | timestamp without time zone | log_month | integer | program | character varying(255) | msg | text | seq | bigint | not null default nextval('logs_seq_seq'::regclass) msg_filtered | text | Indexes: "logs_09_pkey" PRIMARY KEY, btree (seq) "idx_logs_09_lower_host" btree (lower(host::text) varchar_pattern_ops) "idx_logs_09_trg_msg" gin (msg gin_trgm_ops) "logs_09_date_index" btree (log_date) Check constraints: "logs_09_log_month_check" CHECK (log_month = 9) It works fine (thanks!) but we have some disk space issues. Now this tables are huge, about 70 to 100 Mio rows each. Every day, we insert around 2 mio new rows and also delete 2 mio old rows. Now for a month that is not active, i.e. I created the index on msg and no data was written or deleted since then, the index on msg is around 15 GB: <N (N.oid = C.relnamespace)^J WHERE nspname IN ('public')^J AND relkind = 'i' and relname like '%trg_msg%'^J ORDER BY 1; indexname | pg_size_pretty ----------------------------+---------------- public.idx_logs_01_trg_msg | 14 GB public.idx_logs_02_trg_msg | 13 GB public.idx_logs_03_trg_msg | 17 GB public.idx_logs_04_trg_msg | 16 GB public.idx_logs_05_trg_msg | 15 GB But in the index that is currently active, we observe a growth of around ~5 GB per day, which only seems to be stopped by doing a REINDEX. E.g. here, I did a reindex three days ago, and idx_logs_09_trg_msg has already grown back from 15 to 36 GB: public.idx_logs_09_trg_msg | 36 GB I have read some discussions about pending list bloat issues, but there it was suggested that vacuuming the table should reclaim the space, and this does not seem to the case. The only way I found to reduce the size is by doing a REINDEX. The string in msg can be quite large, but average to around 200 characters: select avg(length(msg)) from (select msg from logs_09 where log_date between '2015-09-01' and '2015-09-08') x; avg ---------------------- 199.3491688585874446 Any insights on what might be happening here? Can I somehow check if the space is used up in sparsely filled pages or this list structures? Maybe sizes like this are just normal given our usage pattern? But it does seem like quite an extreme growth to me. Thanks Christian
On Wed, Sep 9, 2015 at 2:54 PM, Christian Ramseyer <rc@networkz.ch> wrote:
Your best bet for now might be to turn off fastupdate on that index. It will eliminate the re-occurrence of the bloat, but might cause your insertions to become too slow (on the other hand, it might make them faster on average, it is hard to know without trying it). If you can't turn it off, then you can set the table-specific autovacuum_analyze_scale_factor to a very small value (even zero) to get autoanalyze to process the table more often.
I have read some discussions about pending list bloat issues, but there
it was suggested that vacuuming the table should reclaim the space, and
this does not seem to the case. The only way I found to reduce the size
is by doing a REINDEX.
Vacuuming will allow the space to be reused internally. It will not visibly shrink the index, but will mark that space as eligible for reuse.
If you have a 36GB index and a reindex would have reduced it to 15GB, then a vacuum will leave it at 36GB but with 21GB of that as free space. The index should then stop growing and remain at the same size for 4 days while it fills up the internally freed space, at which point it would start growing again at its usual rate (until you did another vacuum).
Your best bet for now might be to turn off fastupdate on that index. It will eliminate the re-occurrence of the bloat, but might cause your insertions to become too slow (on the other hand, it might make them faster on average, it is hard to know without trying it). If you can't turn it off, then you can set the table-specific autovacuum_analyze_scale_factor to a very small value (even zero) to get autoanalyze to process the table more often.
The string in msg can be quite large, but average to around 200 characters:
select avg(length(msg)) from (select msg from logs_09 where log_date
between '2015-09-01' and '2015-09-08') x;
avg
----------------------
199.3491688585874446
Any insights on what might be happening here? Can I somehow check if the
space is used up in sparsely filled pages or this list structures?
You can use pg_freespacemap once the vacuum (or autoanalyze) completes to see how many pages have 0 space available, and how many of 8160 space available:
select avail, count(*) from pg_freespace('pgbench_accounts_gin_idx') group by avail;
select avail, count(*) from pg_freespace('pgbench_accounts_gin_idx') group by avail;
This won't work while the bloat is actively happening, though. During those times, freespacemap is not aware that the space is free, which is the root of the problem. The newest version of pageinspect has gin_page_opaque_info which will show those pages as being deleted, but that version is not yet released.
Cheers,
Jeff
On 10/09/15 06:40, Jeff Janes wrote: > Vacuuming will allow the space to be reused internally. It will not > visibly shrink the index, but will mark that space as eligible for reuse. > > If you have a 36GB index and a reindex would have reduced it to 15GB, > then a vacuum will leave it at 36GB but with 21GB of that as free > space. The index should then stop growing and remain at the same size > for 4 days while it fills up the internally freed space, at which point > it would start growing again at its usual rate (until you did another > vacuum). > Hi Jeff Thanks, I didn't think about that. I tried a manual analyze 4 days ago (10.09.) when it was at 41 GB, and it stayed the same size since then, so this works as expected. > Your best bet for now might be to turn off fastupdate on that index. It > will eliminate the re-occurrence of the bloat, but might cause your > insertions to become too slow (on the other hand, it might make them > faster on average, it is hard to know without trying it). If you can't > turn it off, then you can set the table-specific > autovacuum_analyze_scale_factor to a very small value (even zero) to get > autoanalyze to process the table more often. > > Yeah the default autovacuum settings are what allowed the index to go unchecked to about 120 GB and fill our disk, I'll tune this for these tables. The data sometimes arrives in bursts and I'm a bit affraid of making the inserts slower, but I'll see if I can do a benchmark of fastupdate vs. nofastupdate and will post it here if I get to it. Thanks for your help Christian
Hi Christian: On Mon, Sep 14, 2015 at 1:54 PM, Christian Ramseyer <rc@networkz.ch> wrote: > I agree with your append-only and disposable partition approach, it > would work a lot better. The idea with using a second schema for > selective backups is great, I'll totally steal this :) Feel free. Just remember if you use redirection trigers/rules for insert into the partitions it may lead to problems ( I do not normally have them as I either use a partition aware dedicated inserter or zap the archived tables from rules, as I only insert for the current date, maintenance updates are done directly in the partitions ). Francisco Olarte.