首页 新闻 会员 周边

使用MySQL在插入或更新时出现偶发性卡顿问题

0
悬赏园豆:50 [已解决问题] 解决于 2022-12-06 17:37

我们的系统是应用在手术中的, 每台手术会新建一个数据库. 各数据库间互不干扰.
因此数据库的规模并不大. 一共也就7张表. 大多表也就存了几条记录而已.

下面是数据库配置:

[mysqld]
port=3306
basedir=C:\Program Files\MySQL\MySQL Server 5.6
datadir=D:\Database
key_buffer_size=512M
bulk_insert_buffer_size=256M
sort_buffer_size=256k
default-storage-engine=myisam
max_allowed_packet=64M
skip-grant-tables
max_connections=100

系统运行在单机环境中,数据库操作没有并发需求, 因此选择了MyISAM引擎. 我们使用mysql的C++ API跟mysql交互.
下面是相关操作实现:

    MYSQL mysql;
    std::string host, user, password, database;

    // 连接数据库(进入时执行一次). 
    // 同一时间只会打开一个病例, 一个人操作. 病例打开期间, 每张表都会维持一个数据库连接
    mysql_init(&mysql);
    char value = 1;
    mysql_options(&mysql, MYSQL_OPT_RECONNECT, &value);
    mysql_real_connect(&mysql, host.c_str(), user.c_str(), password.c_str(), NULL, 0, NULL, 0);
    mysql_select_db(&mysql, database.c_str());

    ....


    mutex.lock();
    std::string sql = "INSERT INTO POINT..."; // 拼接SQL语句
    mysql_ping(&mysql);
    mysql_real_query(&mysql, sql.c_str(), sql.length());   // 该条语句的执行会偶发卡顿
    long recNO = mysql_insert_id(&mysql);
    mutex.unlock();

其中某张表上限一万条记录,运行过程中大都执行插入操作. 偶尔会删除.

运行过程中发现,在对该表进行插入时, mysql_real_query()函数的执行时间大都在毫秒级,但偶尔会需要5秒钟.
发生卡顿时,CPU和内存占用也没有太大波动.

该表有数十个字段, 包括数个BLOB. 只有一个自增主键, 没有索引. 每次插入的数据量基本一致,
sql语句的长度约为800,000.(即sql.length()的数值).

执行的插入语句形式是: INSERT INTO POINT(IDNEX, NAME, TYPE...) VALUES(...),(...);
但无论是一次性插入多条记录还是分开顺序插入多条记录, 都会偶发卡顿.

被这个卡顿问题困扰了好久了. 不知道有谁能否提供一些帮助或线索来解决. 谢谢!

问题补充:

各位好. 在安装mysql的时候, 有3种服务器配置类型: Development Computer, Server Computer和Delicated Computer. 我们选择的是默认的Development Computer. 我看网上介绍说这种类型占用的系统资源最少. 卡顿会不会跟这个选项有关呢?
我们的主机只用来跑自己的软件, 当然MySQL也安装在这台主机上. 这种情况应该选择什么样的配置类型呢?

补充:
在发生一次卡顿后, 我执行了show status来查询当前的数据库状态. 以下是一些我觉着可疑或不太理解的查询结果:
Bytes_received = 725253871;
Bytes_sent = 244159062;

Com_admin_commands = 4665;
Com_change_db = 1;
Com_insert = 469;
Com_select = 3;
Com_show_status = 1;
Com_update = 4192;

Connection_errors_max_connections = 25491;
Connections = 3538;
Created_tmp_files = 5;
Flush_commands = 1;
Handler_external_lock = 9328;
Handler_read_key = 4192;
Handler_read_rnd = 4248;
Handler_read_rnd_next = 12747;
Handler_update = 4192;
Handler_write = 8442;

Key_blocks_unused = 106176;
Key_blocks_used = 995;
Key_read_requests = 132747;
Key_reads = 5020;
Key_write_requests = 22289;
Key_writes = 2581;
Last_query_cost = 64576.356813;
Last_query_partial_plans = 1;
Max_used_connections = 101;
Open_files = 1028;
Open_table_definitions = 514;
Open_tables = 514;
Opened_files = 5881;

