Cuted down unused autovacuum monitoring

[orlovmyk]

Results from transactions monitoring are also including autovacuum queries during checks, this fix will cut them down. Let me show few examples of running check queries at real database:

1. psql -U postgres -t -c "select coalesce(extract(epoch from max(age(now(), query_start))), 0) as exec_time, query from pg_stat_activity where state <> 'idle in transaction' and state <> 'idle' group by query order by exec_time desc", it will show us all running transactions, which is not in idle state in this output: quert_execution_time, query

   
   1950.319903 | VACUUM (ANALYZE);
   1500.462969 | autovacuum: VACUUM public.orders (to prevent wraparound)
   0.770166 | \r                                                                                                                                                                                                            +
            |         SELECT \r                                                                                                                                                                                             +
            |             orders.order_id, \r                                                                                                                                                                               +
            |             orders.service_id, \r                                                                                                                                                                             +
            |             service_types.service_short_name, \r                                                                                                                                                              +
            |             format(\r                                                                                                                                                                                         +
            |                 '<b>%s%s</b>%s',\r                                                                                                                                                                            +
            |                 CASE WHEN NOT vcwc.is_account THEN '<i class="icon-shopping-cart" title="Non Account"></i> ' END,\r                                                                                           +
            |                 vcwc.customer_name_iconed,\r                                                                                                                                                                  +
            |                 ', ' ||\r                                                                                                                                                                                     +
            |                 CASE WHEN address_coordinates2.lat IS NULL THEN \r                                                                                                                                            +
            |                     vcwc.full_address_iconed || ' <a href="/system/address&customer_address_id=' \r                                                                                                           +
            |                         || vcwc.customer_address_id || '" title="Set coordinates" target="_blank">'\r                                                                                                         +
            |                         || '<i class="icon-map-marker"></i></a>'\r                                                                                                                                            +
            |                 ELSE \r                                                                                                                                                                                       +
            |                     '<a href="/system/address&customer_address_id=' || vcwc.customer_address_id || '" '\r                                                                                                     +
            |                         || 'target="_blank"><i class="icon-map-marker"></i> ' || vcwc.full_address_iconed || '</a>'\r                                                                                         +
            |                 END\r                                                                                                                                                                                         +
            |             ) as name_and_address,\r                                                                                                                                                                          +
            |             order_weights.weight_gross, \r                                                                                                                                                                    +
            |             order_weights.weight_tare, \r                                                                                                                                                                     +
            |             order_weights.weight_ticket, \r                                                                                                                                                                   +
            |             grade, \r                                                                                                                                                                                         +
            |             grade_id, \r                                                                                                                                                                                      +
            |             orders.tip_id,\r                                                                                                                                                                                  +
            |                 \r                                                                                                                                                                                            +
            |                 CASE WHEN address_coordinates.lat IS NULL THEN \r                                                                                                                                             +
            |                 '<b>' || vcwc_tip.tip_name_iconed || '</b>, ' || vcwc_tip.tip_address_iconed \r                                                                                                               +
            |                     || ' <a href="/system/address&customer_address_id=' || vcwc_tip.customer_address_id || '" '\r                                                                                             +
            |                     || 'title="Set coordinates" target="_blank"><i class="icon-map-marker"></i></a>'\r                                                                                                        +
            |             ELSE \r                                                                                                                                                                                           +
            |                 '<a href="/system/address&customer_address_id=' || vcwc_tip.customer_address_id \r                                                                                                            +
            |                     || '" target="_blank"><i class="icon-map-marker"></i> ' || '<b>' || vcwc_tip.tip_name_iconed || '</b>, ' \r                                                                               +
            |                     || vcwc_tip.tip_address_iconed || '</a>'\r                                                                                                                                                +
            |             END as tip,\r                                                                                                                                                                                     +
            | \r                                                                                                                                                                                                            +
            | \r                                                                                                                                                                                                            +
            |                 driversheets.order_id, \r                                                                                                                                                                     +
            |                 vehicle_id, \r                                                                                                                                                                                +
            |                 job_order, \r                                                                                                                                                                                 +
            |                 reg_number, \r                                                                                                                                                                                +
            |                 cb.container as con
   0.001189 |                                                                                                                                                                                                               +
            |         WITH more_??_invoice_lines as (                                                                                                                                                                      +
            |             SELECT count(*), invoice_id                                                                                                                                                                       +
            |             FROM invoice_lines                                                                                                                                                                                +
            |             GROUP BY invoice_id                                                                                                                                                                               +
            |             HAVING count(*) >= ???                                                                                                                                                                        +
            |             ORDER BY count DESC                                                                                                                                                                               +
            |         )                                                                                                                                                                                                     +
            |         SELECT DISTINCT                                                                                                                                                                                       +
            |                                                                                                                                                                                                               +
            |             invoice_start,                                                                                                                                                                                    +
            |             is_closed,                                                                                                                                                                                        +
            |             a.is_exported,                                                                                                                                                                                    +
            |             cn.is_exported as notes_is_exported,                                                                                                                                                              +
            |             orders.customer_id,                                                                                                                                                                               +
            |             a.in_advance,                                                                                                                                                                                     +
            |             a.invoice_line_id,                                                                                                                                                                                +
            |             a.self_bill,                                                                                                                                                                                      +
            |                                                                                                                                                                                                               +
            |             CASE WHEN service_id IN (????)                                                                                                                                           +
            |                 THEN                                                                                                                                                                                          +
            |                     CASE WHEN a.in_advance IS TRUE                                                                                                                                                            +
            |                     THEN actual_quantity || ' (<b>' || quantity || '</b>)'           

As you can see the most slow transaction is autovacuum, whcih runs for over ~2000 second, or 33 minutes, but those results are not real. Autovacuum is system function and i think it's not the best decision to monitor it

2. Query from script returns us the value of avutovacuum

[root@zabbix ~]# zabbix_get -s ???.???.???.??? -k pgsql.transactions.active[*] 3351.53421

3. After fix we are getting real values of query's time

[root@zabbix ~]# zabbix_get -s ???.???.???.??? -k pgsql.transactions.active[*] 14.695067

Until this fix we need to put macros  {$PG_LONG_QUERY_THRESHOLD} up to value of 1800 (seconds) to get rid of alerts and didn't get alerts of real long queries

Regards
Regards

[lesovsky]

Thanks, You're completely right, previous version of the query takes into account all kind of activity, including autovacuum too. Accordingly to item's name autovacuum should be excluded.

[stephankn]

I still have trouble setting up a test environment to run all postgres versions 9.4 up. Is it confirmed that all autovacuum queries in these versions match that pattern (and it is not over-matching?)

Based on this, it should probably be "autovacuum: " https://github.com/postgres/postgres/blob/292ae8af79b4f1b09a327d39e80ef70943a28194/src/backend/postmaster/autovacuum.c#L3138

At least it is in since decades, so I assume it is safe to match it. https://github.com/postgres/postgres/commit/7aa4164363f48c9a8345a0fda48e21de75f499d6

[lesovsky]

There is no huge difference between "autovacuum" and "autovacuum: " and proposed query will effectively cut down autovacuums. Ideally, the query also should take into account pg_stat_activity.backend_type field, but this field added since pg-10.

[lesovsky]

regexp changed here 09033b5, accordingly to postgres source Thanks @stephankn for observations

[stephankn]

Thanks. Glad you double checked the field backend_type. To ensure compatibility it is better to match only the string, which should be quite unique already. I think you missed out the wildcard when changing the pattern. See me comment on the change.

[lesovsky]

yes, my mistake, returned wildcard here 00caf89