Troubleshooting MySQL Replication with mysqlbinlog

[root@vmxdb01 mysql]# mysqlbinlog mysql-bin.000002


SET TIMESTAMP=1277302367/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
insert into employees values('a','b',1)
/*!*/;
# at 1053
#100623 22:33:07 server id 1  end_log_pos 1154  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1277303587/*!*/;
insert into employees values('b','c',2)
/*!*/;
# at 1154
#100623 22:42:34 server id 1  end_log_pos 1255  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1277304154/*!*/;
insert into employees values('b','c',3)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

[root@vmxdb01 mysql]# mysql -h vmxdb01 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select from_unixtime(1277304154);
+---------------------------+
| from_unixtime(1277304154) |
+---------------------------+
| 2010-06-23 22:42:34       |
+---------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

LOBSEGMENT defragmentation

Method 1: Shrink space, which is slow
Method 2: "Move" LobSegment

[oracle@vmxdb01 ~]$ sqlplus donghua/ora123

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 8 15:02:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @lob_test.sql
SQL> col segment_name for a30
SQL> col index_name for a30
SQL> set lin 90
SQL> col mbytes for 99999999999
SQL> set timing on
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:01.70
SQL>
SQL> CREATE TABLE tbl_lob_test
  2  (
  3     col1    CHAR (2000),
  4     col2    CHAR (2000),
  5     col3    CHAR (2000),
  6     message   CLOB
  7  )
  8  LOB (message) STORE AS (TABLESPACE users DISABLE STORAGE IN ROW);

Table created.

Elapsed: 00:00:00.21
SQL>
SQL> insert into tbl_lob_test
  2  select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:00:56.44
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME     from dba_lobs
  2  where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME                   INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000074592C00004$$      SYS_IL0000074592C00004$$

Elapsed: 00:00:00.14
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$            4194304
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:02.15
SQL>
SQL> delete from tbl_lob_test;

72458 rows deleted.

Elapsed: 00:01:13.64
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select segment_name,sum(bytes)     mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           11534336
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:02.62
SQL>
SQL> insert into tbl_lob_test
  2  select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:01:26.81
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes)     mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           14680064
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$        1207959552

Elapsed: 00:00:01.68
SQL>
SQL> alter table tbl_lob_test modify lob (message) (shrink space);

Table altered.

Elapsed: 00:02:29.18
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           14680064
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:01.09
SQL>
SQL> alter table tbl_lob_test
  2  move lob (message)  STORE AS tbl_lob_test_lob_msg
  3          (TABLESPACE users
  4          index tbl_lob_test_lob_msg_idx (tablespace users) );

Table altered.

Elapsed: 00:01:06.87
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$            4194304
TBL_LOB_TEST                      603979776
TBL_LOB_TEST_LOB_MSG              603979776

Elapsed: 00:00:00.05
SQL>
SQL> truncate table tbl_lob_test;

Table truncated.

Elapsed: 00:00:02.10
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$              65536
TBL_LOB_TEST                          65536
TBL_LOB_TEST_LOB_MSG                  65536

Elapsed: 00:00:00.06
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:00.49
SQL>
SQL> CREATE TABLE tbl_lob_test
  2  (
  3     col1      CHAR (2000),
  4     col2      CHAR (2000),
  5     col3      CHAR (2000),
  6     MESSAGE   CLOB
  7  )
  8  LOB (
  9     MESSAGE)
 10     STORE AS
 11        tbl_lob_test_lob_msg (
 12     TABLESPACE users
 13     DISABLE STORAGE IN ROW
 14     INDEX tbl_lob_test_lob_msg_idx ( TABLESPACE users ));

Table created.

Elapsed: 00:00:00.18
SQL>
SQL> insert into tbl_lob_test values (1,2,3,4);

1 row created.

Elapsed: 00:00:00.06
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
TBL_LOB_TEST_LOB_MSG_IDX              65536
TBL_LOB_TEST                          65536
TBL_LOB_TEST_LOB_MSG                  65536

Elapsed: 00:00:00.05
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME     from dba_lobs
  2  where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME                   INDEX_NAME
------------------------------ ------------------------------
TBL_LOB_TEST_LOB_MSG           TBL_LOB_TEST_LOB_MSG_IDX

Elapsed: 00:00:00.04