java - 如何针对 Java 应用程序中的大规模批量插入优化 MySQL？

Question

我正在 Windows 7 Pro 64 位上运行一些测试
它有 i7-6700 和 8GB RAM
我从 SSD 访问文件，并通过 Java 控制台应用程序处理它们，该应用程序将它们转换并加载到同一台计算机上的 mySQL 服务器中，但位于单独的机械 HDD 上。

我已禁用页面归档
我已将 innodb_buffer_pool_size 从 8M 设置为 2G
我已将 innodb_thread_concurrency 从 17 设置为 32
我已将 innodb_buffer_pool_instances 从 8 设置为 16
我已将 ma​​x_connections 从 151 设置为 256
无论出于何种原因，任何高于此值的值都会导致服务器在启动时崩溃。我检查了安装情况，MySQL 报告说这是 AMD64 安装，但我遇到的内存限制让我怀疑它是否真的是 32 位安装。

我对这个对象特别有疑问，下面的结构
自定义对象1
字符串 custObj1str1
字符串 custObj1str2
字符串 custObj1str3
字符串 custObj1str4
int custObj1int1
int custObj1int2
float [7] custObj1fltArr1
float [7] custObj1fltArr2
ArrayList custObj2

自定义对象2
int custObj2int1
float [4] custObj2fltArr1

我从 custObj1str1、custObj1str2、custObj1str3、custObj1str4、custObj1int1、custObj1int2 为 custObj1 创建了一个 HashKey，并将其用作主键。该对象进入 4 个单独的表。

表1
int hashkey(主键)
varchar custObj1str1
varchar custObj1str2
varchar custObj1str3
varchar custObj1str4
int custObj1int1
int custObj1int2

表2
int hashkey(主键)
float custObj1fltArr1[0] ... float custObj1fltArr1[6]

表3
int hashkey(主键)
float custObj1fltArr2[0] ... float custObj1fltArr2[6]

表4
int hashkey(主键，第 1 部分)
int custObj2int1(主键，第 2 部分)
float custObj1fltArr2[0] ... float custObj2fltArr1[4]

在 Java 中，我正在使用批处理来执行准备好的 SQL 语句
对于 table1 -> “INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE "+ PrimaryKey + "= "+ PrimaryKey
对于 table4 -> “INSERT INTO table4 VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE "+ PrimaryKey + "= "+ PrimaryKey + "AND "+foreignKey + "="+foreignKey
我相信对于 table4 来说，它会导致一些数据被覆盖，因为它的数据太多(超过 30M 记录)。

这只是一天的数据，我可能需要管理 4 年的数据。

Image of Table Status (sensitive info redacted)
任何建议将不胜感激。

** 更新 **

我尝试在 MacBook Pro(2013 年末配备 i7、16GB RAM 和 SSD)上使用 mySQL。它很慢，但仍然比 Windows 机器快得多。

MacBook Metrics
我将批量上传的方法设置为同步，以限制导入到同一个表中的数据量。我应该在每个数据库的基础上限制它，保持原样，还是完全删除它？我正在使用 8 个线程池，但我想增加它。

Answer 1

该 Data_length 可疑地接近 2^31。 mysql 驻留在哪个文件系统上？ NTFS 应该没问题，但我怀疑 FAT16 和 FAT32 有限制。 (数据库的增长速度比 Windoz 还要快。)

让我们看看日志。 32 位会解释崩溃(并且会在日志中)。如果是 32 位，请放弃您提到的 4 项更改，但要有 innodb_buffer_pool_size = 1500M。即使 64 位且崩溃，看看这是否有帮助。

要判断批量插入，请提供SHOW CREATE TABLE以及您一次批量插入的行数。

操作系统限制？

首次升级到 64 位 MySQL。如果这还不够……

查看涉及什么文件系统并查看是否有解决方法。否则...

如果问题是操作系统对文件大小的限制，那么可能可以通过 MySQL 找到解决方法。

• ibdata1 实际上可以是一组文件，您可以将每个文件限制为 1GB。请参阅手册。如果你找不到，我就挖出来。

• 表可以进行分区，这样每个分区就足够小以适应操作系统的限制。这需要 innodb_file_per_table=ON 并仔细设计如何进行分区。在提供进一步建议之前，我需要查看 SHOW CREATE TABLE 并了解每列中的值。

• 5.7 允许指定每个分区的放置位置——如果整个驱动器有限制，这会很方便。 (与每个文件相反。)