单用户对比PG 9.5.4和SYBASE 15.7对超大表的操作性能

背景从某真实环境导出一张1.5亿记录的超大表,单用户分别在ABase 3.5.3(PostgreSQL9.5.4)和SYBASE15.7 下进行增删改查操作,对比一下两个数据库的性能基本环境
  • 操作系统,该环境为进行数据加压搭建的测试机器,性能比较好,硬件配置如下:
  • [root@localhost ~]# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 64 On-line CPU(s) list: 0-63 Thread(s) per core: 2 Core(s) per socket: 16 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 63 Stepping: 2 CPU MHz: 2298.768 BogoMIPS: 4597.14 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 40960K NUMA node0 CPU(s): 0-15,32-47 NUMA node1 CPU(s): 16-31,48-63 [root@localhost ~]# free -g total used free shared buffers cached Mem: 315 173 141 0 0 168 -/+ buffers/cache: 5 310 Swap: 3 0 3 [root@localhost ~]# cat /proc/scsi/scsi Attached 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 @@version 2> 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_helpcache 2> 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 Configured Named 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_ZD 2> go Parse 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 ms 1> select * from T_CMPT_RZ_SJRZ_ZD where C_ZDZ_ZQ = 'aaa' 2> go Parse 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> go Parse 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 1000000 Time: 1818.625 ms 1> select top 1000000 * into T_CMPT_RZ_FZ from T_CMPT_RZ_SJRZ_ZD 2> go Parse 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 1000000 Time: 19427.569 ms 1> insert into T_CMPT_RZ_SJRZ_ZD select * from T_CMPT_RZ_FZ 2> go Parse 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 2000000 Time: 92120.924 ms 1> 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,才是高性能的根本。

  • 相关内容推荐