需要监控数据库的序列，在达到最大值前，进行告警。特别是mysql，往往因为字段的定义和auto incremental的定义不同，导致各自的上限不同。
Oracle：
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT x.*, CASE WHEN increment_by<0 THEN round(last_number/min_value*100,4) WHEN increment_by>0 THEN round(last_number/max_value*100,4) ELSE 0 END AS percent_usage from DBA_SEQUENCES x WHERE cycle_flag='N' ORDER BY percent_usage DESC; |
SQL Server:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT max( CASE WHEN increment_by<0 THEN round(last_number/min_value*100,4) WHEN increment_by>0 THEN round(last_number/max_value*100,4) ELSE 0 END ) AS percent_usage from DBA_SEQUENCES x WHERE cycle_flag='N' ORDER BY percent_usage DESC; |
pg:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
--(1)初始化部署： --(1.1)数据库内部署表和函数（注，如果一个pg实例中有多个数据库需要监控，需要部署到多个库）： drop table oracleblog_pg_sequence; create table oracleblog_pg_sequence ( record_time timestamp with time zone, sequence_name TEXT , last_value bigint , start_value bigint , increment_by bigint , max_value bigint , min_value bigint , cache_value bigint , log_cnt bigint , is_cycled boolean, is_called boolean, sequence_schema TEXT, table_name text, column_name TEXT, data_type text, datatype_maxval bigint, datatype_minval bigint, cap_value bigint ); DROP function oracleblog_get_seqval(); CREATE OR REPLACE FUNCTION oracleblog_get_seqval() RETURNS void AS $sequence_values$ DECLARE nsp_name TEXT; seq_name TEXT; BEGIN EXECUTE 'truncate table oracleblog_pg_sequence'; FOR nsp_name, seq_name IN SELECT nspname::text, relname::text FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid WHERE relkind='S' LOOP EXECUTE 'INSERT into oracleblog_pg_sequence(sequence_name,last_value,start_value,increment_by,max_value,min_value,cache_value,log_cnt,is_cycled,is_called,sequence_schema) SELECT x.*,' || '''' || nsp_name || '''' ||' from ' || nsp_name ||'.' ||seq_name ||' x'; END LOOP; update oracleblog_pg_sequence a set table_name=(select table_name from INFORMATION_SCHEMA.COLUMNS b where SPLIT_PART(COLUMN_DEFAULT, '''', 2) = SEQUENCE_NAME and sequence_schema=table_schema), column_name=(select column_name from INFORMATION_SCHEMA.COLUMNS b where SPLIT_PART(COLUMN_DEFAULT, '''', 2) = SEQUENCE_NAME and sequence_schema=table_schema), data_type=(select data_type from INFORMATION_SCHEMA.COLUMNS b where SPLIT_PART(COLUMN_DEFAULT, '''', 2) = SEQUENCE_NAME and sequence_schema=table_schema), datatype_maxval=(select CASE lower(DATA_TYPE) when 'smallint' then 32767 when 'integer' then 2147483647 when 'serial' then 2147483647 when 'bigint' then 9223372036854775807 when 'bigserial' then 9223372036854775807 else null end from INFORMATION_SCHEMA.COLUMNS b where SPLIT_PART(COLUMN_DEFAULT, '''', 2) = SEQUENCE_NAME and sequence_schema=table_schema), datatype_minval=(select CASE lower(DATA_TYPE) when 'smallint' then -32767 when 'integer' then -2147483647 when 'serial' then 1 when 'bigint' then -9223372036854775807 when 'bigserial' then 1 else null end from INFORMATION_SCHEMA.COLUMNS b where SPLIT_PART(COLUMN_DEFAULT, '''', 2) = SEQUENCE_NAME and sequence_schema=table_schema); update oracleblog_pg_sequence set cap_value=( case when INCREMENT_BY < 0 then (case when min_value>=datatype_minval then min_value when min_value<=datatype_minval then datatype_minval end) when INCREMENT_BY > 0 then (case when max_value>=datatype_maxval then datatype_maxval when max_value<=datatype_maxval then max_value end) end), record_time=now() ; END; $sequence_values$ LANGUAGE plpgsql; --(1.2)在没有安装pgAgent的环境下，用crontab实现定时刷新（注，如果一个pg实例中有多个数据库需要监控，需要部署多个crontab，每个crontab的-d参数后跟不同的库）： cat /data001/PRD/postgres/9.6.2/home/postgres/crontab_script/fresh_oracleblog_pg_sequence.sh #!/bin/bash /data/PRD/postgres/base/9.6.2/bin/psql -d dbinfo -c "select oracleblog_get_seqval()" chmod +x /data001/PRD/postgres/9.6.2/home/postgres/crontab_script/fresh_oracleblog_pg_sequence.sh crontab -l 01 * * * * /bin/bash /data001/PRD/postgres/9.6.2/home/postgres/crontab_script/fresh_oracleblog_pg_sequence.sh --(2)给zabbix用户授权： grant select on oracleblog_pg_sequence to zabbix; --(3)查询结果： --(3.1)用于监控语句： select max(round((1-(cap_value-last_value)::numeric/(cap_value-start_value)::numeric)*100,4)) as max_usage_percent from oracleblog_pg_sequence where is_cycled='f'; --(3.2)平时运维检查： select SEQUENCE_NAME, last_value, start_value, increment_by, cap_value, round((1-(cap_value-last_value)::numeric/(cap_value-start_value)::numeric)*100,4) as usage_percent from oracleblog_pg_sequence where is_cycled='f' order by usage_percent desc; |
另外，如果发生在从库检查，不能写入或者update临时表，可以用下面的匿名块来实现：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
-- For pg 9.5, pg 9.6, which does NOT has view pg_sequences -- Use (1 - (cap_value - last_value)/(cap_value - start_value)), in case increase_by is negative number, like "CREATE SEQUENCE mysequence INCREMENT -1 MAXVALUE 100 START 10" -- Minimum serial and bigserial should be 1, They can not be negative number. -- do language plpgsql $$ declare max_seq_used numeric:= 0; bigger_seq_used numeric:= 0; nsp name; rel name; val numeric; begin for nsp,rel in select nspname,relname from pg_class t2 , pg_namespace t3 where t2.relnamespace=t3.oid and t2.relkind='S' loop execute format($_$with tmp_tab2 as (with tmp_tab1 as (select sequence_schema, sequence_name, a.increment ::numeric as INCREMENT_BY, minimum_value ::numeric as min_value, maximum_value ::numeric as max_value, b.data_type, CASE lower(b.DATA_TYPE) when 'smallint' then 32767 when 'integer' then 2147483647 when 'serial' then 2147483647 when 'bigint' then 9223372036854775807 when 'bigserial' then 9223372036854775807 else null end as datatype_maxval, CASE lower(b.DATA_TYPE) when 'smallint' then -32767 when 'integer' then -2147483647 when 'serial' then 1 when 'bigint' then -9223372036854775807 when 'bigserial' then 1 else null end as datatype_minval from information_schema.sequences a, INFORMATION_SCHEMA.COLUMNS b where SPLIT_PART(b.COLUMN_DEFAULT, '''', 2) = a.sequence_name and table_schema = a.sequence_schema) select sequence_schema, sequence_name, INCREMENT_BY, min_value, max_value, data_type, case when INCREMENT_BY < 0 then (case when min_value >= datatype_minval then min_value when min_value <= datatype_minval then datatype_minval end) when INCREMENT_BY > 0 then (case when max_value >= datatype_maxval then datatype_maxval when max_value <= datatype_maxval then max_value end) end as cap_value from tmp_tab1), tmp_tab3 as (select * from %I.%I) select round((1 - (cap_value - last_value) ::numeric / (cap_value - start_value) ::numeric) * 100, 16) as seq_usage_percent from tmp_tab2 x, tmp_tab3 y where x.sequence_name = y.sequence_name$_$,nsp, rel) into val; raise notice 'Sequence %.% usage percent is: %',nsp,rel,val; if (val>bigger_seq_used) then bigger_seq_used :=val; end if; --raise notice 'Right now thw biggest sequence usage percent is : %',bigger_seq_used; end loop; max_seq_used:=bigger_seq_used; raise notice 'Max sequence usage percent is : %',max_seq_used; end; $$; -- For pg 10 and pg 10+, which has the view pg_sequences -- Use (1 - (cap_value - last_value)/(cap_value - start_value)), in case increase_by is negative number, like "CREATE SEQUENCE mysequence INCREMENT -1 MAXVALUE 100 START 10" -- Minimum serial and bigserial should be 1, They can not be negative number. -- do language plpgsql $$ declare max_seq_used numeric:= 0; bigger_seq_used numeric:= 0; nsp name; rel name; val numeric; begin for nsp,rel in select nspname,relname from pg_class t2 , pg_namespace t3 where t2.relnamespace=t3.oid and t2.relkind='S' loop execute format($_$with tmp_tab2 as (with tmp_tab1 as (select sequence_schema, sequence_name, a.increment ::numeric as INCREMENT_BY, minimum_value ::numeric as min_value, maximum_value ::numeric as max_value, b.data_type, CASE lower(b.DATA_TYPE) when 'smallint' then 32767 when 'integer' then 2147483647 when 'serial' then 2147483647 when 'bigint' then 9223372036854775807 when 'bigserial' then 9223372036854775807 else null end as datatype_maxval, CASE lower(b.DATA_TYPE) when 'smallint' then -32767 when 'integer' then -2147483647 when 'serial' then 1 when 'bigint' then -9223372036854775807 when 'bigserial' then 1 else null end as datatype_minval from information_schema.sequences a, INFORMATION_SCHEMA.COLUMNS b where SPLIT_PART(b.COLUMN_DEFAULT, '''', 2) = a.sequence_name and table_schema = a.sequence_schema) select sequence_schema, sequence_name, INCREMENT_BY, min_value, max_value, data_type, case when INCREMENT_BY < 0 then (case when min_value >= datatype_minval then min_value when min_value <= datatype_minval then datatype_minval end) when INCREMENT_BY > 0 then (case when max_value >= datatype_maxval then datatype_maxval when max_value <= datatype_maxval then max_value end) end as cap_value from tmp_tab1), tmp_tab3 as (select * from pg_sequences where schemaname = '%I' and sequencename = '%I') select round((1 - (cap_value - last_value) ::numeric / (cap_value - start_value) ::numeric) * 100, 16) as seq_usage_percent from tmp_tab2 x, tmp_tab3 y where x.sequence_name = y.sequencename$_$,nsp, rel) into val; raise notice 'Sequence %.% usage percent is: %',nsp,rel,val; if (val>bigger_seq_used) then bigger_seq_used :=val; end if; --raise notice 'Right now thw biggest sequence usage percent is : %',bigger_seq_used; end loop; max_seq_used:=bigger_seq_used; raise notice 'Max sequence usage percent is : %',max_seq_used; end; $$; |
MySQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_awr_getauto_increment_status`() BEGIN TRUNCATE TABLE myawr.auto_increment_status; INSERT INTO myawr.auto_increment_status (clock, table_schema, table_name,auto_increment_increment,auto_increment_offset,auto_increment_max,auto_increment_used) SELECT now() AS clock, b.table_schema, b.table_name ,(select VARIABLE_VALUE from performance_schema.global_variables where VARIABLE_NAME = 'auto_increment_increment') as auto_increment_increment ,(select VARIABLE_VALUE from performance_schema.global_variables where VARIABLE_NAME = 'auto_increment_offset') as auto_increment_offset , CASE WHEN lower(COLUMN_TYPE) LIKE 'bigint%' THEN 9223372036854775808 WHEN lower(COLUMN_TYPE) LIKE 'int%' THEN 2147483647 WHEN lower(COLUMN_TYPE) LIKE 'int% unsigned' THEN 4294967295 WHEN lower(COLUMN_TYPE) LIKE 'mediumint%' THEN 8388607 WHEN lower(COLUMN_TYPE) LIKE 'mediumint% unsigned' THEN 16777215 WHEN lower(COLUMN_TYPE) LIKE 'smallint%' THEN 32767 WHEN lower(COLUMN_TYPE) LIKE 'smallint% unsigned' THEN 65535 WHEN lower(COLUMN_TYPE) LIKE 'tinyint%' THEN 127 WHEN lower(COLUMN_TYPE) LIKE 'tinyint% unsigned' THEN 255 ELSE 'other' END AS auto_increment_max , CASE WHEN lower(COLUMN_TYPE) LIKE 'bigint%' THEN format(b.auto_increment / 9223372036854775808 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'int%' THEN format(b.auto_increment / 2147483647 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'int% unsigned' THEN format(b.auto_increment / 4294967295 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'mediumint%' THEN format(b.auto_increment / 8388607 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'mediumint% unsigned' THEN format(b.auto_increment / 16777215 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'smallint%' THEN format(b.auto_increment / 32767 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'smallint% unsigned' THEN format(b.auto_increment / 65535 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'tinyint%' THEN format(b.auto_increment / 127 * 100, 0) WHEN lower(COLUMN_TYPE) LIKE 'tinyint% unsigned' THEN format(b.auto_increment / 255 * 100, 0) ELSE '100' END AS auto_increment_used FROM information_schema.columns a JOIN information_schema.tables b ON a.table_name = b.table_name AND a.table_schema = b.table_schema WHERE EXTRA = 'auto_increment' ORDER BY auto_increment_used + 0 DESC LIMIT 10; CREATE DEFINER=`root`@`localhost` EVENT `event_awr_getauto_increment_status` ON SCHEDULE EVERY 1 HOUR STARTS '2019-04-17 11:45:34' ON COMPLETION PRESERVE ENABLE DO call myawr.proc_awr_getauto_increment_status() |