List:Internals« Previous MessageNext Message »
From:Mats Kindahl Date:November 20 2009 10:32am
Subject:Re: Primary key is miss in row based replication.
View as plain text  

Yuan Wang wrote:
> Hi Mats
> 
> I have a question. Why set all columns to be replicated? It will leads
> to serious performance problem. For example, if a table has lobs, then
> all lobs will be written to binlog and send to slave, even when those
> lobs are not read or updated?

Yes, that is correct.

> I think only primary key is needed by the slave to identify which row
> to update. I think setting all columns is only needed when a table
> don't have a primary key.

Only setting the primary key will lead to all kinds of problems if the index
structure is different between the master and the slave.

We have a worklog 5092 that Luis, one of our replication developers, is working
on to solve this problem in a good way to allow improving performance while
still ensuring that replication won't break if there are different indexes on
master and slave.

You find the worklog on http://forge.mysql.com/worklog/task.php?id=5092 but it
was accidentally marked private, so I removed the flag and it will be available
in a short while.

Best wishes,
Mats Kindahl

> 
> On Fri, Nov 20, 2009 at 6:06 PM, Mats Kindahl <mats@stripped> wrote:
>> Hi Yuan,
>>
>> I just checked the code for 5.1.33, and it sets all columns to be replicated.
>>
>> If the hexdump shows that columns are missing, it is clearly a bug in the server
>> (but I cannot easily tell where).
>>
>> If the hexdump shows that the columns are there, it is a bug in mysqlbinlog.
>>
>> Best wishes,
>> Mats Kindahl
>>
>> Yuan Wang wrote:
>>> Hi Mats
>>>
>>> I'm sorry that I made a mistake.
>>>
>>> To reproduce this senario, we do the following operations, where ntse
>>> is the name of our engine.
>>>
>>> mysql> create table testrbr(a varchar(8) primary key, b varchar(8), c
>>> int, d varchar(8)) engine = ntse;
>>> Query OK, 0 rows affected (0.84 sec)
>>>
>>> mysql> insert into testrbr values('1', 'a', 1, 'aa');Query OK, 1 row
>>> affected (0.42 sec)
>>>
>>> mysql> update testrbr set c = c + 1 where b = 'a';Query OK, 1 row
>>> affected (38.78 sec)
>>> Rows matched: 1  Changed: 1  Warnings: 0
>>>
>>> And with mysqlbinlog --base64-output=DECODE-ROWS --verbose, we can
>>> print the content of the binlog. As follows
>>>
>>> /*!*/;
>>> # at 1531
>>> #091120 14:10:39 server id 1  end_log_pos 1599  Query   thread_id=1
>>>  exec_time=39    error_code=0
>>> SET TIMESTAMP=1258697439/*!*/;
>>> BEGIN
>>> /*!*/;
>>> # at 1599
>>> # at 1654
>>> #091120 14:10:39 server id 1  end_log_pos 1654  Table_map:
>>> `test`.`testrbr` mapped to number 17
>>> #091120 14:10:39 server id 1  end_log_pos 1702  Update_rows: table id
>>> 17 flags: STMT_END_F
>>> ### UPDATE test.testrbr
>>> ### WHERE
>>> ###   @1=''
>>> ###   @2='a'
>>> ###   @3=1
>>> ###   @4=''
>>> ### SET
>>> ###   @1=''
>>> ###   @2='a'
>>> ###   @3=2
>>> ###   @4=''
>>> # at 1702
>>> #091120 14:10:39 server id 1  end_log_pos 1771  Query   thread_id=1
>>>  exec_time=39    error_code=0
>>> SET TIMESTAMP=1258697439/*!*/;
>>> COMMIT
>>>
>>> You can see that primary key is missing.
>>>
>>> We also use gdb to confirm that primary key is not set in the read_set.
>>>
>>> We implements table_flags as follows.
>>>
>>> ulonglong ha_ntse::table_flags() const {
>>>        ftrace(ts.mysql, tout << this);
>>>        ulonglong flags = HA_NO_TRANSACTIONS;
>>>        flags |= HA_PARTIAL_COLUMN_READ;
>>>        flags |= HA_NULL_IN_KEY;
>>>        flags |= HA_DUPLICATE_POS;
>>>        flags &= ~HA_NO_PREFIX_CHAR_KEYS;
>>>        flags |= HA_NO_AUTO_INCREMENT;
>>>        flags |= HA_FILE_BASED;
>>>        flags |= HA_BINLOG_ROW_CAPABLE;
>>>        flags |= HA_REC_NOT_IN_SEQ;
>>>        return flags;
>>>
>>> On Fri, Nov 20, 2009 at 4:41 PM, Yuan Wang <wangyuanzju@stripped>
> wrote:
>>>> Hi Mats
>>>>
>>>> I had posted an example.
>>>>
>>>> Best wishes
>>>> Yuan WANG
>>>>
>>>> On Fri, Nov 20, 2009 at 4:04 PM, Mats Kindahl <mats@stripped>
> wrote:
>>>>> Hi Yuan,
>>>>>
>>>>> In 5.1, the full row is sent over to the slave, so I am a little
> surprised that
>>>>> there is anything missing at all.
>>>>>
>>>>> Do you have an example I can look at?
>>>>>
>>>>> Best wishes,
>>>>> Mats Kindahl
>>>>>
>>>>> Yuan Wang wrote:
>>>>>> We are developed a storage engine using row based replication.
> During
>>>>>> the test we found that primary key values will not be included in
> the
>>>>>> binlog event. So the slave is impossible to identify which row
> to
>>>>>> update.
>>>>>>
>>>>>> Is this a bug or is there a flag to tell MySQL to include primary
> key?
>>>>>> We are based on 5.1.33 currently.
>>>>>>
>>>>> --
>>>>> Mats Kindahl
>>>>> Senior Software Engineer
>>>>> Database Technology Group
>>>>> Sun Microsystems
>>>>>
>> --
>> Mats Kindahl
>> Senior Software Engineer
>> Database Technology Group
>> Sun Microsystems
>>
> 
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
> 

-- 
Mats Kindahl
Senior Software Engineer
Database Technology Group
Sun Microsystems
Thread
Primary key is miss in row based replication.Yuan Wang20 Nov
  • Re: Primary key is miss in row based replication.Venu Kalyan20 Nov
  • Re: Primary key is miss in row based replication.Venu Kalyan20 Nov
  • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov
    • Re: Primary key is miss in row based replication.Yuan Wang21 Nov
      • Re: Primary key is miss in row based replication.Mats Kindahl24 Nov
        • Re: Primary key is miss in row based replication.Yuan Wang25 Nov
          • Re: Primary key is miss in row based replication.Kristian Nielsen25 Nov
          • Re: Primary key is miss in row based replication.Mats Kindahl25 Nov
            • Re: Primary key is miss in row based replication.Yuan Wang26 Nov
              • Re: Primary key is miss in row based replication.Kristian Nielsen26 Nov
                • Re: Primary key is miss in row based replication.Mats Kindahl26 Nov
              • Re: Primary key is miss in row based replication.Mats Kindahl26 Nov
Re: Primary key is miss in row based replication.Yuan Wang20 Nov
  • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov
    • Re: Primary key is miss in row based replication.Yuan Wang20 Nov
  • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov
    • Re: Primary key is miss in row based replication.Yuan Wang20 Nov
      • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov