Wednesday, June 23, 2010

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

Tuesday, June 8, 2010

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