تحسين أداء 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
 نوع نظام التشغيل: لينكس
 # DB نوع: الويب
 # إجمالي الذاكرة (RAM): 8 جيجابايت
 # وحدات المعالجة المركزية الأسطوانات: 1
 # اتصالات الأسطوانات: 100
 # تخزين البيانات: الأقراص الصلبة

 max_connections = 100 # الحد الأقصى لعدد اتصالات قاعدة البيانات المتزامنة
 Shared_buffers = 2GB # حجم الذاكرة لمختلف المخازن المؤقتة (ذاكرة التخزين المؤقت بشكل رئيسي من كتل الجدول وكتل الفهرس) في الذاكرة المشتركة
 effect_cache_size = 6GB # الحد الأقصى لحجم الذاكرة المطلوبة لتنفيذ الاستعلام باستخدام الفهارس
 maintenance_work_mem = 512 ميجابايت # يؤثر على سرعة العمليات VACUUM ، ANALYZE ، CREATE INDEX
 checkpoint_completion_target = 0.7 # الوقت المستهدف لإكمال إجراءات نقاط التفتيش
 wal_buffers = 16 ميغابايت # مقدار الذاكرة المستخدمة من قبل الذاكرة المشتركة للحفاظ على سجلات المعاملات
 default_statistics_target = 100 # كمية الإحصائيات التي يتم جمعها بواسطة أمر ANALYZE - عند الزيادة ، يبني المحسن الاستعلامات ببطء أكثر ، لكن أفضل
 random_page_cost = 4 # التكلفة الشرطية للوصول إلى الفهرس إلى صفحات البيانات - تؤثر على قرار استخدام الفهرس
 effect_io_concurrency = 2 # عدد عمليات الإدخال / الإخراج غير المتزامنة التي سيحاول DBMS تنفيذها في جلسة منفصلة
 work_mem = 10485 كيلو بايت # مقدار الذاكرة المستخدمة للفرز وجداول التجزئة قبل استخدام الملفات المؤقتة على القرص
 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 = إيقاف # تسجيل مدة كل أمر مكتمل
 log_statement = 'none' # التي أوامر SQL الكتابة إلى السجل ، والقيم: بلا (تعطيل) ، ddl ، وزارة الدفاع وجميع (جميع الأوامر)
 debug_print_plan = إيقاف # إخراج شجرة خطة الاستعلام لمزيد من التحليل

 # ضغط الحد الأقصى للخروج من قاعدة البيانات وكن جاهزًا للحصول عليه لأي عطل (للأكثر قمعًا ، الذين يتجاهلون وجود SSD ومجموعة نظامية موزعة)
 #fsync = off # الكتابة الفعلية على قرص التغييرات ، يؤدي تعطيل fsync إلى زيادة السرعة ، ولكن يمكن أن يؤدي إلى فشل دائم
 #synchronous_commit = off # يسمح لك بالرد على العميل حتى قبل أن تكون معلومات المعاملة في WAL - بديل آمن تقريبًا لتعطيل fsync
 #full_page_writes = off # shutdown يسرع العمليات العادية ، لكن يمكن أن يؤدي إلى تلف البيانات أو تلف البيانات إذا تعطل النظام 

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

 # حيث UUID هو معرف القرص ، يمكنك رؤيته باستخدام الأداة المساعدة blkid
 UUID = 121efe29-70bf-410b-bc71-90704568ce3b / var / lib / pgsql / 10 / data / base ext4 defaults، noatime 0 0 

 يم تثبيت pg_pathman10
 nano /var/pgsqldb/postgresql.conf
 Shared_preload_libraries = 'pg_pathman' # مهم - هنا اكتب pg_pathman آخر مرة في القائمة

 systemctl إعادة تشغيل postgresql-10.service
 psql -d zabbix -U postgres
 إنشاء تمدد pg_pathman ؛
 # تكوين يوم واحد لجداول بيانات الرصد الأساسية
 # 1552424400 - العد التنازلي كما الطابع الزمني يونيكس ، 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 - العد التنازلي كما الطابع الزمني يونيكس ، 259200 - ثانية في ثلاثة أيام
 حدد create_range_partitions ("الاتجاهات" ، "الساعة" ، 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 عدد صحيح ، اتجاهات عدد صحيح ، عدد صحيح str_days)
  عوائد النص
  اللغة plpgsql
 كما $ وظيفة $
 / *
 تحذف الدالة جميع الأقسام الأقدم من عدد الأيام المحدد:
 history_days - للأقسام history_x ، history_uint_x
 trends_days - بالنسبة للجدران trends_x ، trends_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 ؛
 يعلن النص_الفصل النص ؛
 إعلان النص clock_max ؛
 إعلان عن خطأ في كتابة النص ؛
 قم بتعريف t_start timestamp = clock_timestamp ()؛
 أعلن t_end الطابع الزمني ؛
 بدأ
     إذا كان $ 1 <= 0 ثم إرجاع "ups" ، فهذا خطأ: يجب أن تكون الوسيطة history_days قيمة عدد صحيح موجب "؛  نهاية إذا ؛
     إذا كان $ 2 <= 0 ثم إرجاع "ups" ، هناك خطأ ما: يجب أن تكون وسيطة trends_days قيمة عدد صحيح موجب "؛  نهاية إذا ؛
     إذا كان $ 3 <= 0 ثم عاود عمليات ، فهذا خطأ: يجب أن تكون الوسيطة str_days قيمة عدد صحيح موجب ؛  نهاية إذا ؛
     clock_today_start = extract (epoch from date_trunc ('day'، now ()) :: :: int؛
     clock_delete_less_history = extract (epoch from date_trunc ('day'، now ()) - ($ 1 :: text || 'days') :: interval) :: int؛
     clock_delete_less_trends = extract (epoch from date_trunc ('day'، now ()) - ($ 2 :: text || 'days') :: interval) :: int؛
     clock_delete_less_strings = extract (epoch from date_trunc ('day'، now ()) - ($ 3 :: text || 'days') :: interval) :: 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٪ (٪)٪ أيام" ، to_timestamp (clock_delete_less_trends) ، clock_delete_less_trends ، $ 2 ؛
     - لاحظ إشعار "clock_delete_less_strings٪ (٪)٪ أيام" ، to_timestamp (clock_delete_less_strings) ، clock_delete_less_strings ، $ 3 ؛
     بالنسبة لـ partition_name ، clock_max في قسم محدد ، range_max من pathman_partition_list حيث 
     range_max :: int <= الأعظم (clock_delete_less_history و clock_delete_less_trends و clock_delete_less_strings) و 
     (قسم :: نص مثل "السجل٪" أو القسم :: نص مثل "الاتجاهات٪") حسب القسم تصاعدي
     أنشوطة
         if (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' و 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 ؛
             رفع إشعار '٪' ، تنسيق ('!!! حذف٪ s٪ s' ، partition_name ، clock_max) ؛
             حدد max (reltuples :: int) ، pg_size_pretty (sum (pg_relation_size (pg_class.oid))) كـ "size" من pg_class حيث يعيد تسمية مثل partition_name ||  '٪' إلى buf_table_len صارمة ، buf_table_size ؛
             إذا result_str! = '' ثم result_str = result_str ||  ''؛  نهاية إذا ؛
             result_str = result_str ||  format ('٪ 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 date' ، to_char (to_timestamp (clock_delete_less) ، 'YYYY-MM-DD')) ؛ 
     else result_str = format ('أقسام٪ s المحذوفة في٪ s ثانية:' ، مكرر ، trunc (استخراج (بالثواني من (clock_timestamp () - t_start)) :: numeric، 3)) ||  result_str.
     نهاية إذا ؛
     - رفع إشعار '٪' ، result_str ؛
     return result_str؛
 استثناء عند الآخرين ثم
    الحصول على تشخيص مكدس err_detail = PG_EXCEPTION_CONTEXT ؛
    صيغة الإرجاع (عمليات الإغلاق ، خطأ ما:٪ 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 ، تم حذف 3 أقسام في 0.024 ثانية: trends_78 (dt <2019-08-17 ، لين 1 ، 48 كيلو بايت) ، history_193 (dt <2019-09-13 ، لين 85343 ، 9448 كيلو بايت ) ، history_uint_186 (dt <2019-09-13 ، لين 27969 ، 3480 كيلو بايت)

/*
     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