0

One of or customers has a MySQl back end as part of their solution.

They have configured it to have a common master database and a specific slave database per client (they have 10+ slaves). They are using MySQL proxy for this.

They are facing some performance issues including database inserts/updates being queued and taking quite some time to write to the slave databases.

Can you suggest how this can be improved? Are there tools that can be used to help identify where the problems are? Does this seem like a standard approach to you (common master with client specific slaves controlled via MySQL proxy)?

Any advice would be appreciated.

Thanks,

Andy

user2060819
  • 21
  • 3

1 Answers1

0

I had the same behavior, but my trouble was next:
one of my updates was finishing with error, mysql proxy (and rw-splitter.lua specific) treat this situation like connection might be reused by another client, and return connection to the pool. That is mean, that when client receive an error and tried to rollback transaction, it is was passed to another connection or new connection from pool and it is has no effect. Meanwhile failed UPDATE process which had an error in transaction was in lock, until transaction will not rolled back by timeout (but in my case and mysql-proxy by default it is 28800 seconds) so quite long.

Problem was resolved.

Patch:

find in rw-splitter.lua next block:

    is_in_transaction = flags.in_trans
    local have_last_insert_id = (res.insert_id and (res.insert_id > 0))

    if not is_in_transaction and
       not is_in_select_calc_found_rows and
       not have_last_insert_id then

and change it to 

    if res.query_status == proxy.MYSQLD_PACKET_ERR and is_in_transaction then
         if is_debug then
            print ("(read_query_result) ERROR happened while transaction staying on the same backend")
        end
        return
    end

    is_in_transaction = flags.in_trans
    local have_last_insert_id = (res.insert_id and (res.insert_id > 0))

    if not is_in_transaction and
       not is_in_select_calc_found_rows and
       not have_last_insert_id then
SpalaX
  • 23
  • 5