Connet to mariadb galera cluster through socket

[liu lei]

Hi all,
I try to use sysbench to test the cluster we create recently. I can not run sysbench through socket. The error is as below:
# sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-host=172.17.7.210 --mysql-db=sbtest --mysql-user=root --mysql-password=zabbix --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run --mysql-socket=/opt/mysql/mysql.sock
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored


Threads started!

FATAL: unable to connect to MySQL server, aborting...
(last message repeated 1 times)
FATAL: error 2002: Can't connect to local MySQL server through socket '/opt/mysql/mysql.sock' (2)
FATAL: unable to connect to MySQL server, aborting...
PANIC: unprotected error in call to Lua API (Failed to connect to the database)
FATAL: error 2002: Can't connect to local MySQL server through socket '/opt/mysql/mysql.sock' (2)
PANIC: unprotected error in call to Lua API (Failed to connect to the database)

How can i connect to cluster through socket which is faster then the normal connection? Or it is not matter?
Thanks for your help.
Best Regards,
Ritchie

[Philip Stoev]

Hello,

Are you running the sysbench on the same host where a Galera node is
running? UNIX sockets are only used for local connections.

Philip Stoev

--
You received this message because you are subscribed to the Google Groups
"codership" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[liu lei]

No. I am running sysbench from another host which called arbiter.
I think i can not run sysbench to connect to VIP through socket.

Also i realise the performance on transaction per seconds ,much lower (700) comparing between VIP and individual node (2070). The same issue as below:
Refer to http://dba.stackexchange.com/questions/81619/transaction-speed-benchmarks-for-mysql-v5-6-replication-seems-very-slow
I do not understand why. How to improve it?
Anybody has any idea?
Thanks.
Ritchie

[Philip Stoev]

If you are running sysbench from another host, then you can not use
the --mysql-socket option . --mysql-host=172.17.7.210 should be sufficient.

With Galera, each transaction must be replicated to all nodes in the cluster
before it is acknowledged and sysbench can issue another transaction on the
same connection. This will cause lower benchmark numbers if the cluster as a
whole is not tuned properly or there is an I/O or disk bottleneck of some
sort.

Also, with --num-threads=8 , sysbench can only have 8 transactions in
flight at any given time. Try with higher values and you will probably see
an increase in numbers when benchmarking Galera Cluster.

[liu lei]

Hi Phil,

Thank you very much for the help.

I did tested again with increase the --num-threads=14,20,40,100. The interesting is the result is improve a lot (up to 1100 transactions/sec) then it drop to 700 transactions/sec at 100).
Also there are more errors show in the results below:

[  10s] threads: 20, tps: 1444.13, reads: 10108.88, writes: 3610.31, response time: 16.94ms (95%), errors: 0.00, reconnects:  0.00
[  11s] threads: 20, tps: 1359.19, reads: 19053.74, writes: 5428.77, response time: 16.74ms (95%), errors: 0.00, reconnects:  0.00
[  12s] threads: 20, tps: 1316.00, reads: 18439.96, writes: 5271.99, response time: 18.45ms (95%), errors: 1.00, reconnects:  0.00
[  13s] threads: 20, tps: 1180.33, reads: 16551.60, writes: 4726.32, response time: 24.97ms (95%), errors: 0.00, reconnects:  0.00
[  14s] threads: 20, tps: 930.53, reads: 13004.38, writes: 3714.11, response time: 31.26ms (95%), errors: 1.00, reconnects:  0.00
[  15s] threads: 20, tps: 867.72, reads: 12186.01, writes: 3476.86, response time: 29.50ms (95%), errors: 1.00, reconnects:  0.00
[  16s] threads: 20, tps: 1136.07, reads: 15864.04, writes: 4542.30, response time: 23.54ms (95%), errors: 0.00, reconnects:  0.00
[  17s] threads: 20, tps: 1290.33, reads: 18119.58, writes: 5161.30, response time: 18.97ms (95%), errors: 1.00, reconnects:  0.00
[  18s] threads: 20, tps: 1134.92, reads: 15885.92, writes: 4545.69, response time: 27.61ms (95%), errors: 0.00, reconnects:  0.00
[  19s] threads: 20, tps: 1035.02, reads: 14469.31, writes: 4140.09, response time: 28.61ms (95%), errors: 0.00, reconnects:  0.00
[  20s] Checkpoint report:
OLTP test statistics:
    queries performed:
        read:                            159234
        write:                           45474
        other:                           22740
        total:                           227448
    transactions:                        11368  (1136.80 per sec.)
    read/write requests:                 204708 (20470.74 per sec.)
    other operations:                    22740  (2273.99 per sec.)
    ignored errors:                      5      (0.50 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0001s
    total number of events:              11368
    total time taken by event execution: 199.9322s
    response time:
         min:                                 12.23ms
         avg:                                 17.59ms
         max:                                 66.14ms
         approx.  95 percentile:              26.71ms

Threads fairness:
    events (avg/stddev):           568.4000/13.04
    execution time (avg/stddev):   9.9966/0.01


[  10s] threads: 100, tps: 765.26, reads: 6632.24, writes: 2550.86, response time: 241.84ms (95%), errors: 0.00, reconnects:  0.00
[  11s] threads: 100, tps: 871.51, reads: 13435.18, writes: 3625.62, response time: 295.72ms (95%), errors: 84.34, reconnects:  0.00
[  12s] threads: 100, tps: 1111.01, reads: 17018.19, writes: 4567.05, response time: 229.50ms (95%), errors: 104.00, reconnects:  0.00
[  13s] threads: 100, tps: 1015.98, reads: 15320.77, writes: 4182.94, response time: 264.48ms (95%), errors: 84.00, reconnects:  0.00
[  14s] threads: 100, tps: 758.90, reads: 11930.36, writes: 3179.56, response time: 356.78ms (95%), errors: 90.99, reconnects:  0.00
[  15s] threads: 100, tps: 797.96, reads: 12243.43, writes: 3285.85, response time: 311.91ms (95%), errors: 76.00, reconnects:  0.00
[  16s] threads: 100, tps: 909.18, reads: 13950.75, writes: 3769.74, response time: 302.17ms (95%), errors: 96.02, reconnects:  0.00
[  17s] threads: 100, tps: 1042.96, reads: 16204.45, writes: 4339.85, response time: 225.88ms (95%), errors: 105.00, reconnects:  0.00
[  18s] threads: 100, tps: 867.99, reads: 12761.92, writes: 3531.98, response time: 269.84ms (95%), errors: 51.00, reconnects:  0.00
[  19s] threads: 100, tps: 861.00, reads: 13364.97, writes: 3572.99, response time: 276.96ms (95%), errors: 87.00, reconnects:  0.00
[  20s] Checkpoint report:
OLTP test statistics:
    queries performed:
        read:                            137269
        write:                           36995
        other:                           18747
        total:                           193011
    transactions:                        8942   (894.20 per sec.)
    read/write requests:                 174264 (17426.39 per sec.)
    other operations:                    18747  (1874.70 per sec.)
    ignored errors:                      861    (86.10 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0000s
    total number of events:              8942
    total time taken by event execution: 999.6209s
    response time:
         min:                                 11.76ms
         avg:                                111.79ms
         max:                                923.42ms
         approx.  95 percentile:             284.27ms

Threads fairness:
    events (avg/stddev):           89.4200/6.83
    execution time (avg/stddev):   9.9962/0.14