MySQL은 마스터에서는 더 빠르게 선택되지만 슬레이브에서는 더 느리게 선택됩니다.

MySQL은 마스터에서는 더 빠르게 선택되지만 슬레이브에서는 더 느리게 선택됩니다.

우리는 MariaDB 데이터베이스를 실행하여 데이터가 매우 빠르게 축적되는 즉각적인 이벤트를 기록하고 있습니다.MaxScale 클러스터에 이벤트를 삽입하는 1000개 이상의 센서가 서버로 데이터를 전송합니다.클러스터에는 한 마스터가 이벤트를 삽입하고 두 슬레이브에 트랜잭션을 복제합니다.

이벤트는 시계열로 기록되므로 테이블에는 MySQL datetime 유형인 EventTime이라는 열이 있습니다.Sensor라는 컬럼도 있습니다.센서를 구분하는 ID로서, 바카르(20)의 종류를 갖는 것을 특징으로 하는 ID.데이터는 두 달 만에 약 4억 행으로 증가했고, 최종적으로는 약 20억 행으로 증가할 것입니다.

다음과 같은 선택 쿼리는 슬레이브 노드에서 마스터 노드보다 훨씬 느립니다.

SELECT * FROM `table0` 
WHERE 
    (EventTime >= '2023-03-23 00:00:00' OR '2023-03-23 00:00:00' is null) 
AND 
    (EventTime <= '2023-03-23 23:59:59' OR '2023-03-23 23:59:59' is null) 
AND 
    (SensorID IN ('SL-1031-QL') OR COALESCE('SL-1031-QL') is null) 

마스터와 슬레이브에 대한 위의 쿼리에 대한 설명은 동일합니다.

MariaDB [db1000]> explain SELECT * FROM `table0` WHERE (EventTime >= '2023-03-23 00:00:00' OR '2023-03-23 00:00:00' is null) AND (EventTime <= '2023-03-23 23:59:59' OR '2023-03-23 23:59:59' is null) AND (EventID IN ('SL-1031-QL') OR COALESCE('SL-1031-QL') is null);
+------+-------------+-------+------+--------------------------+---------+---------+-------+--------+------------------------------------+
| id   | select_type | table | type | possible_keys            | key     | key_len | ref   | rows   | Extra                              |
+------+-------------+-------+------+--------------------------+---------+---------+-------+--------+------------------------------------+
|    1 | SIMPLE      | table0   | ref  | index_3,index_2,cindex_0 | index_2 | 62      | const | 365040 | Using index condition; Using where |
+------+-------------+-------+------+--------------------------+---------+---------+-------+--------+------------------------------------+
1 row in set (0.318 sec)

인덱스는 다음과 같습니다.

MariaDB [db1000]> show index from db1000.table0;
+-------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table0   |          0 | PRIMARY  |            1 | SensorID         | A         |   218159126 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | index_3  |            1 | EventTime | A         |    21815912 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | index_2  |            1 | EventID          | A         |      433715 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | cindex_0 |            1 | EventTime | A         |    36359854 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | cindex_0 |            2 | EventID          | A         |   218159126 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.000 sec)

데이터가 메모리에 이미 캐시되어 있으므로 디스크 어레이에서 데이터를 가져올 필요가 없으므로 마스터 노드가 더 빠른지 궁금합니다(마스터 SELECT는 거의 모두 10초 이내에 완료되는 반면 슬레이브 SELECT는 300초에서 1000초 사이입니다).그렇다면 슬레이브 셀렉트 성능을 향상시키기 위해서는 어떻게 해야 합니까?

효율성 문제를 해결하기 위한 이전의 시도:

조정을 시도했습니다.innodb_buffer_pool_size그리고.innodb_buffer_pool_instances=8버퍼 풀이 슬레이브 노드의 효율성에 영향을 미치는지 확인합니다.조정후innodb_buffer_pool_size메모리의 약 50%에 이르는 슬레이브 노드 모두에서 쿼리 시간이 눈에 띄게 향상되지 않습니다.마스터 노드 값innodb_buffer_pool_size128M(메모리의 1% 미만)에 불과하며,innodb_buffer_pool_instances=1; 그러나 마스터 노드는 슬레이브 노드보다 훨씬 더 나은 성능을 유지합니다.

우리는 또한 다음 두가지 케이스를 시도했습니다.

• query_cache_type=OFF그리고.query_cache_size=0
• query_cache_type=ON그리고.query_cache_size=16777216

쿼리 기간에도 관찰 가능한 차이가 없습니다.

마스터 노드에서 SELECT 쿼리를 실행하면 디스크 어레이에서 데이터를 가져오는 일이 거의 없는 반면 슬레이브 노드에서 실행하면 디스크 어레이에서 상당한 네트워크 트래픽이 발생한다는 것을 알 수 있습니다.

데이터는 1Gbps 이더넷을 통해 SAN에 저장되었습니다.데이터를 로컬 SSD로 이동한 후에는 효율성이 훨씬 향상되었습니다.

언급URL : https://stackoverflow.com/questions/75830839/mysql-select-faster-in-master-but-slower-in-slaves