2018-12-24

Aurora のエンドポイント

AWS

クラスターエンドポイント

$ dig +trace aurora-postgres105.cluster-********.ap-northeast-1.rds.amazonaws.com
; <<>> DiG 9.8.3-P1 <<>> +trace aurora-postgres105.cluster-********.ap-northeast-1.rds.amazonaws.com
;; global options: +cmd
.			124164	IN	NS	i.root-servers.net.
.			124164	IN	NS	a.root-servers.net.
.			124164	IN	NS	j.root-servers.net.
.			124164	IN	NS	k.root-servers.net.
.			124164	IN	NS	l.root-servers.net.
.			124164	IN	NS	m.root-servers.net.
.			124164	IN	NS	b.root-servers.net.
.			124164	IN	NS	c.root-servers.net.
.			124164	IN	NS	d.root-servers.net.
.			124164	IN	NS	e.root-servers.net.
.			124164	IN	NS	f.root-servers.net.
.			124164	IN	NS	g.root-servers.net.
.			124164	IN	NS	h.root-servers.net.
;; Received 508 bytes from 192.168.10.1#53(192.168.10.1) in 131 ms

com.			172800	IN	NS	a.gtld-servers.net.
com.			172800	IN	NS	e.gtld-servers.net.
com.			172800	IN	NS	j.gtld-servers.net.
com.			172800	IN	NS	f.gtld-servers.net.
com.			172800	IN	NS	i.gtld-servers.net.
com.			172800	IN	NS	l.gtld-servers.net.
com.			172800	IN	NS	b.gtld-servers.net.
com.			172800	IN	NS	h.gtld-servers.net.
com.			172800	IN	NS	g.gtld-servers.net.
com.			172800	IN	NS	k.gtld-servers.net.
com.			172800	IN	NS	m.gtld-servers.net.
com.			172800	IN	NS	c.gtld-servers.net.
com.			172800	IN	NS	d.gtld-servers.net.
;; Received 506 bytes from 192.112.36.4#53(192.112.36.4) in 37 ms

amazonaws.com.		172800	IN	NS	u1.amazonaws.com.
amazonaws.com.		172800	IN	NS	u2.amazonaws.com.
amazonaws.com.		172800	IN	NS	r1.amazonaws.com.
amazonaws.com.		172800	IN	NS	r2.amazonaws.com.
;; Received 334 bytes from 192.33.14.30#53(192.33.14.30) in 120 ms

ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-978.awsdns-58.net.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1592.awsdns-07.co.uk.
;; Received 227 bytes from 156.154.64.10#53(156.154.64.10) in 15 ms

aurora-postgres105.cluster-********.ap-northeast-1.rds.amazonaws.com. 5 IN CNAME aurora-postgres105-a.********.ap-northeast-1.rds.amazonaws.com.
aurora-postgres105-a.********.ap-northeast-1.rds.amazonaws.com. 5 IN CNAME ec2-**-***-109-247.ap-northeast-1.compute.amazonaws.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1592.awsdns-07.co.uk.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-978.awsdns-58.net.
;; Received 331 bytes from 205.251.197.116#53(205.251.197.116) in 8 ms

リーダーエンドポイント

$ dig +trace aurora-postgres105.cluster-ro-********.ap-northeast-1.rds.amazonaws.com

; <<>> DiG 9.8.3-P1 <<>> +trace aurora-postgres105.cluster-ro-********.ap-northeast-1.rds.amazonaws.com
;; global options: +cmd
.			124045	IN	NS	e.root-servers.net.
.			124045	IN	NS	f.root-servers.net.
.			124045	IN	NS	g.root-servers.net.
.			124045	IN	NS	h.root-servers.net.
.			124045	IN	NS	i.root-servers.net.
.			124045	IN	NS	j.root-servers.net.
.			124045	IN	NS	k.root-servers.net.
.			124045	IN	NS	l.root-servers.net.
.			124045	IN	NS	m.root-servers.net.
.			124045	IN	NS	a.root-servers.net.
.			124045	IN	NS	b.root-servers.net.
.			124045	IN	NS	c.root-servers.net.
.			124045	IN	NS	d.root-servers.net.
;; Received 508 bytes from 192.168.10.1#53(192.168.10.1) in 22 ms

