Pool Module

sqlrelay
mysql_proxy

Using MySQL with memcached

http://dev.mysql.com/doc/refman/5.0/en/ha-memcached.html

Oracle Backup Recovery Solutions

http://www.qudong.com/soft/program/Oracle/shujuguanli/20080319/2537.html

http://www.qudong.com/soft/program/Oracle/shujuguanli/list_155_2.html

DBA Task

1.Evaluate Server and Install
2.Plan,Implement,Create,Open Database
3.Security (User and Database)
4.Tuning (SQL and Performance)
5.Backup and Recovery
6.Other : AP,RAC,TOOLS,STREAM,DATA GUARD

select * from goods_file
AS OF TIMESTAMP timestamp'2008-06-12 14:53:47'
where g_no = 11080531367948

Oracle DBCA (Database Configuration Assistant)

Template
1.Data Warehouse
2.Transaction Processing
3.General Purpose


1.Dedicated Server Mode
2.Shared Server Mode

Reset SYS , SYSTEM account default password.

Oracle Instance
1.System Global Area
SGA_MAX_SIZE , DB_CACHE_SIZE , LOG_BUFFER , SHARED_POOL_SIZE , LARGE_POOL_SIZE
2.Background processes

Data Cache Buffer
1.Write List (dirty buffers wait for write to disk)
2.LRU list (free , dirty , pinned buffers)
LRU Least Recently Used
MRU Most Recently Used

Total Size = DB_BLOCK_SIZE(2~32KB) * DB_BLOCK_BUFFERS

Redo Log Buffer (bytes)
Default : Redo Log Buffer = 4 * System Block Size

Share Pool (SHARED_POOL_SIZE = 8 MB in 32-bit , 6 MB in 64-bit system )
1. Library cache
Share SQL Area (SQL parse tree , execution plan)
Private SQL Area
PL/SQL Program Unit
2. Dictionary cache
parse information like table , view name , column name , data type
user privileges

DBWn - Database Writer
write dirty blocks from Data Cache Buffer to data files. => checkpoint
default only DBW0
btw in OLTP , update busy
DBW1~DBW9 is prefered.

LGWR - Log Writer
Write to redo log files
When
1. transaction commited
2. Auto Writing each 3 seconds.
3. free size of Redo log buffer is less than 1/3.
4. Before DBWn write dirty blocks to data files
Write - ahead protocol :
if LGWR write Redo data to redo logs finished
DBWn write dirty blocks to data files
else
Call LGWR write Redo data to redo logs
Then DBWn do its work.

SMON - System Monitor
Database Start Recovery checking
1. After Database restart , nessesary recovery activities.
2. Release not used temporary segments.
3. Collect and manage free extents
4. Recovery abort transaction.

PMON - Process Monitor
1. Recovery abort Process
2. Clear Data Buffer cache Unused block, reset transaction table status,
Unlock row , remove terminated process ID.

CKPT - Checkpoint
1. make sure MRU Dirty Data will write to data file regularly.
2. when instance recovery , can ignore the record before the currently checkpoint.
3. if(checkpoint finished)
CKPT call DBWn;
Then update control file and header of data file.
RECO (Recover)
handle transaction in Cluster database.
in-doubt transaction . transaction between servers.

ARCn (Archiver) (LOG_ARCHIVE_MAX_PROCESSES LIMIT IS 10 OR ALTER SYSTEM to change)
log switch. Archived logs.
indeed, Oracle will judge it automatically.

MySQL Index Building Tips

1. 資料種類較少 ex 男, 女 的index 在複合 index 中 放越前越好 (B-Tree divide)

2. 會按 index 建立的時間來吃, 吃到就不會繼續往下找,即使有更好的index~~

3. where date_format('A') = '20080723'
mod('A') = 5
=> 在設計之初 , 就要規劃好 避免在where 中 轉 格式.

4. where A is NULL => 會 full table scan
可以放default , ex: '0' => where A = 0 or "NULL" => where A = 'NULL'