Shulou(Shulou.com)06/01 Report--

PostgreSQL provides the parameter client_min_messages to control the level of messages returned to the customer. The size of messages returned by different levels varies, and the default is NOTICE. In general, if the client executes SQL statements infrequently, the default parameter can be used, but if the client executes a lot of SQL, it makes sense to set this parameter to a higher level: you can reduce the size of the message, thereby reducing the network load and increasing throughput.

Brief introduction of parameters

Let's create a stored procedure where raise notice displays notification information

[local]: 5432 pg12@testdb=# CREATE OR REPLACE FUNCTION foo () RETURNS void ASpg12@testdb-# $$pg12@testdb$# BEGINpg12@testdb$# RAISE NOTICE 'some message';pg12@testdb$# RETURN;pg12@testdb$# END;pg12@testdb$# $$LANGUAGE' plpgsql';CREATE FUNCTIONTime: 389.227 ms [local]: 5432 pg12@testdb=#

Execute stored procedure

[local]: 5432 pg12@testdb=# show client_min_messages; client_min_messages-notice (1 row) Time: 0.589 ms [local]: 5432 pg12@testdb=# select foo (); NOTICE: some message foo-(1 row) Time: 26.838 ms [local]: 5432 pg12@testdb=# set client_min_messages='ERROR';SETTime: 0.361 ms [local]: 5432 pg12@testdb=# select foo () Foo-(1 row) Time: 1.638 ms [local]: 5432 pg12@testdb=#

When client_min_messages is set to ERROR, NOTICE notification information is no longer displayed.

Let's use the benchmarksql stress test tool to test the performance change after the parameter is changed from NOTICE to ERROR.

1.NOTICE

[xdb@localhost run] $. / runBenchmark.sh props.pg2019-09-25 12 Term-00 15 INFO-Term-00, 2019-09-25 12 12 INFO-Term-00, +-+ 2019-09-25 12 12 Term-00 25435 INFO-Term-00 BenchmarkSQL v4.1.12019-09-25 12 Term-00 15 INFO-Term-00, +-+ 2019-09-25 12 12 INFO-Term-00, (c) 2003, Raul Barbosa2019-09-25 12 12 INFO-Term-00 (C) 2004-2016, Denis Lussier2019-09-25 12 Jan Wieck2019 1515 INFO-Term-00, (c) 2016, Jan Wieck2019-09-25 12 12 INFO-Term-00, +-+ 2019-09-25 12 12 Jan Wieck2019 25435 INFO-Term-00 2019-09-25 12 driver=org.postgresql.Driver2019 15 INFO-Term-00, conn=jdbc:postgresql://192.168.26.28:5432/benchmarkdb2019-09-25 12 12 INFO 1515 INFO-Term-00, conn=jdbc:postgresql://192.168.26.28:5432/benchmarkdb2019-09-25 12 12 INFO 25435 INFO-Term-00, user=pg122019-09-25 12 12 INFO 15435 INFO-Term-00, 2019-09-25 12 12 INFO-Term-00, warehouses=322019-09-25 12 12 INFO-Term-00, terminals=202019-09-25 12 12 15 INFO-Term-00, terminals=202019-09-25 1212 RunMins=52019-09-25 12 Term-00 15 limitTxnsPerMin=02019 25435 INFO-Term-00, 2019-09-25 12 12 limitTxnsPerMin=02019 25435 INFO-Term-00, newOrderWeight=452019-09-25 12 12 paymentWeight=432019 25435 INFO-Term-00, paymentWeight=432019-09-25 12 12 paymentWeight=432019 25435 INFO-Term-00, orderStatusWeight=4 Term-00, Running Average 2019-09-25 12 12 limitTxnsPerMin=02019 2020 25706 INFO-Term-00 52216 Memory Usage: 26MB / 37MB 2019-09-25 12 INFO-Term-00, 2019-09-25 12 12 INFO-Term-00, 2019-09-25 12 INFO-Term-00, Measured tpmC (NewOrders) = 7448.89 2019-09-25 12 INFO-Term-00 Measured tpmTOTAL = 16626.58 2019-09-25 12 12 Term-00 20 INFO-Term-00, Session Start = 2019-09-25 12 12 Term-00 152019-09-25 12 12 INFO-Term-00, Session End = 2019-09-25 12 12 Term-00 25707 INFO-Term-00, Transaction Count = 83171 [xdb@localhost run] $[xdb@localhost run] $

2.ERROR

