postgresql批量插入性能问题(与mysql相关)
我有很多数据，我想插入到数据库至少一段时间。我做了一些测试。我创建了一个包含21列的表(使用下面的脚本*1)。1列是int，其他20列是string。没有索引。我写了一个测试代码，创建一个随机值并插入到DB (使用insert sql *2)。在运行sql命令之前，调用conn.setAutoCommit(false)而不是conn.commit()。此操作大约需要6-7秒。有官方文件(*3)说使用"COPY“命令进行大容量插入。创建一个类似的ascii文件，并重新测试它，这个测试完成了大约5秒。在同一台机器上使用相同的测试代码，将相同的数据插入到Mysql中，测试时间不超过1秒。我真的很惊讶，相对于6-7秒，性能有了很大的提高。这种不同是真的存在还是我忽略了任何东西。
感谢你的帮助
我的测试配置是Solaris10、PostgreSQL 9.0.2和MySQL5.0.85。
(*1) PostgreSQL创建数据库脚本
CREATE TABLE tablo
(
id integer,
column1 character varying(50),
column2 character varying(50),
column3 character varying(50),
....
column20 character varying(50)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tablo OWNER TO pgadmin;(*2)插入查询
INSERT INTO tablo values (1,'column67062724628797','column26007603757271','column73982294239806','column43213154421324','column97722282440805','column79000889379973','column10680880337755','column14322827996050','column80720842739399','column22777514445036','column77771307997926','column92799724462613','column89992937353110','column61693061355353','column43804223262229','column62209656630047','column52150955786400','column85726157993572','column33358888005133','column77743799989746'),(2,'column77383691774831','column67841193885377','column36149612452454','column51161680852595','column91649734476301','column57283307765550','column14997046117948','column29457857794726','column91157683305554','column44413196495111','column40702778794938','column24744999726868','column38356057278249','column16808618337554','column64362413535503','column19577167594144','column72943639162993','column46830376244427','column01942608599939','column66479131355003'),
....
10K lines(*3) PostgreSql官方文档地址http://www.postgresql.org/docs/8.3/interactive/populate.html
回答 3
Stack Overflow用户
发布于 2011-01-20 19:46:34
看起来很奇怪，你没有看到像使用COPY这样的东西的加速。我生成了一个脚本来创建一个类似的表，并用10,000行填充它，我发现：
表最慢:每次插入1行，没有事务块，fsync=on
- Faster：
- 语句(create table，insert....)在transaction
- Same速度为2: 1的情况下，使用由pg_dump创建的脚本执行fsync=off
- Fastest:恢复，以使用COPY
填充表
方法2和3比方法1快4倍左右，方法4比2或3快10倍左右。
如果我将相同的数据导入到机器上的mysql中，所需的时间大约是方法2或3的一半。转储和重新加载也是一样的。使用-e转储和重新加载，相同。使用InnoDB将时间缩短到与方法2或3相同。
因此，至少在我的硬件/操作系统组合上，两者之间的速度是相当的…虽然我当然会更好地处理postgresql的设置，但对于这样一个小表，我不会期望像缓冲区缓存大小这样的东西有多大关系？
现在，至于JDBC对批量插入的支持有多好，我不知道。我只使用命令行客户端就完成了所有这些工作。
Stack Overflow用户
发布于 2011-01-20 17:11:46
这里有两个主要的考虑因素：
- mysql的设计速度高于其他任何东西，postgres并非如此，默认的mysql表类型甚至不处理事务...postgres支持的这些额外功能在某些情况下会增加相当多的开销，使得possible...
- This不是一个非常常见的用例-在大多数应用程序中，一次插入10000个条目是非常不常见的。更常见的是同时插入来自不同连接的小块数据，在这种情况下postgres使用行级锁定-默认的mysql表类型不支持这一特性，这(至少在理论上)应该会使它的性能优于mysql。
因此，除非从单个连接批量插入是应用程序的标准，否则这个测试实际上不会显示任何有用的东西……更有可能的是，您将同时使用数十个连接来插入、查询和/或更新小块数据
Stack Overflow用户
发布于 2011-09-02 01:26:39
- 创建不带任何索引、只有列和三种类型的父表(create table some_data (c_1 int，c_2 VARCHAR...))
- create sequence for new id from sequence
- create new table for real table with ' like‘关键字的真实数据(create table some_data_X like real data in some_data_X with copy in binary real index，约束(使用到postgresql)
- inherit父表的多个连接为内核赋能
- 现在可以选择！
通过这种方式，我在10列(2个至强，24个内核，24 Gb内存，SSD)上创建索引，每秒可以实现400000-500000次插入。
- 的好处:在间隔线程中删除旧数据(带有最小X的some_data_X)：带索引的巨大循环缓冲区！
https://stackoverflow.com/questions/4745042
复制相似问题
腾讯云开发者
