So you're running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from its starting 10MB size.
What could be the reason of this growth and what you can do about it ?

There are few things which are always stored in main tablespace - these are system tables, also known as data dictionary - table and index definitions and related stuff, double write buffer, insert buffer which temporary stores records to be inserted in the index tree and undo segment holding previous versions of changed rows.

The system tables size depends on the number and complexity of the tables you have in the system. If you have hundreds of thousands of tables it may consume gigabytes of space but for most installations we're speaking about tens of megabytes or less. Double Write Buffer is fixed size (2MB = 128 of 16KB pages) and so will not affect growth of your main Innodb Tablespace. Insert Buffer size is also restricted to half of the buffer pool size (can be changed via innodb_ibuf_max_size option) which can be significant size for systems with large amount of memory. Finally undo space can grow unbound depending on your transaction size.

The challenge is both Insert buffer and undo space will grow and shrink during the database operation and unless you can them on being large your would not know what caused your system tablespace size to explode - they will shrink to the normal size and all what you have will be free space, which you unfortunately can't reclaim without reloading tour database. This is where Trending can help, for example MySQL CACTI Templates will have the graphs you need.

If you're looking at SHOW INNODB STATUS this is how you can see Insert Buffer Size:

In this case we can see the segment is allocated to 173551 pages which is about 2.7GB only about 2/3 of it is in use right now but for sake of monitoring tablespace size you need a full allocated number.

Lets now look at the Undo Space which is a lot more interesting (and which is the most likely cause of getting extremely large system tablespace)

The records can be stored in the undo tablespace for 2 reasons. First it is re

In this case the History Length is about 4 million which means there are 4 million of transactions which are not yet purged. This is not very helpful as single transaction may modify single row and so be responsible for one undo entry or it may modify millions of rows. Row size can also vary a lot. For many OLTP applications though which have a lot of tiny transactions this is a very good indicator.

If you're running Percona Server the following can be helpful:

curr_size will display the current size of RSEG which defines undo segment size, though there is a complex data structure and you can't easily convert this number to number of data pages for undo slots or number of rows stored.

From the practical standpoint there are 3 reasons a lot of undo space may be required:

Running Transaction which does a lot of changes - If transaction modifies a lot of rows it has to use a lot of space in undo segment and there is no way around it. Be careful in particular with update or delete transactions which go over a lot of rows. In many cases doing such processes in chunks, updating/deleting may be thousands of rows per transaction may be better if your application can handle it. Note ALTER TABLE will not require excessive amount of undo space even for very large tables as it internally commits every 10000 rows.

Running Very Long Transaction If you're running very long transaction, be it even SELECT, Innodb will be unable to purge records for changes which are done after this transaction has started, in default REPEATABLE-READ isolation mode. This means very long transactions are very bad causing a lot of garbage to be accommodated in the database. It is not limited to undo slots. When we're speaking about Long Transactions the time is a bad measure. Having transaction in read only database open for weeks does no harm, however if database has very high update rate, say 10K+ rows are modified every second even 5 minute transaction may be considered long as it will be enough to accumulate about 3 million of row changes.

Purge Thread Falling Behind This is the most dangerous reason. It is possible for database updates happen faster than purge thread can purge records when they are no more needed which means undo space can just grow until it consumes all free space (or specified max size for ibdata1 file). "Good" thing is performance typically starts to suffer terribly well before that and it gets noticed. There are to things you can do about this problem first you can use innodb_max_purge_lag to make a threads doing modifications slow down if purge thread can't keep up. This however does not work in all cases. If you're running XtraDB you can also use innodb_use_purge_thread to use dedicated purge thread, which works a bit faster as it does not need to compete with other activities of main theread. You can also use more than one purge thread by setting this variable to higher values though this functionality is a bit experimental.

One related question I get asked often is why Master and Slave may get so much different table space size in the end. The answer is of course their workload is very different. On one hand slave has all updates going from one thread so it has less chance for purge thread to fall behind, on other a lot of people use slave for very long queries which may make long transactions reasons a lot more likely. So it can be both - either master or slave can have it main tablespace growing larger than its counterpart.