;; Truncated, retrying in TCP mode.
com.			172800	IN	NS	l.gtld-servers.net.
com.			172800	IN	NS	b.gtld-servers.net.
com.			172800	IN	NS	c.gtld-servers.net.
com.			172800	IN	NS	d.gtld-servers.net.
com.			172800	IN	NS	e.gtld-servers.net.
com.			172800	IN	NS	f.gtld-servers.net.
com.			172800	IN	NS	g.gtld-servers.net.
com.			172800	IN	NS	a.gtld-servers.net.
com.			172800	IN	NS	h.gtld-servers.net.
com.			172800	IN	NS	i.gtld-servers.net.
com.			172800	IN	NS	j.gtld-servers.net.
com.			172800	IN	NS	k.gtld-servers.net.
com.			172800	IN	NS	m.gtld-servers.net.
;; Received 889 bytes from 192.203.230.10#53(192.203.230.10) in 16 ms

amazonaws.com.		172800	IN	NS	u1.amazonaws.com.
amazonaws.com.		172800	IN	NS	u2.amazonaws.com.
amazonaws.com.		172800	IN	NS	r1.amazonaws.com.
amazonaws.com.		172800	IN	NS	r2.amazonaws.com.
;; Received 337 bytes from 192.52.178.30#53(192.52.178.30) in 177 ms

ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-978.awsdns-58.net.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1592.awsdns-07.co.uk.
;; Received 230 bytes from 156.154.65.10#53(156.154.65.10) in 16 ms

aurora-postgres105.cluster-ro-********.ap-northeast-1.rds.amazonaws.com. 1 IN CNAME	aurora-postgres105-c.********.ap-northeast-1.rds.amazonaws.com.
aurora-postgres105-c.********.ap-northeast-1.rds.amazonaws.com. 5 IN CNAME ec2-**-***-100-225.ap-northeast-1.compute.amazonaws.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1592.awsdns-07.co.uk.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-978.awsdns-58.net.
;; Received 334 bytes from 205.251.195.210#53(205.251.195.210) in 101 ms

リーダーエンドポイント

$ dig +trace aurora-postgres105.cluster-ro-********.ap-northeast-1.rds.amazonaws.com

; <<>> DiG 9.8.3-P1 <<>> +trace aurora-postgres105.cluster-ro-********.ap-northeast-1.rds.amazonaws.com
;; global options: +cmd
.			123850	IN	NS	f.root-servers.net.
.			123850	IN	NS	g.root-servers.net.
.			123850	IN	NS	h.root-servers.net.
.			123850	IN	NS	i.root-servers.net.
.			123850	IN	NS	j.root-servers.net.
.			123850	IN	NS	k.root-servers.net.
.			123850	IN	NS	l.root-servers.net.
.			123850	IN	NS	m.root-servers.net.
.			123850	IN	NS	a.root-servers.net.
.			123850	IN	NS	b.root-servers.net.
.			123850	IN	NS	c.root-servers.net.
.			123850	IN	NS	d.root-servers.net.
.			123850	IN	NS	e.root-servers.net.
;; Received 508 bytes from 192.168.10.1#53(192.168.10.1) in 43 ms

com.			172800	IN	NS	k.gtld-servers.net.
com.			172800	IN	NS	d.gtld-servers.net.
com.			172800	IN	NS	l.gtld-servers.net.
com.			172800	IN	NS	f.gtld-servers.net.
com.			172800	IN	NS	c.gtld-servers.net.
com.			172800	IN	NS	g.gtld-servers.net.
com.			172800	IN	NS	b.gtld-servers.net.
com.			172800	IN	NS	h.gtld-servers.net.
com.			172800	IN	NS	i.gtld-servers.net.
com.			172800	IN	NS	e.gtld-servers.net.
com.			172800	IN	NS	a.gtld-servers.net.
com.			172800	IN	NS	j.gtld-servers.net.
com.			172800	IN	NS	m.gtld-servers.net.
;; Received 509 bytes from 192.33.4.12#53(192.33.4.12) in 121 ms

