【MySQL】一个参数让MySQL 8.0数据库性能提升7倍

[root@localhost ~]# lscpu

CPU(s): 96

Model name: Inter(R) Xeon(R) Platinum 8255C CPU @ 2.5GHz

[root@localhost ~]# mysql -hlocalhost -uroot -p

mysql> select version();

+-------------+

| version()  |

+-------------+

|MySQL 8.0.23 |

+-------------+

[root@localhost ~]# sysbench /usr/share/sysbench/oltp_point_select.lua --mysql-user=mysql --mysql-password=1111aaA_ --mysql-socker=/tmp/mysql.sock --time=180000 --mysql-db=mysqlslap --tables=8 --tables_size=10000000 --report-interval=1 --threads=64 run

QPS:21W

通过perf命令查看当前调用最多函数有哪些：

[root@localhost ~]# perf top -G -p `pidof mysqld`

通过查看当前引发自选锁等待调用最多的函数是trans_begin和plugin_lock。

[root@localhost ~]# mysql -hlocalhost -uroot -p

mysql> show variables like 'replication_optimize_for_static_plugin_config';

[root@localhost ~]# sysbench /usr/share/sysbench/oltp_point_select.lua --mysql-user=mysql --mysql-password=1111aaA_ --mysql-socker=/tmp/mysql.sock --time=180000 --mysql-db=mysqlslap --tables=8 --tables_size=10000000 --report-interval=1 --threads=64 run

QPS:21W

[root@localhost ~]# mysql -hlocalhost -uroot -p

mysql> set global replication_optimize_for_static_plugin_config=on;

[root@localhost ~]# sysbench /usr/share/sysbench/oltp_point_select.lua --mysql-user=mysql --mysql-password=1111aaA_ --mysql-socker=/tmp/mysql.sock --time=180000 --mysql-db=mysqlslap --tables=8 --tables_size=10000000 --report-interval=1 --threads=64 run

QPS：140W

[root@localhost ~]# top

 

通过调用Memcache插件使MySQL 8.0性能达到220万QPS

[root@localhost ~]# mysql -hlocalhost -uroot -p

mysql> install plugin daemon_memcached soname 'libmemcached.so';

mysql> use innodb_memcached;

mysql> select * from containers\G;

mysql> use mysqlslap

mysql> select * from sbtest1 where id = 1;

[root@localhost ~]# netstat -tnaulp

[root@localhost ~]# telnet 127.0.0.1 11211

get 1

[root@localhost ~]# LD_PRELOAD=/usr/lib64/libjemalloc.so.1 ./my_test 64 1 my_test 1.0

QPS：220W

[root@localhost ~]# perf top -G -p `pidof mysqld`

 

MySQL 5.7.33 QPS:120W

此文件属于性能调优视频笔记，原视频来源于姜承尧老师的抖音公从号。