Tag Archives: db

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 – InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

오라클 홈페이지에 가니 플러거블 스토리지 엔진 기반으로 동작하는 MySQL 아키텍쳐 그림을 볼 수 있었습니다. 플러거블(Pluggable)이라 함은 스토리지 방식을 선택할 수 있다는 것인데요. 이는 각각의 스토리지가 컴포넌트처럼 동작한다는 의미를 내포합니다.

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 - InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

MySQL 5.5.5 이전 버젼에서는 MyISAM이 기본 스토리지 엔진이었습니다, 그런데 5.5.5부터는 InnoDB가 기본적으로 붙도록 바뀌었습니다. 쓰는 입장에서는 별 차이를 느낄 수 없을 것이라 생각됩니다만, 고도화 된 연산을 하는 부분에서는 성능차이가 발생 할 수 있을 것으로 생각됩니다. 즉, 개발된지 오래 된 MyISAM은 성능적으로 좀 느린 피드백을 줄 가능성이 높습니다. 그렇지만 256TB의 대용량을 지원 및 검색 기능은 무시 할 수 없을것 같군요.

MyISAM 스토리지의 기본피쳐는 아래와 같습니다.

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 - InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

이와 비교할만한 DB로 InnoDB를 살펴봅시다.  보시는 바와 같이 스토리지는 64TB로 제한적이지만 주 메모리에 데이터와 인덱스(Index)를 상시 캐싱(Caching)하는 구조로 인한 퍼포먼스상의 잇점을 무시할 수 없을 것 같습니다. 즉, 인덱싱이 잘 되어져 있는 DB를 쓰시고 계신다면 설계시 감안했던 모든 잇점을 다 살리실 수 있을 것입니다.

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 - InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

좀더 자세한 기술적 자료는 아래 링크를 참조하세요.

https://docs.oracle.com/cd/E19957-01/mysql-refman-5.5/storage-engines.html

JPG 저장된 DB 이미지 JSP로 보여주기

Blob Type으로 저장된 DB 이미지를 JSP로 보여주는 비기를 공개한다.

<%@ page language=”java” contentType=”image/jpeg” %>
<%@ page import=”java.util.*, java.sql.*, java.io.*” %>
<%
  String sql = “”;
  Connection con = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;

  OutputStream output = response.getOutputStream();
  InputStream input = null;

  try {
    con = DriverManager.getConnection(“Jdbc:oracle:thin:@서버주소:PORT:SID”,”USER”,”PASSWORD”);
    sql =” select imagedata from imageTable where id = ‘00000’ “;
    pstmt = con.prepareStatement(sql);
    rs = pstmt.executeQuery();
    if (rs.next()) {
      input = rs.getBinaryStream(“imagedata”);
      int byteRead;
      while((byteRead = input.read()) != -1) {
        output.write(byteRead);
      }
      input.close();
    }
  } catch(Exception e) {
    out.print(e);
  } finally {
    try {if (rs != null) rs.close();} catch (Exception ex) {}
    try {if (pstmt != null) pstmt.close();} catch (Exception ex) {}
    try {if (con != null) con.close();} catch (Exception ex) {}
  }
  input.close();
  output.flush();
  output.close();
%>

오라클8i 글자수 무제한 게시판소스

턱없이 부족한 실력이지만 구현해보았습니다.
intermedia text 를 사용하지 않고 4000byte 가 넘는 텍스트를 like 검색
(경계에 걸리는 단어는 검색불가능합니다만 그외의 단어는 검색가능합니다.
SQL을 잘 안다면 잘라진 텍스트를 붙여서 검색하는 쿼리도 생각해 볼 수 있겠죠.
^^; 불가능 할 수도 있을 거구요.)
가능하도록 쪼개서 넣는 로직으로 구현했습니다.
한글이 맨 마지막에 들어갈 경우 4001 바이트가 되므로 한 덩어리 크기는
3999 bytes로 잘랐습니다.

소스를 보시고 더 좋은 의견이 있으신 분은 답글 달아주시면 감사하겠습니다.
index는 달지 않았습니다. 습작이기 때문이기도 하고, 제가 db를 잘 모르는
이유도 있습니다.