amazonaws.com.		172800	IN	NS	u1.amazonaws.com.
amazonaws.com.		172800	IN	NS	u2.amazonaws.com.
amazonaws.com.		172800	IN	NS	r1.amazonaws.com.
amazonaws.com.		172800	IN	NS	r2.amazonaws.com.
;; Received 337 bytes from 192.43.172.30#53(192.43.172.30) in 120 ms

ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-978.awsdns-58.net.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1592.awsdns-07.co.uk.
;; Received 230 bytes from 156.154.64.10#53(156.154.64.10) in 21 ms

aurora-postgres105.cluster-ro-********.ap-northeast-1.rds.amazonaws.com. 1 IN CNAME	aurora-postgres105-a-ap-northeast-1a.********.ap-northeast-1.rds.amazonaws.com.
aurora-postgres105-a-ap-northeast-1a.********.ap-northeast-1.rds.amazonaws.com. 5 IN CNAME ec2-54-65-33-213.ap-northeast-1.compute.amazonaws.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1592.awsdns-07.co.uk.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-978.awsdns-58.net.
;; Received 348 bytes from 205.251.192.158#53(205.251.192.158) in 119 ms

インスタンスエンドポイント

$ dig +trace aurora-postgres105-a.********.ap-northeast-1.rds.amazonaws.com 

; <<>> DiG 9.8.3-P1 <<>> +trace aurora-postgres105-a.********.ap-northeast-1.rds.amazonaws.com
;; global options: +cmd
.			122728	IN	NS	j.root-servers.net.
.			122728	IN	NS	k.root-servers.net.
.			122728	IN	NS	l.root-servers.net.
.			122728	IN	NS	m.root-servers.net.
.			122728	IN	NS	b.root-servers.net.
.			122728	IN	NS	c.root-servers.net.
.			122728	IN	NS	d.root-servers.net.
.			122728	IN	NS	e.root-servers.net.
.			122728	IN	NS	f.root-servers.net.
.			122728	IN	NS	g.root-servers.net.
.			122728	IN	NS	h.root-servers.net.
.			122728	IN	NS	i.root-servers.net.
.			122728	IN	NS	a.root-servers.net.
;; Received 508 bytes from 192.168.10.1#53(192.168.10.1) in 19 ms

;; Truncated, retrying in TCP mode.
com.			172800	IN	NS	l.gtld-servers.net.
com.			172800	IN	NS	b.gtld-servers.net.
com.			172800	IN	NS	c.gtld-servers.net.
com.			172800	IN	NS	d.gtld-servers.net.
com.			172800	IN	NS	e.gtld-servers.net.
com.			172800	IN	NS	f.gtld-servers.net.
com.			172800	IN	NS	g.gtld-servers.net.
com.			172800	IN	NS	a.gtld-servers.net.
com.			172800	IN	NS	h.gtld-servers.net.
com.			172800	IN	NS	i.gtld-servers.net.
com.			172800	IN	NS	j.gtld-servers.net.
com.			172800	IN	NS	k.gtld-servers.net.
com.			172800	IN	NS	m.gtld-servers.net.
;; Received 880 bytes from 192.5.5.241#53(192.5.5.241) in 28 ms

amazonaws.com.		172800	IN	NS	u1.amazonaws.com.
amazonaws.com.		172800	IN	NS	u2.amazonaws.com.
amazonaws.com.		172800	IN	NS	r1.amazonaws.com.
amazonaws.com.		172800	IN	NS	r2.amazonaws.com.
;; Received 328 bytes from 192.55.83.30#53(192.55.83.30) in 188 ms

ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-1592.awsdns-07.co.uk.
ap-northeast-1.rds.amazonaws.com. 300 IN NS	ns-978.awsdns-58.net.
;; Received 221 bytes from 205.251.195.199#53(205.251.195.199) in 100 ms

aurora-postgres105-a.********.ap-northeast-1.rds.amazonaws.com. 5 IN CNAME ec2-**-***-109-247.ap-northeast-1.compute.amazonaws.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1396.awsdns-46.org.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-158.awsdns-19.com.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-1592.awsdns-07.co.uk.
ap-northeast-1.rds.amazonaws.com. 1800 IN NS	ns-978.awsdns-58.net.
;; Received 277 bytes from 205.251.195.210#53(205.251.195.210) in 96 ms

参考

dev.classmethod.jp
qiita.com

2018-12-18

fio の結果の見方

