Re: [PATCH] Increase the maximum value track_activity_query_size

Here is what ORMs do:

select length('SELECT "column_name_1001", "column_name_1002",
"column_name_1003", "column_name_1004", "column_name_1005",
"column_name_1006", "column_name_1007", "column_name_1008",
"column_name_1009", "column_name_1010", "column_name_1011",
"column_name_1012", "column_name_1013", "column_name_1014",
"column_name_1015", "column_name_1016", "column_name_1017",
"column_name_1018", "column_name_1019", "column_name_1020",
"column_name_1021", "column_name_1022", "column_name_1023",
"column_name_1024", "column_name_1025", "column_name_1026",
"column_name_1027", "column_name_1028", "column_name_1029",
"column_name_1030", "column_name_1031", "column_name_1032",
"column_name_1033", "column_name_1034", "column_name_1035",
"column_name_1036", "column_name_1037", "column_name_1038",
"column_name_1039", "column_name_1040", "column_name_1041",
"column_name_1042", "column_name_1043", "column_name_1044",
"column_name_1045", "column_name_1046", "column_name_1047",
"column_name_1048", "column_name_1049", "column_name_1050" FROM
"some_table";');
length
--------
1024
(1 row)

That's it – with default settings, you won't see WHERE clause or
anything else.

It is not only about analytical workloads. I see it for regular OLTP
workloads literally *any* large project that use an ORM. Ruby on Rails'
ActiveRecord does it, Java'sHibernate does, and so on.

As a result, many queries exceed track_activity_query_size, and we
end up having queries trimmed in pg_stat_activity. Why it is bad:- it
makes an automated analysis involving pg_stat_activity impossible,
it complicates any manual troubleshooting involving pg_stat_activity.

Changing this parameter in a mission-critical database is difficult
because it requires a restart.

+1 for changing it to 1M or at least to 100k. If the penalty is significant,
at least 10k.

What is the overhead here except the memory consumption?
Consumption of, say,100 * 1M = 1MiB of RAM is a low price for much
better transparency here. But what about the performance penalty?
Some benchmark would be nice to answer this.

On Fri, Dec 20, 2019 at 6:48 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Fri, Dec 20, 2019 at 08:57:04AM -0500, Bruce Momjian wrote:
> > I can imagine using larger pgstat_track_activity_query_size values for
> > data warehouse queries, where they are long and there are only a few of
> > them.
>
> Why are those queries that long anyway? A too long IN clause with an
> insane amount of parameters which could be replaced by an ANY clause
> with an array?
> --
> Michael
>