Meningkatkan Kinerja Zabbix + PostgreSQL dengan Partisi dan Pengindeksan

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) 

 # Versi DB: 11
 # Jenis OS: linux
 # Jenis DB: web
 # Total Memory (RAM): 8 GB
 # CPU num: 1
 # Koneksi num: 100
 # Penyimpanan Data: hdd

 max_connections = 100 # jumlah maksimum koneksi database bersamaan
 shared_buffers = ukuran memori 2GB # untuk berbagai buffer (terutama cache blok tabel dan blok indeks) dalam memori bersama
 effective_cache_size = 6GB # ukuran maksimum memori yang diperlukan untuk eksekusi permintaan menggunakan indeks
 maintenance_work_mem = 512MB # memengaruhi kecepatan operasi VACUUM, ANALYZE, CREATE INDEX
 checkpoint_completion_target = 0,7 # target waktu untuk menyelesaikan prosedur pos pemeriksaan
 wal_buffers = 16 MB # jumlah memori yang digunakan oleh Memori Bersama untuk memelihara log transaksi
 default_statistics_target = 100 # jumlah statistik yang dikumpulkan oleh perintah ANALYZE - ketika meningkat, optimizer membangun kueri lebih lambat, tetapi lebih baik
 random_page_cost = 4 # biaya bersyarat dari akses indeks ke halaman data - memengaruhi keputusan untuk menggunakan indeks
 effective_io_concurrency = 2 # jumlah operasi I / O asinkron yang akan dilakukan oleh DBMS dalam sesi terpisah
 work_mem = 10485kB # jumlah memori yang digunakan untuk mengurutkan dan tabel hash sebelum menggunakan file sementara pada disk
 min_wal_size = batas 1GB # di bawah jumlah file WAL yang akan didaur ulang untuk digunakan di masa mendatang
 max_wal_size = batas 2GB # di atas jumlah file WAL yang akan didaur ulang untuk digunakan di masa mendatang 

 # mengelola penangan permintaan bersamaan
 max_worker_processes = 8 # jumlah maksimum proses latar belakang - setidaknya satu per basis data
 max_parallel_workers_per_gather = 4 # jumlah maksimum proses paralel dalam satu permintaan
 max_parallel_workers = 8 # jumlah maksimum proses kerja yang dapat didukung sistem untuk operasi paralel

 # pengaturan logging (cara mudah untuk mengetahui tentang waktu eksekusi permintaan tanpa menggunakan ekstensi pg_stat_statements)
 log_min_duration_statement = 3000 # tulis ke log, durasi pelaksanaan semua perintah yang waktu operasinya> = dari nilai yang ditentukan dalam ms
 log_duration = off # catat durasi setiap perintah yang diselesaikan
 log_statement = 'tidak ada' # yang perintah SQL untuk menulis ke log, nilai: tidak ada (dinonaktifkan), ddl, mod dan semua (semua perintah)
 debug_print_plan = off # output dari pohon rencana kueri untuk analisis lebih lanjut

 # peras maksimal dari database dan bersiaplah untuk kegagalan apa pun (untuk yang paling tertekan, yang mengabaikan keberadaan ssd dan gugus terdistribusi)
 #fsync = off # tulis fisik ke disk perubahan, menonaktifkan fsync memberikan peningkatan kecepatan, tetapi dapat menyebabkan kegagalan permanen
 #synchronous_commit = off # memungkinkan Anda untuk merespons klien bahkan sebelum informasi transaksi ada di WAL - sebuah alternatif yang hampir aman untuk menonaktifkan fsync
 #full_page_writes = off # shutdown mempercepat operasi normal, tetapi dapat menyebabkan korupsi data atau korupsi data jika sistem macet 

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

 # di mana UUID adalah pengidentifikasi disk, Anda dapat melihatnya menggunakan utilitas blkid
 UUID = 121efe29-70bf-410b-bc71-90704568ce3b / var / lib / pgsql / 10 / data / base ext4 default, noatime 0 0 

 yum instal pg_pathman10
 nano /var/pgsqldb/postgresql.conf
 shared_preload_libraries = 'pg_pathman' # important - di sini tulis pg_pathman terakhir dalam daftar

 systemctl restart postgresql-10.service
 psql -d zabbix -U postgres
 BUAT EXTENSION pg_pathman;
 # konfigurasikan satu hari untuk tabel data pemantauan utama
 # 1552424400 - hitung mundur sebagai cap waktu unix, 86400 - detik dalam beberapa hari
 pilih create_range_partitions ('history', 'clock', 1552424400, 86400);
 pilih create_range_partitions ('history_uint', 'clock', 1552424400, 86400);
 pilih create_range_partitions ('history_text', 'clock', 1552424400, 86400);
 pilih create_range_partitions ('history_str', 'clock', 1552424400, 86400);
 pilih create_range_partitions ('history_log', 'clock', 1552424400, 86400);
 # konfigurasikan selama tiga hari untuk tabel data pemantauan teragregasi
 # 1552424400 - hitung mundur sebagai cap waktu unix, 259200 - detik dalam tiga hari
 pilih create_range_partitions ('tren', 'jam', 1545771600, 259200);  
 pilih create_range_partitions ('tren_uint', 'jam', 1545771600, 259200); 

 # Daftar umum tabel yang dipartisi, penyimpanan konfigurasi utama:
 pilih * dari pathman_config;
 # representasi dengan semua bagian yang ada, serta orang tua dan batas jangkauan mereka:
 pilih * dari pathman_partition_list;
 # parameter tambahan yang menggantikan perilaku pg_pathman standar:
 pilih * dari pathman_config_params;
 # salin konten kembali ke tabel induk dan hapus partisi:
 pilih 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 

 BUAT ATAU GANTI FUNGSI public.delete_old_partitions (integer history_days, integer trend_days, str_days integer)
  RETURNS teks
  Plpgsql BAHASA
 AS $ fungsi $
 / *
 Fungsi menghapus semua partisi yang lebih lama dari jumlah hari yang ditentukan:
 history_days - untuk partisi history_x, history_uint_x
 tren_hari - untuk partisi tren_x, tren_uint_x
 str_days - untuk partisi history_str_x, history_text_x, history_log_x
 * /
 mendeklarasikan clock_today_start int;
 mendeklarasikan clock_delete_less_history int = 0;
 mendeklarasikan clock_delete_less_trends int = 0;
 mendeklarasikan clock_delete_less_strings int = 0;
 clock_delete_less int = 0;
 mendeklarasikan iterator int = 0;
 mendeklarasikan result_str text = '';
 mendeklarasikan teks buf_table_size;
 mendeklarasikan teks buf_table_len;
 mendeklarasikan teks partisi_name;
 mendeklarasikan teks clock_max;
 mendeklarasikan err_detail teks;
 mendeklarasikan t_start timestamp = clock_timestamp ();
 mendeklarasikan cap waktu t_end;
 mulai
     jika $ 1 <= 0 lalu mengembalikan 'up, sesuatu yang salah: argumen history_days harus berupa nilai integer positif';  berakhir jika;
     jika $ 2 <= 0 lalu mengembalikan 'up, sesuatu yang salah: argumen tren_days harus berupa nilai integer positif';  berakhir jika;
     jika $ 3 <= 0 lalu mengembalikan 'up, sesuatu yang salah: argumen str_days harus berupa nilai integer positif';  berakhir jika;
     clock_today_start = ekstrak (epoch from date_trunc ('day', now ())) :: int;
     clock_delete_less_history = ekstrak (epoch from date_trunc ('day', now ()) - ($ 1 :: teks || 'days') :: interval) :: int;
     clock_delete_less_trends = ekstrak (epoch from date_trunc ('day', now ()) - ($ 2 :: teks || 'days') :: interval) :: int;
     clock_delete_less_strings = ekstrak (zaman dari date_trunc ('hari', sekarang ()) - ($ 3 :: teks || 'hari') :: interval) :: int;
     clock_delete_less = sedikitnya (clock_delete_less_history, clock_delete_less_trends, clock_delete_less_strings);
     --meningkatkan pemberitahuan 'clock_today_start% (%)', to_timestamp (clock_today_start), clock_today_start;
     --meningkatkan pemberitahuan 'clock_delete_less_history% (%)% days', to_timestamp (clock_delete_less_history), clock_delete_less_history, $ 1;
     --meningkatkan pemberitahuan 'clock_delete_less_trends% (%)% days', to_timestamp (clock_delete_less_trends), clock_delete_less_trends, $ 2;
     --meningkatkan pemberitahuan 'clock_delete_less_strings% (%)% days', to_timestamp (clock_delete_less_strings), clock_delete_less_strings, $ 3;
     untuk partisi_name, clock_max di pilih partisi, range_max dari pathman_partition_list mana 
     range_max :: int <= terhebat (clock_delete_less_history, clock_delete_less_trends, clock_delete_less_strings) dan 
     (partisi :: teks seperti 'history%' atau partisi :: teks seperti 'tren%') dipesan oleh partisi asc
     lingkaran
         if (partisi_name ~ 'history_uint_ \ d' dan clock_max :: int <= clock_delete_less_history)
         atau (partisi_name ~ 'history_ \ d' dan clock_max :: int <= clock_delete_less_history)
         atau (partisi_name ~ 'tren_ \ d' dan clock_max :: int <= clock_delete_less_trends)
         atau (partisi_name ~ 'history_log_ \ d' dan clock_max :: int <= clock_delete_less_strings)
         atau (partisi_name ~ 'history_str_ \ d' dan clock_max :: int <= clock_delete_less_strings)
         atau (partisi_name ~ 'history_text_ \ d' dan clock_max :: int <= clock_delete_less_strings)
         lalu 
             iterator = iterator +1;
             naikkan pemberitahuan '%', format ('!!! delete% s% s', partisi_name, clock_max);
             pilih max (reltuples :: int), pg_size_pretty (jumlah (pg_relation_size (pg_class.oid)))) sebagai "size" dari pg_class di mana relname seperti partisi_name ||  '%' menjadi buf_table_len ketat, buf_table_size;
             jika result_str! = '' maka result_str = result_str ||  ',';  berakhir jika;
             result_str = result_str ||  format ('% s (dt <% s, len% s,% s)', partisi_name, to_char (to_timestamp (clock_max :: int), 'YYYY-MM-DD'), buf_table_len, buf_table_size);
             jalankan format ('drop table jika ada% s', partisi_name);
         berakhir jika;
     loop akhir;
     jika iterator = 0 lalu result_str = format ('tidak ada partisi untuk dihapus yang lebih lama, lalu% s date', to_char (to_timestamp (clock_delete_less), 'YYYY-MM-DD')); 
     else result_str = format ('dihapus% s partisi dalam% s detik:', iterator, trunc (ekstrak (detik dari (clock_timestamp () - t_start)) :: numeric, 3)) ||  result_str;
     berakhir jika;
     --menaikkan pemberitahuan '%', result_str;
     return result_str;
 pengecualian saat orang lain saat itu
    dapatkan diagnosa bertumpuk err_detail = PG_EXCEPTION_CONTEXT;
    format pengembalian ('ups, ada yang salah:% s [kode err% s],% s', sqlerrm, sqlstate, err_detail);
 akhir; 
 $ function $;

 - ketik: monitor basis data
 - name: delete_old_history_partitions
 - key: db.odbc.select [delete_old_history_partitions, zabbix]
 - ekspresi sql: pilih delete_old_partitions (3, 30, 30);
 # di sini, parameter panggilan fungsi delete_old_partitions menunjukkan waktu penyimpanan dalam beberapa hari 
 # untuk nilai numerik, nilai numerik teragregasi, dan nilai string
 - tipe data: Teks
 - interval pembaruan: 0
 - Interval pengguna: dijadwalkan dalam h2
 - periode penyimpanan riwayat: 90 hari
 - grup elemen data: Database

  2019-09-16 02:00:00, menghapus 3 partisi dalam 0,024 detik: tren_78 (dt <2019-08-17, len 1, 48 kB), history_193 (dt <2019-09-13, len 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
AS
    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
AS
    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