Client1: (groupid=0, jobs=1): err= 0: pid=16109: Sat Jun 24 12:07:54 2017
  write: IOPS=88, BW=623KiB/s (638kB/s)(30.4MiB/50032msec)
    slat (nsec): min=500, max=145500, avg=8318.00, stdev=4781.50
    clat (usec): min=170, max=78367, avg=4019.02, stdev=8293.31
     lat (usec): min=174, max=78375, avg=4027.34, stdev=8291.79
    clat percentiles (usec):
     |  1.00th=[  302],  5.00th=[  326], 10.00th=[  343], 20.00th=[  363],
     | 30.00th=[  392], 40.00th=[  404], 50.00th=[  416], 60.00th=[  445],
     | 70.00th=[  816], 80.00th=[ 6718], 90.00th=[12911], 95.00th=[21627],
     | 99.00th=[43779], 99.50th=[51643], 99.90th=[68682], 99.95th=[72877],
     | 99.99th=[78119]
   bw (  KiB/s): min=  532, max=  686, per=0.10%, avg=622.87, stdev=24.82, samples=  100
   iops        : min=   76, max=   98, avg=88.98, stdev= 3.54, samples=  100
  lat (usec)   : 250=0.04%, 500=64.11%, 750=4.81%, 1000=2.79%
  lat (msec)   : 2=4.16%, 4=1.84%, 10=4.90%, 20=11.33%, 50=5.37%
  lat (msec)   : 100=0.65%
  cpu          : usr=0.27%, sys=0.18%, ctx=12072, majf=0, minf=21
  IO depths    : 1=85.0%, 2=13.1%, 4=1.8%, 8=0.1%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued rwt: total=0,4450,0, short=0,0,0, dropped=0,0,0
     latency   : target=0, window=0, percentile=100.00%, depth=8
（中略）

read/write/trim

The string before the colon shows the I/O direction the statistics are for. IOPS is the average I/Os performed per second. BW is the average bandwidth rate shown as: value in power of 2 format (value in power of 10 format). The last two values show: (total I/O performed in power of 2 format / runtime of that thread).

slat

Submission latency (min being the minimum, max being the maximum, avg being the average, stdev being the standard deviation). This is the time it took to submit the I/O. For sync I/O this row is not displayed as the slat is really the completion latency (since queue/complete is one operation there). This value can be in nanoseconds, microseconds or milliseconds — fio will choose the most appropriate base and print that (in the example above nanoseconds was the best scale). Note: in --minimal mode latencies are always expressed in microseconds.

clat

Completion latency. Same names as slat, this denotes the time from submission to completion of the I/O pieces. For sync I/O, clat will usually be equal (or very close) to 0, as the time from submit to complete is basically just CPU time (I/O has already been done, see slat explanation).

lat

Total latency. Same names as slat and clat, this denotes the time from when fio created the I/O unit to completion of the I/O operation.

bw

Bandwidth statistics based on samples. Same names as the xlat stats, but also includes the number of samples taken (samples) and an approximate percentage of total aggregate bandwidth this thread received in its group (per). This last value is only really useful if the threads in this group are on the same disk, since they are then competing for disk access.

1. fio - Flexible I/O tester rev. 3.12 — fio 3.12 documentation

2018-12-18

EFS を使ってみる

AWS

インストールとマウント

$ sudo yum install -y amazon-efs-utils fio
$ sudo mkdir /efs
$ sudo mount -t efs -o tls fs-d462f79c:/ /efs

ベンチマーク実行

$ sudo fio -filename=/efs/test1 -rw=read -direct=1 -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file1
$ sudo fio -filename=/efs/test2 -rw=randread  -direct=1 -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file2
$ sudo fio -filename=/efs/test3 -rw=write -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file3
$ sudo fio -filename=/efs/test4 -rw=randwrite -direct -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file4

結果1

