zabbix之烦人的ids死锁问题

ids死锁问题是啥?

zabbix 数据库中有张表为ids,ids维护了很多张表的nextid,利用nextid来作为users、

events等表的自增id,如eventid等。

那么问题在哪里呢?高并发例如某个proxy故障或者大量设备同时报警的时候,就会引发

大量线程同时去更新eventid,引发竞争,导致死锁,如果有看过《Zabbix In PPTV》这个PPT

的同学应该知道这个是就是数据库的TX Lock问题。

死锁

如何触发的?

问题到底是如何触发的呢,前面也说到过,当量级达到一定,因为网络问题或其他问题

导致Proxy异常时,server在一段时间内接收不到监控主机的数据,对应的trigger会触发,

于是大量触发告警、更新events表,更是events的时候需要获取eventid,在1.8的版本中

eventid是有ids的一条记录维护的(nextid)。

如何解决?

这个问题困扰了我们很久,最夸张的一段时间内,一周内挂了两次,而我们的量又

非常大,每次恢复起来得要3、4个小时,简直是崩溃。

措施

长话短说,目前是我们采用了如下几个措施进行缓解:

  • 修改监控不可用的条件

    我们后续分析时发现触发ids的问题基本都是某个proxy故障时,引发的大量

    监控不可用、或者其他的告警导致的,那么修改监控不可用的条件是可以从一定程度上

    进行缓解的

  • 上ssd,利用硬件的性能提升来缓解

    ssd就更简单了,简单、粗暴,利用硬件的性能加快ids更新的速度,从而缓解;

实际效果

最直观的感受换了ssd之后,慢查询sql减少了20倍,目前已经运行了两个月了,基本

没有再遇到这个问题。当然可见换ssd是有不错的效果的,我们是全库上了ssd,即采用

ssd做存储。当然了,前提是我们的前台页面做了读写分离,不然太多的历史数据根本

没办法全部放到ssd上的。

2.2.4的变更

最近在测2.2.4,发现这个eventid的问题已经修复了。目前最新的代码、测试结果都

表明eventid已经不再由ids表的nextid维护了,而是改用代码中的逻辑来保证的。

数据库验证


mysql> select * from events limit 10 ;
+---------+--------+--------+----------+------------+-------+--------------+----------+
| eventid | source | object | objectid | clock      | value | acknowledged | ns       |
+---------+--------+--------+----------+------------+-------+--------------+----------+
|       1 |      3 |      4 |    23261 | 1404353339 |     1 |            0 | 36439052 |
|       2 |      3 |      4 |    23262 | 1404353339 |     1 |            0 | 36503352 |
|       3 |      3 |      4 |    23263 | 1404353339 |     1 |            0 | 36516479 |
|       4 |      3 |      4 |    23267 | 1404353339 |     1 |            0 | 36573239 |
|       5 |      3 |      4 |    23328 | 1404353339 |     1 |            0 | 36595806 |
|       6 |      3 |      4 |    23635 | 1404353339 |     1 |            0 | 36663655 |
|       7 |      0 |      0 |    13075 | 1404353339 |     0 |            0 | 36419740 |
|       8 |      0 |      0 |    13473 | 1404353339 |     0 |            0 | 36331795 |
|       9 |      0 |      0 |    13474 | 1404353339 |     0 |            0 | 36376373 |
|      10 |      0 |      0 |    13475 | 1404353339 |     0 |            0 | 36395530 |
+---------+--------+--------+----------+------------+-------+--------------+----------+
10 rows in set (0.00 sec)

