精华内容
下载资源
问答
  • zabbix数据库优化

    2020-09-06 22:53:13
    zabbix数据库的授权 读写权限,用作zabbix自身访问: grant all privileges on zabbix. to 'zabbix'@'1.1.1.1' identified by'zabbix'; 只读权限,用作二次开发只读zabbix数据库: grant SELECT on zab

    MySQL版本推荐

    MySQL5.7及以上版本,便捷的在线DDL方便zabbix的快速升级 链接数据库方式:zabbix的server、proxy、MySQL数据库尽量使用域名方式连接,方便进行故障切换。

    zabbix数据库的授权

    读写权限,用作zabbix自身访问:

    grant all privileges on zabbix. to 'zabbix'@'1.1.1.1' identified by'zabbix';
    

    只读权限,用作二次开发只读zabbix数据库:

    grant SELECT on zabbix. to 'zabbix_ro'@'1.1.1.1' identified by'zabbixro';
    

    MySQL配置文件

    展开全文
  • zabbix数据库优化(未完)

    千次阅读 2019-08-04 15:19:40
    姑且当这个是对zabbix数据库弄乱了index修复过程,因为也没去细对比zabbix默认数据库index。 补充一段关于数据库索引的信息: Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化方式。 ...

    姑且当这个是对zabbix数据库弄乱了index修复过程,因为也没去细对比zabbix默认数据库index。

    补充一段关于数据库索引的信息:

    Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化方式。

    创建索引的目的是快速从整体集合中选择性地读取满足条件的一部分集合。为了实现以最少的索引满足对某个表的多样化的数据读取要求,应当为每个索引分配合理的任务。

        1)在允许的情况下,对具有较好离散度的列单独创建索引,这样可以提高该索引的使用弹性;

        2)对于离散度较差的列,通过对多列进行合理的组合来创建组合索引,虽然这样做在很大程度上降低了各个列的使用弹性,但是却可以发挥多个列的综合效应。

    using index 和using where只要使用了索引我们基本都能经常看到,而using index condition则是在mysql5.6后新加的新特性,我们先来看看mysql文档对using index condition的描述

    附上mysql文档链接:https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

    简单来说,mysql开启了ICP的话,可以减少存储引擎访问基表的次数

    下面来简单的介绍一下这三者的区别

    using index :使用覆盖索引的时候就会出现

    using where:在查找使用索引的情况下,需要回表去查询所需的数据

    using index condition:查找使用了索引,但是需要回表查询数据

    using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据

    以上四点就能看出它们之前的区别,或许有部分人都存在疑惑 using index & using where 和using index condition那个比较好,从上面的的解释中就能看出是前者比较好,毕竟不需要回表查询数据,效率上应该比较快的

    下面是在stackoverflow中找到的答案:

    附上stackoverflow链接:https://stackoverflow.com/questions/28759576/mysql-using-index-condition-vs-using-where-using-index
    --------------------- 
    实例处理一

    [root@gt-zabbix mariadb]# tail -f slow.log
    
    # Time: 190803 19:49:08
    # User@Host: zabbix[zabbix] @ localhost []
    # Thread_id: 91  Schema: zabbix  QC_hit: No
    # Query_time: 2.138774  Lock_time: 0.000105  Rows_sent: 51907  Rows_examined: 182133
    SET timestamp=1564832948;
    select i.itemid,f.functionid,f.function,f.parameter,t.triggerid from hosts h,items i,functions f,triggers t where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and h.status in (0,1) and t.flags<>2;
    

    上面一次查询18万条。

    MariaDB [zabbix]> use zabbix;
    MariaDB [zabbix]> explain select i.itemid,f.functionid,f.function,f.parameter,t.triggerid from hosts h,items i,functions f,triggers t where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and h.status in (0,1) and t.flags<>2\g
    
    

     

     

    MariaDB [zabbix]> show index from triggers;

     看到列flags没有建立索引,

    为列flags添加索引

    MariaDB [zabbix]> alter table triggers add index idx_f(flags);

     

    MariaDB [zabbix]> explain select i.itemid,f.functionid,f.function,f.parameter,t.triggerid from hosts h,items i,functions f,triggers t where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and h.status in (0,1) and t.flags<>2;
    

    看到优化后triggers使用idx_f 索引,检索行数由于26078减少为16165,extra由using where变为using where; Using index

    MariaDB [zabbix]> select SQL_NO_CACHE i.itemid,f.functionid,f.function,f.parameter,t.triggerid from hosts h,items i,functions f,triggers t where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and h.status in (0,1) and t.flags<>2;

    看到执行过程只花0.19s。

     

    展开全文
  • zabbix数据库优化之zabbix server优化(三)一,配置优化LogFile=/var/log/zabbix/zabbix_server.logLogFileSize=1024DebugLevel=3 #日志级别PidFile=/var/run/zabbix/zabbix_server.pidDBHost=localhostDBName=...

    zabbix数据库优化之zabbix server优化(三)

    一,配置优化

    LogFile=/var/log/zabbix/zabbix_server.log

    LogFileSize=1024

    DebugLevel=3                 #日志级别

    PidFile=/var/run/zabbix/zabbix_server.pid

    DBHost=localhost

    DBName=zabbix

    DBUser=zabbix

    DBPassword=zabbix

    DBSocket=/var/lib/mysql/mysql.sock

    StartPollers=100              #poller进程  100

    StartPollersUnreachable=30    #无法访问的主机轮询进程30

    StartPingers=30               #ping轮询数量

    StartDiscoverers=30

    StartTimers=10

    SenderFrequency=30     #发送报警超时

    SNMPTrapperFile=/var/log/snmptrap/snmptrap.log

    CacheSize=4096M              #存储主机,项目和触发器数据的共享内存

    CacheUpdateFrequency=120      #执行配置缓存的更新频率

    StartDBSyncers=24            #数据库同步进程

    HistoryCacheSize=2048M

    HistoryIndexCacheSize=2048M

    TrendCacheSize=2048M         #趋势数据最大2G

    ValueCacheSize=2048M         #缓存项历史数据请求,历史值缓存

    Timeout=30

    UnreachablePeriod=120        #几秒钟的不可达性将主机视为不可用。  不可用

    UnavailableDelay=60          #主机在不可用期间内检查可用性的频率(秒)。  不可用

    UnreachableDelay=5           #不可达检测频率   解决wait for 3 seconds

    AlertScriptsPath=/usr/lib/zabbix/alertscripts

    ExternalScripts=/usr/lib/zabbix/externalscripts

    LogSlowQueries=2000          #记录慢查询

    HousekeepingFrequency=1      #从历史记录,警报和警报表中删除不必要的信息  不超过4个小时  每隔1小时启动一次,删除过期数据

    MaxHousekeeperDelete=1000000 #清除过期数据,超过这个阀值的行都会被清理。

    展开全文
  • zabbix数据库优化

    千次阅读 2018-06-07 09:13:11
    zabbix数据库优化 参考文档: https://www.zabbix.org/wiki/Docs/howto/mysql_partition https://www.kancloud.cn/devops-centos/centos-linux-devops/375488 my.cnf的参数的调整要根据实际应用和长期使用中慢慢...

    zabbix数据库优化

    参考文档:
    https://www.zabbix.org/wiki/Docs/howto/mysql_partition
    https://www.kancloud.cn/devops-centos/centos-linux-devops/375488

    my.cnf的参数的调整要根据实际应用和长期使用中慢慢去调整,此处主要是创建四个存储过程:
    create,drop,maintenance,verify去对zabbix几个大表去做处理,

    大表如下:
    这里写图片描述

    创建脚本如下:

    cat partition_call.sql
    
    
    DELIMITER $$
    CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
    BEGIN
            /*
               SCHEMANAME = The DB schema in which to make changes
               TABLENAME = The table with partitions to potentially delete
               PARTITIONNAME = The name of the partition to create
            */
            /*
               Verify that the partition does not already exist
            */
            DECLARE RETROWS INT;
            SELECT COUNT(1) INTO RETROWS
            FROM information_schema.partitions
            WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_description >= CLOCK;
            IF RETROWS = 0 THEN
                    /*
                       1. Print a message indicating that a partition was created.
                       2. Create the SQL to create the partition.
                       3. Execute the SQL from #2.
                    */
                    SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
                    SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
                    PREPARE STMT FROM @sql;
                    EXECUTE STMT;
                    DEALLOCATE PREPARE STMT;
            END IF;
    END$$
    DELIMITER ;
    
    DELIMITER $$
    CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
    BEGIN
            /*
               SCHEMANAME = The DB schema in which to make changes
               TABLENAME = The table with partitions to potentially delete
               DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
            */
            DECLARE done INT DEFAULT FALSE;
            DECLARE drop_part_name VARCHAR(16);
            /*
               Get a list of all the partitions that are older than the date
               in DELETE_BELOW_PARTITION_DATE.  All partitions are prefixed with
               a "p", so use SUBSTRING TO get rid of that character.
            */
            DECLARE myCursor CURSOR FOR
                    SELECT partition_name
                    FROM information_schema.partitions
                    WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
            /*
               Create the basics for when we need to drop the partition.  Also, create
               @drop_partitions to hold a comma-delimited list of all partitions that
               should be deleted.
            */
            SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
            SET @drop_partitions = "";
            /*
               Start looping through all the partitions that are too old.
            */
            OPEN myCursor;
            read_loop: LOOP
                    FETCH myCursor INTO drop_part_name;
                    IF done THEN
                            LEAVE read_loop;
                    END IF;
                    SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
            END LOOP;
            IF @drop_partitions != "" THEN
                    /*
                       1. Build the SQL to drop all the necessary partitions.
                       2. Run the SQL to drop the partitions.
                       3. Print out the table partitions that were deleted.
                    */
                    SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
                    PREPARE STMT FROM @full_sql;
                    EXECUTE STMT;
                    DEALLOCATE PREPARE STMT;
                    SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
            ELSE
                    /*
                       No partitions are being deleted, so print out "N/A" (Not applicable) to indicate
                       that no changes were made.
                    */
                    SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;
            END IF;
    END$$
    DELIMITER ;
    
    DELIMITER $$
    CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
    BEGIN
            DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
            DECLARE PARTITION_NAME VARCHAR(16);
            DECLARE OLD_PARTITION_NAME VARCHAR(16);
            DECLARE LESS_THAN_TIMESTAMP INT;
            DECLARE CUR_TIME INT;
            CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
            SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));
            SET @__interval = 1;
            create_loop: LOOP
                    IF @__interval > CREATE_NEXT_INTERVALS THEN
                            LEAVE create_loop;
                    END IF;
                    SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);
                    SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00');
                    IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN
                            CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
                    END IF;
                    SET @__interval=@__interval+1;
                    SET OLD_PARTITION_NAME = PARTITION_NAME;
            END LOOP;
            SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');
            CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);
    END$$
    DELIMITER ;
    
    DELIMITER $$
    CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
    BEGIN
            DECLARE PARTITION_NAME VARCHAR(16);
            DECLARE RETROWS INT(11);
            DECLARE FUTURE_TIMESTAMP TIMESTAMP;
            /*
             * Check if any partitions exist for the given SCHEMANAME.TABLENAME.
             */
            SELECT COUNT(1) INTO RETROWS
            FROM information_schema.partitions
            WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name IS NULL;
            /*
             * If partitions do not exist, go ahead and partition the table
             */
            IF RETROWS = 1 THEN
                    /*
                     * Take the current date at 00:00:00 and add HOURLYINTERVAL to it.  This is the timestamp below which we will store values.
                     * We begin partitioning based on the beginning of a day.  This is because we don't want to generate a random partition
                     * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
                     * end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000").
                     */
                    SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));
                    SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');
                    -- Create the partitioning query
                    SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`)");
                    SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));");
                    -- Run the partitioning query
                    PREPARE STMT FROM @__PARTITION_SQL;
                    EXECUTE STMT;
                    DEALLOCATE PREPARE STMT;
            END IF;
    END$$
    DELIMITER ;

    调用存储过程去清理,当然也可以用python去写脚本去处理,这边觉得shell更加简单直接

    cat partition_all.sql
    
    DELIMITER $$
    CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
    BEGIN
                    CALL partition_maintenance(SCHEMA_NAME, 'history', 90, 24, 14);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_log', 90, 24, 14);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_str', 90, 24, 14);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_text', 90, 24, 14);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 90, 24, 14);
                    CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);
                    CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
    END$$
    DELIMITER ;

    由于需要监控到每天的存储过程是否调用,有两个思路,一个是记录到日志,另外一个是邮件告警,我这边采用的是邮件通知,部署脚本如下

    #!/bin/bash
    mkdir -p /usr/local/msmtp/etc
    cat > /root/.msmtprc << EOF
    host mail.xxxx.com
    tls off
    auth plain
    from zabbix@xxxx.com
    user zabbix
    password xxxxxx
    
    EOF
    
    
    cat > /root/.muttrc << EOF
    set sendmail="/usr/local/msmtp/bin/msmtp"
    set use_from=yes
    set from=zabbix@xxxx.com
    set envelope_from=yes
    
    EOF
    
    cat >  /usr/local/msmtp/etc/msmtprc  << EOF
    defaults 
    account zabbix
    host mail.xxx.com
    from zabbix@xxx.com       
    auth login
    port 25
    tls off
    user zabbix@xxx.com 
    password  xxxxx
    account default : zabbix
    logfile /usr/local/msmtp/log/msmtp.log
    
    EOF
    
    mkdir -p /usr/local/msmtp/log
    echo 'set sendmail="/usr/local/msmtp/bin/msmtp"' >>/etc/Muttrc
    echo "set use_from=yes" >>/etc/Muttrc
    echo 'set realname="zabbix@xxxx.com"' >>/etc/Muttrc
    echo 'set editor="vim"' >>/etc/Muttrc
    
    
    tar jxvf msmtp-1.4.30.tar.bz2
    cd msmtp-1.4.30
    ./configure --prefix=/usr/local/msmtp
    make
    sleep 3 
    make install
    sleep 3
    cd ..
    tar zxvf mutt-1.5.21.tar.gz 
    cd mutt-1.5.21
    ./configure --prefix=/usr/local/mutt
    make
    sleep 3
    make install
    sleep 3
    cd ..
    ln -s /usr/local/msmtp/bin/msmtp /usr/bin
    
    echo  测试  | /usr/local/mutt/bin/mutt  -s  "测试"  xxx@xxxxx.com

    计划任务
    0 0 * * * sh /apps/sh/zabbix_partition/housekeeping.sh

    cat housekeeping.sh

    #!/bin/bash
    MAILTO=xxx@xxxx.com
    tmpfile=/tmp/housekeeping$$
    
    date >$tmpfile
    /usr/bin/mysql -uzabbix -pxxxxx -hlocalhost zabbix -e "CALL partition_maintenance_all('zabbix');" >>$tmpfile 2>&1
    /usr/local/mutt/bin/mutt -s "Zabbix MySql Partition Housekeeping" $MAILTO <$tmpfile
    rm -f $tmpfile

    此处的邮箱和密码按照自己的配置进行更改,往知悉,响应的软件包自行下载

    最后要记得在全局配置中去修改housekeeping的时间来统一定义所有模版的history和trend,不然你得每个模版去配置,有点麻烦,过程如下:

    这里写图片描述

    这里写图片描述

    展开全文
  • 最近负责公司的zabbix项目,由于前期缺少整体性能架构的规划,导致后期出一系列的问题,玩zabbix的兄弟都知道,zabbix最大的瓶颈本身不在zabbix服务,而在mysql数据库的压力上,因此,优化zabbix其实就是优化zabbix的...
  • zabbix数据库优化之数据库优化二简介数据库history设置是保存7天。然后如果你没有分区。虽然数据在减少但是表空间不会减少。浪费硬盘空间的同事缓存内的cache部分也没有被释放。分区后可以迁移分区合并分区删除已经...
  • 主要介绍了Mysql优化Zabbix分区优化的详细方法和优缺点分析,一起学习下。
  • zabbix数据库优化之zabbix server优化(三)一,配置优化LogFile=/var/log/zabbix/zabbix_server.logLogFileSize=1024DebugLevel=3 #日志级别PidFile=/var/run/zabbix/zabbix_server.pidDBHost=localhostDBName=...
  • 1,数据库分区优化,buffer优化,hash优化 http://tlinux.blog.51cto.com/7288656/1970022 2,zabbix server http://tlinux.blog.51cto.com/7288656/1970024 3,apache http://tlinux.blog.51cto...
  • 一,Apache调整StartServers10MinSpareThreads75MaxSpareThreads250ThreadsPerChild25MaxRequestWorkers400MaxConnectionsPerChild0OptionsFollowSymLinksAllowOverrideNoneRequireallgrantedphp_valuemax_ex...
  • http://devliangel.blog.51cto.com/469347/1428325最近负责公司的zabbix项目,由于前期缺少整体性能架构的规划,导致后期出一系列的问题,玩zabbix的兄弟都知道,zabbix最大的瓶颈本身不在zabbix服务,而在mysql数...
  • Zabbix配置文件的参数优化StartPollers=60StartPollersUnreacheable=80StartTrappers=20StartPingers=100StartDiscoverers=120 #zabbix提示进程繁忙时修改此参数,最大二百五,建议100左右,值大消耗cpu性能大CacheSize...
  • zabbix数据库优化脚本

    2014-05-07 23:45:19
    分享一个zabbix数据库优化脚本,适合2.0版本。对history,hostory_uint按日分区,trends,trends_uint按月分区;关闭Houserkeeper:vim zabbix_server.confDisableHousekeeper=1对history,hostory_uint每日建立第二...
  • zabbix 数据库数据清理

    2021-02-07 15:30:47
    1、查看表占用空间情况SELECT table_name AS "Tables",round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"FROM information_schema.TABLESWHERE table_schema = ‘zabbix‘ORDER BY (data_length...
  • 一、安装postgresql数据库 win64安装postgresql10 下载 下载地址https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 安装 打开安装包,傻瓜式默认安装,请谨记 “数据库密码” 和 “端口号” ,...
  • Zabbix数据库优化

    2011-08-24 09:30:32
    部署过Zabbix的运维工程师都知道,Zabbix的数据...这里提供一个自己写的Zabbix数据库删除,如果想为了更安全,请事先备份到其他硬盘空间,并定期删除备份内容。 #!/usr/bin/perl #Description:Optimize Zabbix My...
  • zabbix:性能优化

    千次阅读 2019-03-20 15:16:18
    zabbix性能低下的表现如下: zabbix队列有太多被延迟的item,可以通过administration-queue查看 zabbix绘图中经常出现断图,一些item没有数据 ...架构优化,如使用 分布式,个服务器功能独立 Item 、Trigge...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,393
精华内容 2,557
关键字:

zabbix数据库优化