$ sudo fio -filename=/efs/test1 -rw=read -direct=1 -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file1
file1: (g=0): rw=read, bs=4K-4K/4K-4K/4K-4K, ioengine=psync★="Basic pread(2) or pwrite(2) I/O" , iodepth=1
...
fio-2.14
Starting 2 processes
Jobs: 2 (f=2): [R(2)] [100.0% done] [2064KB/0KB/0KB /s] [516/0/0 iops] [eta 00m:00s]
file1: (groupid=0, jobs=2): err= 0: pid=19277: Sat Dec 22 19:28:24 2018
  read : io=24700KB, bw=2469.2KB/s, iops=617, runt= 10004msec
    clat (msec): min=1, max=44, avg= 3.23, stdev= 1.01
     lat (msec): min=1, max=44, avg= 3.23★, stdev= 1.01
    clat percentiles (usec):
     |  1.00th=[ 2352],  5.00th=[ 2480], 10.00th=[ 2544], 20.00th=[ 2640],
     | 30.00th=[ 2736], 40.00th=[ 2832], 50.00th=[ 2960], 60.00th=[ 3216],
     | 70.00th=[ 3696], 80.00th=[ 3824], 90.00th=[ 4016], 95.00th=[ 4192],
     | 99.00th=[ 5408], 99.50th=[ 6304], 99.90th=[ 7968], 99.95th=[ 8640],
     | 99.99th=[44288]
    lat (msec) : 2=0.03%, 4=88.62%, 10=11.32%, 50=0.03%
  cpu          : usr=0.60%, sys=0.00%, ctx=6187, majf=0, minf=18
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=6175/w=0/d=0, short=r=0/w=0/d=0, drop=r=0/w=0/d=0
     latency   : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
   READ: io=24700KB, aggrb=2469KB/s, minb=2469KB/s, maxb=2469KB/s, mint=10004msec, maxt=10004msec

結果2

$ sudo fio -filename=/efs/test1 -rw=randread -direct=1 -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file1
file1: (g=0): rw=randread, bs=4K-4K/4K-4K/4K-4K, ioengine=psync★, iodepth=1
...
fio-2.14
Starting 2 processes
Jobs: 2 (f=2): [r(2)] [100.0% done] [2344KB/0KB/0KB /s] [586/0/0 iops] [eta 00m:00s]
file1: (groupid=0, jobs=2): err= 0: pid=19373: Sat Dec 22 19:46:29 2018
  read : io=24192KB, bw=2417.8KB/s, iops=604, runt= 10006msec
    clat (msec): min=2, max=40, avg= 3.30, stdev= 1.29
     lat (msec): min=2, max=40, avg= 3.30★, stdev= 1.29
    clat percentiles (usec):
     |  1.00th=[ 2384],  5.00th=[ 2480], 10.00th=[ 2576], 20.00th=[ 2672],
     | 30.00th=[ 2768], 40.00th=[ 2832], 50.00th=[ 2960], 60.00th=[ 3216],
     | 70.00th=[ 3568], 80.00th=[ 3792], 90.00th=[ 4016], 95.00th=[ 4384],
     | 99.00th=[ 7712], 99.50th=[10944], 99.90th=[18560], 99.95th=[19840],
     | 99.99th=[40704]
    lat (msec) : 4=89.65%, 10=9.69%, 20=0.61%, 50=0.05%
  cpu          : usr=0.57%, sys=0.00%, ctx=6058, majf=0, minf=15
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=6048/w=0/d=0, short=r=0/w=0/d=0, drop=r=0/w=0/d=0
     latency   : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
   READ: io=24192KB, aggrb=2417KB/s, minb=2417KB/s, maxb=2417KB/s, mint=10006msec, maxt=10006msec

結果3

$ sudo fio -filename=/efs/test1 -rw=write -direct=1 -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file1
file1: (g=0): rw=write, bs=4K-4K/4K-4K/4K-4K, ioengine=psync★, iodepth=1
...
fio-2.14
Starting 2 processes
Jobs: 2 (f=2): [W(2)] [100.0% done] [0KB/840KB/0KB /s] [0/210/0 iops] [eta 00m:00s]
file1: (groupid=0, jobs=2): err= 0: pid=19378: Sat Dec 22 19:49:08 2018
  write: io=8284.0KB, bw=847942B/s, iops=207, runt= 10004msec
    clat (msec): min=6, max=52, avg= 9.65, stdev= 2.29
     lat (msec): min=6, max=52, avg= 9.65★, stdev= 2.29
    clat percentiles (usec):
     |  1.00th=[ 7584],  5.00th=[ 7968], 10.00th=[ 8160], 20.00th=[ 8384],
     | 30.00th=[ 8768], 40.00th=[ 9024], 50.00th=[ 9280], 60.00th=[ 9536],
     | 70.00th=[ 9792], 80.00th=[10304], 90.00th=[11072], 95.00th=[12480],
     | 99.00th=[19328], 99.50th=[20608], 99.90th=[26496], 99.95th=[50432],
     | 99.99th=[52480]
    lat (msec) : 10=74.89%, 20=24.43%, 50=0.58%, 100=0.10%
  cpu          : usr=0.18%, sys=0.00%, ctx=2080, majf=0, minf=20
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=0/w=2071/d=0, short=r=0/w=0/d=0, drop=r=0/w=0/d=0
     latency   : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
  WRITE: io=8284KB, aggrb=828KB/s, minb=828KB/s, maxb=828KB/s, mint=10004msec, maxt=10004msec

