单用户对比PG 9.5.4和SYBASE 15.7对超大表的操作性能
来源：互联网 发布：修改淘宝代码 编辑：程序博客网 时间：2024/04/28 20:09
背景
从某真实环境导出一张1.5亿记录的超大表，单用户分别在ABase 3.5.3(PostgreSQL9.5.4)和SYBASE15.7 下进行增删改查操作，对比一下两个数据库的性能
基本环境
- 操作系统，该环境为进行数据加压搭建的测试机器，性能比较好，硬件配置如下：
[root@localhost ~]# lscpuArchitecture: x86_64CPU op-mode(s): 32-bit, 64-bitByte Order: Little EndianCPU(s): 64On-line CPU(s) list: 0-63Thread(s) per core: 2Core(s) per socket: 16Socket(s): 2NUMA node(s): 2Vendor ID: GenuineIntelCPU family: 6Model: 63Stepping: 2CPU MHz: 2298.768BogoMIPS: 4597.14Virtualization: VT-xL1d cache: 32KL1i cache: 32KL2 cache: 256KL3 cache: 40960KNUMA node0 CPU(s): 0-15,32-47NUMA node1 CPU(s): 16-31,48-63[root@localhost ~]# free -g total used free shared buffers cachedMem: 315 173 141 0 0 168-/+ buffers/cache: 5 310Swap: 3 0 3[root@localhost ~]# cat /proc/scsi/scsiAttached devices:Host: scsi0 Channel: 02 Id: 00 Lun: 00 Vendor: LSI Model: RAID 510i Rev: 2.13 Type: Direct-Access ANSI SCSI revision: 05[root@localhost ~]# uptime 18:08:56 up 108 days, 23:42, 4 users, load average: 0.00, 0.01, 0.05- ABase数据库信息
nptest=# select version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit(1 row)nptest=# show shared_buffers; shared_buffers ---------------- 30GB(1 row)- SYBASE数据库信息
1> select @@version2> go --------------------------------------------------------------------------------------------------------- Adaptive Server Enterprise/15.7/EBF 21341 SMP SP101 /P/x86_64/Enterprise Linux/ase157sp101/3439/64-bit/FBO/Thu Jun 6 16:08:18 2013 (1 row affected)1> sp_helpcache2> go Cache Name Config Size Run Size Overhead ------------------------------------------------------------------------ ---------------------------------------------------- ---------------------------------------- ---------------------------------------- default data cache 36864.0 Mb 36864.0 Mb 586.55 Mb tempdb01_cache 5120.00 Mb 5120.00 Mb 80.55 Mb tempdbsym_cache 5120.00 Mb 5120.00 Mb 80.55 Mb (3 rows affected)Memory Available For Memory ConfiguredNamed Caches To Named Caches-------------------- ----------------47104.0 Mb 47104.0 Mb------------------ Cache Binding Information: ------------------ Cache Name Entity Name Type Index Name Status---------- ----------- ---- ---------- ------tempdbsym_cache TEMPDB_SYM database V(return status = 0)- 表信息
nptest=# \d+ t_cmpt_rz_sjrz_zd Table "yyfz.t_cmpt_rz_sjrz_zd" Column | Type | Modifiers | Storage | Stats target | Description ------------+------------------------+-----------+----------+--------------+------------- c_id | character(32) | not null | extended | | c_id_rz | character varying(300) | | extended | | c_zdm | character varying(600) | | extended | | c_zdm_fy | character varying(600) | | extended | | c_zdz_zq | character varying(600) | | extended | | c_zdz_zh | character varying(600) | | extended | | c_zdz_zqfy | character varying(600) | | extended | | c_zdz_zhfy | character varying(600) | | extended | | Indexes: "i_cmpt_rz_sjrz_zd_rz" btree (c_id_rz)nptest=# \dt+ t_cmpt_rz_sjrz_zd List of relations Schema | Name | Type | Owner | Size | Description --------+-------------------+-------+--------+-------+------------- yyfz | t_cmpt_rz_sjrz_zd | table | nptest | 21 GB | (1 row)增删改查测试
- count(*)
nptest=# select count(*) from t_cmpt_rz_sjrz_zd; count ----------- 151438388(1 row)Time: 17468.555 ms1> select count(*) from T_CMPT_RZ_SJRZ_ZD2> goParse and Compile Time 0.Adaptive Server cpu time: 0 ms. ----------- 151438388 Execution Time 212.Adaptive Server cpu time: 21220 ms. Adaptive Server elapsed time: 21220 ms.- 全表扫描测试
nptest=# select * from t_cmpt_rz_sjrz_zd where c_zdz_zq = 'aaa'; c_id | c_id_rz | c_zdm | c_zdm_fy | c_zdz_zq | c_zdz_zh | c_zdz_zqfy | c_zdz_zhfy ------+---------+-------+----------+----------+----------+------------+------------(0 rows)Time: 22905.087 ms1> select * from T_CMPT_RZ_SJRZ_ZD where C_ZDZ_ZQ = 'aaa'2> goParse and Compile Time 0.Adaptive Server cpu time: 0 ms.Execution Time 82.Adaptive Server cpu time: 8229 ms. Adaptive Server elapsed time: 8230 ms. C_ID C_ID_RZ C_ZDM C_ZDM_FY C_ZDZ_ZQ C_ZDZ_ZH C_ZDZ_ZQFY C_ZDZ_ZHFY --------------------------------------------------------------------------------------------(0 rows affected)- 索引查询测试
nptest=# select * from t_cmpt_rz_sjrz_zd where c_id_rz = 'aaa'nptest-# ; c_id | c_id_rz | c_zdm | c_zdm_fy | c_zdz_zq | c_zdz_zh | c_zdz_zqfy | c_zdz_zhfy ------+---------+-------+----------+----------+----------+------------+------------(0 rows)Time: 0.508 ms1> select * from T_CMPT_RZ_SJRZ_ZD where C_ID_RZ = 'aaa' 2> goParse and Compile Time 0.Adaptive Server cpu time: 0 ms.Execution Time 0.Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 21 ms. C_ID C_ID_RZ C_ZDM C_ZDM_FY C_ZDZ_ZQ C_ZDZ_ZH C_ZDZ_ZQFY C_ZDZ_ZHFY -----------------------------------------------------------------------------------------------(0 rows affected)- 复制100万数据测试
nptest=# create table t_cmpt_rz_fz as (select * from t_cmpt_rz_sjrz_zd limit 1000000);SELECT 1000000Time: 1818.625 ms1> select top 1000000 * into T_CMPT_RZ_FZ from T_CMPT_RZ_SJRZ_ZD 2> goParse and Compile Time 0.Adaptive Server cpu time: 0 ms.Execution Time 18.Adaptive Server cpu time: 1877 ms. Adaptive Server elapsed time: 4029 ms.(1000000 rows affected)- 插入100万数据测试
nptest=# insert into t_cmpt_rz_sjrz_zd select * from t_cmpt_rz_fz;INSERT 0 1000000Time: 19427.569 ms1> insert into T_CMPT_RZ_SJRZ_ZD select * from T_CMPT_RZ_FZ2> goParse and Compile Time 0.Adaptive Server cpu time: 0 ms.Execution Time 74.Adaptive Server cpu time: 7501 ms. Adaptive Server elapsed time: 17250 ms.(1000000 rows affected)- 删除200万数据
nptest=# delete from T_CMPT_RZ_SJRZ_ZD where C_ID IN (SELECT C_ID from T_CMPT_RZ_FZ);DELETE 2000000Time: 92120.924 ms1> delete from T_CMPT_RZ_SJRZ_ZD where C_ID IN (SELECT C_ID from T_CMPT_RZ_FZ)2> go;Parse and Compile Time 0.Adaptive Server cpu time: 0 ms.Execution Time 7791.Adaptive Server cpu time: 778976 ms. Adaptive Server elapsed time: 779193 ms.(2000000 rows affected)结论
在配置基本相同的情况下
ABase对超大表的操作性能更稳定，整体速度情况更快一点。
Sybase缓存策略应该更好一点，在反复多次查询下，数据全部加载缓存后会有查询速度较快的情况。
基于索引查询超大表都很快，良好的索引设计及优良SQL，才是高性能的根本。
阅读全文
0 0
- 单用户对比PG 9.5.4和SYBASE 15.7对超大表的操作性能
- ArrayList和LinkedList的操作性能对比
- ArrayList和LinkedList的操作性能对比
- ArrayList和LinkedList的操作性能对比
- PG（HGDB）中查看用户对表的操作权限
- DecimalFormat和BigDecimal对格式化的性能对比
- Sybase数据库超大表数据矫正更新
- 超大数字的操作
- sybase单用户启动
- PG的ALTER常用操作
- IList和ArrayList操作性能对比
- DataTable 的操作 性能的对比
- PG和Oracle的差异
- Oracle中对一个表中的数据和输入的参数对比，做出对应的操作(存储过程)
- PG普通类型数据和Jsonb数据对比试验
- PG 对比 Oracle
- 关于使用FileMapping和直接操作File+缓存的IO性能对比
- PostgreSQL的类型扩展相关的几个系统表（包含pg的操作符类和操作符族等）
- Mybatis,Specified class is an interface
- 自定义控件之视差效果
- 第七次多校联合hdu6129Just do it（题名好棒）
- 数据结构实验之排序六：希尔排序
- Ambari-2.2.2.0文档翻译--准备工作（4）
- 单用户对比PG 9.5.4和SYBASE 15.7对超大表的操作性能
- 自定义控件之仿电话簿汉字排序侧边拼音
- Xlistview使用方法
- iOS-78-解决AFNetWorking NSParameterAssert(URLString)为空问题
- js实现分页
- poj 1651 Multiplication Puzzle
- Chunkize warning while installing gensim 疑难杂症
- Show Me the Money
- 国王游戏（贪心）