board_insert2.jsp 파일에 텍스트를 자르는 루틴이 들어있습니다.
db schema는 답변형 게시판으로 잡았지만 아직 구현되지는 않았습니다.
readme.txt 파일에 설치및 기능 설명을 해 놓았습니다.

읽어주셔서 감사합니다. 좋은 하루 되십시오.
/*
   okboard 0.61
   작성자 : kenu@okjsp.pe.kr
   배포처 : http://www.okjsp.pe.kr
   작성일 : 2001-05-23 6:33오전
   테스트 : http://210.219.132.222/okboard/board.jsp (no guarantee)
*/
Oracle8i, jspSmartUpload 를 이용한 자료게시판입니다.

개발환경
apache 1.3.19,
tomcat 3.2.1, Resin 1.2.5,
Oracle 8.1.5, 8.1.6, 8.1.7

설치법
root(예: c:/jsphome)
c:/jsphome/index.html
c:/jsphome/okboard/board_create.jsp  <– 게시판 생성 한번실행 후 파일명 바꾸시거나 삭제하세요.
c:/jsphome/okboard/board_delete.jsp  <– 게시판 drop 시킵니다 파일명 바꾸시거나 삭제하세요.
c:/jsphome/okboard/board.jsp         <– 게시물리스트
c:/jsphome/okboard/board_insert.jsp  <– 자료 입력폼
c:/jsphome/okboard/board_insert2.jsp <– 자료 입력처리
c:/jsphome/okboard/board_reply.jsp   <– 답변 입력폼
c:/jsphome/okboard/board_reply2.jsp  <– 답변 입력처리
c:/jsphome/okboard/passchk.jsp       <– 수정권한 암호 입력
c:/jsphome/okboard/board_modify.jsp  <– 수정폼
c:/jsphome/okboard/board_modify2.jsp <– 수정처리
c:/jsphome/okboard/db.jsp            <– jdbc 정보파일
c:/jsphome/okboard/go.jsp            <– back 방지용 중계처리
c:/jsphome/okboard/delete.jsp        <– 삭제권한 암호 입력
c:/jsphome/okboard/delete2.jsp       <– 삭제처리
c:/jsphome/okboard/path.jsp          <– jvm의 classpath 확인
c:/jsphome/okboard/download.jsp      <– 다운로드 처리
c:/jsphome/okboard/okboard.css       <– 페이지 CSS
c:/jsphome/okboard/okboard.js        <– javascript

c:/jsphome/okboard/images/           <– 그림파일 디렉토리
c:/jsphome/WEB-INF/classes/com/      <– JspSmartUpload 컴포넌트 디렉토리
c:/jsphome/upload/                   <– 파일이 업로드되는 디렉토리

※ D:oracleora81jdbclibclasses12.zip 이 jvm 의 classpath 에 인식이 되어야 합니다.
   그렇지 않을 경우 java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver 메세지가 뜹니다.
   Oracle 8.0 이하는 쿼리가 제대로 동작하지 않을 수 있습니다.

 

실행법
설치 완료 후 tomcat을 구동시킨 후
브라우저에서 http://localhost/okboard/board_create.jsp 를 요청합니다.
kboard 라는 table 이 만들어 지면 됩니다.
board_create.jsp 파일 이름을 바꿔주세요.
board_delete.jsp 파일 이름을 바꿔주세요. 테이블리셋 시에 바꾼이름을 브라우저에서 요청하면 됩니다.

보안
oracle의 데이터베이스의 기본 접근권한으로 되어있습니다. (scott, tiger)
변경을 원하시면 db.jsp 파일만 수정하시면 됩니다.