結果4

$ sudo fio -filename=/efs/test1 -rw=randwrite -direct=1 -bs=4k -size=2G -numjobs=2 -runtime=10 -group_reporting -name=file1
file1: (g=0): rw=randwrite, bs=4K-4K/4K-4K/4K-4K, ioengine=psync★, iodepth=1
...
fio-2.14
Starting 2 processes
Jobs: 2 (f=2): [w(2)] [100.0% done] [0KB/764KB/0KB /s] [0/191/0 iops] [eta 00m:00s]
file1: (groupid=0, jobs=2): err= 0: pid=19391: Sat Dec 22 19:53:22 2018
  write: io=7960.0KB, bw=814778B/s, iops=198, runt= 10004msec
    clat (msec): min=7, max=36, avg=10.04, stdev= 2.28
     lat (msec): min=7, max=36, avg=10.04★, stdev= 2.28
    clat percentiles (usec):
     |  1.00th=[ 7776],  5.00th=[ 8096], 10.00th=[ 8256], 20.00th=[ 8640],
     | 30.00th=[ 9024], 40.00th=[ 9280], 50.00th=[ 9536], 60.00th=[ 9920],
     | 70.00th=[10176], 80.00th=[10816], 90.00th=[11968], 95.00th=[13632],
     | 99.00th=[20096], 99.50th=[22912], 99.90th=[33024], 99.95th=[36608],
     | 99.99th=[36608]
    lat (msec) : 10=63.77%, 20=35.23%, 50=1.01%
  cpu          : usr=0.17%, sys=0.00%, ctx=2000, majf=0, minf=19
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=0/w=1990/d=0, short=r=0/w=0/d=0, drop=r=0/w=0/d=0
     latency   : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
  WRITE: io=7960KB, aggrb=795KB/s, minb=795KB/s, maxb=795KB/s, mint=10004msec, maxt=10004msec

計測

$ nfsiostat 5

参考

1. fio - Flexible I/O tester rev. 3.12 — fio 3.12 documentation

2018-12-17

Redshift の "ERROR: 1023 DETAIL: Serializable isolation violation"

Redshift

準備

psql で Redshift に接続する。

psql "host=ds2xl-2n.******.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"

テーブルを作成してデータを投入する。

\echo :AUTOCOMMIT;
CREATE TABLE tab1 (col1 INTEGER NOT NULL);
CREATE TABLE tab2 (col1 INTEGER NOT NULL);
CREATE TABLE tab3 (col1 INTEGER NOT NULL);
CREATE TABLE tab4 (col1 INTEGER NOT NULL);
insert into tab1 values (1);
insert into tab2 values (1);
insert into tab3 values (1);
insert into tab4 values (1);

Serializable isolation violation を再現させる

セッション1

ds2xl-2n awsuser 18:35:20 =# begin;
BEGIN
Time: 2.073 ms
ds2xl-2n awsuser 18:35:35 =# select * from tab1;
 col1
------
    1
(1 row)

Time: 10.063 ms
ds2xl-2n awsuser 18:35:43 =# insert into tab2 values (1);
INSERT 0 1
Time: 16.216 ms
ds2xl-2n awsuser 18:35:56 =#

セッション2

ds2xl-2n awsuser 18:35:28 =# begin;
BEGIN
Time: 2.100 ms
ds2xl-2n awsuser 18:35:36 =# insert into tab1 values (1);
INSERT 0 1
Time: 18.034 ms
ds2xl-2n awsuser 18:35:49 =# select * from tab2;
ERROR:  1023
DETAIL:  Serializable isolation violation on table - 100229, transactions forming the cycle are: 11610, 11611 (pid:19693)
Time: 54.440 ms
ds2xl-2n awsuser 18:36:04 =#

