mariadb mysql 5.7_浅谈MYSQL5.7以及MariaDB10.3小差异

导读

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

二者基础架构是一样的，默认变量参数数据也大同小异

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%性能