通过分区和索引改善Zabbix + PostgreSQL性能

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版本：11
 ＃操作系统类型：linux
 ＃DB类型：web
 ＃总内存（RAM）：8 GB
 ＃CPU数量：1
 ＃连接数：100
 ＃数据存储：硬盘

 max_connections = 100＃最大并发数据库连接数
 shared_buffers = 2GB＃共享内存中各种缓冲区（主要是表块和索引块的缓存）的内存大小
 effective_cache_size = 6GB＃使用索引执行查询所需的最大内存大小
 maintenance_work_mem = 512MB＃影响操作速度VACUUM，ANALYZE和CREATE INDEX
 checkpoint_completion_target = 0.7＃完成检查点程序的目标时间
 wal_buffers = 16MB＃共享内存用于维护事务日志的内存量
 default_statistics_target = 100＃ANALYZE命令收集的统计信息的数量-当增加时，优化器生成查询的速度会变慢，但会更好
 random_page_cost = 4＃索引访问数据页的条件开销-影响使用索引的决策
 effective_io_concurrency = 2＃DBMS将在单独的会话中尝试执行的异步I / O操作数
 work_mem = 10485kB＃在磁盘上使用临时文件之前用于排序和哈希表的内存量
 min_wal_size = 1GB＃限制在WAL文件数量以下，将被回收以备将来使用
 max_wal_size = 2GB＃限制将被回收以供将来使用的WAL文件的数量 

 ＃管理并发请求处理程序
 max_worker_processes = 8＃后台进程的最大数量-每个数据库至少一个
 max_parallel_workers_per_gather = 4＃单个请求中的最大并行进程数
 max_parallel_workers = 8＃系统可以支持并行操作的最大工作流程数

 ＃日志记录设置（一种无需使用pg_stat_statements扩展即可了解请求执行时间的简便方法）
 log_min_duration_statement = 3000＃将所有操作时间> =指定值（以毫秒为单位）的命令的执行持续时间写入日志
 log_duration = off＃记录每个已完成命令的持续时间
 log_statement ='none'＃将哪个SQL命令写入日志，值：无（禁用），ddl，mod和全部（所有命令）
 debug_print_plan = off＃查询计划树的输出以供进一步分析

 ＃将最大值从数据库中挤出，并准备好在发生任何故障时获取它（对于最压抑的人，他们忽略了ssd和分布式集群的存在）
 #fsync = off＃物理写入更改磁盘，禁用fsync可提高速度，但可能导致永久性故障
 #synchronous_commit = off＃允许您甚至在事务信息进入WAL之前就响应客户端-禁用fsync几乎是安全的选择
 #full_page_writes = off＃关机可加快正常操作的速度，但如果系统崩溃，则可能导致数据损坏或数据损坏 

 挂载/ dev / sdc1 / var / lib / pgsql / 10 /数据/ base -o noatime 

 ＃其中UUID是磁盘的标识符，您可以使用blkid实用程序查看它
 UUID = 121efe29-70bf-410b-bc71-90704568ce3b / var / lib / pgsql / 10 /数据/基本ext4默认值，noatime 0 0 

百胜安装pg_pathman10
纳米/var/pgsqldb/postgresql.conf
 shared_preload_libraries ='pg_pathman'＃重要-这里将pg_pathman写在列表的最后

 systemctl重新启动postgresql-10.service
 psql -d zabbix -U postgres
创建扩展pg_pathman;
 ＃为主要监视数据表配置一天
 ＃1552424400-以unix时间戳记的倒数，86400-天数秒
选择create_range_partitions（'history'，'clock'，1552424400，86400）;
选择create_range_partitions（'history_uint'，'clock'，1552424400，86400）;
选择create_range_partitions（'history_text'，'clock'，1552424400，86400）;
选择create_range_partitions（'history_str'，'clock'，1552424400，86400）;
选择create_range_partitions（'history_log'，'clock'，1552424400，86400）;
 ＃配置三天的汇总监视数据表
 ＃1552424400-以unix时间戳记的倒数，259200-三天内的秒数
选择create_range_partitions（'trends'，'clock'，1545771600，259200）;  
选择create_range_partitions（'trends_uint'，'clock'，1545771600，259200）; 

 ＃分区表的一般列表，主要配置存储：
从pathman_config中选择*;
 ＃表示所有现有节及其父级和范围边界：
从pathman_partition_list中选择*；
 ＃覆盖标准pg_pathman行为的其他参数：
从pathman_config_params中选择*;
 ＃将内容复制回父表并删除分区：
选择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 

创建或替换功能public.delete_old_partitions（history_days整数，trends_days整数，str_days整数）
 返回文字
 语言plpgsql
 AS $功能$
 / *
该函数删除所有早于指定天数的分区：
 history_days-用于分区history_x，history_uint_x
 Trends_days-对于趋势趋势_x，趋势趋势_uint_x分区
 str_days-用于分区history_str_x，history_text_x，history_log_x
 * /