mysql> select * from ids ;
+--------+----------------------+------------------------+--------+
| nodeid | table_name           | field_name             | nextid |
+--------+----------------------+------------------------+--------+
|      0 | acknowledges         | acknowledgeid          |      2 |
|      0 | actions              | actionid               |      7 |
|      0 | application_template | application_templateid |     39 |
|      0 | applications         | applicationid          |    469 |
|      0 | auditlog             | auditid                |     64 |
|      0 | auditlog_details     | auditdetailid          |     15 |
|      0 | conditions           | conditionid            |     13 |
|      0 | dchecks              | dcheckid               |      4 |
|      0 | drules               | druleid                |      3 |
|      0 | functions            | functionid             |  13194 |
|      0 | graphs               | graphid                |    563 |
|      0 | graphs_items         | gitemid                |   1858 |
|      0 | groups               | groupid                |      8 |
|      0 | hosts                | hostid                 |  10105 |
|      0 | hosts_groups         | hostgroupid            |    113 |
|      0 | hosts_templates      | hosttemplateid         |     43 |
|      0 | interface            | interfaceid            |      2 |
|      0 | item_discovery       | itemdiscoveryid        |    270 |
|      0 | items                | itemid                 |  23745 |
|      0 | items_applications   | itemappid              |   5985 |
|      0 | media                | mediaid                |      1 |
|      0 | media_type           | mediatypeid            |      4 |
|      0 | operations           | operationid            |      7 |
|      0 | opmessage_grp        | opmessage_grpid        |      5 |
|      0 | opmessage_usr        | opmessage_usrid        |      1 |
|      0 | profiles             | profileid              |    235 |
|      0 | rights               | rightidd                |      1 |
|      0 | triggers             | triggerid              |  13590 |
|      0 | user_history         | userhistoryid          |      2 |
|      0 | users                | userid                 |      3 |
|      0 | users_groups         | id                     |      5 |
+--------+----------------------+------------------------+--------+
31 rows in set (0.00 sec)

看到了吧,ids表中确实没有events表的nextid值了。
所以还在受折磨的同学,赶紧升级吧~

ids问题代码的深入分析

首先看下eventid是怎么获取的:

DBget_maxid_num函数


/******************************************************************************
 *                                                                            *
 * Function: save_events                                                      *
 *                                                                            *
 * Purpose: flushes the events into a database                                *
 *                                                                            *
 ******************************************************************************/
static void save_events()
{
    size_t      i;
    zbx_db_insert_t db_insert;
    int     new_events = 0;
    zbx_uint64_t    eventid;

    zbx_db_insert_prepare(&db_insert, "events", "eventid", "source", "object", "objectid", "clock", "ns", "value",
            NULL);

    for (i = 0; i < events_num; i++)
    {
        if (0 == events[i].eventid)
            new_events++;
    }

    eventid = DBget_maxid_num("events", new_events);

    for (i = 0; i < events_num; i++)
    {
        if (0 == events[i].eventid)
            events[i].eventid = eventid++;

        zbx_db_insert_add_values(&db_insert, events[i].eventid, events[i].source, events[i].object,
                events[i].objectid, events[i].clock, events[i].ns, events[i].value);
    }

    zbx_db_insert_execute(&db_insert);
    zbx_db_insert_clean(&db_insert);
}


其实这个函数我们最关心的只有一行:

eventid = DBget_maxid_num("events", new_events);

那DBget_maxid_num是怎么定义的呢?继续找:


[~/zabbix-2.2.4]$ grep 'DBget_maxid_num' -rn *
include/db.h:461:#define DBget_maxid(table) DBget_maxid_num(table, 1)
include/db.h:462:zbx_uint64_t   DBget_maxid_num(const char *tablename, int num);
src/zabbix_server/operations.c:156:     hostgroupid = DBget_maxid_num("hosts_groups", groupids->values_num);

找到DBget_maxid_num函数,继续追踪:

DBget_maxid_num


zbx_uint64_t    DBget_maxid_num(const char *tablename, int num)
{
    if (0 == strcmp(tablename, "history_log") ||
            0 == strcmp(tablename, "history_text") ||
            0 == strcmp(tablename, "events") ||
            0 == strcmp(tablename, "dservices") ||
            0 == strcmp(tablename, "dhosts") ||
            0 == strcmp(tablename, "alerts") ||
            0 == strcmp(tablename, "escalations") ||
            0 == strcmp(tablename, "autoreg_host") ||
            0 == strcmp(tablename, "graph_discovery") ||
            0 == strcmp(tablename, "trigger_discovery"))
        return DCget_nextid(tablename, num);

    return DBget_nextid(tablename, num);
}

可以看出这部分很简单,就是一个判断,如果table表名为events等就直接

调用DCget_nextid函数,不然就调用DBget_nextid函数。

这部分也是1.8和2.2差别最明显的一个地方,1.8中events走的逻辑是下面的

DBget_nextid,有兴趣的同学自己对比看就知道了。

那DCget_nextid是个啥东西,与DBget_nextid有啥区别呢?

DCget_nextid

/******************************************************************************
 *                                                                            *
 * Function: DCget_nextid                                                     *
 *                                                                            *
 * Purpose: Return next id for requested table                                *
 *                                                                            *
 * Author: Alexander Vladishev                                                *
 *                                                                            *
 ******************************************************************************/