Qcache_free_blocks = 1;
Qcache_free_memory = 1031432;
Qcache_not_cached = 3626;
Qcache_total_blocks = 1;
Queries = 67325;
Questions = 4666;

Select_scan = 3;
Slave_running = OFF;
Sort_rows = 4248;
Sort_scan = 3;
Ssl_session_cache_mode = NONE;
Table_locks_immediate = 29972;
Table_open_cache_hits = 4664;

Threads_cached = 33;
Threads_connected = 17;
Threads_created = 101;
Threads_running = 1;

惊雷阁的主页 惊雷阁 | 初学一级 | 园豆:159
提问于:2022-09-02 14:36
< >
分享
最佳答案
0

调整 bulk_insert_buffer_size看看是否有影响

收获园豆:40
Adming | 初学一级 |园豆:119 | 2022-09-04 21:49

看你前面的描述,你这个是打开一个病例就建立一个数据库连接,如果未关闭就一直连着,不建议数据库长连接,
这样,如果有大量病例被打开,按你的配置超过100个应该会报错。这还是最好排查的错误,更多隐形的坑就可能是你遇到的这种,我查了一下,bulk_insert_buffer_size参数是为每个thread分配一个的,简单的理解就是一个连接一个写入缓存区(你配置的256M,默认只有8M),因为你的连接是一直打开的,那么就有写满的时候,遇到某次写入时发现无缓存可用时就会比平常有缓存时慢上许多。

Adming | 园豆:119 (初学一级) | 2022-09-04 22:21

@Adming: 打开一个病例的时候会关闭上一个病例的连接. 同一时刻只会只会打开一个病例. 在测试的时候, 其实也就是新建一个病例就开始测试, 也不存在关闭上一个病例的情况. 不过在打开病例后, 数据库确实是一直连着的. 从打开软件到出现卡顿, 大概也就是一个小时左右的时间. 中间一直在不断的进行插入操作. 时间太长了么?

惊雷阁 | 园豆:159 (初学一级) | 2022-09-05 09:44

@惊雷阁: 你的应用难道就只有一个人在用吗?

Adming | 园豆:119 (初学一级) | 2022-09-05 09:49

@惊雷阁: 你可以尝试把bulk_insert_buffer_size调为 8M 16M 32M 或直接 0 看看,出现的频率是否更高或故障消失。

Adming | 园豆:119 (初学一级) | 2022-09-05 09:55

@Adming: 对啊. 是单机软件啊, 没有并发需求的...

惊雷阁 | 园豆:159 (初学一级) | 2022-09-05 10:11

@Adming: 好的, 我试下

惊雷阁 | 园豆:159 (初学一级) | 2022-09-05 10:12

@Adming: 对了, 病例打开期间, 我们会对每张表维持一个数据库连接.

惊雷阁 | 园豆:159 (初学一级) | 2022-09-05 18:31

@惊雷阁: 先不管这个了,先试下bulk_insert_buffer_size设置为0或默认8M这个故障出现的频率是否有变化。

Adming | 园豆:119 (初学一级) | 2022-09-05 18:34

@Adming: 测试了下, 改成16M或8M后卡顿发生的概率好像是降低了一些, 不过还是会出现...

惊雷阁 | 园豆:159 (初学一级) | 2022-09-20 10:45
其他回答(7)
0

每台手术新建一个mysql数据库,我以为是sqlite呢,还回过眼又看了一下,溜了溜了

ensleep | 园豆:1682 (小虾三级) | 2022-09-02 14:52
0

每次插入数据都要建立连接?
建立连接是不是要耗费比较多的时间?
在Java中,有数据库连接池的概念,,C++ 有没?

快乐的凡人721 | 园豆:3975 (老鸟四级) | 2022-09-02 14:53

不是哈. 连接数据库只是在一开始打开病例的时候才会做. 连接成功后就不会重复连接了. 只会执行后面的拼接SQL语句和执行SQL语句指令.

支持(0) 反对(0) 惊雷阁 | 园豆:159 (初学一级) | 2022-09-02 15:34

每次操作数据都新建立连接才是数据库推荐的用法。
是的,建立新连接耗费时间和性能开销,所有才有连接池的出现。
java/.net/go等语言通常都是推荐执行数据库操作前打开、用完就关,中间操作尽可能的短的占用连接