Writing this blog post I also discovered even in XtraDB we do not have as much transparency in regards to undo space and purging as I would like. I filed number of feature requests and I hope we'll have a chance to fix this soon.

Entry posted by peter | One comment

Add to: | | | |

Logical standby would be more mature and popular in future, i did some test about some feathures behavior in Oracle11g logical standby env.

Here is conclusion.

1) Text index is not FULLY support. It’s only Partially support.  But it can be maintained on logical standby via SQL apply.

2) ROWID datatype are not supported in logical standby, you will see “ORA-16129: unsupported dml encountered” when it’s ignored by logical standby

3) Rows in compressed table can be replicated to logical standby.

4)virtual column  and related function based index is also supported by logical standby

5)Logical standby will replicate “analyze table” statement from primary to standby, not copy statistics in dictionary from primary to standby.

6)Online redefinition is supported

7)Outline is supported. But manually updates in outln schema is not replicated to logical standby.

8)Oracle replicate sequence value very well and make sence.

9)During convert to some character set, we need to do convert on both databases.

10)System trigger like “after logon”or “after startup” is also supported on logical standby side.

i did some test about some feathures behavior in Oracle11g logical standby env.

Here is conclusion.

1) Text index is not FULLY support. It’s only Partially support.  But it can be maintained on logical standby via SQL apply.

2) ROWID datatype are not supported in logical standby, you will see “ORA-16129: unsupported dml encountered” when it’s ignored by logical standby

3) Rows in compressed table can be replicated to logical standby.

4)virtual column  and related function based index is also supported by logical standby

5)Logical standby will replicate “analyze table” statement from primary to standby, not copy statistics in dictionary from primary to standby.

6)Online redefinition is supported

7)Outline is supported. But manually updates in outln schema is not replicated to logical standby.

8)Oracle replicate sequence value very well and make sence.

9)During convert to some character set, we need to do convert on both databases.

10)System trigger like “after logon”or “after startup” is also supported on logical standby side.

————————————see more detail beow————————————————————-

1) Text index is not FULLY support. It’s only Partially support.  But it can be maintained on logical standby via SQL apply.

Per OTN document, Logical standby databases do not support the following datatypes:

BFILE
Collections (including VARRAYS and nested tables)
Multimedia data types (including Spatial, Image, and Oracle Text)
ROWID, UROWID
User-defined types
XMLType stored as Object Relational
Binary XML

below is simple test to show how oracle handle TEXT index.

Step 1) creaet text index on primary

SQL> select * from document;
ID TITLE
———- —————————————-
1 house tax start
2 global economy will twice drop
3 people lost job

SQL> create index document_ctx on document(title) indextype is ctxsys.ctxcat;
Index created.

SQL> select * from tab;
TNAME               TABTYPE
—————————-
DOCUMENT            TABLE
DR$DOCUMENT_CTX$I   TABLE

SQL>  select * from document where catsearch(title,’tax’,”)>0;
ID TITLE
———- —————————————-
1 house tax start

Step 2) Check if index is replicated to logical standby,

on logical standby, we see objects DR$ is created.

SQL> select * from tab;
TNAME                TABTYPE                CLUSTERID
——————– ——————— ———-
DOCUMENT             TABLE
DR$DOCUMENT_CTX$I    TABLE

Text search also works well.

SQL>  select * from document where catsearch(title,’tax’,”)>0;
ID TITLE
———- —————————————-
1 house tax start

However, we see many warnning events on logical standby, the event means it ignores many DML on CTXSYS schema.
CTXSYS schema store many metadata for text index.

STATUS
——————————————————————————–
EVENT
——————————————————————————–
ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR_ID_SEQ”

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$INDEX”

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$INDEX_OBJECT”

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$INDEX_VALUE”

ORA-16227: DDL skipped due to missing object
begin
“SYS”.”DBMS_DDL”.”SET_TRIGGER_FIRING_PROPERTY” (
“TRIG_OWNER” => ‘”BIN

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$FEATURE_USED”

So to fully have logical standby to support TEXT index, it’s better to re-create it on logical standby side.

2) ROWID datatype are not supported in logical standby, you will see “ORA-16129: unsupported dml encountered” when it’s ignored by logical standby

