Verbessern der Zabbix + PostgreSQL-Leistung durch Partitionierung und Indizierung

import argparse """     .   20   5000    ( 2500  :  echo,  ;  ping,  ) """ TEMP_HEAD = """ <?xml version="1.0" encoding="UTF-8"?> <zabbix_export> <version>2.0</version> <date>2015-08-17T23:15:01Z</date> <groups> <group> <name>Templates</name> </group> </groups> <templates> <template> <template>Template Zabbix Srv Stress {count} passive {char}</template> <name>Template Zabbix Srv Stress {count} passive {char}</name> <description/> <groups> <group> <name>Templates</name> </group> </groups> <applications/> <items> """ TEMP_END = """</items> <discovery_rules/> <macros/> <templates/> <screens/> </template> </templates> </zabbix_export> """ TEMP_ITEM = """<item> <name>{k}</name> <type>0</type> <snmp_community/> <multiplier>0</multiplier> <snmp_oid/> <key>{k}</key> <delay>1m</delay> <history>3</history> <trends>365</trends> <status>0</status> <value_type>{t}</value_type> <allowed_hosts/> <units/> <delta>0</delta> <snmpv3_contextname/> <snmpv3_securityname/> <snmpv3_securitylevel>0</snmpv3_securitylevel> <snmpv3_authprotocol>0</snmpv3_authprotocol> <snmpv3_authpassphrase/> <snmpv3_privprotocol>0</snmpv3_privprotocol> <snmpv3_privpassphrase/> <formula>1</formula> <delay_flex/> <params/> <ipmi_sensor/> <data_type>0</data_type> <authtype>0</authtype> <username/> <password/> <publickey/> <privatekey/> <port/> <description/> <inventory_link>0</inventory_link> <applications/> <valuemap/> <logtimefmt/> </item> """ TMP_FNAME_DEFAULT = "Template_App_Zabbix_Server_Stress_{count}_passive_{char}.xml" chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" if __name__ == "__main__": parser = argparse.ArgumentParser( description='     zabbix') parser.add_argument('--items', dest='items', type=int, default=1000, help='-   (default: 1000)') parser.add_argument('--templates', dest='templates', type=int, default=1, help=f'-  [1-{len(chars)}] (default: 1)') args = parser.parse_args() items_count = args.items tmps_count = args.templates if not (tmps_count >= 1 and tmps_count <= len(chars)): sys.exit(f"Templates must be in range 1 - {len(chars)}") for i in range(tmps_count): fname = TMP_FNAME_DEFAULT.format(count=items_count, char=chars[i]) with open(fname, "w") as output: output.write(TEMP_HEAD.format(count=items_count, char=chars[i])) for k,t in [('stress.ping[{}-I-{:06d}]',3), ('stress.echo[{}-S-{:06d}]',4)]: for j in range(int(items_count/2)): output.write(TEMP_ITEM.format(k=k.format(chars[i],j),t=t)) output.write(TEMP_END) 

 # DB Version: 11
 # Betriebssystemtyp: Linux
 # DB Typ: Web
 # Gesamtspeicher (RAM): 8 GB
 # CPUs num: 1
 # Verbindungsnummer: 100
 # Datenspeicherung: Festplatte

 max_connections = 100 # maximale Anzahl gleichzeitiger Datenbankverbindungen
 shared_buffers = 2 GB # Speichergröße für verschiedene Puffer (hauptsächlich Cache von Tabellenblöcken und Indexblöcken) im gemeinsam genutzten Speicher
 effektive_cache_size = 6 GB # maximale Größe des erforderlichen Speichers für die Ausführung von Abfragen mithilfe von Indizes
 wartung_arbeit_mem = 512MB # beeinflusst die Betriebsgeschwindigkeit VACUUM, ANALYZE, CREATE INDEX
 checkpoint_completion_target = 0,7 # Zielzeit, um die Checkpoint-Prozedur abzuschließen
 wal_buffers = 16 MB # Speicherplatz, der vom gemeinsam genutzten Speicher zum Verwalten von Transaktionsprotokollen verwendet wird
 default_statistics_target = 100 # Anzahl der vom Befehl ANALYZE gesammelten Statistiken. Beim Erhöhen erstellt das Optimierungsprogramm Abfragen langsamer, aber besser
 random_page_cost = 4 # bedingte Kosten für den Indexzugriff auf Datenseiten - wirkt sich auf die Entscheidung zur Verwendung des Index aus
 effektive_io_concurrency = 2 # Anzahl asynchroner E / A-Vorgänge, die das DBMS in einer separaten Sitzung ausführen möchte
 work_mem = 10485kB # Die Menge an Speicher, die zum Sortieren und Hashing von Tabellen verwendet wird, bevor temporäre Dateien auf der Festplatte verwendet werden
 min_wal_size = 1 GB # liegt unter der Anzahl der WAL-Dateien, die für die zukünftige Verwendung recycelt werden
 max_wal_size = 2 GB # begrenzt zusätzlich die Anzahl der WAL-Dateien, die für die zukünftige Verwendung recycelt werden 

 # gleichzeitige Anforderungshandler verwalten
 max_worker_processes = 8 # die maximale Anzahl von Hintergrundprozessen - mindestens einer pro Datenbank
 max_parallel_workers_per_gather = 4 # maximale Anzahl paralleler Prozesse innerhalb einer einzelnen Anforderung
 max_parallel_workers = 8 # Die maximale Anzahl von Arbeitsprozessen, die das System für parallele Operationen unterstützen kann

 # Protokollierungseinstellungen (eine einfache Möglichkeit, die Ausführungszeit von Abfragen zu ermitteln, ohne die Erweiterung pg_stat_statements zu verwenden)
 log_min_duration_statement = 3000 # Schreibe die Dauer der Ausführung aller Befehle in die Protokolle, deren Betriebszeit> = des angegebenen Wertes in ms ist
 log_duration = off # zeichnet die Dauer jedes abgeschlossenen Befehls auf
 log_statement = 'none' # welche SQL-Befehle in das Protokoll geschrieben werden sollen, Werte: none (deaktiviert), ddl, mod und all (alle Befehle)
 debug_print_plan = off # Ausgabe des Abfrageplanbaums zur weiteren Analyse

 # Drücken Sie das Maximum aus der Datenbank heraus und seien Sie bereit, es für jeden Fehler zu erhalten (für die am meisten unterdrückten, die die Existenz von ssd und eines verteilten Clusters ignorieren)
 #fsync = off # physisches Schreiben auf die Änderungsdiskette. Das Deaktivieren von fsync führt zu einem Geschwindigkeitsgewinn, kann jedoch zu dauerhaften Fehlern führen
 Mit #synchronous_commit = off # können Sie dem Client antworten, noch bevor sich die Transaktionsinformationen in der WAL befinden - eine nahezu sichere Alternative zum Deaktivieren von fsync
 #full_page_writes = off # Das Herunterfahren beschleunigt den normalen Betrieb, kann jedoch zu Datenbeschädigung oder Datenbeschädigung führen, wenn das System abstürzt 

  mount / dev / sdc1 / var / lib / pgsql / 10 / data / base -o noatime 

 # wobei UUID die Kennung der Festplatte ist, können Sie sie mit dem Dienstprogramm blkid anzeigen
 UUID = 121efe29-70bf-410b-bc71-90704568ce3b / var / lib / pgsql / 10 / data / base ext4-Standardeinstellungen, noatime 0 0 

 yum installiere pg_pathman10
 nano /var/pgsqldb/postgresql.conf
 shared_preload_libraries = 'pg_pathman' # wichtig - hier schreibe pg_pathman zuletzt in die Liste

 systemctl starte postgresql-10.service neu
 psql -d zabbix -U postgres
 CREATE EXTENSION pg_pathman;
 # Konfigurieren Sie einen Tag für die Tabellen der primären Überwachungsdaten
 # 1552424400 - Countdown als Unix-Zeitstempel, 86400 - Sekunden in Tagen
 Wählen Sie create_range_partitions ('history', 'clock', 1552424400, 86400).
 Wählen Sie create_range_partitions ('history_uint', 'clock', 1552424400, 86400).
 Wählen Sie create_range_partitions ('history_text', 'clock', 1552424400, 86400).
 Wählen Sie create_range_partitions ('history_str', 'clock', 1552424400, 86400).
 Wählen Sie create_range_partitions ('history_log', 'clock', 1552424400, 86400).
 # Konfigurieren Sie drei Tage lang für aggregierte Überwachungsdatentabellen
 # 1552424400 - Countdown als Unix-Zeitstempel, 259200 - Sekunden in drei Tagen
 Wählen Sie create_range_partitions aus ('Trends', 'Uhr', 1545771600, 259200).  
 Wählen Sie create_range_partitions aus ('trend_uint', 'clock', 1545771600, 259200). 

 # allgemeine Liste der partitionierten Tabellen, Hauptkonfigurationsspeicher:
 wähle * aus pathman_config;
 # Darstellung mit allen vorhandenen Abschnitten sowie deren Eltern und Bereichsgrenzen:
 Wählen Sie * aus pathman_partition_list.
 # zusätzliche Parameter, die das Standardverhalten von pg_pathman überschreiben:
 Wählen Sie * aus pathman_config_params;
 # Kopieren Sie den Inhalt zurück in die übergeordnete Tabelle und löschen Sie Partitionen:
 select drop_partitions ('table_name' :: regclass, false);

 /*       */ SELECT nspname AS schemaname, relname, relkind, cast (reltuples as int), pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') and (relname like 'history%' or relname like 'trends%') and relkind = 'r' -- and reltuples > 0 -- and pg_relation_size(C.oid) >= 0 ORDER BY schemaname, relname 

 CREATE OR REPLACE FUNCTION public.delete_old_partitions (Ganzzahl history_days, Ganzzahl trend_days, Ganzzahl str_days)
  RETURNS Text
  SPRACHE plpgsql
 AS $ function $
 / *
 Die Funktion löscht alle Partitionen, die älter als die angegebene Anzahl von Tagen sind:
 history_days - für Partitionen history_x, history_uint_x
 Trends_Tage - für Partitionen Trends_x, Trends_Uint_x
 str_days - für Partitionen history_str_x, history_text_x, history_log_x
 * /
 deklarieren clock_today_start int;
 deklariere clock_delete_less_history int = 0;
 deklariere clock_delete_less_trends int = 0;
 deklariere clock_delete_less_strings int = 0;
 clock_delete_less int = 0;
 deklariere Iterator int = 0;
 deklariere result_str text = '';
 deklarieren Sie buf_table_size Text;
 deklarieren Sie buf_table_len text;
 deklariere partition_name text;
 deklariere clock_max text;
 deklariere err_detail text;
 deklariere t_start timestamp = clock_timestamp ();
 deklarieren Sie den Zeitstempel t_end;
 beginnen
     Wenn $ 1 <= 0, dann wird 'ups zurückgegeben, etwas stimmt nicht: Das Argument history_days muss ein positiver ganzzahliger Wert sein';  ende wenn;
     Wenn $ 2 <= 0 ist, wird "ups" zurückgegeben. Es stimmt etwas nicht: Das Argument "Trends_days" muss ein positiver ganzzahliger Wert sein. "  ende wenn;
     Wenn $ 3 <= 0, dann wird 'ups zurückgegeben, etwas stimmt nicht: Das Argument str_days muss ein positiver ganzzahliger Wert sein.';  ende wenn;
     clock_today_start = extract (Epoche von date_trunc ('day', now ())) :: int;
     clock_delete_less_history = extrahieren (Epoche von date_trunc ('Tag', jetzt ()) - ($ 1 :: Text || 'Tage') :: Intervall) :: int;
     clock_delete_less_trends = extract (Epoche von date_trunc ('Tag', jetzt ()) - ($ 2 :: Text || 'Tage') :: Intervall) :: int;
     clock_delete_less_strings = extract (Epoche von date_trunc ('Tag', jetzt ()) - ($ 3 :: text || 'Tage') :: Intervall) :: int;
     clock_delete_less = least (clock_delete_less_history, clock_delete_less_trends, clock_delete_less_strings);
     - Erhöhen Sie den Hinweis 'clock_today_start% (%)', to_timestamp (clock_today_start), clock_today_start;
     - Erhöhen Sie den Hinweis 'clock_delete_less_history% (%)% days', to_timestamp (clock_delete_less_history), clock_delete_less_history, $ 1;
     - Benachrichtigung 'clock_delete_less_trends% (%)% days', to_timestamp (clock_delete_less_trends), clock_delete_less_trends, $ 2;
     - Erhöhen Sie den Hinweis 'clock_delete_less_strings% (%)% days', to_timestamp (clock_delete_less_strings), clock_delete_less_strings, $ 3;
     für partition_name, clock_max in der ausgewählten Partition, range_max von pathman_partition_list where 
     range_max :: int <= am größten (clock_delete_less_history, clock_delete_less_trends, clock_delete_less_strings) und 
     (Partition :: Text wie 'Verlauf%' oder Partition :: Text wie 'Trends%') Reihenfolge nach Partition aufsteigend
     Schleife
         if (Partitionsname ~ 'history_uint_ \ d' und clock_max :: int <= clock_delete_less_history)
         oder (partition_name ~ 'history_ \ d' und clock_max :: int <= clock_delete_less_history)
         oder (Partitionsname ~ 'Trends_ \ d' und clock_max :: int <= clock_delete_less_trends)
         oder (Partitionsname ~ 'history_log_ \ d' und clock_max :: int <= clock_delete_less_strings)
         oder (Partitionsname ~ 'history_str_ \ d' und clock_max :: int <= clock_delete_less_strings)
         oder (partition_name ~ 'history_text_ \ d' und clock_max :: int <= clock_delete_less_strings)
         dann 
             Iterator = Iterator + 1;
             Benachrichtigung auslösen '%', Format ('!!!% s% s löschen', Partitionsname, Uhr_max);
             Wählen Sie max (reltuples :: int), pg_size_pretty (sum (pg_relation_size (pg_class.oid))) als "size" aus pg_class, wobei relname wie partition_name ||  '%' in strikte buf_table_len, buf_table_size;
             wenn result_str! = '' dann result_str = result_str ||  ',';  ende wenn;
             result_str = result_str ||  Format ('% s (dt <% s, len% s,% s)', Partitionsname, to_char (to_timestamp (clock_max :: int), 'JJJJ-MM-TT'), buf_table_len, buf_table_size);
             Format ausführen ('Tabelle löschen, falls vorhanden% s', Partitionsname);
         ende wenn;
     Endschleife;
     wenn iterator = 0, dann result_str = format ('es gibt keine Partitionen zum Löschen älterer, dann% s date', to_char (to_timestamp (clock_delete_less), 'YYYY-MM-DD')); 
     sonst result_str = format ('% s Partitionen in% s Sekunden gelöscht:', Iterator, Trunc (Extrakt (Sekunden von (clock_timestamp () - t_start)) :: numeric, 3)) ||  result_str;
     ende wenn;
     - Hinweis '%', result_str;
     return result_str;
 Ausnahme wenn andere dann
    Get Stacked Diagnostics err_detail = PG_EXCEPTION_CONTEXT;
    Rückgabeformat ('ups, etwas stimmt nicht:% s [Fehlercode% s],% s', sqlerrm, sqlstate, err_detail);
 Ende; 
 $ function $;

 - Typ: Datenbankmonitor
 - name: delete_old_history_partitions
 - Schlüssel: db.odbc.select [delete_old_history_partitions, zabbix]
 - SQL-Ausdruck: Wählen Sie delete_old_partitions (3, 30, 30).
 # Hier geben die Parameter des Funktionsaufrufs delete_old_partitions die Speicherzeit in Tagen an 
 # für numerische Werte, aggregierte numerische Werte und Zeichenfolgenwerte
 - Datentyp: Text
 - Aktualisierungsintervall: 0
 - Benutzerintervall: geplant in h2
 - Verlaufslagerzeit: 90 Tage
 - Datenelementgruppe: Datenbank

  2019-09-16 02:00:00, 3 Partitionen in 0,024 Sekunden gelöscht: Trends_78 (dt <2019-08-17, Länge 1, 48 kB), Verlauf_193 (dt <2019-09-13, Länge 85343, 9448 kB) ), history_uint_186 (dt <2019-09-13, len 27969, 3480 kB)

