Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
-Original Message- From: Tomas Vondra [mailto:t...@fuzzy.cz] Sent: Friday, June 20, 2014 8:14 PM To: Pavel Stehule Cc: Huang, Suya; pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 On 20 Červen 2014, 5:33, Pavel Stehule wrote: 2014-06-20 1:44 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Thanks Pavel, to be more clear, what does pg_stat_reset really reset? In the document it says Reset all statistics counters for the current database to zero(requires superuser privileges) . I thought it would reset all statistics of all tables/indexes, thus why I am thinking of re-run analyze database to gather statistics. Because if table/indexes don't have statistics, the query plan would be affected which is not a good thing to a production box... I'm not so sure if I understand run statistics you mentioned here. you have true - anyway you can clean a content of this directory - but if your database has lot of database objects, your stat file will have a original size very early Pavel No, he's not right. Suya, as I wrote in my previous message, there are two kinds of statistics in PostgreSQL a) data distribution statistics - histograms, MCV lists, number of distinct values, ... - stored in regular tables - used for planning - collected by ANALYZE - not influenced by pg_stat_reset() at all b) runtime statistics - number of scans for table/index, rows fetched from table/index, ... - tracks activity within the database - stored in pgstat.stat file (or per-db files in the recent releases) - used for monitoring, not for planning - removed by pg_stat_reset() So running pg_stat_reset will not hurt planning at all. regards Tomas Hi Tomas, You're right, my DB version is 8.3.11, I remembered the wrong version... we've got a new project using the latest version 9.3.4, and the old DB will be decommissioned in the future, so that's why the management people don't want to spend resources on upgrading and QA, etc. Still have a question of why the file would become so big, is that related to the number of objects I have in database? Thanks again for your clear explanation on the two different statistics in PostgreSQL DB, really helped a lot! I'm wondering if they should also exist in the documentation, as it really confuses people... :) Thanks, Suya -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
On 20 Červen 2014, 5:33, Pavel Stehule wrote: 2014-06-20 1:44 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Thanks Pavel, to be more clear, what does pg_stat_reset really reset? In the document it says Reset all statistics counters for the current database to zero(requires superuser privileges) . I thought it would reset all statistics of all tables/indexes, thus why I am thinking of re-run analyze database to gather statistics. Because if table/indexes don't have statistics, the query plan would be affected which is not a good thing to a production box... I'm not so sure if I understand run statistics you mentioned here. you have true - anyway you can clean a content of this directory - but if your database has lot of database objects, your stat file will have a original size very early Pavel No, he's not right. Suya, as I wrote in my previous message, there are two kinds of statistics in PostgreSQL a) data distribution statistics - histograms, MCV lists, number of distinct values, ... - stored in regular tables - used for planning - collected by ANALYZE - not influenced by pg_stat_reset() at all b) runtime statistics - number of scans for table/index, rows fetched from table/index, ... - tracks activity within the database - stored in pgstat.stat file (or per-db files in the recent releases) - used for monitoring, not for planning - removed by pg_stat_reset() So running pg_stat_reset will not hurt planning at all. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
On 19 Červen 2014, 7:35, Huang, Suya wrote: From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:28 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 Hello The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be one per database cluster or one per database (from 9.3), These statistics should by reset by call pg_stat_reset() http://www.postgresql.org/docs/9.2/static/monitoring-stats.html Autovacuum on large stat files has significant overhead - it can be increasing by using new PostgreSQL (9.3) and by migration stat directory to ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on Linux) Regards Pavel 2014-06-19 6:38 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Hi group, We’ve found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be: 1. What’s a reasonable size of pgstat.stat file, can it be estimated? 2. What’s the safest way to reduce the file size to alleviate the IO impact on disk? 3. If need to drop all statistics, would a “analyze DB” command enough to eliminate the performance impact on queries? Thanks, Suya Hi Pavel, our version is 8.3.24, not 9.3. I also want to know the impact caused by run pg_stat_reset to application, is that able to be mitigated by doing an analyze database command? Hi, I really doubt you're on 8.3.24. The last version in 8.3 branch is 8.3.23. Running pg_stat_reset has no impact on planning queries. There are two kinds of statistics - those used for planning are stored withing the database, not in pgstat.stat file and are not influenced by pg_stat_reset. The stats in pgstat.stat are 'runtime stats' used for monitoring etc. so you may see some distuption in your monitoring system. ANALYZE command has nothing to do with the stats in pgstat.stat. However, if you really have a pgstat.stat this large, this is only a temporary solution - it will grow back, possibly pretty quickly, depending on how often you access the objects. Another option is to move the file to a tmpfs (ramdisk) partition. It will eliminate the IO overhead, but it will consume more CPU (because it still needs to be processed, and IO is not the bottleneck anymore). The other thing is that you should really start thinking about upgrading to a supported version. 8.3 did not get updates for 1 year (and won't). Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:41 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 2014-06-19 7:35 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:28 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 Hello The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be one per database cluster or one per database (from 9.3), These statistics should by reset by call pg_stat_reset() http://www.postgresql.org/docs/9.2/static/monitoring-stats.html Autovacuum on large stat files has significant overhead - it can be increasing by using new PostgreSQL (9.3) and by migration stat directory to ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on Linux) Regards Pavel 2014-06-19 6:38 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Hi group, We’ve found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be: 1. What’s a reasonable size of pgstat.stat file, can it be estimated? 2. What’s the safest way to reduce the file size to alleviate the IO impact on disk? 3. If need to drop all statistics, would a “analyze DB” command enough to eliminate the performance impact on queries? Thanks, Suya Hi Pavel, our version is 8.3.24, not 9.3. I also want to know the impact caused by run pg_stat_reset to application, is that able to be mitigated by doing an analyze database command? your version is too old - you can try reset statistics. ANALYZE statement should not have a significant impact on these runtime statistics. Pavel Attention: PostgreSQL 8.3 is unsupported now Thanks, Suya Thanks Pavel, to be more clear, what does pg_stat_reset really reset? In the document it says Reset all statistics counters for the current database to zero(requires superuser privileges) . I thought it would reset all statistics of all tables/indexes, thus why I am thinking of re-run analyze database to gather statistics. Because if table/indexes don't have statistics, the query plan would be affected which is not a good thing to a production box... I'm not so sure if I understand run statistics you mentioned here. Thanks, Suya -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
2014-06-20 1:44 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:41 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 2014-06-19 7:35 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:28 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 Hello The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be one per database cluster or one per database (from 9.3), These statistics should by reset by call pg_stat_reset() http://www.postgresql.org/docs/9.2/static/monitoring-stats.html Autovacuum on large stat files has significant overhead - it can be increasing by using new PostgreSQL (9.3) and by migration stat directory to ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on Linux) Regards Pavel 2014-06-19 6:38 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Hi group, We’ve found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be: 1. What’s a reasonable size of pgstat.stat file, can it be estimated? 2. What’s the safest way to reduce the file size to alleviate the IO impact on disk? 3. If need to drop all statistics, would a “analyze DB” command enough to eliminate the performance impact on queries? Thanks, Suya Hi Pavel, our version is 8.3.24, not 9.3. I also want to know the impact caused by run pg_stat_reset to application, is that able to be mitigated by doing an analyze database command? your version is too old - you can try reset statistics. ANALYZE statement should not have a significant impact on these runtime statistics. Pavel Attention: PostgreSQL 8.3 is unsupported now Thanks, Suya Thanks Pavel, to be more clear, what does pg_stat_reset really reset? In the document it says Reset all statistics counters for the current database to zero(requires superuser privileges) . I thought it would reset all statistics of all tables/indexes, thus why I am thinking of re-run analyze database to gather statistics. Because if table/indexes don't have statistics, the query plan would be affected which is not a good thing to a production box... I'm not so sure if I understand run statistics you mentioned here. you have true - anyway you can clean a content of this directory - but if your database has lot of database objects, your stat file will have a original size very early Pavel Thanks, Suya
Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
Hello The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be one per database cluster or one per database (from 9.3), These statistics should by reset by call pg_stat_reset() http://www.postgresql.org/docs/9.2/static/monitoring-stats.html Autovacuum on large stat files has significant overhead - it can be increasing by using new PostgreSQL (9.3) and by migration stat directory to ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on Linux) Regards Pavel 2014-06-19 6:38 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Hi group, We’ve found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be: 1. What’s a reasonable size of pgstat.stat file, can it be estimated? 2. What’s the safest way to reduce the file size to alleviate the IO impact on disk? 3. If need to drop all statistics, would a “analyze DB” command enough to eliminate the performance impact on queries? Thanks, Suya
Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:28 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 Hello The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be one per database cluster or one per database (from 9.3), These statistics should by reset by call pg_stat_reset() http://www.postgresql.org/docs/9.2/static/monitoring-stats.html Autovacuum on large stat files has significant overhead - it can be increasing by using new PostgreSQL (9.3) and by migration stat directory to ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on Linux) Regards Pavel 2014-06-19 6:38 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Hi group, We’ve found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be: 1. What’s a reasonable size of pgstat.stat file, can it be estimated? 2. What’s the safest way to reduce the file size to alleviate the IO impact on disk? 3. If need to drop all statistics, would a “analyze DB” command enough to eliminate the performance impact on queries? Thanks, Suya Hi Pavel, our version is 8.3.24, not 9.3. I also want to know the impact caused by run pg_stat_reset to application, is that able to be mitigated by doing an analyze database command? Thanks, Suya -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
2014-06-19 7:35 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:28 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 Hello The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be one per database cluster or one per database (from 9.3), These statistics should by reset by call pg_stat_reset() http://www.postgresql.org/docs/9.2/static/monitoring-stats.html Autovacuum on large stat files has significant overhead - it can be increasing by using new PostgreSQL (9.3) and by migration stat directory to ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on Linux) Regards Pavel 2014-06-19 6:38 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: Hi group, We’ve found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be: 1. What’s a reasonable size of pgstat.stat file, can it be estimated? 2. What’s the safest way to reduce the file size to alleviate the IO impact on disk? 3. If need to drop all statistics, would a “analyze DB” command enough to eliminate the performance impact on queries? Thanks, Suya Hi Pavel, our version is 8.3.24, not 9.3. I also want to know the impact caused by run pg_stat_reset to application, is that able to be mitigated by doing an analyze database command? your version is too old - you can try reset statistics. ANALYZE statement should not have a significant impact on these runtime statistics. Pavel Attention: PostgreSQL 8.3 is unsupported now Thanks, Suya
