db mysql / mysql cluster 5.7.19 / useServerPrepStmts / cachePrepStmts

s

https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html

1. 增加批量写的速度：
useServerPrepStmts=false  
rewriteBatchedStatements=true  
useCompression=true
2. 增加读的速度：
useServerPrepStmts=true
cachePrepStmts=true

案例 ：JDBC的批量批量插入

https://www.cnblogs.com/DreamDrive/p/5757693.html

MySQL5.7 union all + jdbc预编译参数useServerPrepStmts=true Bug 踩坑

https://www.jianshu.com/p/6245cd7ee134

通过在MySQL 源码的git log中搜索内部Bug号#27422376，对比代码差异，定位到 union all 在遇到DATE类型字段时，返回的是内部使用的NEWDATE类型，导致jdbc接收到这种NEWDATE类型无法识别，报错。官方在修复这个问题时，通过real_type_to_type 这个函数，将 MYSQL_TYPE_NEWDATE 转换为 MYSQL_TYPE_DATE。

4. 规避方案
升级MySQL到5.7.22 及以上版本
关闭jdbc预编译参数useServerPrepStmts=false，可能有性能损耗
修改sql，在外面包装一层select，如 select * from <your sql>
修改表结构，不使用DATE类型字段

案例：kettle大数据量读写mysql性能优化

https://www.cnblogs.com/cl1234/p/9627833.html

修改kettleDB连接设置

1. 增加批量写的速度：
useServerPrepStmts=false  
rewriteBatchedStatements=true  
useCompression=true
2. 增加读的速度：
useServerPrepStmts=true
cachePrepStmts=true

参数说明：

1）useCompression=true，压缩数据传输，优化客户端和MySQL服务器之间的通信性能。

2）rewriteBatchedStatements=true  ，开启批量写功能

将会使大批量单条插入语句：

INSERT INTO t (c1,c2) VALUES ('One',1);
INSERT INTO t (c1,c2) VALUES ('Two',2);
INSERT INTO t (c1,c2) VALUES ('Three',3);

改写成真正的批量插入语句：

INSERT INTO t (c1,c2) VALUES ('One',1),('Two',2),('Three',3);

3）useServerPrepStmts=false  关闭服务器端编译，sql语句在客户端编译好再发送给服务器端，发送语句如上。

如果为true,sql会采用占位符方式发送到服务器端，在服务器端再组装sql语句。

占位符方式：INSERT INTO t (c1,c2) VALUES (？,？),(？,？),(？,？);

此方式就会产生一个问题，当列数*提交记录数>65535

时就会报错：Prepared statement contains too many placeholders,

这是由于我把“提交记录数量”设为10000，而要插入记录的表字段有30个，所以要进行批量插入时需要30*10000=300000  > 65535 ，故而报错。

解决方案：

方案1：把DB连接中的 rewriteBatchedStatements 给设置为false（或者去掉），不过这个操作会影响数据的插入速度。

方案2：更改表输出的设计。确保30个输出字段的和提交记录数量的乘积不超过65535。比如把提交记录数量由10000更改为450（30*2000=60000< 65535）

当然我们的目的是为了提高数据库写速度，所以当rewriteBatchedStatements =true时useServerPrepStmts=false必须配合使用。

 mysql参数调优可以参考如下文档

https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html

数据丢失问题：

性能提升后，遇到另外个问题，86万数据丢失了130多条，kettle无报错，各种mysql参数设置之后都无效果，耗时近一天，最终查到是重复数据导致。

估计是因为重复数据在mysql写不进去导致该批次数据写失败，但是kettle无报错这个就比较坑。

解决办法就是：1）取消数据表主键或者唯一索引 ，当然这是治标不治本的做法。2）根本的做法就是排查重复数据，从源头杜绝重复数据

end