table 구성 (kboard, kboard_cnts)
CREATE TABLE KBOARD (
 BBSID VARCHAR2 (10) NOT NULL,
 REF NUMBER NOT NULL,
 STEP NUMBER DEFAULT 0 NOT NULL,
 LEV NUMBER DEFAULT 0 NOT NULL,
 WRITER VARCHAR2 (50) NOT NULL,
 SUBJECT VARCHAR2 (500) NOT NULL,
 PASSWORD VARCHAR2 (8),
 EMAIL VARCHAR2 (50),
 READ NUMBER DEFAULT 0 NOT NULL,
 FILENAME VARCHAR2 (200),
 MASKNAME VARCHAR2 (20),
 FILESIZE NUMBER DEFAULT 0,
 DOWNLOAD NUMBER DEFAULT 0,
 WHEN DATE,
 IP VARCHAR2 (24)) ;

CREATE TABLE KBOARD_CNTS (
 BBSID VARCHAR2 (10) NOT NULL,
 REF NUMBER NOT NULL,
 STEP NUMBER NOT NULL,
 LEV NUMBER NOT NULL,
 CONTENT VARCHAR2 (4000),
 CID NUMBER DEFAULT 0 NOT NULL) ;

0.61 –  2001-05-23 6:33오전
리스트 버그 수정

0.6 –  2001-05-21 1:58오전
게시판 제목, 이름, 내용 검색 추가
select 항목 지정하는 javascript 추가

0.5 –  2001-05-20 1:11오전
수정기능 추가
Cookie로 입력신상 저장

0.4 –  2001-05-18 10:56오전
답변기능 추가
Fn.java 버그 수정

0.3 –  2001-05-17 5:57오후
오라클의 4000바이트 제한을 풀었습니다.
다운로드 방식을 바꾸었습니다.
한글제목파일, 이미지 다운로드시 이상없습니다.
게시물 작성 후 refresh 되도록 했습니다.
email 링크로 변경했습니다.
doc, xls, ppt, gif, img 모두 다운로드 창 뜹니다.
jsp 코드 올려도 실행되지 않습니다.

전신
kboard 를 오라클용으로 전환합니다.

초간단 php-oracle 연동 클래스

PHP에서 오라클 연동하려면 좀 고통스럽다.

뭔가 mySQL이랑 좀 다른 부분도 있는것 같고..

하여 클래스로 만들어보았다.



class Oracle
{
  var $DBID = “id”;
  var $DBPW = “pw”;
  var $DBSID = “sid”;

  var $conn;
  var $stmt;
        
  var $error = false;    // 에러 발생하면 true 로 수정됨. commit,rollback 결정에 사용
  var $transaction = false;    // true 면 auto commit 않함

  var $bind = array();
  var $data_size = array();

  // php4 의 생성자
  function Oracle(){    
            
    $this->connect();
  }
  // php5 의 생성자
  function __construct(){    
            
    $this->connect();
  }
  // php5 의 소멸자
  function __destruct(){    
            
    $this->disconnect();
  }

  function connect(){

    if(!$this->conn)
      $this->conn = OCILogon($this->DBID,$this->DBPW,$this->DBSID);
  }
        
