pg_stat_statements fwrite not save

less than 1 minute read

背景

PostgreSQL 9.4 对pg_stat_statements插件的一个重大修改,它在使用过程中需要使用文件,所有的操作都会fwrite文件。

http://www.postgresql.org/docs/9.4/static/release-9-4.html

这么做的好处是,可以记录很多SQL。

但是这里也带来了其他问题,IO的问题,当然WRITE操作会由操作系统调度刷脏页,所以实际上可能不会产生太多的文件系统IO。

不过即使你pg_stat_statements.save=off,也会写文件,记住了哦。

因为save=off只是在启动和关闭时清理文件,而在使用过程中都是需要写文件的。

E.5.3.12.2. pg_stat_statements  
  
Make pg_stat_statements use a file, rather than shared memory, for query text storage (Peter Geoghegan)  
  
This removes the previous limitation on query text length, and allows a higher number of unique statements to be tracked by default.  
  
Allow reporting of pg_stat_statements's internal query hash identifier (Daniel Farina, Sameer Thakur, Peter Geoghegan)  
  
Add the ability to retrieve all pg_stat_statements information except the query text (Peter Geoghegan)  
  
This allows monitoring tools to fetch query text only for just-created entries, improving performance during repeated querying of the statistics.  
  
Make pg_stat_statements ignore DEALLOCATE commands (Fabien Coelho)  
  
It already ignored PREPARE, as well as planning time in general, so this seems more consistent.  
  
Save the statistics file into $PGDATA/pg_stat at server shutdown, rather than $PGDATA/global (Fujii Masao)  

详见代码:

contrib/pg_stat_statements/pg_stat_statements.c

这三个函数会

pgss_post_parse_analyze  
pgss_ExecutorEnd  
pgss_ProcessUtility  

调用

pgss_store

调用

qtext_store

调用

write 写文件

9.3 以及以前的版本,如果pg_stat_statements.save=off, 则绝对不会写文件。只有=on时会把内存中的信息dump到pg_stat/*中。

Flag Counter

digoal’s 大量PostgreSQL文章入口