Py学习  »  DATABASE

MySQL C API-批量插入性能问题

Bryan Heden • 4 年前 • 294 次点击  

我正在为一个更流行的应用程序(Nagios)重写一个流行的附加模块(NDOUtils)。此模块通过使数据库中每个对象的对象/状态/历史记录可用,向Nagios添加功能。

当前的可用版本从Nagios获取数据(通过一些注册的回调/函数指针),并通过一个socket发送数据,在这个socket中,另一个进程监听并将数据排队。最后,一个额外的过程从队列中弹出数据并构建mysql查询以插入。

虽然它可以工作,并且已经工作了相当长的一段时间,但是我们在更大的系统上遇到了问题(在Nagios配置中定义的15k+对象)。我们决定重新开始并重新编写模块来直接处理数据库调用(通过mysql c api准备的语句)。

这对状态数据非常有用。我们面临的一个问题是,在启动时,我们需要将对象定义放入数据库。由于定义可以在每次进程启动时更改,因此我们截断适当的表并重新创建每个对象。这对大多数系统都很好。。。

但是对于大型系统,这个过程可能需要几分钟以上——这是一个阻塞过程——几分钟是不可接受的,这在关键的监控设置上更是雪上加霜。

所以,为了开始重写,我把事情简单化了。首先,我循环遍历每个对象定义,构建一个简单的查询,然后插入。一旦插入了每种类型的对象,我就在所有相关对象的对象上循环(例如,每个主机定义可能有一个联系人或联系人组与之关联)。这些关系需要确认)。这是最容易阅读的,但在一个拥有15k主机和25k服务的系统上速度非常慢。 非常 慢到3分钟。

当然我们可以做得更好。我重写了主要函数(主机和服务),使其只需在对象列表上循环两次,而不是为每个对象或关系发送单独的查询,而是构建一个大容量插入查询。代码如下:

#define MAX_OBJECT_INSERT 50

/* this is large because the reality is that the contact/host/services object queries
   are several thousand characters before any concatenation happens */
#define MAX_SQL_BUFFER ((MAX_OBJECT_INSERT * 150) + 8000)

#define MAX_SQL_BINDINGS 400

MYSQL_STMT * ndo_stmt = NULL;
MYSQL_BIND ndo_bind[MAX_SQL_BINDINGS];
int ndo_bind_i = 0;

int ndo_max_object_insert_count = 20;



int ndo_write_hosts()
{
    host * tmp = host_list;
    int host_object_id[MAX_OBJECT_INSERT] = { 0 };
    int i = 0;

    char query[MAX_SQL_BUFFER] = { 0 };

    char * query_base = "INSERT INTO nagios_hosts (instance_id, config_type, host_object_id, name) VALUES ";
    size_t query_base_len = strlen(query_base);
    size_t query_len = query_base_len;

    char * query_values = "(1,?,?,?),";
    size_t query_values_len = strlen(query_values);

    char * query_on_update = " ON DUPLICATE KEY UPDATE instance_id = VALUES(instance_id), config_type = VALUES(config_type), host_object_id = VALUES(host_object_id), name = VALUES(name)";
    size_t query_on_update_len = strlen(query_on_update);

    /* lock the tables */
    mysql_query(mysql_connection, "LOCK TABLES nagios_logentries WRITE, nagios_objects WRITE, nagios_hosts WRITE");

    strcpy(query, query_base);

    /* reset mysql bindings */
    memset(ndo_bind, 0, sizeof(ndo_bind));
    ndo_bind_i = 0;

    while (tmp != NULL) {

        /* concat the query_values to the current query */
        strcpy(query + query_len, query_values);
        query_len += query_values_len;

        /* retrieve this object's object_id from `nagios_objects` */
        host_object_id[i] = ndo_get_object_id_name1(TRUE, NDO_OBJECTTYPE_HOST, tmp->name);

        ndo_bind[ndo_bind_i].buffer_type = MYSQL_TYPE_LONG;
        ndo_bind[ndo_bind_i].buffer      = &(config_type);
        ndo_bind_i++;

        ndo_bind[ndo_bind_i].buffer_type = MYSQL_TYPE_LONG;
        ndo_bind[ndo_bind_i].buffer      = &(host_object_id[i]);
        ndo_bind_i++;

        ndo_bind[ndo_bind_i].buffer_type   = MYSQL_TYPE_STRING;
        ndo_bind[ndo_bind_i].buffer_length = MAX_BIND_BUFFER;
        ndo_bind[ndo_bind_i].buffer        = tmp->name;
        ndo_tmp_str_len[ndo_bind_i]        = strlen(tmp->name);
        ndo_bind[ndo_bind_i].length        = &(ndo_tmp_str_len[ndo_bind_i]);
        ndo_bind_i++;

        i++;

        /* we need to finish the query and execute */
        if (i >= ndo_max_object_insert_count || tmp->next == NULL) {

            memcpy(query + query_len - 1, query_on_update, query_on_update_len);

            mysql_stmt_prepare(ndo_stmt, query, query_len + query_on_update_len);
            mysql_stmt_bind_param(ndo_stmt, ndo_bind);
            mysql_stmt_execute(ndo_stmt);

            /* remove everything after the base query */
            memset(query + query_base_len, 0, MAX_SQL_BUFFER - query_base_len);

            query_len = query_base_len;
            ndo_bind_i = 0;
            i = 0;
        }

        tmp = tmp->next;
    }

    mysql_query(mysql_connection, "UNLOCK TABLES");
}

这篇文章是为了简洁而编辑的,目的是至少对这里发生的事情有一个基本的了解。实际上,每次mysql返回后都会进行真正的错误检查。

不管怎样,即使 ndo_max_object_insert_count 设置为高位(50、100等)-对于15k主机和25k服务,这仍然需要大约50秒。

我绞尽脑汁想把它做得更快,所以如果有人看到一些我没有注意到的突出问题,或者对如何使这种字符串操作/大容量插入的方式更有效率有任何建议,我都会全神贯注。

更新1

自从发布这篇文章以来,我已经完成并更新了循环,以避免连续重写字符串,并停止重新准备语句和重新绑定参数。现在它只更新查询的第一个循环,然后更新最后一个循环(取决于 number of hosts % max object inserts ). 这实际上已经缩短了几秒钟,但没有实质性的进展。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/49303
 
294 次点击  
文章 [ 1 ]  |  最新文章 4 年前
Sasha Pachev
Reply   •   1 楼
Sasha Pachev    4 年前

乍一看,您的代码似乎没有任何问题会导致这样的性能问题。有了这么多的数据,在正常的硬件/操作系统行为下,我希望代码能在几秒钟内运行。我建议检查两个可能的痛点:

  1. 生成要插入的数据的速度有多快?(将代码的插入部分替换为NOOP)
  2. 如果在步骤1中确定数据生成足够快,则问题在于数据库的写性能。

无论如何,很可能必须在数据库服务器级别进行故障排除-运行SHOW PROCESSLIST启动,然后在使用INNODB时显示ENGINE INNODB STATUS,如果所有其他操作都失败,则使用gdb获取mysqld进程的stacktrace快照。

可能的罪魁祸首是服务器的I/O子系统出现了可怕的错误,或者启用了某种形式的同步复制,但是如果没有服务器级的诊断,很难确定。