  function disConnect(){

    if($this->stmt)
      @OCIFreeStatement($this->stmt);
    if($this->conn)
      @OCILogoff($this->conn);
  }
  // 바인드변수 값 지정
  // 같은 값이라도 executeDML() 호출전에 반드시 매번 호출해야 함(executeDML() 함수호출후 초기화되므로)
  function setBind($bind){

    if(is_array($bind))
      $this->bind = $bind;
    else if($bind)
      $this->bind = array($bind);
  }
  // 바인드변수 사이즈 지정. 지정안하면 해당변수의 최대사이즈가 기본값임
  function setDataSize($data_size){

    if(is_array($data_size))
      $this->data_size = $data_size;
    else if($data_size)
      $this->data_size = array($data_size);
  }
  // 쿼리문 결과를 ‘다중배열($rs[필드명][인덱스])’로 리턴한다
  // $preferch_size는 가져올 레코드건수를 지정함(옵션)
  function selectList($query,$preferch_size=1){
            
    $this->connect();
    $this->stmt = OCIparse($this->conn,$query);

    if($this->stmt){

      $this->bindByName();                                                        
      $this->prefetch($preferch_size);                                        

      if($this->transaction){                        
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
      }
      $rows = @OCIFetchStatement($this->stmt,$rs);                
    
      if($rows){                    
        if(!$this->transaction)
          @OCIFreeStatement($this->stmt);
        return $rs;
      }
    }            
    return array();
  }
  // 쿼리문 결과 1건을 ‘배열($rs[필드명 or 인덱스])’로 리턴한다
  // $option 은 OCI_ASSOC(필드명) or OCI_NUM(인덱스) 을 지정(옵션)
  function selectRow($query,$option=OCI_ASSOC){
            
    $this->connect();
    $this->stmt = OCIparse($this->conn,$query);

    if($this->stmt){

      $this->bindByName();

      if($this->transaction){                        
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
      }
      if(@OCIFetchInto($this->stmt,$rs,$option+OCI_RETURN_NULLS)){
        if(!$this->transaction)
          @OCIFreeStatement($this->stmt);
        return $rs;
      }                    
    }            
    return array();
  }
  // 한개 값만 리턴하는 쿼리문을 처리한다
  // ROWID,LOB,FILE 등 외에는 스트링으로 반환한다
  function selectOne($query){

    $this->connect();
    $this->stmt = OCIparse($this->conn,$query);        

    if($this->stmt){

      $this->bindByName();

      if($this->transaction){                        
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
      }
      if(@OCIFetch($this->stmt)){

        // 인수 1은 컬럼순서 인덱스(1부터 시작함에 주의!,컬럼명 지정도 가능)
        $value = @OCIResult($this->stmt,1);    
        if(!$this->transaction)
          @OCIFreeStatement($this->stmt);
        return $value;
      }
    }
  }
  // insert,update,delete등을 실행후 영향받은 로우의 갯수를 리턴한다(auto commit일 경우만 갯수를 리턴함)
  // $transaction=true 일 경우 마지막에 반드시 commit() 함수를 호출한다
  function executeDML($sql){

    $this->connect();
    $this->stmt = OCIparse($this->conn,$sql);    
            
    if($this->stmt){

      $this->bindByName();

      if($this->transaction){    // auto commit 이 아닐 경우
        @OCIexecute($this->stmt,OCI_DEFAULT);
        $this->error();
      }else{
        @OCIexecute($this->stmt);
        $num = @OCIRowCount($this->stmt);
        @OCIFreeStatement($this->stmt);
                    
        return $num;                    
      }
    }
  }
  // auto commit 않하고 명시적으로 transaction 시작
  // executeDML() 호출후 마지막에 반드시 commit() 함수를 호출한다
  function transaction(){

    $this->transaction = true;
  }
  // transaction 완료후 commit 이면 true를 리턴함
  // executeDML() 에서 에러가 하나라도 발생하면 자동 rollback 됨
  function commit(){

    if(!$this->error){
      @OCICommit($this->conn);
      $commit = true;
    }else{
      @OCIRollback($this->conn);
      $commit = false;
    }
    if($this->stmt)
      @OCIFreeStatement($this->stmt);

    $this->transaction = false;
    $this->error = false;

    return $commit;
  }
  // $transaction=true 일 경우 매번 executeDML 에서 자동호출된다
  // 에러 발생할 경우 commit,rollback의 판단기준인 $error 값을 변경
  function error(){

    if($error = @OCIError($this->stmt)){                
      //echo “<p> Error is : ” . $error[“code”] . ” – ” . $error[“message”] . “<p>”;
      $this->error = true;
    }
  }
  // :b1,:b2,:b3…에 바인드 변수 지정
  // 바인드변수명은 반드시 :b1,:b2,:b3… 으로 지정한다
  function bindByName(){

    $size = sizeof($this->bind);

    for($i=0 ; $i < $size ; $i++){

      $ds = $this->data_size[$i];
      if(!$ds) $ds = -1;

      @OCIBindByName($this->stmt,”:b”.($i+1),$this->bind[$i],$ds);
    }
    $this->bind = array();                                                        
    $this->data_size = array();
  }
  // 오라클 클라이언트 버퍼에 저장되는 레코드의 수를 지정
  // 여러 레코드를 select할 경우 디폴트 값이 작아서 비효율적이면 가져올 건수만큼 지정
  function prefetch($preferch_size){

    if($preferch_size > 1)
      @OCISetPrefetch($this->stmt,$preferch_size);
  }
}