zbx_uint64_t    DCget_nextid(const char *table_name, int num)
{
    const char  *__function_name = "DCget_nextid";
    int     i, nodeid;
    DB_RESULT   result;
    DB_ROW      row;
    const ZBX_TABLE *table;
    ZBX_DC_ID   *id;
    zbx_uint64_t    min, max, nextid, lastid;

    zabbix_log(LOG_LEVEL_DEBUG, "In %s() table:'%s' num:%d",
            __function_name, table_name, num);

    LOCK_CACHE_IDS;

    for (i = 0; i < ZBX_IDS_SIZE; i++)
    {
        id = &ids->id[i];
        if ('\0' == *id->table_name)
            break;

        if (0 == strcmp(id->table_name, table_name))
        {
            nextid = id->lastid + 1;
            id->lastid += num;
            lastid = id->lastid;

            UNLOCK_CACHE_IDS;

            zabbix_log(LOG_LEVEL_DEBUG, "End of %s() table:'%s' [" ZBX_FS_UI64 ":" ZBX_FS_UI64 "]",
                    __function_name, table_name, nextid, lastid);

            return nextid;
        }
    }

    if (i == ZBX_IDS_SIZE)
    {
        zabbix_log(LOG_LEVEL_ERR, "insufficient shared memory for ids");
        exit(-1);
    }

    zbx_strlcpy(id->table_name, table_name, sizeof(id->table_name));

    table = DBget_table(table_name);
    nodeid = CONFIG_NODEID >= 0 ? CONFIG_NODEID : 0;

    min = ZBX_DM_MAX_HISTORY_IDS * (zbx_uint64_t)nodeid;

    if (table->flags & ZBX_SYNC)
    {
        min += ZBX_DM_MAX_CONFIG_IDS * (zbx_uint64_t)nodeid;
        max = min + ZBX_DM_MAX_CONFIG_IDS - 1;
    }
    else
        max = min + ZBX_DM_MAX_HISTORY_IDS - 1;

    result = DBselect("select max(%s) from %s where %s between " ZBX_FS_UI64 " and " ZBX_FS_UI64,
            table->recid,
            table_name,
            table->recid,
            min, max);

    if (NULL == (row = DBfetch(result)) || SUCCEED == DBis_null(row[0]))
        id->lastid = min;
    else
        ZBX_STR2UINT64(id->lastid, row[0]);

    nextid = id->lastid + 1;
    id->lastid += num;
    lastid = id->lastid;

    UNLOCK_CACHE_IDS;

    DBfree_result(result);

    zabbix_log(LOG_LEVEL_DEBUG, "End of %s() table:'%s' [" ZBX_FS_UI64 ":" ZBX_FS_UI64 "]",
            __function_name, table_name, nextid, lastid);

    return nextid;
}

