单用户对比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 ms
1> 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 ms
1> 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，才是高性能的根本。