声明clock_today_start int;
声明clock_delete_less_history int = 0;
声明clock_delete_less_trends int = 0;
声明clock_delete_less_strings int = 0;
 clock_delete_less int = 0;
声明迭代器int = 0;
声明result_str text ='';
声明buf_table_size文本；
声明buf_table_len文本；
声明partition_name文本；
声明clock_max文本；
声明err_detail文本；
声明t_start timestamp = clock_timestamp（）;
声明t_end时间戳；
开始
    如果$ 1 <= 0，则返回'ups，出了点问题：history_days参数必须为正整数值'; 如果结束
    如果$ 2 <= 0，则返回'ups，出了点问题：trends_days参数必须为正整数值'; 如果结束
    如果$ 3 <= 0，则返回'ups，出了点问题：str_days参数必须为正整数值'; 如果结束
     clock_today_start =提取（date_trunc的纪元（'day'，现在（）））:: int;
     clock_delete_less_history =提取（date_trunc（'day'，now（））中的时期-（$ 1 :: text ||'days'）:: interval）:: int;
     clock_delete_less_trends =提取（date_trunc（'day'，now（））中的纪元-（$ 2 ::文本||'days'）::间隔）:: int;
     clock_delete_less_strings =提取（date_trunc（'day'，now（））中的时期-（$ 3 ::文本||'days'）::间隔）:: int;
     clock_delete_less =最少（clock_delete_less_history，clock_delete_less_trends，clock_delete_less_strings）；
     -提高通知'clock_today_start％（％）'，to_timestamp（clock_today_start），clock_today_start;
     -提高通知'clock_delete_less_history％（％）％天'，to_timestamp（clock_delete_less_history），clock_delete_less_history，$ 1;
     -提高通知'clock_delete_less_trends％（％）％days'，to_timestamp（clock_delete_less_trends），clock_delete_less_trends，$ 2;
     -提高通知'clock_delete_less_strings％（％）％days'，to_timestamp（clock_delete_less_strings），clock_delete_less_strings，$ 3;
    对于partition_name，在选择分区中为clock_max，在pathman_partition_list中为range_max，其中 
     range_max :: int <=最大值（clock_delete_less_history，clock_delete_less_trends，clock_delete_less_strings）和 
     （分区::文本（如“历史％”）或分区::文本（如“趋势％”））按分区升序排列
    循环
        如果（partition_name〜'history_uint_ \ d'和clock_max :: int <= clock_delete_less_history）
        或（partition_name〜'history_ \ d'和clock_max :: int <= clock_delete_less_history）
        或（partition_name〜'trends_ \ d'and clock_max :: int <= clock_delete_less_trends）
        或（partition_name〜'history_log_ \ d'和clock_max :: int <= clock_delete_less_strings）
        或（partition_name〜'history_str_ \ d'和clock_max :: int <= clock_delete_less_strings）
        或（partition_name〜'history_text_ \ d'和clock_max :: int <= clock_delete_less_strings）
        然后 
            迭代器=迭代器+ 1;
            引发通知'％'，格式（'!!! delete％s％s'，partition_name，clock_max）;
            从pg_class中选择max（reltuples :: int），pg_size_pretty（sum（pg_relation_size（pg_class.oid）））作为“大小”，其中relname如partition_name ||  '％'转换为严格的buf_table_len，buf_table_size；
            如果result_str！=''，则result_str = result_str ||  '，'; 如果结束
             result_str = result_str || 格式（'％s（dt <％s，len％s，％s）'，partition_name，to_char（to_timestamp（clock_max :: int），'YYYY-MM-DD'），buf_table_len，buf_table_size）;
            执行格式（如果存在％s，则删除表，partition_name）；
        如果结束
    结束循环
    如果iterator = 0，则result_str = format（“没有分区可删除较旧的日期，则为％s日期”，to_char（to_timestamp（clock_delete_less），“ YYYY-MM-DD”））； 
    否则result_str =格式（“已删除的％s个分区，以％s秒为单位：”，迭代器，截断（提取（从（clock_timestamp（）-t_start）的秒数）::数字3））  result_str;
    如果结束
     -提高通知'％'，result_str;
    返回result_str;
当别人那么例外
   获得堆叠的诊断信息err_detail = PG_EXCEPTION_CONTEXT;
   返回格式（'ups，出了点问题：％s [err code％s]，％s'，sqlerrm，sqlstate，err_detail）；
结束 
 $功能$;

 -类型：数据库监视器
 -名称：delete_old_history_partitions
 -键：db.odbc.select [delete_old_history_partitions，zabbix]
 -sql表达式：选择delete_old_partitions（3，30，30）;
 ＃在这里，delete_old_partitions函数调用的参数以天为单位指示存储时间 
 ＃用于数字值，聚合数字值和字符串值
 -数据类型：文本
 -更新间隔：0
 -用户间隔：计划为h2
 -历史记录存储期：90天
 -数据元素组：数据库

  2019-09-16 02:00:00，在0.024秒内删除了3个分区：trends_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）

/ *
该脚本允许您在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