再現しないケース1

psql はデフォルトで auto commit = on のため begin で明示的にトランザクションを開始しない場合は DML を発行する度に commit が発行されるため、"Serializable isolation violation" は発生しない。

セッション1

ds2xl-2n awsuser 18:38:49 =# select * from tab1;
 col1
------
    1
(1 row)

Time: 2.011 ms
ds2xl-2n awsuser 18:38:50 =# insert into tab2 values (1);
INSERT 0 1
Time: 358.094 ms
ds2xl-2n awsuser 18:39:07 =#

セッション2

ds2xl-2n awsuser 18:38:34 =# insert into tab1 values (1);
INSERT 0 1
Time: 392.567 ms
ds2xl-2n awsuser 18:39:00 =# select * from tab2;
 col1
------
    1
    1
    1
(3 rows)

Time: 8.442 ms
ds2xl-2n awsuser 18:39:12 =#

再現しないケース2

当然、異なるテーブルに対するトランザクションでは発生しない。

セッション1

ds2xl-2n awsuser 18:44:32 =# begin;
BEGIN
Time: 1.718 ms
ds2xl-2n awsuser 18:44:35 =# select * from tab1;
 col1
------
    1
    1
(2 rows)

Time: 1527.429 ms
ds2xl-2n awsuser 18:44:42 =# insert into tab2 values (1);
INSERT 0 1
Time: 16.169 ms
ds2xl-2n awsuser 18:44:56 =#

セッション2

ds2xl-2n awsuser 18:44:36 =# begin;
BEGIN
Time: 1.375 ms
ds2xl-2n awsuser 18:44:38 =# insert into tab3 values (1);
INSERT 0 1
Time: 15.809 ms
ds2xl-2n awsuser 18:44:49 =# select * from tab4;
 col1
------
    1
(1 row)

Time: 8.116 ms
ds2xl-2n awsuser 18:45:02 =#

参考

https://aws.amazon.com/jp/premiumsupport/knowledge-center/redshift-serializable-isolation/

2018-12-12

Amazon chime の Chat room の URL を確認する

AWS

https://app.chime.aws/rooms/ にアクセスすると自分が参加している chat room の一覧が表示されるので、chat room をクリックすると URL にジャンプする。chato room への invite を依頼しなくてもリンクをクリックすると、アクセス許可依頼が飛ぶ。

2018-12-09

RDS Oracle EE 作成時に "Please specify an option group containing the TDE HSM option" と怒られる

AWS

事象

RDS Oracle (Enterprise Edition) でデータベース作成時に"Please specify an option group containing the TDE HSM option" と怒られる。

Please specify an option group containing the TDE HSM option (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterCombination; Request ID: 0dbe3f20-fdea-40dc-b362-f98e6208d275)

回避策

利用したいバージョン（11.2、12.1、12.2）のオプショングループを作成して TDE_HSM を追加するとエラーが出なくなった（作成したオプショングループを使わなくても）。

2018-12-09

SwingBench の Data Generator で Sales History のデータを生成する

Oracle

準備

RDS Oracle

RDS Oracle データベースを作成する。

EC2

EC2インスタンスを作成する。
JDKをインストールする。

$ sudo yum install -y java-1.8.0-openjdk.x86_64

Data Generator をダウンロードする。

$ wget http://dominicgiles.com/swingbench/datagenerator17Nov.zip

解凍する。

$ unzip datagenerator17Nov.zip

"ORA-12899: value too large for column" を回避するため CUSTOMER.CUST_EMAIL(VARCHAR2) を 30 byte から 50 byte に変更する。

$ cd datagenerator/bin
$ cp -p sh.xml sh.xml.org
         <Column>
            <ColumnName>CUST_EMAIL</ColumnName>
            <DataType>VARCHAR2</DataType>
            <PrimaryKey>false</PrimaryKey>
            <NullsAllowed>true</NullsAllowed>
            <Size>30</Size> ★これを30から50に変更する

Oracle SQL Developerコマンドライン SQLcl 4.2 をダウンロードして解凍する。

$ unzip sqlcl-18.3.0.259.2029.zip

データを生成する

データを生成する

