Обсуждение: reason for default PGSTAT_ACTIVITY_SIZE
From my angle, it looks as if the default for PGSTAT_ACTIVITY_SIZE is too small, or rather that it ought to be configurable at least, so that longer current_query strings that are shown via pg_stat_get_backend_activity() in the system view pg_stat_activity aren't truncated. The use case is to watch pg_stat_activity when troubleshooting a busy/struggling live server which is building up a queue of long running queries, to be able to see which of your SQL is most problematic, and where it's coming from: We append a code reference as a comment to all SQL that is sent by the ORM (we use a python web application development framework, namely Django), in order to figure out where it originates, which is otherwise tricky with SQL that is generated by a translation layer - and this code reference is often cut off because the string is limited. Is there a good reason to set PGSTAT_ACTIVITY_SIZE at 256 and is my only option to recompile the server? Is there a practical limit/drawback to making the variable say 4 or 8 times the default? I am referring to this post here: http://archives.postgresql.org/pgsql-sql/2002-05/msg00087.php and this one: http://archives.postgresql.org/pgsql-novice/2004-02/msg00187.php according to which recompiling would be the only option, but that was a few years back so maybe it's a different story now. Regards, Frank
Le 05/01/2010 18:02, frank joerdens a écrit : > From my angle, it looks as if the default for PGSTAT_ACTIVITY_SIZE is > too small, or rather that it ought to be configurable at least, so > that longer current_query strings that are shown via > pg_stat_get_backend_activity() in the system view pg_stat_activity > aren't truncated. > > The use case is to watch pg_stat_activity when troubleshooting a > busy/struggling live server which is building up a queue of long > running queries, to be able to see which of your SQL is most > problematic, and where it's coming from: We append a code reference as > a comment to all SQL that is sent by the ORM (we use a python web > application development framework, namely Django), in order to figure > out where it originates, which is otherwise tricky with SQL that is > generated by a translation layer - and this code reference is often > cut off because the string is limited. > > Is there a good reason to set PGSTAT_ACTIVITY_SIZE at 256 and is my > only option to recompile the server? Is there a practical > limit/drawback to making the variable say 4 or 8 times the default? > > I am referring to this post here: > > http://archives.postgresql.org/pgsql-sql/2002-05/msg00087.php > > and this one: > > http://archives.postgresql.org/pgsql-novice/2004-02/msg00187.php > > according to which recompiling would be the only option, but that was > a few years back so maybe it's a different story now. > On current releases, it has a higher value (1024 IIRC). And it's a configuration parameter on 8.4. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
On Tue, Jan 5, 2010 at 6:24 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: [...] >> Is there a good reason to set PGSTAT_ACTIVITY_SIZE at 256 and is my >> only option to recompile the server? Is there a practical >> limit/drawback to making the variable say 4 or 8 times the default? [...] > On current releases, it has a higher value (1024 IIRC). And it's a > configuration parameter on 8.4. A cool that's great news! We'll probably deal with this issue then by going from 8.3->8.4 asap. But I just grepped the 8.3 source tree for PGSTAT_ACTIVITY_SIZE and it's not there? So the value/variable has a different name now? Cheers, Frank
Le 05/01/2010 20:36, Frank Joerdens a écrit : > On Tue, Jan 5, 2010 at 6:24 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: > [...] >>> Is there a good reason to set PGSTAT_ACTIVITY_SIZE at 256 and is my >>> only option to recompile the server? Is there a practical >>> limit/drawback to making the variable say 4 or 8 times the default? > [...] >> On current releases, it has a higher value (1024 IIRC). And it's a >> configuration parameter on 8.4. > > A cool that's great news! We'll probably deal with this issue then by > going from 8.3->8.4 asap. > > But I just grepped the 8.3 source tree for PGSTAT_ACTIVITY_SIZE and > it's not there? So the value/variable has a different name now? > PGBE_ACTIVITY_SIZE since 8.2. In 8.4, the GUC is named track_activity_query_size. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com