/*
     gin    biginteger  integer    .
 -   gin     int2, int4, int8,
       bigint     ,     bigint (<= 2147483647)
        intger_ops,  :
create index on tablename using gin(columnname int8_family_ops) with (fastupdate = false);
*/

--       btree_gin
CREATE EXTENSION btree_gin;

CREATE OPERATOR FAMILY integer_ops using gin;

CREATE OPERATOR CLASS int4_family_ops
FOR TYPE int4 USING gin FAMILY integer_ops
 Wie
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 btint4cmp(int4,int4),
    FUNCTION 2 gin_extract_value_int4(int4, internal),
    FUNCTION 3 gin_extract_query_int4(int4, internal, int2, internal, internal),
    FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
    FUNCTION 5 gin_compare_prefix_int4(int4,int4,int2, internal),
STORAGE int4;

CREATE OPERATOR CLASS int8_family_ops
FOR TYPE int8 USING gin FAMILY integer_ops
 Wie
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 btint8cmp(int8,int8),
    FUNCTION 2 gin_extract_value_int8(int8, internal),
    FUNCTION 3 gin_extract_query_int8(int8, internal, int2, internal, internal),
    FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
    FUNCTION 5 gin_compare_prefix_int8(int8,int8,int2, internal),
STORAGE int8;