$ ./datagenerator -cl -db -c sh.xml -debug -cs oracle-ee-112.******.ap-northeast-1.rds.amazonaws.com:1521/orcl -u awsuser -p ********

テーブルが作成されデータが投入されていることを確認する。

SQL> select table_name from user_tables;
TABLE_NAME
PROMOTIONS
PRODUCTS
CHANNELS
CUSTOMERS
SUPPLEMENTARY_DEMOGRAPHICS
SALES
COUNTRIES

7 rows selected.
SQL> select count(1) from customers;
COUNT(1)
   1954180

CSV出力用SQL スクリプト（sh2csv.sql）を作成する。

SET ARRAYSIZE 2000
SET SQLFORMAT CSV
SET HEADING OFF FEEDBACK OFF
SET TERMOUT OFF

SPOOL promotions.csv
SELECT * FROM promotions;
SPOOL OFF

SPOOL products.csv
SELECT * FROM products;
SPOOL OFF

SPOOL channels.csv
SELECT * FROM channels;
SPOOL OFF

SPOOL customers.csv
SELECT * FROM customers;
SPOOL OFF

SPOOL supplementary_demographics.csv
SELECT * FROM supplementary_demographics;
SPOOL OFF

SPOOL sales.csv
SELECT * FROM sales;
SPOOL OFF

SPOOL countries.csv
SELECT * FROM countries;
SPOOL OFF

JSON形式で出力する。

$ sqlcl/bin/sql awsuser/<Password>@oracle-ee-112.******.ap-northeast-1.rds.amazonaws.com:1521/orcl
SQL> @sh2csv.sql

補足

デバッグするときは -debug オプションを指定する。

$ ./datagenerator -cl -debug ...

実行中に以下のエラーが発生したので、

$ ./datagenerator -cl -db -c sh.xml -debug -cs oracle-ee-112.******.ap-northeast-1.rds.amazonaws.com:1521/orcl -u awsuser -p ********

10:58:25 PM SEVERE   [86] com.dom.datagen.kernel.DatabaseGenerationThread run() Unexpected Error :  ORA-12899: value too large for column "AWSUSER"."CUSTOMERS"."CUST_EMAIL" (actual: 31, maximum: 30)
java.sql.BatchUpdateException: ORA-12899: value too large for column "AWSUSER"."CUSTOMERS"."CUST_EMAIL" (actual: 31, maximum: 30)

ファイルにデータを出力して、

./datagenerator -cl -f -c sh.xml  -cs oracle-ee-112.******.ap-northeast-1.rds.amazonaws.com:1521/orcl  -u awsuser -p ********

中身を確認すると、30文字以上のメールアドレスが存在する。

$ cd generateddata/
$ head -2 CUSTOMERS_124998.csv
692529,julio,silvia,F,1954,divorced,57 " "ablaze" "lane,OPZ" "803,Pembroke,924,Cambridgeshire,0,52770,080381212,G: 130000 - 149999,15000,julio.silvia@verizon.com★メールアドレスは17列目,A924,305,0,02-Feb-2002 00:00:00,31-Dec-2010 00:00:00,A,
692531,freddie,chinn,M,1954,divorced,68 " "funny" "lane,OIP" "164,Kilmalcolm,922,Lincolnshire,0,52777,016491962,L: 300000 and above,10000,freddie.chinn@verizon.com,A922,410,0,08-Jun-2004 00:00:00,31-Dec-2010 00:00:00,A,
$ perl -F, -lane 'print $F[16]' CUSTOMERS_124998.csv|wc -L
38 ★メールアドレスの文字数は最大38
$ perl -F, -lane 'print $F[16] if(length($F[16])>30)' CUSTOMERS_124998.csv|head -3
fletcher.stonebraker@googlemail.com ★30文字を超えているメールアドレスを確認する
fredrick.trowbridge@hotmail.com
clayton.candelaria@googlemail.com

sh.xml を変更する。

$ cd bin
$ vi sh.xml
         <Column>
            <ColumnName>CUST_EMAIL</ColumnName>
            <DataType>VARCHAR2</DataType>
            <PrimaryKey>false</PrimaryKey>
            <NullsAllowed>true</NullsAllowed>
            <Size>30</Size> ★これを30から50に変更すると回避できる

ablog

不器用で落着きのない技術者のメモ