나무를 심어야지

※ 함수명 DATEDIFF() 

1. 사용 : DATEDIFF() : MSSQL 

2. 설명   

  - 지정한 두 날짜 사이에 있는 날짜와 시간 경계의 수를 반환합니다.

3. 형식

   -  DATEDIFF(datepart, startdate, enddate) 

※datepart

※startdate, edndate 는 DATETIME 형식이거나 날짜형식을 띄는 문자열 

4. 예제

   - SELECT DATEDIFF(Week,'20110101','20110101')  : 0
   - SELECT DATEDIFF(Week,'20110101','20111231')  : 52
   - SELECT DATEDIFF(Day,'20110101','20111231')  : 364
   - SELECT DATEDIFF(Month,'20110101','20111231')  : 11
   - SELECT DATEDIFF(Year,'20110101','20111231')  : 0

SELECT 'rs.getString("' + c.name +'")'
     FROM dbo.sysobjects o
          JOIN syscolumns c on o.id = c.id
                WHERE o.id = object_id('테이블명')

msdn 링크 : http://msdn.microsoft.com/ko-kr/library/ms187752.aspx

기타 데이터 형식

decimal , numeric, cursor, timestamp, sql_variant, uniqueidentifier, table, xml 

함수를 활용해 적용한 모습

SELECT top 10 * FROM TOrder WHERE no not in (SELECT top {$page_args['first']} no FROM TOrder ORDER BY no DESC ) AND UserId='1' ORDER BY no DESC 

This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

MySQL Database의 경우 Oracle 이나 MS SQL Server에 비해서 대용량의 자료를 처리하는 경우가 적기에 튜닝에 필요성이 적은 것 같습니다. 그러나 웹이라는 환경은 많은 사용자가 동시에 접속을 할 수 있기에 항상 모니터링과 최적화는 기본이라고 생각합니다.

본 강좌에서는 기본적인 모니터링 방법과 Connection과 Memory 부분에 대한 튜닝 방법을 소개하도록 하겠습니다.

가. 모니터링 및 초기화 명령어

• show status - MySQL 데이타베이스의 현재 상황
• show Processlist - MySQL 프로세스 목록
• show variables - 설정 가능한 모든 변수 목록
• flush logs - MySQL의 로그파일 초기화
• flush status - MySQL 상태정보 초기화
• flush thread - 쓰레드 캐시에 저장된 쓰레드 초기화
• flush tables - MySQL에 캐싱된 테이블 초기화
• flush privileges - 권한정보 재 설정

나. Connection 튜닝

1. status

• Aborted_clients - 클라이언트 프로그램이 비 정상적으로 종료된 수
• Aborted_connects - MySQL 서버에 접속이 실패된 수
• Max_used_connections - 최대로 동시에 접속한 수
• Threads_cached - Thread Cache의 Thread 수
• Threads_connected - 현재 연결된 Thread 수
• Threads_created - 접속을 위해 생성된 Thread 수
• Threads_running - Sleeping 되어 있지 않은 Thread 수

2. system variables

• wait_timeout - 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
• thread_cache_size - thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
• max_connections - 최대 동시 접속 가능 수

그외에 status 또는 system variables 값은 참고의 Mysql 메뉴얼을 참조해 주십시요.

mysql> show variables like '%max_connection%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections | 100   |

+-----------------+-------+

1 row in set (0.00 sec)
mysql> show status like '%connect%';

+----------------------+---------+

| Variable_name        | Value   |

+----------------------+---------+

| Aborted_connects     | 3782    |

| Connections          | 2961108 |

| Max_used_connections | 90      |

| Threads_connected    | 1       |

+----------------------+---------+

4 rows in set (0.01 sec)
mysql> show status like '%clients%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| Aborted_clients | 2160  |

+-----------------+-------+

1 row in set (0.00 sec)
mysql> show status like '%thread%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Delayed_insert_threads | 0     |

| Slow_launch_threads    | 0     |

| Threads_cached         | 7     |

| Threads_connected      | 1     |

| Threads_created        | 1364  |

| Threads_running        | 1     |

+------------------------+-------+

6 rows in set (0.00 sec)

Cache Miss Rate(%) =  Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

위의 경우는 Cache Miss Rate(%) = 0.05%, Connection Miss Rate(%) = 0.12%, Connection Usage(%) = 1%

3. 튜닝

• Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.
• DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
• Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
• MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

다. Memory 튜닝

1. status

• key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
• key_reads - Key Block 읽기 요청시 Disk을 읽은 수
• key_read_requests - Key Block 읽기 요청수

2. system variables

• key_buffer_size - 인덱스를 메모리에 저장하는 버퍼의 크기
• table_cache - 전체 쓰레드가 사용할 오픈 가능한 테이블 수
• myisam_sort_buffer_size - 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
• join_buffer_size - 조인을 위한 메모리 버퍼 크기
• record_buffer - 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
• record_rnd_buffer - order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
• sort_buffer - order by 와 group by에 사용되는 메모리 버퍼 크기
• tmp_table_size - group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
• key_cache_block_size - block 의 크기(bytes, 기본값 1024)

mysql> show status like '%key%';

+------------------------+-----------+

| Variable_name          | Value     |

+------------------------+-----------+

| Com_preload_keys       | 0         |

| Com_show_keys          | 2945      |

| Handler_read_key       | 365020739 |

