数说数据库选型

作者: 饿虎嗷呜 | 来源:发表于2021-06-22 14:43 被阅读0次

数说数据库选型
django 数据库迁移命令详解
MongoDB开发之监控和诊断
框架学习系列二（数据库选型）
不同场景下，如何选择数据库？（本文以爱奇艺为例）
JMeter从入门到精通（2）
influxDB 常用操作
数据库设计
数据库选型
MongoDB基本操作

数说数据库选型

最近在评估低资源情况下的数据存储方案的选型。选择了常见的五种数据库进行评估，其中包括MongoDB，Click House，MySQL，PostgreSQL以及Elasticsearch。虽然测试是在低资源使用情况下进行的，但是测试结果对一般性的使用也有一定的参考价值。

部署

本次评估所使用的数据库都是使用Docker进行部署，映射宿主机文件目录做数据存储。宿主机采用Ubuntu18.04操作系统，拥有1个socket4核以及16GB内存。

宿主机CPU规格：

vendor_id       : GenuineIntel
cpu family      : 6
model           : 85
model name      : Intel(R) Xeon(R) Silver 4116 CPU @ 2.10GHz
stepping        : 4
microcode       : 0x2000065
cpu MHz         : 2095.078
cache size      : 16896 KB
physical id     : 6
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 6
initial apicid  : 6
fpu             : yes
fpu_exception   : yes
cpuid level     : 22

Docker 部署数据库，其中除了ES之外，其他数据库都是分配了1核500MB内存。ES在只有500MB内存情况下基本上无法正常运行，后续测试分配了4G内存，因为这一点，实际上已经将ES排除出了选项，在此进行测试取其结果作为对比。

MongoDB

docker run --rm --name mongodb -p 27017:27017 -v /mnt/mongo/data:/data/db -m 500m --cpus=1 -d mongo:latest

Click House

docker run --rm --name ck-test -m 500m --cpus=1 --ulimit nofile=262144:262144 -p 8123:8123 -v /mnt/ck:/var/lib/clickhouse --privileged=true --user=root -d yandex/clickhouse-server

MySQL

docker run --rm --name mysql-test -m 500m --cpus=1  -v /mnt/mysql:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=admin123 -d mysql:5.7

PostgreSQL

 docker run --rm --name postgre-test -m 500m --cpus=1 -v /mnt/posgre:/var/lib/postgresql/data  -p 5432:5432 -e POSTGRES_PASSWORD=admin123 -d postgres

Elasticsearch

mkdir /mnt/es
chmod g+rwx /mnt/es
chgrp 0 /mnt/es

docker run --rm --name es-test -v /mnt/es:/usr/share/elasticsearch/data -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" -m 4g --cpus=1 -d elasticsearch

准备数据

本次评估使用python Faker包预先产生测试数据进行测试。数据格式类似如下

  {
    _id: ObjectId("60b89eacc28d48d8e6dbdc36"),
    event_time: '2021-06-02T11:40:26',
    src_addr: '192.168.75.190',
    src_port: 58612,
    dst_addr: '161.125.178.34',
    dst_port: 786,
    url: 'http://www.orozco.net/',
    mime_type: 'text/css',
    md5: '6aa1af2ba586a0dfae3f880e155f9fdb',
    file_name: 'apply.jpeg',
    server_token: 'Varnish',
    user_agent: 'Mozilla/5.0 (compatible; MSIE 8.0; Windows 98; Trident/4.1)'
  }

产生了1组20,000条数据，1组200,000条数据以及3组2,000,000条数据进行测试。其中，2,000,000条数据的测试组，每组原始数据大小约为800MB。

测试方法

本次评估中所有测试均采用Python脚本编写，其中:

Mongodb使用pymongo库，MySQL与PostgreSQL采用sqlalchemy，Click House的sqlalchemy库长时间未更新以及没有足够文档，本次测试使用infi.clickhouse_orm（https://github.com/Infinidat/infi.clickhouse_orm），ElasticSearch使用python的ElasticSearch库进行测试。

测试场景

本次评估测试了如下场景：

单条插入2万条记录的性能
单索引情况插入2百万条记录的性能
6索引情况下插入2百万条记录的性能
20万条记录情况下聚合性能
200万条记录情况下聚合性能
200万条记录情况下查询性能

所有查询与聚合都是在有索引的情况下进行的。

测试数据

单条插入2万条记录

image-20210622110201793.png

从测试数据来看，单条插入情况下，MongoDB的表现是最好的，能达到2000条每秒，然后是PostgreSQL。CK和MySQL处于一个水平线上，都是100出头，最差是ES。

在此，先不做结论，让我们继续看后续的测试数据。

单索引插入2百万条记录

image-20210622110710484.png