支持(0) 反对(0) Adming | 园豆:119 (初学一级) | 2022-09-04 22:45
0

神奇,还有这种操作........为什么每次手术都要新建一个数据库?

会长 | 园豆:12459 (专家六级) | 2022-09-02 14:58

打开一个病例的时候不需要知道另一个病例的数据. 这样让各个病例的数据不会互相干扰吧.
我觉着还好, 不算很奇怪的设计吧...而且这样的分开设计应该不是导致卡顿的原因吧? 我反倒觉着这样至少不会拖累性能.

支持(0) 反对(0) 惊雷阁 | 园豆:159 (初学一级) | 2022-09-02 15:32

@惊雷阁: 可能是我没接触过医疗软件这一块。我做开发十多年没见过此种设计

支持(0) 反对(0) 会长 | 园豆:12459 (专家六级) | 2022-09-04 10:10

@会长: 说实话, 我也是半路接手...刚开始看到的时候也有点奇怪, 不过想想确实还蛮适合我们的使用场景的. 单机软件, 一个人操作, 同一时间就打开一个病例....

支持(0) 反对(0) 惊雷阁 | 园豆:159 (初学一级) | 2022-09-05 18:35

@惊雷阁: 有点奇怪。问题找到了吗?

支持(0) 反对(0) 会长 | 园豆:12459 (专家六级) | 2022-09-05 18:43

@会长: 没有....先后把key_buffer_size和bulk_insert_buffer_size都降了, max_connections也升了. 可是还是会偶发卡顿...

支持(0) 反对(0) 惊雷阁 | 园豆:159 (初学一级) | 2022-10-13 13:20
0

卡顿后执行会变慢吗?

Biuget-Golang | 园豆:784 (小虾三级) | 2022-09-02 15:52

不会. 我们数据库操作是放在主线程的. 所以那条语句执行5s会导致整个界面的卡顿.
但除了这些偶发5s的插入操作外, 其他的插入操作时间都是毫秒级, 区别不大.

支持(0) 反对(0) 惊雷阁 | 园豆:159 (初学一级) | 2022-09-02 16:06

@惊雷阁:可以看看 redo log是否写满,如redo log写满数据库会停止进行更新语句的执行,从而进行redo log日志同步到磁盘中,此时数据库就会造成卡顿。

支持(0) 反对(0) Biuget-Golang | 园豆:784 (小虾三级) | 2022-09-02 16:43

@Biuget-Golang: 你好, 我查了下, redo log好像是InnoDB引擎才有的吧? 我们用的是MyISAM. 另外, log_bin的状态是OFF.

支持(0) 反对(0) 惊雷阁 | 园豆:159 (初学一级) | 2022-09-02 16:52

@Biuget-Golang: 这个表锁的问题我也考虑过. 我刚才又确认了下代码, 一来, 针对这张表的所有数据库操作都是在主线程中的. 二来, 我在数据库操作语句前后加的有锁, 即使真有读写冲突, 也应该卡顿在锁等待的地方, 而不应该是mysql_real_query()这条语句执行5s吧?

支持(0) 反对(0) 惊雷阁 | 园豆:159 (初学一级) | 2022-09-02 18:21
0

将mysql 5.6 升级到8.0 试一下。

苍洱孤鸿 | 园豆:215 (菜鸟二级) | 2022-09-02 17:56
0

关注中, 如果我遇到这个问题会考虑
1、数据库的连接数或者操作系统的连接数问题,
2、数据库缓存问题
3、死锁问题(使用队列解决)

收获园豆:10
Tom.汤 | 园豆:3046 (老鸟四级) | 2022-09-05 09:02
0

各位好. 该问题暂时解决, 现在结贴.
解决办法是: 将表中的BLOB字段都改为直接存文件. 同时再参考@Adming 的提议修改了一些数据库配置.
目前没有再出现卡顿的情况. 但猜测这种方案只是将卡顿的概率降低到了一个很低的程度. 并没有从根本上找到卡顿5s的原因.
谢谢大家!

惊雷阁 | 园豆:159 (初学一级) | 2022-12-06 17:36
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册