The test first load some table’s obsolte rows ’s rowid into a ROWID type table, then do purge by rowid.

On primary,

SQL> select count(*) from test;
COUNT(*)
———-
267065

SQL> create table testrowid(id number primary key, rl rowid) ;
Table created.

SQL> insert into testrowid select id,rowid from test where rownum<=100;
100 rows created.

SQL> commit;
Commit complete.

SQL> delete from test where id in (select id from testrowid);
100 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(*) from test;
COUNT(*)
———-
266965

Then on logical standby, ROWID table is created but rows are not replicated.

SQL> select * from testrowid;
no rows selected

We would see warning in DBA_LOGSTDBY_EVENTS.

ORA-16129: unsupported dml encountered
DML on “BINZHANG”.”TESTROWID”

Table test rows is deleted on logical standby by redo analysis & SQL apply

SQL> select count(*) from test;
COUNT(*)
———-
266965

3) Rows in compressed table can be replicated to logical standby.

In Oracle10G, rows in compressed table can NOT be replicated to logical standby. In oracle11G, it changed and oracle11G have 2 compress option: basic or OLTP.

Step 1) create basic compress table on primary.

SQL>create table testcom (id number primary key,name varchar2(63)) compress;
Table created.

SQL>insert /*+ append */into testcom select object_id, ‘compress_content’ from
dba_objects where object_id is not null;
72607 rows inserted.

SQL>analyze table testcom compute statistics;

SQL> select count(*) from TESTCOM;
COUNT(*)
———-
72607

And it uses 118 blocks.

SQL>  select AVG_ROW_LEN,blocks from user_tables where table_name=’TESTCOM’;
AVG_ROW_LEN     BLOCKS
———– ———-
25        118

Step 2) check status on logical standby

On logical standby, replcated table keep compress basic option. Rows can be replated,but uses more blocks.
It’s due to apply concept of logical standby: it ignore the direct load.

SQL> select table_name,compression,compress_for from user_tables;

TABLE_NAME      COMPRESSION              COMPRESS_FOR
————— ———————— ————————————
TESTCOM         ENABLED                  BASIC

SQL> select count(*) from TESTCOM;
COUNT(*)
———-
72607

SQL> select AVG_ROW_LEN,blocks from user_tables where table_name=’TESTCOM’;

AVG_ROW_LEN     BLOCKS
———– ———-
25        244

Seems logical standby use conversional insert that compress basic doesn’t support.
Probabbly compress OLTP would make differece.

————————
insert /*+ restrict_all_ref_cons  */ into “BINZHANG”.”TESTCOM”
p(”ID”,”NAME”)values(:1,:2)
—————————————–
| Id  | Operation                | Name |
—————————————–
|   0 | INSERT STATEMENT         |      |
|   1 |  LOAD TABLE CONVENTIONAL |      |
—————————————–

Step 3) Add a new column on compressed table on primary, it also is added into logical standby.

SQL> desc testcom
Name                                      Null?    Type
—————————————– ——– —————————-
ID                                        NOT NULL NUMBER
NAME                                               VARCHAR2(63)
ANOTHER                                            VARCHAR2(30)

4) Move table on primary, on logical standby it’s moved also.

SQL> alter table testcom move  tablespace users02;
Table altered.

then on logical standby ,it also got moved and data object id got changed.

SEGMENT_NAME         TABLESPACE
——————– ———-
TESTCOM              USERS02

SQL> select object_id,data_object_id from dba_objects where object_name=’TESTCOM’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
76859          76861

Logical standby databases support the following table storage types:

*Cluster tables (including index clusters and heap clusters)
*Index-organized tables (partitioned and nonpartitioned, including overflow segments)
* Heap-organized tables (partitioned and nonpartitioned)
*OLTP table compression (COMPRESS FOR OLTP) and basic table compression (COMPRESS BASIC)

4)virtual column  and related function based index is also supported by logical standby

In OTN document, it lists below Unsupported Table Storage Types:

Logical standby databases do not support the following table storage types:
*Tables containing LOB columns stored as SecureFiles (unless the compatibility level is set to 11.2 or higher)
*Tables with virtual columns
*Tables using hybrid columnar compression(ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage)