与第一次测试不同的是，在本次插入200万条的测试过程中，都采用了批量插入的方法进行，每次插入1000条。可以看到插入的性能有了极大提升。可以看到，单索引情况下，MongoDB的插入性能非常高，这一点符合在很多数据仓库中，MongoDB被用来存放原始数据这一定位。另外在测试中，我并没有为ES特意定制schema，因此，ES中所有字段都是有索引的。

本次测试采用插入3次的方式，每次采用不同的数据集，主要考察现存数据对插入性能的影响以及插入性能本身的数据。

这对我们的启示在于，大部分情况下，我们处理数据其实没有单条插入的这种很强烈的需求，如果可以尽量采用批插入的方式，对于性能有比较大的提升。

![image-20210622121320024.png](https://img.haomeiwen.com/i18479088/45775c5a5c5c3b91.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

再看一下插入第一个200w条情况下产生的磁盘IO的情况，可以看到两个传统的关系型数据库产生了大量的写IO，800MB数据写入时IO放大了几倍。而MongoDB，CK和ES基本上IO就比原数据大了一点点。同时，我观察到，在刚写完时，CK的磁盘占用大概是1.2GB，过了一段时间后，磁盘占用变成了218MB，占用磁盘容量是最低的。

六索引插入2百万条记录

image-20210622121320024.png

这个场景测试的是当数据中存在多个索引时的插入性能，同上一节一样，采用批量插入的方式，每次插入1000条。可以看到CK的性能仍然与之前类似，而MongoDB插入性能已经降了下来。

这里发现一个情况，在多索引插入的场景下，用docker --cpus分配给MongoDB容器的核数小于宿主机核数时，插入性能会大大降低。比如说，在宿主机VM的核数为4的情况下，为MongoDB分配2核进行插入测试，此时性能大概是200左右，如果将宿主机换成2核其他条件不变，此时插入性能大概是15000左右。从经验上判断，MongoDB应该是在启动时适配了操作系统核数，从而导致了一些多线程的竞争。由于时间有限，暂时没有去深挖原因，暂时给MongoDB提了一个case ：https://jira.mongodb.org/browse/SERVER-57855，也因为这个原因，如果没有很好的解决方案，MongoDB在这个场景中被排除。

20万条记录进行聚合

我准备了一个相对比较复杂的场景进行聚合的测试，代码大致如下：

        qs = CkEvent.objects_in(self.db)
        dst_ports = qs.only("dst_port").distinct()
        for port in dst_ports:
            res = qs.filter(CkEvent.dst_port == port.dst_port) \
                      .aggregate(CkEvent.src_port, cc=F.count()) \
                      .order_by("-cc")[:5]

        src_addrs = qs.only("src_addr").distinct()
        for addr in src_addrs:
            res = qs.filter(CkEvent.src_addr == addr.src_addr) \
                      .aggregate(CkEvent.dst_addr, cc=F.count()) \
                      .order_by("-cc")[:5]

在20万条记录的数据集上的测试结果为：

image-20210622124756362.png

从数据上来看，CK的表现非常突出。

接下来我们看一下在200万条数据的测试集上的表现。

200万条记录进行聚合

image-20210622140503195.png

在这次测试过程中，由于MongoDB的排序不能超过32MB因此聚合失败了。其余几个数据库的表现如上表所示。

此外，CK和ES对聚合有缓存，在第一次聚合分别使用了1.3秒和14秒的情况下，第二次再进行聚合其结果分别是0.18秒和3.2秒。可以看到，就聚合分析这一项上，CK的表现远远超出其他候选者。

200万条记录查询性能测试（6索引）

image-20210622141811471.png

最后看一下查询性能，可以看到，在存在索引的情况下，MongoDB，CK和ES的查询性能远高于传统关系型数据库。

其他未测试场景

本次测试未做并发查询与写入测试，在单独设备上不存在大量并发场景，此外，除了ES之外，所有数据库都配置了500MB内存，从测试结果来看，基本上都是差不多使用了300-400MB内存，差别不是很大，后续有需要再从其他角度进行评估。

结论

从上述测试结果来看，除了单条插入之外，CK在其他方面表现都具有压倒性优势。

	单条插入20万条(/s)	单索引插入200万条(/s)	六索引插入200万条(/s)	20万条聚合(s)	200万条聚合(s)	200万条查询(s)	200万条所占磁盘(MB)
MongoDB	2000	22000	16933	3.6	NA	0.00011	842
Click house	124	16000	17543	0.18	1.3	0.00028	218
MySQL	110	2152	1255	3.2	55	0.22514	775
PostgreSQL	500	5571	5025	2.1	22	0.15	1536
Elastic Search	88	6736	6474	2.9	14	0.0087	825