Postgres Conference China 2015中国用户大会
PostgreSQL存储安全和性能规划
朱贤文
成都文武信息技术有限公司
Postgres Conference China 2015中国用户大会
关于
–成都文武信息技术有限公司
• 成都，天府软件园
• 专注于PostgreSQL相关解决方案和服务
– Total Solution for PostgreSQL
WwIT
驱动数据成就未来
Postgres Conference China 2015中国用户大会
主题
–性能规划
• Iops需求
• 数据库设计一般规则
• 高效率的sql
–数据库存储安全
• 介质选择
• 文件系统选择
Postgres Conference China 2015中国用户大会
IOPS需求
• OS命令
• iostat –dnx
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
vda
dm-0
dm-1
dm-2
• sar –b
10:21:44 PM tps rtps wtps bread/s bwrtn/s
10:21:46 PM
10:21:48 PM
10:21:50 PM
10:21:52 PM
10:21:54 PM
Postgres Conference China 2015中国用户大会
数据库文件(OLTP)
文件类别 随机读 随机写 顺序读 顺序写
配置文件 是
数据文件 是 是
日志文件 是
归档文件 是
零时文件 是 是
文件类别 随机读 随机写 顺序读 顺序写
配置文件 －
数据文件 IOPS IOPS
日志文件 带宽
归档文件 带宽
零时文件 － 是
Postgres Conference China 2015中国用户大会
数据库物理设计
• 尽量使用本地磁盘
• 谨慎考虑使用ssd
事件 延迟 换算为1秒对应的时间消耗
1 CPU时钟周期 ns 1 s
访问1级缓存 ns 3 s
访问内存 120 ns 6 min
访问PCIe SSD 67 us days
访问磁盘 5~ 10 ms ~ months
Postgres Conference China 2015中国用户大会
Tablespace_4_iops
数据库物理设计
• 将不同io需求的文件隔离
• 将io尽量均匀分布到存储
Requires IOPS
Tablespace_4_BW
Requires BW
Vol_IOPS
File system
Vol_BW
File System
Postgres Conference China 2015中国用户大会
数据库物理设计
• 将索引和数据隔离
• 将索引和临时文件存放在尽量快的存储上
Index_tabspace
Index_aaa
tablespace
table_aaa
Vol_ssd
File system
Vol_sas
File System
tablespace
archive
and log
Vol_sata
File System
Postgres Conference China 2015中国用户大会
高效SQL
• 高效率的sql非常重要
-> Hash Join (cost=.. rows=1 width=1654)
Output: _nav_less_per_fee, _per_creation_unit, _of_units_os, _chn, _eng, ,
_name, , _unit, , _price
Hash Cond: ((()::text = ()::text) AND ((SubPlan 2) = _date))
Join Filter: ((SubPlan 3) = _date)
-> Hash Join (cost=.. rows=9461 width=1744)
Output: _chn, _eng, , _name, , _unit, , ,
_price, , , _date, _market_no, _no
Hash Cond: (((nvl(_no, ))::text = ()::text) AND ((nvl(_market_no,
))::text = ()::text))
-> Hash Left Join (cost=.. rows=18921676 width=78)
Output: _price, , , _date, _market_no, _no
• 优化前：运行5分钟无结果
• 优化后：< 1秒
Postgres Conference China 2015中国用户大会
优化前
SQL:
SELECT _chn_name AS ChnNameOfManager, _eng_name AS EngNameOfManager, AS ChnNameOfETF, _name AS EngNameOfETF, as StockCode,
to_char(to_date(:fileDate, 'yyyy-mm-dd'), 'ddMonYYYY') AS TradingDate, _sign AS Currency, round(nvl(_nav_less_per_fee, 0), 4) AS NAVperUnit,
round(nvl(_nav_less_per_fee * _unit, 0), 2) AS NAVperCreationUnit, round(nvl(_per_creation_unit, 0), 2) AS CashPerCreationUnit,
round(nvl(_of_units_os, 0), 2) AS TotalUnitsHKUnits, round(nvl(_of_units_os, 0), 2) AS TotalUnitsFundTotal, round(nvl(_nav_less_per_fee * _of_units_os, 0), 2) AS
AssetManagementHKUnits,
round(nvl(_nav_less_per_fee * _of_units_os, 0), 2) AS AssetManagementFundTotal,
CASE WHEN _nav_less_per_fee IS NULL OR _nav_less_per_fee = 0 THEN
0
ELSE
round(((nvl(_price, 0) - nvl(_nav_less_per_fee, 0)) / _nav_less_per_fee) * 100, 2)
END AS Premium
FROM hongkong_etf_info h,
(SELECT , _name, , , _sign, _unit, , , _chn as manager_chn_name, _eng as
maanger_eng_name
FROM amc_fund_info amc, currency_tbl c
WHERE =
) a,
cdms_to_etf_quotationinfo_vw q
WHERE =
AND _date =
(SELECT MAX(valuation_date)
FROM hongkong_etf_info
WHERE valuation_Date <= to_date(:fileDate, 'yyyy-mm-dd')
AND cmbno =
)
AND =
AND =
and _date =
(SELECT MAX(the_Date) FROM cdms_to_etf_quotationinfo_vw q1 WHERE = AND = AND _date <= _date
)
VIEW:
CREATE OR REPLACE VIEW _to_etf_quotationinfo_vw AS
SELECT _price, nvl(_market_no, ) AS marketno, nvl(_no, ) AS securno, _date, _price, , , , ,
FROM bdl_quotation_info t
LEFT JOIN bdl_security a ON ::text = _id::text AND ::text = _market_no::text;
Postgres Conference China 2015中国用户大会
优化后
with amc as
(SELECT , ,_id, _chn_name AS ChnNameOfManager, _eng_name AS EngNameOfManager, AS ChnNameOfETF,
_name AS EngNameOfETF, as StockCode, _sign AS Currency, _nav_less_per_fee, _unit, _per_creation_unit, _of_units_os, _date
FROM hongkong_etf_info h,
(SELECT , _name,, , _sign, _unit, , ,_chn as manager_chn_name,
_eng as maanger_eng_name
FROM amc_fund_info amc, currency_tbl c
WHERE =
) a,
bdl_security s
WHERE =
AND _date = (SELECT MAX(valuation_date) FROM hongkong_etf_info WHERE valuation_Date <= to_date('2015-10-22', 'yyyy-mm-dd’ AND cmbno = )
AND = _no AND = _market_no
),
qi as
(SELECT _price,, AS securno, _date,_price,,,,, FROM bdl_quotation_info t, amc
where = _id AND =
union
SELECT _price,, AS securno,_date,_price,,,,, FROM bdl_quotation_info t, amc
where = AND =
)
select ChnNameOfManager,EngNameOfManager,ChnNameOfETF,EngNameOfETF,StockCode,to_char(to_date('2015-10-22', 'yyyy-mm-dd'), 'ddMonYYYY') AS TradingDate,
Currency,round(nvl(net_nav_less_per_fee, 0), 4) AS NAVperUnit,round(nvl(net_nav_less_per_fee * min_unit, 0), 2) AS NAVperCreationUnit,
round(nvl(cash_per_creation_unit, 0), 2) AS CashPerCreationUnit, round(nvl(no_of_units_os, 0), 2) AS TotalUnitsHKUnits,
round(nvl(no_of_units_os, 0), 2) AS TotalUnitsFundTotal, round(nvl(net_nav_less_per_fee * no_of_units_os, 0), 2) AS AssetManagementHKUnits,
round(nvl(net_nav_less_per_fee * no_of_units_os, 0), 2) AS AssetManagementFundTotal,
CASE WHEN net_nav_less_per_fee IS NULL OR net_nav_less_per_fee = 0 THEN
0
ELSE
round(((nvl(_price, 0) - nvl(net_nav_less_per_fee, 0)) / net_nav_less_per_fee) * 100, 2)
END AS Premium
from amc, qi
where = AND = and _date =
(SELECT MAX(the_Date) FROM qi q1 WHERE = AND = AND _date <= _date
);
Postgres Conference China 2015中国用户大会
高效运行
Postgres Conference China 2015中国用户大会
存储安全
• 介质的选择
– SSD
• SLC，MLC，eMLC
• 价格，可靠性
– SAS,SATA
• 容量，转速 (10K rpms, 15K rpms)，尺寸
• IOPS
– RAID card(BBU required)
• LSI or Dell (megaraid,perc)
Postgres Conference China 2015中国用户大会
存储安全
• 介质的选择
–优先DAS(JBOD)，SAN次之
–不要考虑NAS
事件 延迟 换算为1秒对应的时间消耗
1 CPU时钟周期 ns 1 s
访问1级缓存 ns 3 s
访问内存 120 ns 6 min
访问PCIe SSD 67 us days
访问磁盘 5~ 10 ms ~ months
Postgres Conference China 2015中国用户大会
存储安全
• 文件系统(注意mount选项)
OS FS db_file log_file archive
Linux
ext4 yes yes
xfs yes yes
ZFS yes yes yes
Solaris ZFS yes yes yes
FreeBSD UFS2 yes yesZFS yes yes yes
Windows NTFS yes yes yes
Postgres Conference China 2015中国用户大会
驱动数据成就未来
Q & A
