postgresql有可能在win下优化到一秒钟插入一万条记录吗？（非ssd)

一只嗡嗡的苍蝇

如果有可能的话，我也会转向postgresql，目前我没有搞成功过。
机器就是普通的core 5870这种，5400转的硬盘。
因为mysql,oracle的性能都有可能达到这么多。

tanfeng3

看看德哥的这个帖子，批量插入，最高可达插入 249088　条数据每秒，详见
http://blog.163.com/digoal@126/b ... 040201242331551545/

测试环境:
PostgreSQL 9.2 beta1
CentOS 5.x 64bit
HP DL360G5
2 * Intel(R) Xeon(R) CPU           E5440  @ 2.83GHz CPU
SAS 146GB硬盘

一只嗡嗡的苍蝇

硬件没一个符合，win,5400转硬盘，core 5870左右。

tanfeng3

如果觉得帖子不好，就算我没说好了，楼主可以选择不看，　想在 win 下把数据库性能发挥极致，个人觉得不合适。

而且 blog 里的批量插入方法，楼主可以 在 win 下测试下。

另外，不知道楼主 win 下的 pg 是否优化。

yueliangdao0608

把你的写语句以及表结构，POSTGRESQL的配置文件，详细的硬件信息贴出来，一块来看下哦。

yueliangdao0608

把你的写语句以及表结构，POSTGRESQL的配置文件，详细的硬件信息贴出来，一块来看下哦。

wanzai01

我的硬件:
i5 2450M 2.5G
8G 内存
西部数据 500G 5400转硬盘
东芝L生活本
Win7 x64
PostgreSQL 9.3.2 for win-x64

测试如下 :
单步提交性能约1.7万TPS.
100条每个batch的批量提交的话性能约12.5万记录每秒

E:\proc\PostgreSQL\9.3\bin>psql.exe  -U postgres postgres
用户 postgres 的口令：
psql (9.3.2)
输入 "help" 来获取帮助信息.
postgres=# create database digoal with template template0 encoding 'UTF8';
CREATE DATABASE
postgres=# create role digoal nosuperuser login encrypted password 'digoal';
CREATE ROLE
postgres=# grant all on database digoal to digoal;
GRANT
postgres=# \c digoal digoal
digoal=> create schema digoal;
CREATE SCHEMA
digoal=> create table test(id serial8 primary key, info text, crt_time timestamp default clock_timestamp());
CREATE TABLE


vi $PGDATA/pg_hba.conf
host    all             all             127.0.0.1/32            trust

vi $PGDATA/postgresql.conf
shared_buffers = 2048MB
maintenance_work_mem = 256MB
vacuum_cost_delay = 10
vacuum_cost_limit = 10000
bgwriter_delay = 10ms
synchronous_commit = off
wal_buffers = 16384kB
wal_writer_delay = 10ms
checkpoint_segments = 128
effective_cache_size = 8192MB
log_destination = 'csvlog'
log_truncate_on_rotation = on
log_checkpoints = on
log_error_verbosity = verbose
log_autovacuum_min_duration = 0
autovacuum_freeze_max_age = 1500000000
vacuum_freeze_table_age = 1200000000

重启服务

vi e:\test.sql
insert into test (info) values (md5(random()::text));


E:\proc\PostgreSQL\9.3\bin>pgbench -M prepared -n -r -f e:\test.sql -c 6 -j 2 -T
10 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 2
duration: 10 s
number of transactions actually processed: 166166
tps = 16604.667059 (including connections establishing)
tps = 17022.521990 (excluding connections establishing)
statement latencies in milliseconds:
        0.350674        insert into test (info) values (md5(random()::text));

50条一个batch, 约2.5kTPS, 转换成记录数约12.5万条每秒
E:\proc\PostgreSQL\9.3\bin>pgbench -M prepared -n -r -f e:\test.sql -c 8 -j 2 -T
10 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 2
duration: 10 s
number of transactions actually processed: 25118
tps = 2476.191514 (including connections establishing)
tps = 2549.831205 (excluding connections establishing)
statement latencies in milliseconds:
        3.130863        insert into test (info) values ('test'),('test'),('test'
),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test
'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('tes
t'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('te
st'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('t
est'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),('
test'),('test'),('test');

E:\proc\PostgreSQL\9.3\bin>psql -U digoal digoal
psql (9.3.2)
输入 "help" 来获取帮助信息.

digoal=> select count(*) from test;
  count
---------
1741639
(1 行记录)


digoal=> select * from test limit 5;
id |               info               |        crt_time
----+----------------------------------+-------------------------
  1 | fcf4f7dd1d05420683596be124fb1fc8 | 2014-02-15 09:36:02.614
  2 | 8cec0d844b653441c09cbc6699ab6ed0 | 2014-02-15 09:36:02.624
  3 | b86cd74a4c069bd9967d30d65ea414f4 | 2014-02-15 09:36:02.625
  4 | f969b073918087a568d5f73135068fe7 | 2014-02-15 09:36:02.626
  5 | 24ca01ee37f66568b549678b8c98b3a0 | 2014-02-15 09:36:02.627
(5 行记录)

如需其他帮助可浏览我的blog
推荐浏览 :
新手建议, PG优化案例分析, PG培训视频
http://blog.163.com/digoal@126/b ... 040201172183022203/
http://www.tudou.com/home/digoal
http://blog.163.com/digoal@126/b ... 7040201392641033482
http://blog.163.com/digoal@126/b ... 7040201221382150858

一只嗡嗡的苍蝇

thanks i'll try later