Per my test, tables contains  virtual columns can be maintained well by logical standby.

Step 1)create table with virtual column

CREATE TABLE employees (
id          NUMBER ,
first_name  VARCHAR2(10),
last_name   VARCHAR2(10),
salary      NUMBER(9,2),
comm1       NUMBER(3),
comm2       NUMBER(3),
salary1     AS (ROUND(salary*(1+comm1/100),2)),
salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, ‘JOHN’, ‘DOE’, 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, ‘JAYNE’, ‘DOE’, 200, 10, 20);
COMMIT;

SQL> select first_name,salary1,salary2 from employees;

FIRST_NAME     SALARY1    SALARY2
———————- ———-
JOHN               105        110
JAYNE              220        240

Step 2) check table status on logical standby
Per event, table is created.

EVENT
——————————————————————————–
STATUS
——————————————————————————–
CREATE TABLE employees (
id          NUMBER ,
first_name  VARCHAR2(10),
last_name   VARCHAR2(10),
salary      NUMBER(9,2),
comm1       NUMBER(3),
comm2       NUMBER(3),
salary1     AS (ROUND(salary*(1+comm1/100),2)),
salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*
(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
)
ORA-16204: DDL successfully applied

SQL> !oerr ora 16204
16204, 00000, “DDL successfully applied”
// *Cause:  A DDL statement has successfully commited on the logical
//          standby database.
// *Action: No action necessary, this informational statement is provided
//          to record the event for diagnostic purposes.

Rows is also replicated.

SQL>  select first_name,salary1,salary2 from employees;
FIRST_NAME   SALARY1    SALARY2
——————– ———-
JOHN             105        110
JAYNE            220        240

If create a fucntion based index on virtual column, index would also be created on logical standby.

OWNER           U PAR TYPE    Index Name    Column Name
————— - — —— —————————-
BINZHANG        Y NO         EMPLOYEES_PK     ID
BINZHANG        N NO  FUNCTI EMP_FUNIDX       SALARY1

5)Logical standby will replicate “analyze table” statement from primary to standby, not copy statistics in dictionary from primary to standby.

Step 1) Do analyze on primary,

SQL> analyze table employees compute statistics;
Table analyzed.

SQL> select num_rows,LAST_ANALYZED from user_tables where table_name=’EMPLOYEES’;
NUM_ROWS LAST_ANALYZED
———- ——————-
2 2010-06-01 16:04:29

Then on logical standby, table also got analyzed actually. But their “LAST_ANALYZED” is different.
Logical standby apply analyze statement, not analyze result that resides in dict of primary.

SQL>  select num_rows,LAST_ANALYZED from user_tables where table_name=’EMPLOYEES’;

NUM_ROWS LAST_ANALYZED
———- ——————-
2 2010-06-01 10:50:26

6)Online redefinition is supported

Step 1) do online redef on primary, just online switch tablespace.

SQL>  create table large(id number primary key,text varchar(32) )  tablespace users;
Table created.

SQL> insert into large select object_id,object_name from dba_objects where object_id is not null;
72631 rows created.

SQL> commit;
Commit complete.

SQL> exec dbms_redefinition.can_redef_table   ( USER,’LARGE’, DBMS_REDEFINITION.CONS_USE_PK) ;
PL/SQL procedure successfully completed.

create table large_new into tablespace USERS02 with same structure as large

