zfs on CentOS 6.5 x64 compare performance with ext4 use postgresql pgbench

[root@db-172-16-3-150 ssd4]# cd /zptest
[root@db-172-16-3-150 zptest]# mkdir pg93
[root@db-172-16-3-150 zptest]# chown pg93:pg93 pg93
[root@db-172-16-3-150 zptest]# su - pg93
cpg93@db-172-16-3-150-> cd /zptest/pg93/
pg93@db-172-16-3-150-> pg_test_fsync 
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 64kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                                n/a*
        fdatasync                         778.117 ops/sec    1285 usecs/op
        fsync                             756.724 ops/sec    1321 usecs/op
        fsync_writethrough                            n/a
        open_sync                                    n/a*
* This file system and its mount options do not support direct
I/O, e.g. ext4 in journaled mode.

Compare file sync methods using two 64kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                                n/a*
        fdatasync                          96.185 ops/sec   10397 usecs/op
        fsync                             369.918 ops/sec    2703 usecs/op
        fsync_writethrough                            n/a
        open_sync                                    n/a*
* This file system and its mount options do not support direct
I/O, e.g. ext4 in journaled mode.

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
         1 * 16kB open_sync write                    n/a*
         2 *  8kB open_sync writes                   n/a*
         4 *  4kB open_sync writes                   n/a*
         8 *  2kB open_sync writes                   n/a*
        16 *  1kB open_sync writes                   n/a*

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        write, fsync, close               746.761 ops/sec    1339 usecs/op
        write, close, fsync               217.356 ops/sec    4601 usecs/op

Non-Sync'ed 64kB writes:
        write                           37511.610 ops/sec      27 usecs/op

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    4.05    7.59    0.00   88.35
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sdc               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdd               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00 26688.00    0.00 2502.00     0.00 226848.00    90.67     1.29    0.52   0.31  78.10
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

pg93@db-172-16-3-150-> initdb -E UTF8 --locale=C -D /zptest/pg93/pg_root -U postgres -W
pg93@db-172-16-3-150-> cd /zptest/pg93/pg_root
vi postgresql.conf
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
port = 1922                             # (change requires restart)
max_connections = 100                   # (change requires restart)
unix_socket_directories = '.'   # comma-separated list of directories
tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10               # TCP_KEEPCNT;
shared_buffers = 32MB                   # min 128kB
maintenance_work_mem = 512MB            # min 1MB
vacuum_cost_delay = 10                  # 0-100 milliseconds
vacuum_cost_limit = 10000               # 1-10000 credits
bgwriter_delay = 10ms                   # 10-10000ms between rounds
checkpoint_segments = 3         # in logfile segments, min 1, 16MB each
effective_cache_size = 96000MB
log_destination = 'csvlog'              # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr and csvlog
log_truncate_on_rotation = off          # If on, an existing log file with the
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose           # terse, default, or verbose messages
log_timezone = 'PRC'
autovacuum = on                 # Enable autovacuum subprocess?  'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'                       # locale for system error message
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
default_text_search_config = 'pg_catalog.english'

pg93@db-172-16-3-150-> pg_ctl start -D /zptest/pg93/pg_root

pg93@db-172-16-3-150-> psql -h /zptest/pg93/pg_root -p 1922 -U postgres postgres
psql (9.3.3)
Type "help" for help.
postgres=# create table test(id int primary key, info text, crt_time timestamp);
CREATE TABLE
postgres=# create or replace function f_test(v_id int) returns void as $$
declare
begin
  update test set info=md5(random()::text),crt_time=now() where id=v_id;
  if not found then 
    insert into test values (v_id, md5(random()::text), now());
  end if;
  exception when SQLSTATE '23505' then
    return;
end;
$$ language plpgsql strict;
CREATE FUNCTION

postgres=# select f_test(1);
 f_test 
--------
 
(1 row)

postgres=# select * from test;
 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 80fe8163f44df605621a557624740681 | 2014-05-16 22:01:38.677335
(1 row)

postgres=# select f_test(1);
 f_test 
--------
 
(1 row)

postgres=# select * from test;
 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 5b17eb0ba878e15f40f213716c05a3c5 | 2014-05-16 22:01:42.130284
(1 row)

pg93@db-172-16-3-150-> vi test.sql
\setrandom id 1 500000
select f_test(:id);

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -h /zptest/pg93/pg_root -p 1922 -U postgres -c 16 -j 8 -T 30 postgres 
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 8
duration: 30 s
number of transactions actually processed: 121879
tps = 4060.366837 (including connections establishing)
tps = 4062.166607 (excluding connections establishing)
statement latencies in milliseconds:
        0.004635        \setrandom id 1 500000
        3.930118        select f_test(:id);

/dev/sdc3 on /opt type ext4 (rw,noatime,nodiratime)

pg93@db-172-16-3-150-> pg_ctl stop -m fast -D /zptest/pg93/pg_root
waiting for server to shut down.... done
server stopped
pg93@db-172-16-3-150-> exit
logout
[root@db-172-16-3-150 ~]# cp -r /zptest/pg93/pg_root /opt/pg_root
[root@db-172-16-3-150 ~]# chown -R pg93:pg93 /opt/pg_root
[root@db-172-16-3-150 ~]# su - pg93
pg93@db-172-16-3-150-> pg_ctl start -D /opt/pg_root
server starting
pg93@db-172-16-3-150-> LOG:  00000: redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
LOCATION:  SysLogger_Start, syslogger.c:649
pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 1922 -U postgres postgres
psql (9.3.3)
Type "help" for help.
postgres=# truncate test;
TRUNCATE TABLE
postgres=# \q

pg93@db-172-16-3-150-> cd
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -h /opt/pg_root -p 1922 -U postgres -c 16 -j 8 -T 30 postgres
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 8
duration: 30 s
number of transactions actually processed: 39062
tps = 1301.492448 (including connections establishing)
tps = 1302.085710 (excluding connections establishing)
statement latencies in milliseconds:
        0.004463        \setrandom id 1 500000
        12.278317       select f_test(:id);

pg93@db-172-16-3-150-> pg_ctl stop -m fast -D /opt/pg_root
waiting for server to shut down.... done
server stopped
pg93@db-172-16-3-150-> exit
logout
You have new mail in /var/spool/mail/root
[root@db-172-16-3-150 ~]# cp -r /zptest/pg93/pg_root /ssd4/
database/   pg92/       pg931/      pgxl/       test.pl     zfs.log2    
lost+found/ pg93/       pg94/       ssd3/       zfs.log1    
[root@db-172-16-3-150 ~]# cp -r /zptest/pg93/pg_root /ssd4/pg_root
[root@db-172-16-3-150 ~]# chown -R pg93:pg93 /ssd4/pg_root
[root@db-172-16-3-150 ~]# su - pg93
pg93@db-172-16-3-150-> pg_ctl start -D /ssd4/pg_root
server starting
pg93@db-172-16-3-150-> LOG:  00000: redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
LOCATION:  SysLogger_Start, syslogger.c:649

pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 1922 -U postgres postgres
psql (9.3.3)
Type "help" for help.

postgres=# truncate test;
TRUNCATE TABLE
postgres=# \q

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -h /ssd4/pg_root -p 1922 -U postgres -c 16 -j 8 -T 30 postgres
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 8
duration: 30 s
number of transactions actually processed: 266565
tps = 8876.805761 (including connections establishing)
tps = 8881.884999 (excluding connections establishing)
statement latencies in milliseconds:
        0.003877        \setrandom id 1 500000
        1.793971        select f_test(:id);

[root@db-172-16-3-150 ~]# cd /ssd4
[root@db-172-16-3-150 ssd4]# dd if=/dev/zero of=./test.img bs=1024k count=1024
[root@db-172-16-3-150 ssd4]# mkfs.ext4 ./test.img 
[root@db-172-16-3-150 ssd4]# mount -o loop ./test.img /mnt
[root@db-172-16-3-150 ssd4]# su - pg93
pg93@db-172-16-3-150-> pg_ctl stop -m fast -D /ssd4/pg_root
waiting for server to shut down.... done
server stopped
pg93@db-172-16-3-150-> exit
logout
[root@db-172-16-3-150 ssd4]# cp -r /ssd4/pg_root /mnt/
[root@db-172-16-3-150 ssd4]# chown -R pg93:pg93 /mnt/pg_root
[root@db-172-16-3-150 ssd4]# su - pg93
pg93@db-172-16-3-150-> pg_ctl start -D /mnt/pg_root
server starting
pg93@db-172-16-3-150-> LOG:  00000: redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
LOCATION:  SysLogger_Start, syslogger.c:649

pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 1922 -U postgres postgres
psql (9.3.3)
Type "help" for help.

postgres=# truncate test;
TRUNCATE TABLE
postgres=# \q
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -h /mnt/pg_root -p 1922 -U postgres -c 16 -j 8 -T 30 postgres
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 8
duration: 30 s
number of transactions actually processed: 192445
tps = 6410.026454 (including connections establishing)
tps = 6412.254485 (excluding connections establishing)
statement latencies in milliseconds:
        0.004118        \setrandom id 1 500000
        2.486914        select f_test(:id);