ALTER OPERATOR FAMILY integer_ops USING gin add
  OPERATOR 1 <(int4,int8),
  OPERATOR 2 <=(int4,int8),
  OPERATOR 3 =(int4,int8),
  OPERATOR 4 >=(int4,int8),
  OPERATOR 5 >(int4,int8);

ALTER OPERATOR FAMILY integer_ops USING gin add
  OPERATOR 1 <(int8,int4),
  OPERATOR 2 <=(int8,int4),
  OPERATOR 3 =(int8,int4),
  OPERATOR 4 >=(int8,int4),
  OPERATOR 5 >(int8,int4);

/*
        
*/

--   
drop index history_1;
drop index history_uint_1;
drop index history_str_1;
drop index history_text_1;
drop index history_log_1;
--          PK 
-- (   ,         )
alter table trends drop constraint trends_pk;
alter table trends_uint drop constraint trends_uint_pk;

--     bree-gin   itemid    
--   btree-gin  bigint       
-- https://habr.com/ru/company/postgrespro/blog/340978/#comment_10545932
--    create extension btree_gin;
create index on history using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_uint using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_str using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_text using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_log using gin(itemid int8_family_ops) with (fastupdate = false);
create index on trends using gin(itemid int8_family_ops) with (fastupdate = false);
create index on trends_uint using gin(itemid int8_family_ops) with (fastupdate = false);

--     bree-gin   itemid    
--     brin    128 ,    
--           ,
--      https://habr.com/ru/company/postgrespro/blog/346460/
create index on history using brin(clock) with (pages_per_range = 128);
create index on history_uint using brin(clock) with (pages_per_range = 128);
create index on history_str using brin(clock) with (pages_per_range = 128);
create index on history_text using brin(clock) with (pages_per_range = 128);
create index on history_log using brin(clock) with (pages_per_range = 128);
create index on trends using brin(clock) with (pages_per_range = 128);
create index on trends_uint using brin(clock) with (pages_per_range = 128);

 CREATE EXTENSION pg_stat_statements; 

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000 #   sql ,     (     );
pg_stat_statements.track = all # all -   (    ), top -   /, none -  
pg_stat_statements.save = true #     

 SELECT pg_stat_statements_reset(); 

 select substring(query from '[^(]*') as query_sub, sum(calls) as calls, avg(mean_time) as mean_time from pg_stat_statements where query ~ 'insert into' or query ~ 'update trends' group by substring(query from '[^(]*') order by calls desc