1.主库性能预警,CPU LOADING 过高
a. 读写分离:把读操作(查询类,不是写入/修改后即查询数据的)放到DB2节点中进行;
2.日志文件、数据文件同放在一个磁盘中 /data ,没有分离 。暂时不处理,以后项目可考虑分开。
3.开启了 doublewrite ,在SSD,XFS 磁盘存储数据,可以关闭
mysql> show variables like '%doublewrite%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 39223899
Current database: *** NONE ***
+----------------------------------+----------------+
| Variable_name | Value |
+----------------------------------+----------------+
| innodb_doublewrite | ON |
| innodb_parallel_doublewrite_path | xb_doublewrite |
+----------------------------------+----------------+
2 rows in set (0.01 sec)
mysql>
4.参数 table_open_cache 调整为 4096
#配置文件中已设置成 4096 ,但数据库没设置 。此设置可以动态设置,不用重启MYSQL 实例。
命令: set global table_open_cache=4096;
[root@db-1 ~]# cat /etc/my.cnf|grep table_open_cache
table_open_cache = 4096
mysql> show variables like '%table_open_cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 447 |
| table_open_cache_instances | 16 |
+----------------------------+-------+
2 rows in set (0.00 sec)
mysql> show global status like 'open%tables%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Open_tables | 432 |
| Opened_tables | 39578950 |
+---------------+----------+
Opened_tables数值非常大,说明cache太小,导致要频繁地open table
5.query_cache_size 查询缓存参数
5.1配置文件:
query_cache_size = 0
5.2 MYSQL
mysql> SHOW STATUS LIKE 'Qcache%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 41556755
Current database: *** NONE ***
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)
mysql>
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
可配置为 query_cache_size = 128M
6.隔离级别: 现在配置为 可重复读
transaction_isolation = REPEATABLE-READ
修改语句: set transaction isolation level read committed; //设置读提交级别 可动态修改。
7.从节点延迟问题:
现有参数:
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'slave_parallel_type';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.01 sec)
为了减少延迟现象
slave-parallel-type=LOGICAL_CLOCK #基于组提交的并行复制方式 默认值为 DATABASE
slave-parallel-workers=4 #并行数量
8.两个从节点可以把一个设置成延迟1天(或12小时)用于做数据备份使用。
登陆到Slave数据库服务器 ,设置延迟12小时(12*3600)
mysql>stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 43200;
mysql>start slave;
mysql>show slave status\G
查看SQL_Delay的值为600,表示设置成功。
一次MYSQL 服务器性能优化之旅
最新推荐文章于 2024-05-30 14:37:38 发布