mysql-proxy-discuss team mailing list archive

[Joakim Lagerqvist <joakim.lagerqvist@xxxxxxxxx>]

Dear all,

I have done some more investigations regarding my problems and I
*think* I know where the problem is but I am not sure.

It looks like the rw-splitting script only accepts one backend.
Default the script has a function "connect_server" where it loops
through the backends and establish connections to them inside a for
loop (for i=1, #proxy.global.backends do)

Inside this loop there is a if/elseif/elseif statement that breaks the
loop. That is the first time the loop executes it will create a
connection to the master, and then go inside the first if statment
because it is not down and it is a read/write and cur_idle is below
the minimum value (as this is the first execution). Then it breaks the
for loop and the slave will never be used.... Is there an error in
this part of the script?? Thanks for any advices!

                -- prefer connections to the master
                if s.type == proxy.BACKEND_TYPE_RW and
                   s.state ~= proxy.BACKEND_STATE_DOWN and
                   cur_idle < pool.min_idle_connections then
                        proxy.connection.backend_ndx = i
                        break
                elseif s.type == proxy.BACKEND_TYPE_RO and
                       s.state ~= proxy.BACKEND_STATE_DOWN and
                       cur_idle < pool.min_idle_connections then
                        proxy.connection.backend_ndx = i
                        break
                elseif s.type == proxy.BACKEND_TYPE_RW and
                       s.state ~= proxy.BACKEND_STATE_DOWN and
                       rw_ndx == 0 then
                        rw_ndx = i
                end

best,
Joakim

On 29 March 2010 17:20, Joakim wrote:
>
> Dear all,
>
> I have just installed mysql-proxy and I am trying to test the read/write splitting but I have some difficulties. I have one master server and one slave and I would like mysql-proxy to send the read queries to the slave and write statements to the master but right now everything goes to the master. If anyone knows what my problem might be I would be very grateful!
>
> I start the proxy with the following script:
>
> #!/bin/bash
>
> MASTER=172.16.86.129
> SLAVE=172.16.86.130
>
> ROOT_DIR=/usr/local
>
> LUA_PATH='$ROOT_DIR/mysql-proxy/share/doc/mysql-proxy/?.lua' $ROOT_DIR/mysql-proxy/bin/mysql-proxy \
>  --log-level=debug \
>  --proxy-backend-addresses=$MASTER:3306 \
>  --proxy-read-only-backend-addresses=$SLAVE:3306 \
>  --proxy-lua-script=$ROOT_DIR/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
>
> # End script
>
> Then I start my mysql client on the proxy:
>
> mysql -u stress -h127.0.0.1 -P 4040 stress
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 148
> Server version: 5.0.51a-24+lenny1-log (Debian)
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> select * from t;
> +--------+
> | answer |
> +--------+
> |     42 |
> +--------+
> 1 row in set (0.01 sec)
>
>
> From my debug window I see the follow information:
>
> 2010-03-23 15:46:59: (message) mysql-proxy 0.8.0 started
> 2010-03-23 15:46:59: (debug) chassis-limits.c:75: current RLIMIT_NOFILE = 1024 (hard: 1024)
> 2010-03-23 15:46:59: (debug) chassis-limits.c:79: trying to set new RLIMIT_NOFILE = 8192 (hard: 1024)
> 2010-03-23 15:46:59: (critical) chassis-limits.c:81: could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.
> 2010-03-23 15:46:59: (message) proxy listening on port :4040
> 2010-03-23 15:46:59: (message) added read/write backend: 172.16.86.129:3306
> 2010-03-23 15:46:59: (message) added read-only backend: 172.16.86.130:3306
>
> [connect_server] 127.0.0.1:39355
>   [1].connected_clients = 0
>   [1].pool.cur_idle     = 0
>   [1].pool.max_idle     = 8
>   [1].pool.min_idle     = 4
>   [1].type = 1
>   [1].state = 0
>   [1] idle-conns below min-idle
> [read_query] 127.0.0.1:39355
>   current backend   = 0
>   client default db = stress
>   client username   = stress
>   query             = show databases
>   sending to backend : 172.16.86.129:3306
>     is_slave         : false
>     server default db: stress
>     server username  : stress
>     in_trans        : false
>     in_calc_found   : false
>     COM_QUERY       : true
> # cut a lot of info
> [read_query] 127.0.0.1:39355
>   current backend   = 0
>   client default db = stress
>   client username   = stress
>   query             = select * from t
>   sending to backend : 172.16.86.129:3306
>     is_slave         : false
>     server default db: stress
>     server username  : stress
>     in_trans        : false
>     in_calc_found   : false
>     COM_QUERY       : true
>
> As you can see, everything goes to my master (IP ending in .129).
>
> Thanks in advance for any help!
>
> best regards,
> --
> Joakim