################# 여기서부터 사용예 입니다 #################

1. 여러건의 레코드를 select 할 경우
$db = new Oracle();
$query = “select title,name,date from tb where … 생략”;
$rs = $db->selectList($query);
for( $i=0 ; $i < sizeof($rs[“TITLE”]) ; $i++ ){
    echo $rs[“TITLE”][$i] . $rs[“NAME”][$i] . $rs[“DATE”][$i] . “<br>”;
}
* 주의하실건 꼭 배열에 대문자로 적으셔야 한다는 겁니다

2. 한건의 레코드를 select 할 경우
$query = “select title,name,date from tb where rownum=1”;
$rs = $db->selectRow($query);
echo $rs[“TITLE”] . $rs[“NAME”] . $rs[“DATE”];

3. 한개값을  select 할 경우
$query = “select count(*) from tb”;
$value = $db->selectOne($query);
echo “총 ” . $value . ” 건 입니다”;

4. insert,update,delete 할 경우
$sql = “insert into tb (title,name) values (‘$title’,’$name’)”;
$rs = $db->executeDML($sql);
if($rs){
 echo $rs . ” 건의 데이터를 처리하였습니다”;
}    

5. 트랜젝션 처리
$sql1 = “insert into tb1 … 생략”;
$sql2 = “insert into tb2 … 생략”;
$sql3 = “insert into tb3 … 생략”;
$db->transaction();
$db->executeDML($sql1);
$db->executeDML($sql2);
$db->executeDML($sql3);
$rs = $db->commit();
if($rs){
  echo “처리 성공!”;
}else{
  echo “에러 발생!”;
}
transaction() 함수를 호출하면 auto commit 이 false 가 되며 마지막에 commit() 함수를 호출하면 에러가 하나라도 발생하면 자동으로 롤백됩니다 물론 에러가 없으면 커밋되구요 함수명이 commit 이라고 무조건 커밋되는거 아닙니다 ^^
transaction() 함수를 호출않하고 executeDML()을 사용하면 항상 auto commit 입니다

6. 바인드변수 사용
$query = “select name from tb where id=:b1 and pw=:b2”;
$bind = array($id,$pw);
$db->setBind($bind);
$name = $db->selectOne($query);
echo $name;

바인드 변수명은 꼭 :b1,:b2,:b3… 이런식으로 지정하며 setBind() 함수에 배열로 값을 넘겨줍니다
바인드변수가 :b1 한개만 사용된 경우는 배열이 아니라 값하나만 넘겨도 됩니다(아래예 처럼)
넘겨진 값은 selectXxx(),executeDML()등의 함수사용후에 초기화되므로 함수 실행전에 매번 setBind() 함수를 호출해야 합니다
아래예는 틀린 사용예입니다 $cnt2 에는 아무값도 없습니다
$query1 = “select count(*) from tb1 where id=:b1”;
$query2 = “select count(*) from tb2 where id=:b1”;
$db->setBind($id);
$cnt1 = $db->selectOne($query1);
$cnt2 = $db->selectOne($query2);

아래예가 옳은 사용예입니다
$db->setBind($id);
$cnt1 = $db->selectOne($query1);
$db->setBind($id);
$cnt2 = $db->selectOne($query2);

7. 트랜젝션 처리와 바인드 변수 사용
$sql1 = “insert into tb(title,name) values(:b1,:b2)”;
$sql2 = “update tb2 set addr=:b1 where id=:b2”;
$bind1 = array($title,$name);
$bind2 = array($addr,$id);
$db->transaction();
$db->setBind($bind1);
$db->executeDML($sql1);
$db->setBind($bind2);
$db->executeDML($sql2);
$rs = $db->commit();
if($rs){
  echo “처리 성공!”;
}else{
  echo “에러 발생!”;
}