代码很长,但核心的就两点:

 * 获取当前表的最大值:

    result = DBselect("select max(%s) from %s where %s between " ZBX_FS_UI64 " and " ZBX_FS_UI64,

 * 累加一:

    nextid = id->lastid + 1;

即DCget_nextid 是通过查询当前表的id的最大值,例如max(eventid),再累加1获取下一个nextid的。

DBget_nextid


/******************************************************************************
 *                                                                            *
 * Function: DBget_nextid                                                     *
 *                                                                            *
 * Purpose: gets a new identifier(s) for a specified table                    *
 *                                                                            *
 * Parameters: tablename - [IN] the name of a table                           *
 *             num       - [IN] the number of reserved records                *
 *                                                                            *
 * Return value: first reserved identifier                                    *
 *                                                                            *
 ******************************************************************************/
static zbx_uint64_t DBget_nextid(const char *tablename, int num)
{
    const char  *__function_name = "DBget_nextid";
    DB_RESULT   result;
    DB_ROW      row;
    zbx_uint64_t    ret1, ret2;
    zbx_uint64_t    min, max;
    int     found = FAIL, dbres;
    const ZBX_TABLE *table;

    zabbix_log(LOG_LEVEL_DEBUG, "In %s() tablename:'%s'", __function_name, tablename);

    table = DBget_table(tablename);

    if (0 == CONFIG_NODEID)
    {
        min = 0;
        max = ZBX_STANDALONE_MAX_IDS;
    }
    else if (0 != (table->flags & ZBX_SYNC))
    {
        min = ZBX_DM_MAX_HISTORY_IDS * (zbx_uint64_t)CONFIG_NODEID +
            ZBX_DM_MAX_CONFIG_IDS * (zbx_uint64_t)CONFIG_NODEID;
        max = min + ZBX_DM_MAX_CONFIG_IDS - 1;
    }
    else
    {
        min = ZBX_DM_MAX_HISTORY_IDS * (zbx_uint64_t)CONFIG_NODEID;
        max = min + ZBX_DM_MAX_HISTORY_IDS - 1;
    }

    while (FAIL == found)
    {
        /* avoid eternal loop within failed transaction */
        if (0 < zbx_db_txn_level() && 0 != zbx_db_txn_error())
        {
            zabbix_log(LOG_LEVEL_DEBUG, "End of %s() transaction failed", __function_name);
            return 0;
        }

        result = DBselect("select nextid from ids where nodeid=%d and table_name='%s' and field_name='%s'",
                CONFIG_NODEID, table->table, table->recid);

        if (NULL == (row = DBfetch(result)))
        {
            DBfree_result(result);

            result = DBselect("select max(%s) from %s where %s between " ZBX_FS_UI64 " and " ZBX_FS_UI64,
                    table->recid, table->table, table->recid, min, max);

            if (NULL == (row = DBfetch(result)) || SUCCEED == DBis_null(row[0]))
            {
                ret1 = min;
            }
            else
            {
                ZBX_STR2UINT64(ret1, row[0]);
                if (ret1 >= max)
                {
                    zabbix_log(LOG_LEVEL_CRIT, "maximum number of id's exceeded"
                            " [table:%s, field:%s, id:" ZBX_FS_UI64 "]",
                            table->table, table->recid, ret1);
                    exit(FAIL);
                }
            }
            DBfree_result(result);

            dbres = DBexecute("insert into ids (nodeid,table_name,field_name,nextid)"
                    " values (%d,'%s','%s'," ZBX_FS_UI64 ")",
                    CONFIG_NODEID, table->table, table->recid, ret1);

            if (ZBX_DB_OK > dbres)
            {
                /* solving the problem of an invisible record created in a parallel transaction */
                DBexecute("update ids set nextid=nextid+1 where nodeid=%d and table_name='%s'"
                        " and field_name='%s'",
                        CONFIG_NODEID, table->table, table->recid);
            }

            continue;
        }
        else
        {
            ZBX_STR2UINT64(ret1, row[0]);
            DBfree_result(result);

            if (ret1 < min || ret1 >= max)
            {
                DBexecute("delete from ids where nodeid=%d and table_name='%s' and field_name='%s'",
                        CONFIG_NODEID, table->table, table->recid);
                continue;
            }

            DBexecute("update ids set nextid=nextid+%d where nodeid=%d and table_name='%s' and field_name='%s'",
                    num, CONFIG_NODEID, table->table, table->recid);

            result = DBselect("select nextid from ids where nodeid=%d and table_name='%s' and field_name='%s'",
                    CONFIG_NODEID, table->table, table->recid);

            if (NULL != (row = DBfetch(result)) && SUCCEED != DBis_null(row[0]))
            {
                ZBX_STR2UINT64(ret2, row[0]);
                DBfree_result(result);
                if (ret1 + num == ret2)
                    found = SUCCEED;
            }
            else
                THIS_SHOULD_NEVER_HAPPEN;
        }
    }

    zabbix_log(LOG_LEVEL_DEBUG, "End of %s():" ZBX_FS_UI64 " table:'%s' recid:'%s'",
            __function_name, ret2 - num + 1, table->table, table->recid);

    return ret2 - num + 1;
}

比上面的代码还要长,不过我们依然只看重点:

  • 从ids表中获取nextid:

    result = DBselect(“select nextid from ids where nodeid=%d and table_name=’%s’ and field_name=’%s'”,

  • 更新nextid:

    DBexecute(“update ids set nextid=nextid+%d where nodeid=%d and table_name=’%s’ and field_name=’%s'”,

    看到了吧,更新的这条sql就是我们的idsw问题的根源!

2 Responses to “zabbix之烦人的ids死锁问题”

  1. 邓磊 says:

    厉害,我这里也打算从2.0.6变为2.2.5了

Leave a Reply

Your email address will not be published. Required fields are marked *


To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax