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
