浅谈MYSQL5.7以及MariaDB10.3小差异

来源:网络2021-03-30 11:59:23

二者基础架构是一样的,默认变量参数数据也大同小异
mysql> SHOW VARIABLES LIKE 'max_con%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.01 sec)
因为初始连接值太低,不适合高并发能力,所以提高到2000来做测试,这时候就有差异了
差异一,修改max_connetctions方式
Mysql
必须修改linux系统本身的ulimit参数
/ 系统默认参数
[root@Centos7 ~]# ulimit -a | grep open
open files                      (-n) 1024
 
/修改limit参数
vim /etc/security/limits.conf
*  soft nofile 65535
*  hard nofile 65535
/ 修改MYsql配置文件
vim /etc/my.cnf
[mysqld]
max_connections=2000
 
systemctl restart mysqld
 
/ 查看变量,修改成功
mysql> SHOW VARIABLES LIKE 'max_con%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 2000  |
+--------------------+-------+
2 rows in set (0.03 sec)
MariaDB
直接修改参数重启即可
/ 修改MYsql配置文件
vim /etc/my.cnf
[mysqld]
max_connections=2000
 
systemctl restart mysqld
 
/ 查看变量,修改成功
mysql> SHOW VARIABLES LIKE 'max_con%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 2000  |
+--------------------+-------+
2 rows in set (0.03 sec)
 
差异二,并发能力强度对比
测试环境
 
mysqlsap工具
同时并发1000个连接
分别并发1000个连接
同时并发1000个连接
 
/ mysql的并发
[root@Centos7 ~]# mysqlslap -a -c 1000
Benchmark
Average number of seconds to run all queries: 40.796 seconds
Minimum number of seconds to run all queries: 40.796 seconds
Maximum number of seconds to run all queries: 40.796 seconds
Number of clients running queries: 1000
Average number of queries per client: 0
 
/ MariaDB的并发
[root@Centos7 ~]# mysqlslap -a -c 1000
Benchmark
Average number of seconds to run all queries: 19.427 seconds
Minimum number of seconds to run all queries: 19.427 seconds
Maximum number of seconds to run all queries: 19.427 seconds
Number of clients running queries: 1000
Average number of queries per client: 0
分别并发1000个连接
 
/ Mysql先并发1000个连接
[root@Centos7 ~]# mysqlslap -a -c 1000
Benchmark
Average number of seconds to run all queries: 28.096 seconds
Minimum number of seconds to run all queries: 28.096 seconds
Maximum number of seconds to run all queries: 28.096 seconds
Number of clients running queries: 1000
Average number of queries per client: 0
 
/ MariaDB 后续并发1000个连接
 
[root@Centos7 ~]# mysqlslap -a -c 1000
Benchmark
Average number of seconds to run all queries: 14.042 seconds
Minimum number of seconds to run all queries: 14.042 seconds
Maximum number of seconds to run all queries: 14.042 seconds
Number of clients running queries: 1000
Average number of queries per client: 0
 
总结:由此比对得出同时并发时,mysql跟MariaDB差距太大,可以达到2倍差距,分开并发MariaDB比mysql提高60%性能
 
标签:MYSQL57MariaDB103差异数据库