[xdb@localhost run] $. / runBenchmark.sh props.pg2019-09-25 12 INFO-Term-00, 2019-09-25 12 Term-00 22 22 INFO-Term-00, +-+ 2019-09-25 12 INFO-Term-00 BenchmarkSQL v4.1.12019-09-25 12 Term-00 22 57954 INFO-Term-00, +-- + 2019-09-25 12 12 INFO-Term-00, (c) 2003, Raul Barbosa2019-09-25 12 12 INFO-Term-00 2257955 (C) 2004-2016, Denis Lussier2019-09-25 12 Term-00 2257955 INFO-Term-00, (c) 2016, Jan Wieck2019-09-25 12 12 INFO-Term-00, +-- + 2019-09-25 12 Jan Wieck2019 2257955 INFO-Term-00 2019-09-25 12 driver=org.postgresql.Driver2019 22 INFO-Term-00, conn=jdbc:postgresql://192.168.26.28:5432/benchmarkdb2019-09-25 12 12 INFO 22 INFO-Term-00, user=pg122019-09-25 12 12 INFO 2257 955 INFO-Term-00, 2019-09-25 12 12 INFO-Term-00, warehouses=322019-09-25 12 12 INFO-Term-00, terminals=202019-09-25 12 12 INFO-Term-00, terminals=202019-09-25 12 12 INFO-Term-00 RunMins=52019-09-25 12 Term-00 22 Term-00 57955 INFO-Term-00, 2019-09-25 25 12 22 limitTxnsPerMin=02019 57955 INFO-Term-00, newOrderWeight=452019-09-25 12 22 Term-00 57955 INFO-Term-00, paymentWeight=432019-09-25 12 12 paymentWeight=432019 2257 955 INFO-Term-00, orderStatusWeight=4 Term-00, Running Average 2019-09-25 12 2752 INFO-Term-00 79156 Memory Usage: 18MB / 37MB 2019-09-25 12 INFO-Term-00, 2019-09-25 12 37MB 27 INFO-Term-00, Measured tpmC (NewOrders) = 7815.72 2019-09-25 12 INFO-Term-00 Measured tpmTOTAL = 17440.12 2019-09-25 12 INFO-Term-00, Session Start = 2019-09-25 12 Term-00 2222 INFO-Term-00, Session End = 2019-09-25 12 Term-00 2758 2019-09-25 12 2758 2019-09-25 12 24 INFO-Term-00, Transaction Count = 87254 [xdb@localhost run] $

TPMC 7815 vs 7448, an increase of 4.9%

In order to avoid the error caused by random disturbance of the system, it is retested.

-- NONE2019-09-25 1415 53 Term-00 05384 INFO-Term-00, Measured tpmC (NewOrders) = 7168.43 2019-09-25 1453 53 NONE2019 05384 INFO-Term-00, Measured tpmTOTAL = 15890.5 2019-09-25 1453 53 INFO-Term-00, Session Start = 2019-09-25 1453 1453 INFO-Term-00 Session End = 2019-09-25 14 xdb@localhost run 53 Term-00 052019-09-25 14 15 15 15 INFO-Term-00, Transaction Count = 79491 [xdb@localhost run] $--vacuum full -- client_min_messages=error2019-09-25 14 Term-00 44Measured tpmC 29101 INFO-Term-00, Measured tpmC (NewOrders) = 7239.02 2019-09-25 1441 14V 44V 29101 INFO-Term-00, Measured tpmTOTAL = 16079.06 2019-09-25 144V 44V 29101 INFO-Term-00, Session Start = 2019-09-25 14Rd 39140 282019-09-25 14Rd 44v 29102 INFO-Term-00 Session End = 2019-09-25 14 Term-00 4415 INFO-Term-00, Transaction Count = 80434 Murray-vacuum full -- update_process_title=off-- track_activities=off2019-09-25 1515 Term-00 01Term-00 INFO-Term-00, Measured tpmC (NewOrders) = 7253.43 2019-09-25 15151401Term-00 INFO-Term-00, Measured tpmTOTAL = 16111.73 2019-09-25 151Term-00 011862 INFO-Term-00, Session Start = 2019-09-25 141414145112019-09-2515151Rod 011862 INFO-Term-00 Session End = 2019-09-25 15 Term-00 01purl 112019-09-25 151purl 01purl 11862 INFO-Term-00, Transaction Count = 80584 Muir-vacuum full -- client_min_messages=error-- update_process_title=off-- track_activities=off2019-09-25 1515 Term-00 08Term-00 46923 INFO-Term-00, Measured tpmC (NewOrders) = 7194.55 2019-09-25 1515VOUR 46923 INFO-Term-00, Measured tpmTOTAL = 16059.38 2019-09-25 15VOV 08VOLAR 46923 INFO-Term-00, Session Start = 2019-09-25 15VOULAR 462019-09-25 15RAPHING 08RAPHING 46923 INFO-Term-00 Session End = 2019-09-25 15 INFO-Term-00, Transaction Count = 80350

It doesn't seem to have any effect.

references

Reducing log messages on the client

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.