使用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;
最佳答案
0
调整 bulk_insert_buffer_size看看是否有影响
收获园豆：40
看你前面的描述，你这个是打开一个病例就建立一个数据库连接，如果未关闭就一直连着，不建议数据库长连接，
这样，如果有大量病例被打开，按你的配置超过100个应该会报错。这还是最好排查的错误，更多隐形的坑就可能是你遇到的这种，我查了一下，bulk_insert_buffer_size参数是为每个thread分配一个的，简单的理解就是一个连接一个写入缓存区(你配置的256M，默认只有8M)，因为你的连接是一直打开的，那么就有写满的时候，遇到某次写入时发现无缓存可用时就会比平常有缓存时慢上许多。
@Adming: 打开一个病例的时候会关闭上一个病例的连接. 同一时刻只会只会打开一个病例. 在测试的时候, 其实也就是新建一个病例就开始测试, 也不存在关闭上一个病例的情况. 不过在打开病例后, 数据库确实是一直连着的. 从打开软件到出现卡顿, 大概也就是一个小时左右的时间. 中间一直在不断的进行插入操作. 时间太长了么?
@惊雷阁: 你的应用难道就只有一个人在用吗？
@惊雷阁: 你可以尝试把bulk_insert_buffer_size调为 8M 16M 32M 或直接 0 看看，出现的频率是否更高或故障消失。
@Adming: 对啊. 是单机软件啊, 没有并发需求的...
@Adming: 好的, 我试下
@Adming: 对了, 病例打开期间, 我们会对每张表维持一个数据库连接.
@惊雷阁: 先不管这个了，先试下bulk_insert_buffer_size设置为0或默认8M这个故障出现的频率是否有变化。
@Adming: 测试了下, 改成16M或8M后卡顿发生的概率好像是降低了一些, 不过还是会出现...
其他回答(7)
0
每台手术新建一个mysql数据库，我以为是sqlite呢，还回过眼又看了一下，溜了溜了
0
每次插入数据都要建立连接？
建立连接是不是要耗费比较多的时间？
在Java中，有数据库连接池的概念，，C++ 有没？
不是哈. 连接数据库只是在一开始打开病例的时候才会做. 连接成功后就不会重复连接了. 只会执行后面的拼接SQL语句和执行SQL语句指令.
每次操作数据都新建立连接才是数据库推荐的用法。
是的，建立新连接耗费时间和性能开销，所有才有连接池的出现。
java/.net/go等语言通常都是推荐执行数据库操作前打开、用完就关，中间操作尽可能的短的占用连接
0
神奇，还有这种操作........为什么每次手术都要新建一个数据库？
打开一个病例的时候不需要知道另一个病例的数据. 这样让各个病例的数据不会互相干扰吧.
我觉着还好, 不算很奇怪的设计吧...而且这样的分开设计应该不是导致卡顿的原因吧? 我反倒觉着这样至少不会拖累性能.
@惊雷阁: 可能是我没接触过医疗软件这一块。我做开发十多年没见过此种设计
@会长: 说实话, 我也是半路接手...刚开始看到的时候也有点奇怪, 不过想想确实还蛮适合我们的使用场景的. 单机软件, 一个人操作, 同一时间就打开一个病例....
@惊雷阁: 有点奇怪。问题找到了吗？
@会长: 没有....先后把key_buffer_size和bulk_insert_buffer_size都降了, max_connections也升了. 可是还是会偶发卡顿...
0
卡顿后执行会变慢吗？
不会. 我们数据库操作是放在主线程的. 所以那条语句执行5s会导致整个界面的卡顿.
但除了这些偶发5s的插入操作外, 其他的插入操作时间都是毫秒级, 区别不大.
@惊雷阁:可以看看 redo log是否写满，如redo log写满数据库会停止进行更新语句的执行，从而进行redo log日志同步到磁盘中，此时数据库就会造成卡顿。
@Biuget-Golang: 你好, 我查了下, redo log好像是InnoDB引擎才有的吧? 我们用的是MyISAM. 另外, log_bin的状态是OFF.
@Biuget-Golang: 这个表锁的问题我也考虑过. 我刚才又确认了下代码, 一来, 针对这张表的所有数据库操作都是在主线程中的. 二来, 我在数据库操作语句前后加的有锁, 即使真有读写冲突, 也应该卡顿在锁等待的地方, 而不应该是mysql_real_query()这条语句执行5s吧?
0
将mysql 5.6 升级到8.0 试一下。
0
关注中， 如果我遇到这个问题会考虑
1、数据库的连接数或者操作系统的连接数问题，
2、数据库缓存问题
3、死锁问题（使用队列解决）
收获园豆：10
0
各位好. 该问题暂时解决, 现在结贴.
解决办法是: 将表中的BLOB字段都改为直接存文件. 同时再参考@Adming 的提议修改了一些数据库配置.
目前没有再出现卡顿的情况. 但猜测这种方案只是将卡顿的概率降低到了一个很低的程度. 并没有从根本上找到卡顿5s的原因.
谢谢大家!