| Key_blocks_not_flushed | 0         |

| Key_blocks_unused      | 222601    |

| Key_blocks_used        | 231960    |

| Key_read_requests      | 847204435 |

| Key_reads              | 4195954   |

| Key_write_requests     | 25034738  |

| Key_writes             | 16452136  |

+------------------------+-----------+

10 rows in set (0.00 sec)

Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100 
Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100

* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

3. 튜닝

• key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.
• Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다. 1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다. 또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.

라. 적용

system variables은 my.cnf 또는 my.ini 파일을 수정 후 MySQL Server 를 재시작 해 주십시요.

[www@smson www]$ vi /etc/my.cnf  
# The MySQL server

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-locking

skip-name-resolve

key_buffer = 256M

max_allowed_packet = 1M

table_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache = 8

query_cache_size= 16M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

wait_timeout = 120
~~~
[root@smson mysql]# /usr/local/mysql/share/mysql/mysql.server restart

• http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
• http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
• http://jeremy.zawodny.com/blog/archives/000173.html
• http://www.dbguide.net/know/know101003.jsp?IDX=887&catenum=14
• High Performance MySQL
• http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
• http://www.mysqlperformanceblog.com/mysql-performance-presentations/

출처 : http://www.ihelpers.co.kr/programming/lec.php?CMD=view&TYPE=1&KEY=&SC=S&&CC=&PAGE=1&IDX=253

작성자 : 손상모

Oracle 11g R2 Download

http://www.oracle.com/technology/software/products/database/index.html

/etc/sysctl.conf 파일을 열어 다음 내용을 추가한다

kernel.shmmax = 536870912
kernel.shmall = 2097152

fs.aio-max-nr = 1048576
fs.file-max = 6815744

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586 

kernel.shmax와 kernel.shmall은 Default값이 있어 기존것으로 설정했다.

기본값이 있을 경우 위값보다 높으면 상관없다.

/sbin/sysctl -p 실행

/etc/security/limits.conf 파일을 열어 아래내용 추가


oracle  soft  nproc   2047
   oracle  hard  nproc   16384
oracle  soft  nofile  1024
    oracle  hard  nofile  65536

 

/etc/pam.d/login 파일을 열어 아래내용 추가

session    required     pam_limits.so

 /etc/selinux/config 파일을 열어 SELINUX를 disabled 시켜준다.

셋팅값을 적용하기 위해 OS를 리부팅 시킨다.

부팅후 CentOS 시디나 이미지를 마운트 시킨다.

/media/CentOS_5.4_Final/CentOS로 가서 필요한 패키지를 설치한다.

인터넷에 연결된다면 yum install을 이용해 설치해도 상관없다.

oinstall과 dba그룹을 만들고 oracle 계정을 만든다.

/oracle 디렉토리를 만든다.

오라클 설치할 기본 디렉토리는 /oracle/11g다.

소유자를 oracle로 변경하고 쓰기가 가능하도록 퍼미션을 변경한다.

oracle 계정으로 접속하여(su - oracle)

 .bash_profile 파일에 다음내용을 추가한다.

SID의 경우 NEMI라는 이름으로 지정하였다.


ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_BASE=/oracle/11g; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=NEMI; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

SAMBA나 FTP(oracle계정)를 통해 Oracle 11g R2 zip파일 2개를 서버로 전송한다.

아래와 같이 압축을 푼다.

unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip

이제부터 xming을 통해 Install을 할 것이다.

xming은 원격으로 GUI용 Linux프로그램을 윈도우화면에서 볼 수 있게 해주는 프로그램이다.

xming이 아니더라도 CentOS 메인 화면에서 설치 할 수 있다.

참고로 export LANG=c 를 꼭 해주어야 글자가 안깨진다.

압축푼 폴더에서 ./runInstaller 를 실행시키자.

Email을 입력하면 보안이슈 관련 내용을 보내주는 것 같다.

크게 필요없다면 체크박스를 해제하고 Next를 누른다.

데이터베이스까지 생성하기 때문에 첫번째를 선택한다.

서버용 설치이므로 Server Class를 선택

RAC를 통해 이중화 구성을 하지 않으므로 싱글인스턴스 선택

기본 설치인 Typical Install 선택

설치 위치를 정할 수 있는 화면이다.

글로벌 데이터베이스명과 패스워드를 입력한다.

오라클 인벤토리 설치 위치를 정하는 화면

오라클 설치중에서 제일 중요한 부분이다.

다른 부분보다 파라미터와 패키지는 fail로 나오면 안된다.

파라미터값이 fail일 경우 권장값으로 변경하고

패키지가 fail일 경우 rpm이나 yum을 통해 반드시 설치한다.

패키지의 경우 무시하면 설치 중 에러가 난다.

테스트용 서버라 메모리와 Swap 사이즈는 무시하고 설치하였다.

ignore All를 체크하면 무시하고 설치가 진행된다.

최종 설정 정보를 확인한다.

특이사항이 없으면 Finish를 누른다.

오라클 11g 설치가 진행된다.

데이터베이스 생성 화면

설치가 완료되었다.

Password Management를 누르면 유저 잠금여부와 암호를 지정할 수 있다.

root계정으로 접속하여 2개의 스크립트를 실행하자.

최종적으로 설치가 완료된다.

sqlplus로 접속하여 인스턴스를 확인해 보자.