Hi, Thanks for your answer.
> I don't think that it would move the needle much. Deallocating entries is > very > expensive, even when the query text file isn't being cleaned up, as it needs > to > sort all entries by usage to remove the least recently used all with an > exclusive pgss->lock. The real solution is probably to rely on the new > pluggable statistic architecture rather than using the hash table / query text > file. I'm sorry I left out some details earlier. I found that the garbage collect backend process was in the loop of gc_qtexts while for a long time. The main backtrace is below. ``` #0 0x00007fc528d6aba0 in __write_nocancel () from /lib64/libc.so.6 #1 0x00007fc528cf52f3 in _IO_new_file_write () from /lib64/libc.so.6 #2 0x00007fc528cf5b90 in __GI__IO_file_xsputn () from /lib64/libc.so.6 #3 0x00007fc528cea7e2 in fwrite () from /lib64/libc.so.6 #4 0x00007fc529199dd5 in gc_qtexts () at pg_stat_statements.c:2380 #5 pgss_store #6 0x00007fc52919a2b8 in pgss_post_parse_analyze (query=0x1e9aed8, pstate=0x178a220) at pg_stat_statements.c:900 ``` So I think the main reason for this long lock holding is that the I/O operation takes a long time because of these very long queries. In my production environment. pg_stat_statement.max is set to 1000. I found that when this problem occurred, gc took more than 20 seconds. If I limit the length of a single sql to 8k, it will only take 1.79 seconds. > Isn't the pg_stat_statements_info.dealloc counter enough to figure out the > root > issue? Only in my opinions, pg_stat_statements_info.dealloc doesn't reflect how long it takes for garbage collect. Earlier when I was checking the logs for abnormal periods, there is only some slow parse logging like below. > duration: 20834 ms parse S0_1: …... Best regards, Tinghai Zhao
