【Postgresql】PostgreSQL和MySQL：每秒数百万的查询
这篇博客比较了PostgreSQL和MySQL每秒处理数百万次查询的方式。
Anastasia：开源数据库可以应对每秒数百万次查询吗？许多开源倡导者会回答“是”。然而，断言并不足以提供充分的证据。这就是为什么在这篇博文中，我们分享了Alexander Korotkov（开发，Postgres Professional首席执行官）和Sveta Smirnova（首席技术服务工程师，Percona）的基准测试结果。 PostgreSQL 9.6和MySQL 5.7性能的比较研究对于具有多个数据库的环境尤其有用。
这项研究背后的想法是提供两个流行的RDBMS的诚实比较。 Sveta和Alexander希望使用相同的工具在相同的挑战性工作负载下使用相同的配置参数（如果可能）测试PostgreSQL和MySQL的最新版本。但是，由于PostgreSQL和MySQL生态系统都是独立发展的，每个数据库都使用标准的测试工具（pgbench和SysBench），这不是一件容易的事。
这项任务落到了具有多年实践经验的数据库专家手中。 Sveta曾在Oracle MySQL支持小组的Bugs Verification Group担任高级首席技术支持工程师超过八年，自2015年起担任Percona的首席技术服务工程师。 Alexander Korotkov是PostgreSQL的主要贡献者，也是PostgreSQL众多功能的开发者 - 包括CREATE ACCESS METHOD命令，通用WAL接口，无锁Pin / UnpinBuffer，基于索引的正则表达式搜索等等。所以我们有一个相当不错的演员阵容！
Sveta：Dimitri Kravtchuk定期发布MySQL的详细基准测试，因此我的主要任务不是确认MySQL每秒可以进行数百万次查询。正如我们的图表所示，我们已经通过了那个标记。作为支持工程师，我经常与在他们的商店中拥有异构数据库环境的客户合作，并希望了解将作业从一个数据库迁移到另一个数据库的影响。相反，我发现有机会与Postgres专业公司合作，并确定两个数据库的优点和缺点是一个绝佳的机会。
我们希望使用相同的工具和测试在相同的硬件上测试PostgreSQL和MySQL。我们期望测试基本功能，然后进行更详细的比较。这样我们就可以比较不同的真实用例场景和流行的选项。
剧透：我们距离最终结果还很远。这是博客系列的开始。
大机器上的OpenSource数据库，系列1：“那是关闭......”
Postgres Professional与 Freematiq一起提供了两台现代化，功能强大的测试机器。
硬件配置：
Processors: physical = 4, cores = 72, virtual = 144, hyperthreading = yes
Memory: 3.0T
Disk speed: about 3K IOPS
OS: CentOS 7.1.1503
File system: XFS
我还使用了较小的Percona机器。
硬件配置：
Processors: physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Memory: 251.9G
Disk speed: about 33K IOPS
OS: Ubuntu 14.04.5 LTS
File system: EXT4
请注意，具有较少CPU核心和较快磁盘的计算机比具有较大核心数的计算机更常见于MySQL安装。
我们需要达成共识的第一件事是使用哪种工具。如果工作负载尽可能接近，那么公平的比较才有意义。
用于性能测试的标准PostgreSQL工具是pgbench，而对于MySQL，它是SysBench。 SysBench支持Lua编程语言中的多个数据库驱动程序和脚本化测试，因此我们决定将这个工具用于这两个数据库。
最初的计划是将pgbench测试转换为SysBench Lua语法，然后在两个数据库上运行标准测试。在初步结果之后，我们修改了测试以更好地检查特定的MySQL和PostgreSQL功能。
我将pgbench测试转换为SysBench语法，并将测试放入开放式数据库工作台GitHub存储库中。
然后我们都遇到了困难。
正如我已经写过的那样，我也在Percona机器上运行测试。对于此转换测试，结果几乎相同：
Percona machine:
OLTP test statistics:
transactions: 1000000 (28727.81 per sec.)
read/write requests: 5000000 (143639.05 per sec.)
other operations: 2000000 (57455.62 per sec.)）
Freematiq机器：
OLTP test statistics:
transactions: 1000000 (29784.74 per sec.)
read/write requests: 5000000 (148923.71 per sec.)
other operations: 2000000 (59569.49 per sec.)
我开始调查了。 Percona机器比Freematiq更好的唯一地方是磁盘速度。所以我开始运行pgbench只读测试，这与SysBench的点选择测试相同，内存中有完整的数据集。但这次SysBench使用了50％的可用CPU资源：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4585 smirnova 20 0 0,157t 0,041t 9596 S 7226 1,4 12:27.16 mysqld
8745 smirnova 20 0 1266212 629148 1824 S 7126 0,0 9:22.78 sysbench
反过来，Alexander遇到了SysBench的问题，当使用预处理语句时，它无法在PostgreSQL上创建高负载：
93087 korotkov 20 0 9289440 3,718g 2964 S 242,6 0,1 0:32.82 sysbench
93161 korotkov 20 0 32,904g 81612 80208 S 4,0 0,0 0:00.47 postgres
93116 korotkov 20 0 32,904g 80828 79424 S 3,6 0,0 0:00.46 postgres
93118 korotkov 20 0 32,904g 80424 79020 S 3,6 0,0 0:00.47 postgres
93121 korotkov 20 0 32,904g 80720 79312 S 3,6 0,0 0:00.47 postgres
93128 korotkov 20 0 32,904g 77936 76536 S 3,6 0,0 0:00.46 postgres
93130 korotkov 20 0 32,904g 81604 80204 S 3,6 0,0 0:00.47 postgres
93146 korotkov 20 0 32,904g 81112 79704 S 3,6 0,0 0:00.46 postgres
我们联系了SysBench的作者Alexey Kopytov，他修复了MySQL问题。解决方案是：
- Use SysBench with the options --percentile=0 --max-requests=0 (reasonable CPU usage)
- Use the concurrency_kit branch (better concurrency and Lua processing)
- Rewrite Lua scripts to support prepared statements (pull request: https://github.com/akopytov/sysbench/pull/94)
- Start both SysBench and mysqld with the jemalloc or tmalloc library pre-loaded
PostgreSQL的修复程序正在进行中。目前，Alexander将标准的SysBench测试转换为pgbench格式，我们坚持使用它。对于MySQL来说并不是什么新鲜事，但至少我们有一个比较基线。
我面临的下一个难题是默认的操作系统参数。长话短说，我将它们改为推荐的（如下所述）：
vm.swappiness=1
cpupower frequency-set --governor performance
kernel.sched_autogroup_enabled=0
kernel.sched_migration_cost_ns= 5000000
vm.dirty_background_bytes=67108864
vm.dirty_bytes=536870912
IO scheduler [deadline]
对于PostgreSQL性能，相同的参数也更好。亚历山大同样设置他的机器
解决了这些问题后，我们学习并实施了以下内容：
- 我们不能使用单一工具（暂时）
- 亚历山大为pgbench编写了一个测试，模仿标准的SysBench测试
- 我们仍然无法编写自定义测试，因为我们使用不同的工具
但我们可以将这些测试用作基线。在亚历山大完成工作后，我们坚持使用标准的SysBench测试。我将它们转换为使用准备好的语句，亚历山大将它们转换为pgbench格式。
我应该提一下，对于Read Only和Point Select测试，我无法获得与Dimitri相同的结果。它们很近但略慢。我们需要调查这是不同硬件的结果还是我缺乏性能测试能力。读写测试的结果是类似的。
另一个区别是PostgreSQL和MySQL测试。 MySQL用户通常有很多连接。如今，设置变量max_conenctions的值，并将并行连接的总数限制为数千并不罕见。虽然不推荐，但即使没有线程池插件，人们也会使用此选项。在现实生活中，大多数这些联系都在睡觉。但是，在网站活动增加的情况下，他们总是有机会被使用。
对于MySQL，我测试了多达1024个连接。我使用了2的幂和核心数的倍数：1,2,4,8,16,32,36,64,72,128,144,256,512和1024个线程。
对亚历山大来说，以较小的步骤进行测试更为重要。他从一个线程开始，增加了10个线程，直到达到250个并行线程。因此，您将看到PostgreSQL的更详细的图表，但在250个线程之后没有结果。
这是我们的比较结果。
Point SELECTs
- pgsql-9.6 is standard PostgreSQL
- pgsql-9.6 + pgxact-align is PostgreSQL with this patch (more details can be found in this blog post)
- MySQL-5.7 Dimitri is Oracle’s MySQL Server
- MySQL-5.7 Sveta is Percona Server 5.7.15
OLTP RO
OLTP RW
PostgreSQL中的同步提交是一个功能，类似于InnoDB中的innodb_flush_log_at_trx_commit = 1，异步提交类似于innodb_flush_log_at_trx_commit = 2。
您会看到结果非常相似：两个数据库都在快速发展，并且可以很好地使用现代硬件。
MySQL results which show 1024 threads for reference.
Point SELECT and OLTP RO
OLTP RW with innodb_flush_log_at_trx_commit set to 1 and 2
收到这些结果后，我们做了一些功能特定的测试，这些测试将在单独的博客文章中介绍。
更多信息
用于OLTP RO和Point SELECT测试的MySQL选项：
# general
table_open_cache = 8000
table_open_cache_instances=16
back_log=1500
query_cache_type=0
max_connections=4000# files
innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group=3
innodb_open_files=4000# Monitoring
innodb_monitor_enable = '%'
performance_schema=OFF #cpu-bound, matters for performance#Percona Server specific
userstat=0
thread-statistics=0# buffers
innodb_buffer_pool_size=128000M
innodb_buffer_pool_instances=128 #to avoid wait on InnoDB Buffer Pool mutex
innodb_log_buffer_size=64M# InnoDB-specific
innodb_checksums=1 #Default is CRC32 in 5.7, very fast
innodb_use_native_aio=1
innodb_doublewrite= 1 #https://www.percona.com/blog/2016/05/09/percona-server-5-7-parallel-dou…
innodb_stats_persistent = 1
innodb_support_xa=0 #(We are read-only, but this option is deprecated)
innodb_spin_wait_delay=6 #(Processor and OS-dependent)
innodb_thread_concurrency=0
join_buffer_size=32K
innodb_flush_log_at_trx_commit=2
sort_buffer_size=32K
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=4000
innodb_page_cleaners=4# perf special
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_purge_threads=4
innodb_max_purge_lag_delay=30000000
innodb_max_purge_lag=0
innodb_adaptive_hash_index=0 (depends on workload, always check)
MySQL Options for OLTP RW:
#Open files
table_open_cache = 8000
table_open_cache_instances = 16
query_cache_type = 0
join_buffer_size=32k
sort_buffer_size=32k
max_connections=16000
back_log=5000
innodb_open_files=4000#Monitoring
performance-schema=0#Percona Server specific
userstat=0
thread-statistics=0#InnoDB General
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_numa_interleave=1
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_doublewrite=1
innodb_support_xa=1
innodb_checksums=1#Concurrency
innodb_thread_concurrency=144
innodb_page_cleaners=8
innodb_purge_threads=4
innodb_spin_wait_delay=12 Good value for RO is 6, for RW and RC is 192
innodb_log_file_size=8G
innodb_log_files_in_group=16
innodb_buffer_pool_size=128G
innodb_buffer_pool_instances=128 #to avoid wait on InnoDB Buffer Pool mutex
innodb_io_capacity=18000
innodb_io_capacity_max=36000
innodb_flush_log_at_timeout=0
innodb_flush_log_at_trx_commit=2
innodb_flush_sync=1
innodb_adaptive_flushing=1
innodb_flush_neighbors = 0
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=4000
innodb_adaptive_hash_index=0
innodb_change_buffering=none #can be inserts, workload-specific
optimizer_switch="index_condition_pushdown=off" #workload-specific
MySQL SysBench parameters:
LD_PRELOAD=/data/sveta/5.7.14/lib/mysql/libjemalloc.so
/data/sveta/sbkk/bin/sysbench
[ --test=/data/sveta/sysbench/sysbench/tests/db/oltp_prepared.lua | --test=/data/sveta/sysbench/sysbench/tests/db/oltp_simple_prepared.lua ]
--db-driver=mysql --oltp-tables-count=8 --oltp-table-size=10000000
--mysql-table-engine=innodb --mysql-user=msandbox --mysql-password=msandbox
--mysql-socket=/tmp/mysql_sandbox5715.sock
--num-threads=$i --max-requests=0 --max-time=300
--percentile=0 [--oltp-read-only=on --oltp-skip-trx=on]
PostgreSQL pgbench parameters:
$ git clone https://github.com/postgrespro/pg_oltp_bench.git
$ cd pg_oltp_bench
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install
$ psql DB -f oltp_init.sql
$ psql DB -c "CREATE EXTENSION pg_oltp_bench;"
$ pgbench -c 100 -j 100 -M prepared -f oltp_ro.sql -T 300 -P 1 DB
$ pgbench -c 100 -j 100 -M prepared -f oltp_rw.sql -T 300 -P 1 DB
MySQL 5.7中的功能显着改善了性能：
- InnoDB: transaction list optimization
- InnoDB: Reduce lock_sys_t::mutex contention
- InnoDB: fix index->lock contention
- InnoDB: faster and parallel flushing
- MDL (Meta-Data Lock) scalability
- Remove THR_LOCK::mutex for InnoDB: Wl #6671
- Partitioned LOCK_grant
- Number of partitions is constant
- Thread ID used to assign partition
- Lock-free MDL lock acquisition for DML
Anastasia：这项研究的最初发现在Percona Live Amsterdam 2016上公布。更多的调查结果被添加到 Moscow HighLoad++ 2016的同一个演讲的第二版中。希望本次演讲的第三次演示将在Percona Live Open Source Database Conference 2017 in Santa Clara.上发布。年圣克拉拉数据库大会。
本文：http://pub.intelligentx.net/node/553
讨论：请加入知识星球或者小红圈【首席架构师圈】
- 191 次浏览