SQL>  exec dbms_redefinition.START_REDEF_TABLE ( USER, ‘LARGE’, ‘LARGE_NEW’, NULL, DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.sync_interim_table( USER, ‘LARGE’,'LARGE_NEW’);
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.finish_redef_table( USER, ‘LARGE’,'LARGE_NEW’);
PL/SQL procedure successfully completed.

On primary,

SQL> select table_name,tablespace_name from user_tables where table_name in ( ‘LARGE’,'LARGE_NEW’);
TABLE_NAME           TABLESPACE_NAME
——————– ——————–
LARGE                USERS02
LARGE_NEW            USERS

Step 2) then do same verify query on logical standby, we can see online-redef is still supported.
Table LARGE ’s  tablespace changed from USERS to USERS02 in dict.

SQL> select table_name,tablespace_name from user_tables where table_name in ( ‘LARGE’,'LARGE_NEW’);
TABLE_NAME           TABLESPACE_NAME
——————– ——————–
LARGE                USERS02
LARGE_NEW            USERS

In oracle documents, its description has confilict on DBMS_REDEFINITION package.

4.  Supported PL/SQL Supplied Packages

Oracle PL/SQL supplied packages that do not modify system metadata or user data leave no footprint in the archived redo log files,
and hence are safe to use on the primary database. Examples of such packages are
DBMS_OUTPUT, DBMS_RANDOM, DBMS_PIPE, DBMS_DESCRIBE, DBMS_OBFUSCATION_TOOLKIT, DBMS_TRACE, DBMS_METADATA, DBMS_CRYPTO.
Oracle PL/SQL supplied packages that do not modify system metadata but may modify user data are supported by SQL Apply,
as long as the modified data belongs to the supported data types listed in Section C.1.1. Examples of such packages are DBMS_LOB,
DBMS_SQL, and DBMS_TRANSACTION.

Data Guard logical standby supports replication of actions performed through the following packages: DBMS_RLS, DBMS_FGA, and DBMS_REDEFINITION.

C.9.2 Unsupported PL/SQL Supplied Packages

Oracle PL/SQL supplied packages that modify system metadata typically are not supported by SQL Apply, and
therefore their effects are not visible on the logical standby database. Examples of such packages are DBMS_JAVA, DBMS_REGISTRY,
DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, and DBMS_AQ.

7)Outline is supported. But manually updates in outln schema is not replicated to logical standby.

Step 1) Create outline on primary.

SQL> create outline ol_temp for category temp_plan on select count(*) from binzhang.test;
Outline created.

SQL> select count(*) from ol$;
COUNT(*)
———-
1

SQL> select count(*) from ol$nodes;
COUNT(*)
———-
1

SQL>  select count(*) from ol$hints;
COUNT(*)
———-
6

Step 2) Then on logical standby, check outln shema also.

SQL> select sql_text from ol$;

SQL_TEXT
——————————————————————————–
select count(*) from binzhang.test

We can see outline schema is replicated and maintained by oracle.

step 3) Then did a manually update on OL$ on primary and check if manually update can be replicated

SQL> update ol$ set sql_text=’select * from dual’;
1 row updated.

SQL> commit;
Commit complete.

SQL> select sql_text from ol$;
SQL_TEXT
——————————————————————————–
select * from dual

Then on logical standby, it doesn’t change; so it doesn’t support manually update on outln schema.

SQL> select sql_text from ol$;
SQL_TEXT
——————————————————————————–
select count(*) from binzhang.test

8)Oracle replicate sequence value very well and make sence.

Sequences are usually cached on primary DB. And oracle only replicated bump sequence operation in seq$.
In cocnept, when session query nextval, it just fetch value from row cache, it doesn’t update seq$,so it doesn’t generated redo, so logical standby capture nothing.

For example ,cache size is 1000, when all cached sequences are used up, and new serial of sequences are generated&cached and seq$ would be updated.
Oracle will replcate redo of seq$ update operation to logical standby and applied into dictionary at logical standby.

And when disable dataguard and query sequenct.nextval on logical standby. In this way, seq$.last_number would be larger than primary.
At this time, as long as primary’s seq$.last_nuber < standby seq$.last_nuber, standby seq$.last_number will not be updated when primary.seq$ got updates.

Oracle will keep logical standy.seq$.last_number >= primary.seq$.last_number. This would make sence for switchover.

9)During convert to some character set, we need to do convert on both databases.

Per OTN document, Configurations are not supported in which the primary database and standby database have different character sets.
how to convert charset?
What i did
1) convert primary to UTF8.
2) logical standby can apply
3) convert logical standby to UTF8
4) Replication still works well.

10)System trigger like “after logon”or “after startup” can be created on logical standby side.

Below type trigger can be created on logical standby and it will not block application session logon as it’s executed recursively by SYS.

CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO binzhang.connection_audit
(login_date, user_name)
VALUES
(SYSDATE, USER);
END logon_audit;

• 陨石网日志-让科学流行起来！ » Blog Archive » 理科生的纹身