최대 연결 끊김

최대 연결 끊김

현재 읽기/쓰기 분할 모드에서 3노드로 구성된 Galera 클러스터를 사용하여 Maxscale을 테스트하고 있습니다.기본적으로는 Maxscale은 한쪽 노드를 마스터로, 다른 한쪽 노드를 슬레이브로 정의합니다(내 설정에서는 슬레이브의 100%로 되어 있습니다).

Maxscale이 노드 셧다운을 어떻게 처리하는지 확인하려고 합니다.

문제는 벤치마크(Sysbench, Mysqlslap)와 커스텀스크립트(PHP)의 경우 클러스터의 노드를 셧다운하면 백엔드(MariaDB)에 대한 연결이 손실된다는 것입니다.

오류 로그:

MariaDB Corporation MaxScale    /var/log/maxscale/error1.log Thu Oct 29 13:00:11 2015
-----------------------------------------------------------------------
---     Logging is enabled.
2015-10-29 13:00:11   Error: Failed to obtain address for host ::1, Address family for hostname not supported
2015-10-29 13:00:11   Warning: Failed to add user root@::1 for service [RW Split Router]. This user will be unavailable via MaxScale.
2015-10-29 13:00:11   Warning: Duplicate MySQL user found for service [RW Split Router]: cmon@127.0.0.1 for database: (null)
2015-10-29 13:00:11   Warning: Duplicate MySQL user found for service [RW Split Router]: root@127.0.0.1 for database: (null)
2015-10-29 13:00:11   Warning: Duplicate MySQL user found for service [RW Split Router]: root@10.58.224.113 for database: (null)
2015-10-29 13:00:35   Error : Unable to write to backend due to authentication failure.
2015-10-29 13:00:40   Error : Monitor was unable to connect to server 10.58.224.113:3306 : "Can't connect to MySQL server on '10.58.224.113' (111)"

추적 로그:

2015-10-29 13:00:33   [4]  Route query to slave         10.58.224.113:3306 <
2015-10-29 13:00:33   [4]  Servers and router connection counts:
2015-10-29 13:00:33   [4]  current operations : 0 in    10.58.224.113:3306 RUNNING SLAVE
2015-10-29 13:00:33   [4]  current operations : 0 in    10.26.116.84:3306 RUNNING SLAVE
2015-10-29 13:00:33   [4]  current operations : 0 in    10.26.84.103:3306 RUNNING MASTER
2015-10-29 13:00:33   [4]  Selected RUNNING SLAVE in    10.58.224.113:3306
2015-10-29 13:00:33   [4]  Selected RUNNING SLAVE in    10.26.116.84:3306
2015-10-29 13:00:33   [4]  Selected RUNNING MASTER in   10.26.84.103:3306
2015-10-29 13:00:34   [4]  > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: SELECT COUNT(*) FROM sbtest1
2015-10-29 13:00:34   [4]  Route query to slave         10.58.224.113:3306 <
2015-10-29 13:00:36   [4]  Stopped RW Split Router client session [4]
2015-10-29 13:00:42   Server changed state: server1[10.58.224.113:3306]: slave_down

PHP 테스트 스크립트

<?php

# Test MaxScale

$db = new PDO('mysql:host=127.0.0.1;dbname=sbtest;charset=utf8;port=4446;', 'root', '***', array(PDO::ATTR_TIMEOUT => "10", PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
for($i=0; $i<5000; $i++)
{
    try{
            $q = $db->query('SELECT COUNT(*) FROM sbtest1', PDO::FETCH_NUM);
            if($q){
                    $res = $q->fetchAll();
                    #var_dump($res);
                    echo time()." Result: {$res[0][0]}\n";
                    sleep(1);
            }
    }
    catch(PDOException $Exception) {
            echo "PDOException: " . $Exception->getMessage() . "\n";
            die('forced script to stop');
    }
}

Mysqlslap 벤치마크:

mysqlslap -h127.0.0.1 -uroot -p*** -P4446   --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)"  --query="SELECT * FROM a" --concurrency=50 --iterations=200 --delimiter=";"

Sysbench 벤치마크:

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=2500 --mysql-user=root --mysql-password=*** --mysql-host=127.0.0.1 --db-ps-mode=disable --mysql-port=4446 prepare 

sysbench --num-threads=16 --max-requests=5000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-skip-trx=on --oltp-read-only=on --oltp-table-size=250000 --mysql-host=127.0.0.1  --mysql-user=root --mysql-password=*** --mysql-port=4446 run

오류가 발생했습니다.

PDOException: SQLSTATE[HY000]: General error: 2003 Authentication with backend failed. Session will be closed.
PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
PDOException: SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

최대 스케일 구성:

[maxscale]
threads=4
auth_connect_timeout=20
auth_read_timeout=20
auth_write_timeout=20
log_trace=1

[Galera Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxmon
passwd=***
monitor_interval=30000
backend_connect_timeout=10
backend_read_timeout=10
backend_write_timeout=10

[RW Split Router]
type=service
router=readwritesplit
servers=server2,server3,server1
user=root
passwd=***
max_slave_connections=100%
enable_root_user=1
router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS

[Debug Interface]
type=service
router=debugcli

[CLI]
type=service
router=cli[Debug Interface]
type=service
router=debugcli

[CLI]
type=service
router=cli

[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=4446

[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
address=127.0.0.1
port=4442

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
port=6603

[server1]
type=server
address=10.58.224.113
port=3306
protocol=MySQLBackend

[server2]
type=server
address=10.26.84.103
port=3306
protocol=MySQLBackend

[server3]
type=server
address=10.26.116.84
port=3306
protocol=MySQLBackend

세션 모니터링은 다음 예시와 같이 세션이 비활성화됨을 나타냅니다.

# maxadmin -pmariadb show sessions

Session 9 (0x7f60a4000b50)
State:          Invalid State
Service:        RW Split Router (0x342f460)
Client DCB:     0x7f60a40009a0
Client Address:     root@127.0.0.1
Connected:      Thu Oct 29 13:28:57 2015

Maxscale과 PHP 테스트 스크립트(PDO 타임아웃)에서도 다른 타임아웃 변수와 monitor_interval을 가지고 놀았지만, 문제는 Maxscale이 MySQL 세션을 처리하는 방식인 것 같습니다.

노드 중 하나에서 가장 빠른 응답을 전송하는 Maxscale의 낙관적인 방법에 대해서도 읽었습니다만, 이것이 원인인지는 잘 모르겠습니다.

Maxscale에 의해 클러스터의 모든 슬레이브 노드에 전파되는 SQL 요구에 대해 노드 셧다운을 해롭지 않게 하는 방법이 있습니까?

언급URL : https://stackoverflow.com/questions/33416078/maxscale-lost-connection