MYSQL / R

대용량 table 파티션하는 방법

페이지 정보

profile_image
작성자 최고관리자 작성일 12-02-01 16:51 조회 1,816 댓글 0

본문

MySQL 5.5 릴리즈는 몇 가지 기능향상이 되었습니다. 당연히, 대부분의 내용은 semi-synchronous replication가 주목되는 동안 파티션의 향상된 기능은 도외시되고, 때로는 이 기능의 진정한 의미에 대한 약간의 오해도 발생했습니다. 이 문서를 통해서, 멋진 기능과 특히 잘 이해가 되지 않는 부분을 설명하려 합니다.
직관적인 부분: 비정수(non-integer) 컬럼의 파티션


지금까지 정수형이 아닌 컬럼을 사용하여 파티션을 사용해본 경험이 있다면(MySQL 5.1 파티션 연습 참조) 많은 문제로 일종의 좌절을 경험했을 것입니다.
새로운 추가사항은 범위(RANGE)와 리스트 파티션과 함꼐 작동합니다. 새로운 기능을 소개하는 새로운 컬럼 키워드가 있습니다.
다음과 같은 테이블이 있다고 가정합니다.
CREATE TABLE expenses (
  expense_date DATE NOT NULL,
  category VARCHAR(30),
  amount DECIMAL (10,3)
);
만약에 MySQL 5.1에서 category 컬럼으로 파티션을 원한다면, category 컬럼을 정수형으로 변환하고 category에 대한 내용을 찾아볼 수 있는 테이블을 추가해야만 합니다. MySQL 5.5에서는 간단하게 처리할 수 있습니다.
ALTER TABLE expenses
PARTITION BY LIST COLUMNS (category)
(
  PARTITION p01 VALUES IN ( 'lodging', 'food'),
  PARTITION p02 VALUES IN ( 'flights', 'ground transportation'),
  PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'),
  PARTITION p04 VALUES IN ( 'communications'),
  PARTITION p05 VALUES IN ( 'fees')
);
이 구문은 명확하게 읽을 수 있고 효율적인 결집으로 데이터를 정리하는것 뿐만아니라 오직 열거된 category만 사용이 가능하여, 다른측면에서의 혜택이 있습니다.
MySQL 5.1에서의 또 다른 문제점은 날자열의 처리입니다. 아래와 같은 상황에서 YEAR 또는 TO_DAYS 중 하나를 사용하는 컬럼에 대해서 직접 사용할 수 없지만, 변환이 필요합니다.
/* with MySQL 5.1*/
CREATE TABLE t2
(
  dt DATE
)
PARTITION BY RANGE (TO_DAYS(dt))
(
  PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')),
  PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')),
  PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')),
  PARTITION p04 VALUES LESS THAN (MAXVALUE));
SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
      Table: t2
Create Table: CREATE TABLE `t2` (
  `dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
이 얼마나 무섭고 진정한 고통을 주는 코드입니까? 물론, 해결 방법은 있지만, 많은 문제가 발생했습니다. YEAR 또는 TO_DAYS를 사용하여 파티션을 정의하는 것과, 함수로 파니셔닝된 부분에 질의 문으로 노출된 컬럼을 기준으로 질의를 하는 것은 정말 말도 안 되는 퍼즐을 마추는 것 같습니다.
이젠 상황이 달라졌습니다. 날짜별로 분할하는 것이 쉬고 직관적으로 되었습니다.
/*With MySQL 5.5*/
CREATE TABLE t2
(
  dt DATE
)
PARTITION BY RANGE COLUMNS (dt)
(
  PARTITION p01 VALUES LESS THAN ('2007-01-01'),
  PARTITION p02 VALUES LESS THAN ('2008-01-01'),
  PARTITION p03 VALUES LESS THAN ('2009-01-01'),
  PARTITION p04 VALUES LESS THAN (MAXVALUE));
SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
      Table: t2
Create Table: CREATE TABLE `t2` (
  `dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(dt)
(PARTITION p01 VALUES LESS THAN ('2007-01-01') ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
이전 경우와 같은 파티션 정리는 버릴 수 있습니다. 정의된 컬럼을 기준으로 하기 때문에, 컬럼을 기준으로 함수와 쿼리의 의한 정의 사이에서 혼란이 없습니다. DBA의 업무를 수월하게 하기 위해서, 정의되어진 값을 그대로 유지합니다.
모두가 행복해졌죠? 아마, 대부분 그럴것 입니다. 이제 컬럼 특성에 대한 조금 더 모호한 특성에 대하여 살펴보겠습니다.
직관에 반대되는 부분: 다중 컬럼(multiple columns)


컬럼 키워드는 문자열이나 date 컬럼의 파티션 정의자를 허락하는 것보다 더 좋습니다. 그것은 또한 파티션을 정의하는 다중 컬럼으로 사용이 가능합니다.
공식적인 문서에서 아래의 예문과 비슷한 몇 가지 예제를 본적이 있을 것입니다.
CREATE TABLE p1 (
  a INT,
  b INT,
  c INT
)
PARTITION BY RANGE COLUMNS (a,b)
(
  PARTITION p01 VALUES LESS THAN (10,20),
  PARTITION p02 VALUES LESS THAN (20,30),
  PARTITION p03 VALUES LESS THAN (30,40),
  PARTITION p04 VALUES LESS THAN (40,MAXVALUE),
  PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
CREATE TABLE p2 (
  a INT,
  b INT,
  c INT
)
PARTITION BY RANGE COLUMNS (a,b)
(
  PARTITION p01 VALUES LESS THAN (10,10),
  PARTITION p02 VALUES LESS THAN (10,20),
  PARTITION p03 VALUES LESS THAN (10,30),
  PARTITION p04 VALUES LESS THAN (10,MAXVALUE),
  PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)
)
PARTITION BY RANGE COLUMNS (a,b,c) 을 기준으로 하는 예제도 있습니다. 만약에 이러한 예문으로 부터 대단한 아이디어를 얻은 독자라면, 부담 없이 나를 재미있게 만들어 주세요. 왜냐하면 나는 그렇게 하지 못했기 때문입니다.
오랜 세월 동안 MySQL 5.1 파티션을 사용하는데 있어서, 다중 컬럼에 의한 파티션의 중요성을 파학하지 못했습니다. LESS THAN (10,10) 구문의 의미는 무엇입니까? 그리고 다음 파티션으로 LESS THAN (10,20)이 설정된다면 어떤 일이 발생합니까? (20,30)과 같이 완전히 다른 쌍을 적용한다면 어떻게 되겠습니까? 이러한 모든 질문에 답변이 필요하고, 답변하기 전에 우리가 함께 다루고 있는 이것에 대해서 더 많은 이해가 필요합니다.
처음에는 MySQL 엔지니어 가운데도 약간의 혼동이 있었습니다. 그리고 그것 뿐만 아니라 나는 어리석었습니다! 모든 파티션의 첫 번째 범위 값은 마치 단 하나의 컬럼으로 분할되어진 것과 같이 실질적인 값은 전부 다르다고 믿고 있었습니다. 그러나 이 경우만이 아니었습니다. 다음 예제를 보겠습니다.
CREATE TABLE p1_single (
  a INT,
  b INT,
  c INT
)
PARTITION BY RANGE COLUMNS (a)
(
  PARTITION p01 VALUES LESS THAN (10),
  PARTITION p02 VALUES LESS THAN (20),
  PARTITION p03 VALUES LESS THAN (30),
  PARTITION p04 VALUES LESS THAN (40),
  PARTITION p05 VALUES LESS THAN (MAXVALUE)
);
이것은 p1 테이블에 해당되지 않습니다. 만약에 p1dp (10, 1, 1)을 삽입한다면, 그것은 첫 번째 파티션에 포함될 것입니다. p1_single에 대입하면 2번째 파티션에 포함될 것입니다.
(10,1)은 (10, 10)보다 작기 때문입니다. 만약 오직 첫 번째 값을 초점을 둔 경우라면, 튜플이 아닌 단일 값을 비교하였을 것입니다.
이제부터 어려운 부분입니다. 데이터(row)가 어디로 배치될지 결정이 필요할 때 무슨 일이 발생합니까? (10,9) < (10,10)와 같은 작없을 어떻게 비교 합니까? 답변은 간단합니다: 두개의 레코드를 정렬하여 비교하는 방법이 사용됩니다.
a=10
b=9
(a,b) < (10,10) ?
# 다음과 같이 평가 합니다.:
(a < 10)
OR
((a = 10) AND ( b < 10))
# 다음과 같이 변환을 합니다.:
(10 < 10)
OR
((10 = 10) AND ( 9 < 10))
만약에 3개의 컬럼과 복잡한 표현이 있다고 하여도, 더 이상 복잡하지 않습니다. 우선 첫 번째 항목에 대해서 적은 비용(낮은 리소스의 사용을 의미함)으로 테스트합니다. 만약 두개 이상의 파티션에서 일치하는 경우라면, 두 번째 항목을 테스트 합니다. 그렇게 한 다음에도 하나 이상의 후보 파티션을 가지고 있다면, 다음 세 번째 항목을 테스트합니다.
아래의 그림은 세 개의 평가 기록이 파티션의 정의를 통해서 테이블에 삽입되는 과정을 보여줍니다.
(10,10),
(10,20),
(10,30),
(10, MAXVALUE)
 



그림 1. 첫 번째 (비교)값이 파티션 정의의 첫 번째 범주보다 작은 경우에 모든 것이 쉽습니다. 데이터(row)는 여기에 포함됩니다.
 

그림 2. 튜플 비교. 첫 번째 (비교)값이 파티션 정의의 첫 번째 범주와 같을 때, 두 번째 항목을 비교해야 합니다. 남은 하나가 두 번째 범위보다 작다면, 데이터(row)는 여기에 포함됩니다.
 

그림 3. 튜플 비교. 첫 번째 (비교)값과 두번쨰 (비교)값 둘 다 (첫번째) 해당 범주 값과 동일합니다. 튜플은 정의된 범위보다 작지 않으므로, 여기에 포함되지 않습니다. 다음단계로 넘어갑니다.
 

그림 4. 튜플 비교. 다음 범위를 보면, 첫 번째 (비교)값은 같고, 두 번째 (비교) 값은 작습니다. 따라서 튤플은 작고 데이터(row)는 여기에 포함됩니다.


이와 같은 수치의 도움으로, 우리는 다중 컬럼으로 파니션된 테이블에 레코드를 삽입하는 절체에 대해서 더 많은 이해를 할 수 있게 되었습니다. 이것이 이론이 되었습니다. 내가 했던 것보다 훨씬 더 쉽게 새로운 기능을 파악하는 것을 돕기 위해서, 감각적으로 이해 할려고 하는 독자들에게 다른 예제를 제공하자고 합니다. 런치패드에 있는 MySQL test 데이터베이스의 employees 테이블의 몇 가지를 수정하여 사용합니다.
CREATE TABLE employees (
  emp_no int(11) NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  gender char(1) DEFAULT NULL,
  hire_date date NOT NULL
) ENGINE=MyISAM
PARTITION BY RANGE  COLUMNS(gender,hire_date)
(PARTITION p01 VALUES LESS THAN ('F','1990-01-01') ,
 PARTITION p02 VALUES LESS THAN ('F','2000-01-01') ,
 PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) ,
 PARTITION p04 VALUES LESS THAN ('M','1990-01-01') ,
 PARTITION p05 VALUES LESS THAN ('M','2000-01-01') ,
 PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) ,
 PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE)
위의 예제와 다르게 이것은 이론적인 측면을 보면 너무 많은 모호함을 가지고 있음을 알 수 있습니다. 첫 번째 파티션은 1990년 이전, 두 번째 파티션에서는 1990년에서 2000년 사이, 그리고 세 번째파티션은 남아있는 고용된 여자 직원을 저장합니다. 파티션 p04에서 p06도 비슷한 케이스이지만, 남자 직원을 저장합니다. 마지막 파티션의 조작의 경우: 누군가가 이 파티션에 저장이 되기 위해서는 어딘가에 실수가 있어야 합니다.
마지막 문장을 읽어보면, 올바른 문장인지 확인할 필요가 있습니다. 데이터(row)가 어떤 파티션에 저장되는지 어떻게 알 수 있을까요?
두가지 방법이 있습니다. 첫 번째 방법은 정의 하는데 사용되는 동일한 조건을 가진 질의를 하는 것입니다.
SELECT
CASE
  WHEN gender = 'F' AND hire_date < '1990-01-01'
  THEN 'p1'
  WHEN gender = 'F' AND hire_date < '2000-01-01'
  THEN 'p2'
  WHEN gender = 'F' AND hire_date < '2999-01-01'
  THEN 'p3'
  WHEN gender = 'M' AND hire_date < '1990-01-01'
  THEN 'p4'
  WHEN gender = 'M' AND hire_date < '2000-01-01'
  THEN 'p5'
  WHEN gender = 'M' AND hire_date < '2999-01-01'
  THEN 'p6'
ELSE
  'p7'
END as p,
COUNT(*) AS rows
FROM employees
GROUP BY p;
+------+-------+
| p    | rows  |
+------+-------+
| p1  | 66212 |
| p2  | 53832 |
| p3  |    7 |
| p4  | 98585 |
| p5  | 81382 |
| p6  |    6 |
+------+-------+
만약 MyISAM 또는 ARCHIVE 테이블이라면, INFORMATION_SCHEMA에서 제공되는 통계를 신뢰 할 수 있습니다.
SELECT
  partition_name part,
  partition_expression expr,
  partition_descript-xion descr,
  table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = schema()
  AND TABLE_NAME='employees';
+------+------------------+-------------------+------------+
| part | expr            | descr            | table_rows |
+------+------------------+-------------------+------------+
| p01  | gender,hire_date | 'F','1990-01-01'  |      66212 |
| p02  | gender,hire_date | 'F','2000-01-01'  |      53832 |
| p03  | gender,hire_date | 'F',MAXVALUE      |          7 |
| p04  | gender,hire_date | 'M','1990-01-01'  |      98585 |
| p05  | gender,hire_date | 'M','2000-01-01'  |      81382 |
| p06  | gender,hire_date | 'M',MAXVALUE      |          6 |
| p07  | gender,hire_date | MAXVALUE,MAXVALUE |          0 |
+------+------------------+-------------------+------------+
InnoDB 엔진을 사용하는 경우에는, 위의 값은 근사치이며, 정확한 값이 필요하다면, INFORMATION_SCHEMA는 신뢰 할 수 없습니다.
한 가지 의문점은 위의 모든 설명과 성능에 관련된 것들은, 아직까지 미지수입니다. 이와 같은 partition pruning을 개선 사항으로 볼 수 있겠습니까? 답변은 분명히 '그렇다' 입니다. 5.1 버전과 달리, 5.5에서는 날짜뿐만 아니라, 모든 파티션은 COLUMNS 키워드로 정의된 partition pruning을 사용하는, 두 가지 기능의 파티션닝을 지원합니다. 다음을 보면:
select count(*) from employees where gender='F' and hire_date < '1990-01-01';
+----------+
| count(*) |
+----------+
|    66212 |
+----------+
1 row in set (0.05 sec)
explain partitions select count(*) from employees where gender='F' and hire_date < '1990-01-01'\G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: employees
  partitions: p01
        type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
        rows: 300024
        Extra: Using where
첫 번째 파티션을 정의하는 조건을 사용하여, 매우 최적화된 쿼리를 얻었습니다. 그 뿐만 아니라, 일부 조건은 불필요한 부분이 제거된 파티션으로 부터 (검색) 혜택을 받았습니다.
select count(*) from employees where gender='F';
+----------+
| count(*) |
+----------+
|  120051 |
+----------+
1 row in set (0.12 sec)
explain partitions select count(*) from employees where gender='F'\G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: employees
  partitions: p01,p02,p03,p04
        type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
        rows: 300024
        Extra: Using where
이것은 복잡한 인덱스에 사용된 것과 같은 알고리즘 입니다. 조건이 인덱스의 왼쪽 부분을 의미 하는 경우, MySQL은 그것을 사용합니다. 마찬가지로, 당신은 파티션 정의의 왼쪽 부분을 참조 한다면, 가능한 최대한 불필요한 부분을 제외합니다. 파티션 정의의 오른쪽 부분을 참조하는 경우에는 복합 인덱스와 불필요한 부분이 제거된 파티션이 작동하지 않습니다.
select count(*) from employees where hire_date < '1990-01-01';
+----------+
| count(*) |
+----------+
|  164797 |
+----------+
1 row in set (0.18 sec)
explain partitions select count(*) from employees where hire_date < '1990-01-01'\G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: employees
  partitions: p01,p02,p03,p04,p05,p06,p07
        type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
        rows: 300024
        Extra: Using where
첫 번째를 사용하지 않고, 파티션 정의의 두 번째 부분을 참조하면, 전체 테이블을 검색합니다. 이것은 항상 파티션 생성과 쿼리 디자인을 할 때, 고려해 두어야 합니다.
사용성 개선: TRUNCATE PARTITION


파티션 ID 가장 매력적인 기능 중 하나는 거의 즉시 대량의 데이트를 제거하는 능력입니다. 이 스키마는 DBA들이 로그 데이터를 기록하는 날짜를 기준으로 정의된 파티션된 테이블에서, 정기적으로 오래된 기록이 보관된 파티션을 삭제하는 방법으로 매우 인기를 끌고 있습니다. 이 방법은 아주 잘 작동합니다. 첫 번째 (가장 오래된 기록을 가진) 파티션을 삭제 하고, 끝에 (최신 기록을 저장할) 새로운 파티션을 추가합니다.
모두 좋습니다. 단지 끝부터 잘라내야 합니다. 하지만 중간에 있는 파티션을 제거해야하는 경우에는 상황이 원활하게 진행되지 않습니다. 이런 경우에 대해서 아무 문제없이 파티션을 삭제 할 수 있습니다. 그러나 단지 파티션을 비우는 것을 원한다면, 상당히 어러운 문제에 직면한 것입니다. 파티션으로 부터 모든 레코드를 제거하기 위해서 할 수 있는 것:
DELETE 구문을 사용하여, 잘려진 파티션의 장점의 대부분을 포기하는 방법
DROP PARTITION을 사용한 다음, REORGANIZE PARTITIONS으로 다시 만들려면, 이전의 선택보다 더 많은 부하가 발생합니다.
MySQL 5.5버전에서는 DROP PARTITION과 같지만, 파티션이 삭제되는 대신에 다시 사용할 준비가 되어지는 TRUNCATE PARTITION 구문을 소개합니다.
TRUNCATE PARTITION 구문은 모든 DBA의 도구상자에 있어야 합니다.
더 미세한 조정: TO_SECONDS


보너스로 파티션 기능 개선 패키지는 날짜와 시간 컬럼을 조작하는 새로운 함수가 준비되어 있습니다. TO_SECONDS와 같은 기능으로 날짜 시간 컬럼을 "0"년 부터 초 단위로 변환할 수 있습니다. 만약 하루 보다 작은 시간 간격으로 파티션하려는 경우에 유용한 기능입니다.
향상된 기능의 나머지 부분과 같이, TO_SECONDS으로 파티션을 잘라내는 것은 날짜 함수의 수가 세 가지가 됨으로써, 효율적인 파티션닝을 할 수 있습니다.
TO_DAYS는, FROM_DAYS로 변환할 수 있지만, TO_SECONDS에 대한 그런 기능은 없습니다. 하지만 이런 것을 만드는 것은 어렵지 않습니다.
drop function if exists from_seconds;
delimiter //
create function from_seconds (secs bigint)
returns DATETIME
begin
    declare days INT;
    declare secs_per_day INT;
    DECLARE ZH INT;
    DECLARE ZM INT;
    DECLARE ZS INT;
    set secs_per_day = 60 * 60 * 24;
    set days = floor(secs / secs_per_day);
    set secs = secs - (secs_per_day * days);
    set ZH = floor(secs / 3600);
    set ZM = floor(secs / 60) - ZH * 60;
    set ZS = secs - (ZH * 3600 + ZM * 60);
    return CAST(CONCAT(FROM_DAYS(days), ' ', ZH, ':', ZM, ':', ZS) as DATETIME);
end //
delimiter ;
이런 새로운 무기로 무장한 우리는 하루도 안 되는 시간을 기준으로 정의한 파티션과 같은 테이블을 자신 있게 만들 수 있습니다.
CREATE TABLE t2 (
  dt datetime
)
PARTITION BY RANGE (to_seconds(dt))
(
  PARTITION p01 VALUES LESS THAN (to_seconds('2009-11-30 08:00:00')) ,
  PARTITION p02 VALUES LESS THAN (to_seconds('2009-11-30 16:00:00')) ,
  PARTITION p03 VALUES LESS THAN (to_seconds('2009-12-01 00:00:00')) ,
  PARTITION p04 VALUES LESS THAN (to_seconds('2009-12-01 08:00:00')) ,
  PARTITION p05 VALUES LESS THAN (to_seconds('2009-12-01 16:00:00')) ,
  PARTITION p06 VALUES LESS THAN (MAXVALUE)
);
show create table t2\G
*************************** 1. row ***************************
      Table: t2
Create Table: CREATE TABLE `t2` (
  `dt` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE (to_seconds(dt))
(PARTITION p01 VALUES LESS THAN (63426787200) ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN (63426816000) ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN (63426844800) ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN (63426873600) ENGINE = MyISAM,
 PARTITION p05 VALUES LESS THAN (63426902400) ENGINE = MyISAM,
 PARTITION p06 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
COLUMNS 키워드를 사용하지 않기 때문에 (그리고 우리가 COLUMNS 과 함수의 홈합을 허용하지 않은 이유로 할 수 없었던) TO_SECONDS 함수의 결과로 정의되어진 테이블 안에 값을 기록되었습니다.
그러나 새로운 기능덕분에, 오래된 블로그 포스트에서 본것 같은 값을 변환하지 않아도 사람이 읽을 수 있는 값을 사용할 수 있습니다.
select
  partition_name part,
  partition_expression expr,
  from_seconds(partition_descript-xion) descr,
  table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
    TABLE_SCHEMA = 'test'
    AND TABLE_NAME='t2';
+------+----------------+---------------------+------------+
| part | expr          | descr              | table_rows |
+------+----------------+---------------------+------------+
| p01  | to_seconds(dt) | 2009-11-30 08:00:00 |          0 |
| p02  | to_seconds(dt) | 2009-11-30 16:00:00 |          0 |
| p03  | to_seconds(dt) | 2009-12-01 00:00:00 |          0 |
| p04  | to_seconds(dt) | 2009-12-01 08:00:00 |          0 |
| p05  | to_seconds(dt) | 2009-12-01 16:00:00 |          0 |
| p06  | to_seconds(dt) | 0000-00-00 00:00:00 |          0 |
+------+----------------+---------------------+------------+
요약


MySQL 5.5버전은 파티션을 사용하는 사용자에게 확실히 좋은 소식입니다. (응답 시간과 같은 성능을 평가하는 경우라면) 성능에 직접적인 개선은 없었지만, 기능향상으로 사용이 쉬워지고, 시간을 절약할 수 있는 새로운 TRUNCATE PARTITION 구문과 같은 결과로 DBA나 최종 사용자의 시간을 많이 절약할 수 있습니다.
출처: http://www.mysqlkorea.co.kr/gnuboard4/bbs/board.php?bo_table=develop_05&wr_id=6
원문 : http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

댓글목록

등록된 댓글이 없습니다.

게시물 검색
전체 23건 1 페이지
  • mongodb 사용하기 조회, 추가, 삭제
    명령 프롬프트 상에서 mongo라고 입력해주면 mongo 셸로 접속됨 여기서 use 명령어로 데이터베이스를 선택해줘야함  insert() : 데이터 추가  find()  : 자료 조회 자료조회할때 pretty() 메소드 사용하면 조금더 예쁘게 출력됨 find()만 사용했을때  find().pretty() 사용했을때  remove() : 데이터 삭제 정규표현식 방법으로 소녀라는 단어를 포함한 모든자료를 삭제하기db.users.remove({name:/소녀/}) …
    작성자최고관리자 시간 07-06 조회 281
  • MySQL 현재 접속자 보기 및 MYSQL 모니터링 방법
    출처 : http://www.albumbang.com/board/board_view.jsp?board_name=free&no=139가. 모니터링 및 초기화 명령어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 variableswait_timeout - 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )thread_cache_size - thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.max_connections - 최대 동시 접속 가능 수그외에 status 또는 system variables 값은 참고의 Mysql 메뉴얼을 참조해 주십시요.…
    작성자최고관리자 시간 06-29 조회 328
  • MongoDB 튜토리얼
    MongoDB는 오픈 소스 문서 데이터베이스이자 NoSQL 데이터베이스를 이끌고 있습니다. MongoDB는 C ++로 작성되었습니다. 이 튜토리얼은 고도로 확장 가능하고 성능 지향적 인 데이터베이스를 생성하고 배치하는 데 필요한 MongoDB 개념에 대한 훌륭한 이해를 제공합니다. …
    작성자최고관리자 시간 06-19 조회 303
  • table 앞수(전 행) 뒷수(후 행) 값 가져오기
    SELECT LAG(RN) OVER (ORDER BY RN)앞수,   RN 해당수,   LEAD(RN) OVER (ORDER BY RN)뒷수 FROM TABLE…
    작성자최고관리자 시간 02-01 조회 387
  • R로 크롤링하기 - 보배드림 예제
    크롤링은 기본적인 과정은 web의 html 파일을 가져온 후, 파싱(parsing)을 해서 원하는 데이터에 접근하게 됩니다. parsing 방법에는 html 태그 혹은 css나 id 를 통해 원하는 데이터에 접근하는 방식과 html의 트리 구조를 이용하여 접근하는 XML 방식이 있습니다. 본 포스팅에서는 태그와 css를 이용해 접근하는 방식을 사용해 보겠습니다.먼저, rvest 패키지를 설치합니다.install.packages('rvest')보배 드림 사이트 바로 긁어와 보기본 실습에서는 중고차 사이트인 보배드림을 예로 들겠습니다. 아래는 해당 사이트의 html 태그를 가져와서 parsing하는 작업입니다.#내가 수집하길 원하는 페이지 주소
    url <- "http://www.bobaedream.co.kr/cyber/CyberCar.php?gubun=K&page=1"
    usedCar <- read_html(url) #해당 url 페이지의 html tag를 가져와서 parsing함.
    usedCar## {xml_document}
    ## <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    ## [1] <head>\n <title>중고차, 중고자동차, 중고차매매 | 보배드림 국산차매장</title>\n <meta htt ...
    ## [2] <body>&#13;\n<!--wrap-->&#13;\n<div class="wrap">&#13;\n&#13;\n<link ...이제부터 사이트 구조를 잘 파악해야 하는데, 예를 들어 제가 관심있는 정보가 자동차 종(type), 변속기(transmission), 연료종류(fuel), 거리(distance) 라고 합시다. 이 정보는 모두 .carinfo 라는 class 안에 들어가 있는 걸 확인할 수 있습니다. 이건 어떻게 확인하냐구요? 그건 chrome 이나 explorer 에서 F12키를 누르면 아래와 같이 개발자 도구창이 뜹니다.  이를 통해, 현재 웹 페이지의 html을 볼 수 있는데요. 좌측 상단에 네모위에 화살표가 그려져 있는 아이콘이 있는데 그걸 클릭후 아래와 같이 웹 페이지에서 원하는 부분을 선택해보세요. 그럼 웹 페이지에 대응하는 html 태그를 개발자 모드 창에서 확인할 수 있습니다.위 그림과 같이 우리가 원하는 정보는 <td class = "carinfo"> ... </td> 안에 묶여 있는 걸 알 수 있습니다. 따라서 먼저 carinfo 안의 모든 정보를 가져와 보겠습니다. #parsing한 usedCar 에서 css가 'carinfo' 인 것을 찾아라.
    carInfos <- html_nodes(usedCar, css='.carinfo')
    head(carInfos)## {xml_nodeset (6)}
    ## [1] <td class="carinfo"><a href="/cyber/CyberCar_view.php?no=640718&amp; ...
    ## [2] <td class="carinfo"><a href="/cyber/CyberCar_view.php?no=641415&amp; ...
    ## [3] <td class="carinfo"><a href="/cyber/CyberCar_view.php?no=641410&amp; ...
    ## [4] <td class="carinfo"><a href="/cyber/CyberCar_view.php?no=641379&amp; ...
    ## [5] <td class="carinfo"><a href="/cyber/CyberCar_view.php?no=641386&amp; ...
    ## [6] <td class="carinfo"><a href="/cyber/CyberCar_view.php?no=641421&amp; ...따라서 50개의 ‘.carinfo’ tag가 있기 때문에, carInfos에는 50개의 값이 벡터 형태로 담깁니다. 먼저 첫번째 경우만 예를 들어볼게요.#가장 첫번째 cafInfos로부터 정보 추출해보면,
    carInfos[1] %>% html_nodes('.title') %>% html_text()## [1] "쌍용 티볼리 에어 1.6 디젤 IX"이처럼 제목이 잘 출력된 것을 알 수 있습니다. 그런데 50개 모든 제목을 추출하고 싶으면 carInfos[1] 에서 index만([1]) 제거해 주면 됩니다. 자동으로 50개의 원소에 함수를 적용해 주는 것이죠(마치 apply 처럼)titles <- carInfos %>% html_nodes('.title') %>% html_text()
    head(titles)## [1] "쌍용 티볼리 에어 1.6 디젤 IX"
    ## [2] "현대 YF쏘나타 Y20 프리미어 최고급형"
    ## [3] "르노삼성 QM3 RE"
    ## [4] "현대 그랜져TG 아제라"
    ## [5] "기아 포르테 쿱 2.0 CVVT 프레스티지"
    ## [6] "기아 K7 VG270 럭셔리 프리미엄"변속기, 연료 정보 등은 sub_01이라는 class 안에 있군요. 따라서carDetainInfo <- carInfos %>% html_nodes('.sub_01') %>% html_text()
    head(carDetainInfo)## [1] "자동ㅣ디젤ㅣ3,700 km" "자동ㅣ가솔린ㅣ104,000 km"
    ## [3] "자동ㅣ디젤ㅣ32,000 km" "자동ㅣ가솔린ㅣ54,440 mi"
    ## [5] "수동ㅣ가솔린ㅣ32,000 km" "자동ㅣ가솔린ㅣ120,000 km"흠… 그런데! “자동ㅣ가솔린ㅣ41,000 km” 과 같이 하나의 문자에 정보가 묶여 있네요. 이를 특정 기호를 기준으로 분리해야 우리가 최종적으로 원하는 결과를 얻을 수 있을 것 같습니다.stringr이때, 사용하는게 Hadley아저씨("http://hadley.nz/")가 만든 string다루는 library, stringr 입니다.stringr을 사용하면 문자열을 특정 기호를 기준으로 분할 하거나, 특정 패턴(정규표현식)의 문자를 다른 문자로 바꿔주는 등 string을 다양하게 다룰 수 있습니다.install.packages('stringr')library(stringr)
    carInfos[1] %>% html_nodes('.sub_01') %>% html_text()## [1] "자동ㅣ디젤ㅣ3,700 km"1) ‘ㅣ’ 이라는 문자를 기준으로 나누고 싶을때는? : str_split(문자, ‘분리하고 싶은 문자’)(주의할 것은 키보드에 있는 | 표시와는 다른 문자에요! 복붙 해주세요.)str_split(carInfos[1] %>% html_nodes('.sub_01') %>% html_text(), 'ㅣ')## [[1]]
    ## [1] "자동" "디젤" "3,700 km"2) 쉼표, 문자를 제거하고 싶다면? : str_replace(문자, ‘제거하고 싶은 패턴’,‘대체하고 싶은 패턴’)아래 예제를 몇 개 볼게요.str_replace('20,000km', '0', '')## [1] "2,000km"str_replace_all('20,000km', '0', '')## [1] "2,km"str_replace_all('20,000Km', '[a-zA-Z]', '') #영어 소문자, 대문자 모두## [1] "20,000"str_replace_all('100,운용리스','[,가-하]','') #한글도 마찬가지로 가~하까지 모두## [1] "100"str_replace_all('20,000Km', '[,Km]', '')## [1] "20000"자 이제, 다시 정리해 볼게요.#먼저 첫페이지의 모든 제목 정보를 가져오는 명령어는
    titles <- carInfos %>% html_nodes('.title') %>% html_text()변속기, 연료, 거리는 “자동ㅣ가솔린ㅣ41,000 km”과 같이 특정 문자로 분할하기 위해 lapply를 적용합니다.splitFunction <- function(row){
    return(str_split(row, 'ㅣ')[[1]])
    }
    carDetailInfo <- lapply(carInfos %>% html_nodes('.sub_01') %>% html_text(), splitFunction)
    head(carDetailInfo)## [[1]]
    ## [1] "자동" "디젤" "3,700 km"
    ##
    ## [[2]]
    ## [1] "자동" "가솔린" "104,000 km"
    ##
    ## [[3]]
    ## [1] "자동" "디젤" "32,000 km"
    ##
    ## [[4]]
    ## [1] "자동" "가솔린" "54,440 mi"
    ##
    ## [[5]]
    ## [1] "수동" "가솔린" "32,000 km"
    ##
    ## [[6]]
    ## [1] "자동" "가솔린" "120,000 km"여기에 마무리 작업으로 뭘 하면 될까요? 각각의 데이터를 하나의 자료구조 즉, data.frame 형태로 바꿔야 겠죠. 먼저, 가져온 carDetailInfo는 list이기 때문에 이를 matrix로 바꾸면carDetailInfo <- matrix(unlist(carDetailInfo), ncol=3, byrow=T)
    head(carDetailInfo)## [,1] [,2] [,3]
    ## [1,] "자동" "디젤" "3,700 km"
    ## [2,] "자동" "가솔린" "104,000 km"
    ## [3,] "자동" "디젤" "32,000 km"
    ## [4,] "자동" "가솔린" "54,440 mi"
    ## [5,] "수동" "가솔린" "32,000 km"
    ## [6,] "자동" "가솔린" "120,000 km"#그리고 carDetailInfo 로부터 각각의 정보를 가져오자
    transmission <- carDetailInfo[,1]
    fuel <- carDetailInfo[,2]
    distance <- as.numeric(str_replace_all(carDetailInfo[,3], '[,a-z]','')) #숫자 형태로 바꿔주기 위해따라서 최종적으로 첫페이지에서 수집한 정보는 아래와 같이 data.frame으로 담을 수 있겠죠.data <- data.frame(titles, transmission, fuel, distance)
    head(data)## titles transmission fuel distance
    ## 1 쌍용 티볼리 에어 1.6 디젤 IX 자동 디젤 3700
    ## 2 현대 YF쏘나타 Y20 프리미어 최고급형 자동 가솔린 104000
    ## 3 르노삼성 QM3 RE 자동 디젤 32000
    ## 4 현대 그랜져TG 아제라 자동 가솔린 54440
    ## 5 기아 포르테 쿱 2.0 CVVT 프레스티지 수동 가솔린 32000
    ## 6 기아 K7 VG270 럭셔리 프리미엄 자동 가솔린 120000Quiz위와 비슷하게 가격, 조회수 등도 가져와 보세요for 문을 활용하여 10 page 까지 수집해 보세요.#hint:
    #아래 paste0 함수를 사용하여 뒤에 숫자만 바꿔주면서 수집하면 됩니다.
    url <- paste0("http://www.bobaedream.co.kr/cyber/CyberCar.php?gubun=K&page=",i) 출처: http://insightteller.tistory.com/entry/R로-크롤링하기-보배드림-예제 [Be a Insight teller] …
    작성자최고관리자 시간 07-05 조회 1418
  • rvest : R로 손쉽게 웹 스크래핑하기
    rvest: easy web scraping with RNovember 24, 2014 in Packagesrvest is new package that makes it easy to scrape (or harvest) data from html web pages, inspired by libraries like beautiful soup. It is designed to work with magrittr so that you can express complex operations as elegant pipelines composed of simple, easily understood pieces. Install it with:install.packages("rvest")rvest in actionTo see rvest in action, imagine we’d like to scrape some information about The Lego Movie from IMDB. We start by downloading and parsing the file with html():library(rvest)
    lego_movie <- html("http://www.imdb.com/title/tt1490017/")To extract the rating, we start with selectorgadget to figure out which css selector matches the data we want: strong span. (If you haven’t heard of selectorgadget, make sure to read vignette("selectorgadget") – it’s the easiest way to determine which selector extracts the data that you’re interested in.) We use html_node() to find the first node that matches that selector, extract its contents with html_text(), and convert it to numeric with as.numeric():lego_movie %>%
    html_node("strong span") %>%
    html_text() %>%
    as.numeric()
    #> [1] 7.9We use a similar process to extract the cast, using html_nodes() to find all nodes that match the selector:lego_movie %>%
    html_nodes("#titleCast .itemprop span") %>%
    html_text()
    #> [1] "Will Arnett" "Elizabeth Banks" "Craig Berry"
    #> [4] "Alison Brie" "David Burrows" "Anthony Daniels"
    #> [7] "Charlie Day" "Amanda Farinos" "Keith Ferguson"
    #> [10] "Will Ferrell" "Will Forte" "Dave Franco"
    #> [13] "Morgan Freeman" "Todd Hansen" "Jonah Hill"The titles and authors of recent message board postings are stored in a the third table on the page. We can use html_node() and [[ to find it, then coerce it to a data frame with html_table():lego_movie %>%
    html_nodes("table") %>%
    .[[3]] %>%
    html_table()
    #> X 1 NA
    #> 1 this movie is very very deep and philosophical mrdoctor524
    #> 2 This got an 8.0 and Wizard of Oz got an 8.1... marr-justinm
    #> 3 Discouraging Building? Laestig
    #> 4 LEGO - the plural neil-476
    #> 5 Academy Awards browncoatjw
    #> 6 what was the funniest part? actionjacksinOther important functionsIf you prefer, you can use xpath selectors instead of css: html_nodes(doc, xpath = "//table//td")). Extract the tag names with html_tag(), text with html_text(), a single attribute with html_attr() or all attributes with html_attrs().Detect and repair text encoding problems with guess_encoding() and repair_encoding().Navigate around a website as if you’re in a browser with html_session(), jump_to(), follow_link(), back(), and forward(). Extract, modify and submit forms with html_form(), set_values() and submit_form(). (This is still a work in progress, so I’d love your feedback.)To see these functions in action, check out package demos with demo(package = "rvest").…
    작성자최고관리자 시간 07-05 조회 734
  • R과 MySQL 데이터베이스 연결 방법
    데이터베이스에 연결된 R 환경은 데이터베이스의 저장 용량과 R의 계산능력 사용할 수 있습니다. 이번 포스팅에서는 MySQL과 R을 서로 연결하는 방법을 정리해 보겠습니다.MySQL과 R을 연결하기 위해서는 RMySQL 패키지를 이용합니다. 이 패키지는 R 환경을 위한 데이터베이스 인터페이스와 MySQL 드라이버를 포함한 패키지로 주로 다음과 같은 상황에서 사용할 수 있습니다.데이터가 MySQL 데이터베이스에 저장되어 있고 R 환경에서 이 데이터를 추출해 분석하고 싶을 때R에서 작성한 데이터를 MySQL의 데이터베이스에 저장하고 싶을 때MySQL에 ODBC드라이버를 인스톨하면 RODBC을 이용해도 같은 기능을 실현할 수 있습니다. 최근 버전의 RMySQL는 DBI 패키지에 구현된 데이터베이스 인터페이스 정의를 따르고 있습니다. MS WindowsR 2.12 이후로는 RMySQL의 바이너리 파일이 배포되지 않기 때문에 사용자가 컴파일해서 설치해야 합니다.필수 소프트웨어Rtools (R의 버전에 대응하는 것)MySQL Client C API library (MySQL Community Server 에도 포함되어있음)MySQL Community Server를 PC에 디폴트로 설치한 경우에는 MySQL Client C API library가 이미 들어가 있기 때문에 별도로 설치할 필요는 없습니다. 단, 별도의 호스트에 설치된 MySQL Server를 이용하는 경우에는 MySQL Client C API library를 별도로 설치해야 합니다.RMySQL 컴파일 순서R의 환경변수 MYSQL_HOME에 MySQL 설치 디렉터리를 8.3형식으로 지정합니다.MySQL Community Server 5.6의 MSI Installer판을 디폴트로 설치한 경우C:\Program Files\R\설치한 R 버전\etc 디렉터리에 Renviron.site라는 이름으로 텍스트 파일을 작성하여 이하의 내용을 입력합니다.MYSQL_HOME=C:/PROGRA~1/MySQL/MYSQLS~1.6MySQL이 설치된 디렉터리에 있는 lib\libmysql.lib을 lib\opt\libmysql.lib로 복사합니다.R에서 다음 명령을 실행합니다. 이때 R 콘솔에서 \Rtools\bin, \Rtools\gcc-4.6.3\bin 이하의 실행 파일들을 사용하게 되므로 두 디렉터리의 Path가 지정되어 있어야 합니다(윈도즈에서 Path를 추가하는 방법은 이곳을 참조하세요). 만약 RStudio에서 컴파일을 한다면 Path 지정 필요 없이 그냥 알아서 해 줍니다.> install.packages("DBI")
    > install.packages("RMySQL", type = "source")
    리눅스 (Ubuntu)다음 명령을 실행하여 R과 MySQL을 설치합니다.sudo apt-get update
    sudo apt-get install r-base
    sudo apt-get install r-dev
    sudo apt-get install r-cran-rmysql
    sudo apt-get install r-cran-dbi
    sudo apt-get install mysql-server my-client
    sudo apt-get install libmysqlclient-dev
    윈도즈의 삽질에 비하면 정말 간단하죠? R의 태생이 그런지라 불편 없이 R을 사용하고자 한다면 리눅스 환경을 강력 추천합니다.사용법R과 MySQL이 같은 호스트에서 실행되고 있고, test_db라는 데이터베이스에 test_user라는 사용자가 접근권한을 가지고 있다고 가정할 때, MySQL의 test_db 안의 test_table이라는 테이블의 내용을 쿼리를 이용하여 R의 데이터프레임 test.table로 저장하기 위해서는> library(RMySQL)
    > con <- dbConnect(m, dbname = "test_db", user = "test_user")
    > query.result <- dbSendQuery(con, "SELECT * FROM test_table")
    > test.table <- fetch(query.result)
    > dbDisconnect(con)
    여기서 dbConnect()는 데이터베이스와 접속을 개시하는 함수, dbDisconnect()는 접속을 종료하는 함수입니다. dbSendQuery()은 SQL문을 MySQL서버에 보내는 함수이며 그 응답을 받아 R의 데이터프레임으로 저장해 주는 함수가 fetch()입니다.dbSendQuery()와 fetch()를 동시에 수행하는 dbGetQuery() 함수를 사용하여도 무방합니다.> library(RMySQL)
    > con <- dbConnect(dbDriver("MySQL"), dbname = "test_db", user = "test_user",
    + password = "password")
    > dbListTables(con) #DB abc에 있는 테이블목록 확인
    > test.table <- dbGetQuery(con, "SELECT * FROM test_table")
    > dbDisconnect(con)
    R에서 작성된 result라는 데이터프레임을 MySQL 데이터베이스에 test_table2라는 테이블로 저장하기 위해서는 dbWriteTable() 함수를 이용합니다.> dbWriteTable(con, "test_table2", result, overwrite = TRUE)
    만약, MySQL 서버가 R과 같은 호스트에 있지 않고 dbserver.domain라는 호스트에서 운영되고 있다면、> library(RMySQL)
    > con <- dbConnect(dbDriver("MySQL"), host = "dbserver.domain", dbname = "test_db",
    + user = "test_user", password = "password")
    >
    R 언어에서 명시적으로 데이터베이스 사용자 및 암호를 표시할 때 보안상의 문제점을 일으킬 수 있으므로 로컬 디렉터리 구성 파일 (~/.my/cnf)에서 MySQL을 사용하는 사용자 그룹을 정의 할 수 있습니다.[local]
    user = root
    password = ultra_secret
    host = localhost

    [nerds]
    user = supernerd
    password = galaxy
    host = dbserver.domain
    그런 다음 R 사용자 그룹을 사용하여> library(RMySQL)
    > con <- dbConnect(MySQL(), group = "nerds", dbname = "test_db")
    > test.table <- dbGetQuery(con, "SELECT * FROM test_table")
    > dbDisconnect(con)
    지금까지 R과 MySQL을 연결하는 방법을 방법에 대해 정리해 보았습니다. 적절한 SQL 쿼리를 이용해 필요한 데이터를 만들 수 있다면 데이터 전체를 R로 불러와 데이터 조작을 하는 것보다 메모리의 효율적인 사용에 매우 큰 도움이 될 수 있습니다. …
    작성자최고관리자 시간 07-05 조회 343
  • mysql master slave 동기화
     서론응용 프로그램 혹은 웹 페이지의 작은 기능 단위 연동을 위해서는, rsync로 db 파일을 동기화하는 경우가 있었다.하지만 rsync 서비스도 정상인지 봐야 하고 스케줄링(crontab) 주기도 설정해야 하고 간혹 문제가 생기기도 하고 테이블 index가 꼬이거나 파일이 깨지는 등 번거로운 방법이다. 그래서 향후에는 작은 부분이더라도 Replication을 하기로 했다.특정 DB 혹은 특정 Table만 동기화 설정이 가능하기 때문이다. 선행 작업Master에서 Slave로 동기화하기를 원하는 DB(및 Table)은 미리 Dump를 떠서 최초 1번은 Slave에 생성해 놓아야 한다.  Master 서버 설정우선 server-id가 1로 설정되어 있는지 확인한다.Master는 보통 1로 설정한다. 그래야 Slave에서 부가적인 설정을 할 필요가 없다. # cat /etc/my.cnf -n | grep server-id67 server-id          = 1  이번엔 mysql에 접속해서 상태를 확인한다.  mysql> show master status;+---------------+----------+--------------+------------------+| File          | Position | Binlog_do_db | Binlog_ignore_db |+---------------+----------+--------------+------------------+| mysql-bin.189 | 91120973 |              |                  |+---------------+----------+--------------+------------------+ File, Position 값을 각각 메모해 둔다(mysql-bin.189, 91120973). 마지막으로 본 서버에 접속해서 동기화 받아갈 Slave 서버들에 권한을 주자.  mysql> GRANT REPLICATION SLAVE ON *.* TO 유저@'%' IDENTIFIED BY '패스워드';mysql> FLUSH PRIVILEGES; *.*은 DB.Table 이고 %는 ip 모두 허용... 인데 GRANT는 익숙한 구문이므로 자세한 설명은 생략한다.  Slave 서버 설정Master와 같이 server-id 값을 확인한다.는 Master와 다른 값을 가져야 하므로 2로 수정하는게 좋겠다. # cat /etc/my.cnf -n | grep server-id67 server-id          = 2  Slave의 mysql에 접속해서 동기화 받을 DB가 존재하는 Master 서버의 정보를 입력할 차례다. mysql> STOP SLAVE;mysql> CHANGE MASTER TO MASTER_HOST='192.168.xxx.xxx', MASTER_USER='유저',MASTER_PASSWORD='패스워드', MASTER_LOG_FILE='mysql-bin.189', MASTER_LOG_POS=91120973;mysql> START SLAVE; Master에서 메모한 2개의 값을 MASTER_LOG_FILE 부분과 MASTER_LOG_POS 부분에 대입한다. Slave가 Master에게서 제대로 동기화 받아오는지 확인해 보자. mysql> SHOW SLAVE STATUS \G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.xxx.xxx                                     ...(생략)... Waiting for master to send event 문구가 표시되면 정상이다.  여기서 일단 끝인데... 조금 더 세부적으로...모든 DB를 동기화하는 것이 아니라, 특정 DB 특정 Table만 동기화 하고 싶다면 아래와 같이 my.cnf 파일에 설정한다.  # vi /etc/my.cnf[mysqld]...(생략)...replicate-do-table = DB.Table1replicate-do-table = DB.Table2replicate-do-db = DB1replicate-do-db = DB2  Slave에 설정할 수도 있고 Master에 설정할 수도 있다.한 라인에 여러 값을 기입할 수는 없었다. 여러 라인에 각각 지정해야 한다.replication-ignore-table은 동기화 받지 않을 테이블만 기록하는 설정이다.이 외에도 여러 설정이 존재한다(더 자세한건 구글링을).참고 사항1. 기본적인 것이지만 iptables 등의 방화벽 설정에서 서버 ip, port를 open해야 한다.2. 역시 기본적인 것이지만 my.cnf 파일을 수정했다면 mysqld service를 재시작해야 한다.   출처: http://whiterussian.tistory.com/44 [화이트 러시안] …
    작성자최고관리자 시간 03-22 조회 398
  • CodeIgniter/ActiveRecord setup to use master + slave db 복제
    CODEIGNITER/ACTIVERECORD SETUP TO USE MASTER + SLAVE DB REPLICATIONAUG26This is how you can set up CodeIgniter to direct mysql queries to different read/write hosts in your db replicated environment, using a db_slave for your SELECT’s, and a db_master for the INSERT/UPDATE/DELETE queries.File: application/config/database.phpSpecify the different database hosts in the database config file:< ?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

    $active_group = "master";
    $active_record = TRUE;

    # db_master
    $db['master']['hostname'] = "host1";
    $db['master']['username'] = "username";
    $db['master']['password'] = "password";
    $db['master']['database'] = "exampledb";
    $db['master']['dbdriver'] = "mysql";
    $db['master']['dbprefix'] = "";
    $db['master']['pconnect'] = FALSE;
    $db['master']['db_debug'] = TRUE;
    $db['master']['cache_on'] = FALSE;
    $db['master']['cachedir'] = "";
    $db['master']['char_set'] = "utf8";
    $db['master']['dbcollat'] = "utf8_general_ci";

    #db_slave
    $db['slave']['hostname'] = "host2";
    $db['slave']['username'] = "username";
    $db['slave']['password'] = "password";
    $db['slave']['database'] = "exampledb";
    $db['slave']['dbdriver'] = "mysql";
    $db['slave']['dbprefix'] = "";
    $db['slave']['pconnect'] = FALSE;
    $db['slave']['db_debug'] = TRUE;
    $db['slave']['cache_on'] = FALSE;
    $db['slave']['cachedir'] = "";
    $db['slave']['char_set'] = "utf8";
    $db['slave']['dbcollat'] = "utf8_general_ci";
    ...File: application/core/My_Model.phpAdd this into My_Model:< ?php

    class MY_Model extends CI_Model {
    function __construct(){
    parent::__construct();
    $this->db_master = $this->load->database('default', TRUE);
    $this->db_slave = $this->load->database('default', TRUE);
    }


    }File: application/models/example_model.phpUse the read/write queries in your models like this:< ?php
    class example_model extends MY_Model {

    function example_model()
    {
    parent::MY_Model();
    }

    # read query
    function getSomething()
    {
    $query = $this->db_slave->get('mytable'); // db_slave
    return $query->result();
    }

    # write query
    function insertSomething()
    {
    $this->db_master->insert('mytable', $_POST); // db_master
    return $this->db_master->insert_id();
    }That´s it!…
    작성자최고관리자 시간 03-22 조회 458
  • php 두개의 DB 불러오기

     //db1 연결 함수 선언
     function db1($database = "db1"){
         $host = "host";
         $user = "userid";
         $pass = "password";

         $connect = @mysql_connect($host, $user, $pass) or die("ERROR!");
         mysql_query(" SET NAMES 'utf8' ");
         return $connect;
     }

     //db2 연결 함수 선언
     function db2($database = "db2"){
         $host = "host";
         $user = "userid";
         $pass = "password";

         $connect = @mysql_connect($host, $user, $pass) or die("ERROR!");
         mysql_query(" SET NAMES 'utf8' ");
         return $connect;
     }

     //connection resource variable
     $db1 = db1();
     $db2 = db2();

     $sql = "select * form test_table where userid = '88240'";
     $query = mysql_query($sql, db1); //db1의 test_table을 실행하고 싶을때
     $query = mysql_query($sql, db2);  //db2의 test_table을 실행하고 싶을때

    작성자최고관리자 시간 09-12 조회 1272
  • 문자 order by 특정필드 특정 순서 정의
    select * from pet order by field(species, 'cat', 'dog', 'bird');

     


    +----------+--------+---------+------+------------+------------+
    | name | owner | species | sex | birth | death |
    +----------+--------+---------+------+------------+------------+
    | Puffball | Diane | hamster | f | 1999-03-30 | NULL |
    | Slim | Benny | snake | m | 1996-04-29 | NULL |
    | Claws | Gwen | cat | m | 1994-03-17 | NULL |
    | Fluffy | Harold | cat | f | 1993-02-04 | NULL |
    | Buffy | Harold | dog | f | 1989-05-13 | NULL |
    | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
    | Fang | Benny | dog | m | 1990-08-27 | NULL |
    | Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
    | Whistler | Gwen | bird | N | 1997-12-09 | NULL |
    +----------+--------+---------+------+------------+------------+
    작성자최고관리자 시간 08-08 조회 1017
  • table 초기화
    truncate `tblName`;  truncate `tblName`;
    tblName 대신 table명을 실행하면 초기화된다. 초기화하기전엔 데이터의 중요성을 다시 한번 확인하기 바란다.…
    작성자최고관리자 시간 03-13 조회 857
  • phpMyAdmin 업로드 파일 용량 늘리기
    phpmyadmin 에서는 업로드 용량에 제한이 되어 있다. 초기값은 2M 이다.

    이 값을 변경하기 위해서는 /etc/php5/apache2에 보면 php.ini 파일이 있을 것이다.

    거기서 upload_max_filesize = 2M 을 적당한 값으로 수정한다.



    phpmyadmin import 용량 늘리기(mysql 용량이 업로드제한 용량보다 클때)
    ->usr/local/php/lin/php.ini 파일을 열어 post_max_size=2M를 조정한다.



    phpmyadmin import시 시간초과 일때
    ->usr/local/php/lin/php.ini 파일을 열어 mysql.connect_timeout =60 시간을 수정한다.…
    작성자최고관리자 시간 09-11 조회 3963
  • mysql UTF8 설정
    우분투 경우

    터미널에서 ...
    cd /etc/mysql
    ls -l
    sudo gedit ./my.cnf

    1. my.cnf 파일에 아래 내용 추가
    [client]
    default-character-set=utf8

    [mysqld]
    character-set-client-handshake=FALSE
    init_connect="SET collation_connection = utf8_general_ci"
    init_connect="SET NAMES utf8"
    default-character-set=utf8
    character-set-server=utf8
    collation-server=utf8_general_ci

    [mysqldump]
    default-character-set=utf8

    [mysql]
    default-character-set=utf8

    저장

    2. mysql을 재시작.
    $sudo /etc/init.d/mysql restart

    3. 변경 여부 확인.
    $mysql -u id -p
    mysql> status…
    작성자최고관리자 시간 04-13 조회 936
  • MERGE 형식의 테이블
    MERGE 형식의 테이블은 MRG_MyISAM 테이블이라고도 한다.
    모아 사용할 모든 테이블은 동일한 컬럼을 가지며 key 정보를 가지고 있는 필요한 몇 개의 MyISAM 테이블을 모아서 만든 테이블이 MERGE 테이블이며, MyISAM 테이블처럼 사용 할 수 있다.
    그러므로 오직 select, delete, update만 테이블 집합인 MERGE 테이블에서 수 행할 수 있다.
    MERGE 테이블을 drop하더라도 원래의 테이블의 데이터는 그대로 있고 오직 MERGE 테이블 데이터만 drop된다.
    또한 DELETE FROM merge_table처럼 where 조건 없이 실행하면 해당 테이블의 모든 데이터가 삭제된다.

    컬럼이 서로 다르게 압축되었거나, 아주 동일한 컬럼을 가지고 있지 않거나, key 순서가 테이블마다 다르게 가질 경우에도 테이블들을 merge할 수 없다. 그러나 myisampack로 압축된 테이블은 merge할 수 있다.

    MERGE 테이블을 만들면 테이블 정의 파일인 '.frm'과 테이블 목록 파일인 '.MRG'이 생기며, '.MRG' 파일에는 인덱스 파일 목록인 '.MYI'의 정보도 들어 있다. MERGE 테이블은 물론이고 MERGE 테이블에 모아 쓸 모든 테이블도 물론 같은 데이터베이스에 있어야 한다.

    MERGE 테이블의 장점:
    #&8226; 한 무리의 등록 테이블을 쉽게 관리한다.
    • 속도가 빠르다.
    • 효과적으로 탐색한다.
    • 효과적으로 수리한다.
    • 여러 파일을 하나의 파일처럼 순간적으로 매핑한다.
    • 큰 테이블을 join하기 보다 MERGE 테이블로 하면 빠르고 디스크 공간을 절약한다.
    • 운영체제상의 파일 크기 한계까지 사용할 수 있다.
    • 하나의 테이블에 MERGE로 alias/synonym을 여러 개 붙일 수 있다.

    MERGE 테이블의 단점:
    • 동일한 MyISAM 테이블에만 사용된다.
    • REPLACE 문을 사용할 수 없다.
    • MERGE 테이블은 file descriptor를 더 많이 사용한다. 예를 들어, 10개의 테이블을 매
    핑하여 만든 MERGE 테이블을 10명이 사용한다면, 10*10+10라고 하는 file descriptor가
    요구되는데 이는 (10개의 데이터파일) + (10명의 사용자) + (10개의 공유 인덱스 파일)
    이 필요하기 때문이다.
    • key를 읽는 것이 느리다. 그 이유는 key를 읽을 때 MERGE storage engine은 모든 해
    당 테이블에 대해서 key를 읽겠다고 한 다음, 주어진 key에 어느 것이 가장 근접하게
    매치되는지 체크하기 때문이다.
    • MERGE 테이블에 매핑된 해당 테이블이 'open'되어 있을 경우에는 DROP TABLE, ALTER
    TABLE, where절 없는 DELETE FROM tbl_name, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE
    TABLE, ANALYZE TABLE을 실행할 수 없다. MERGE 테이블이 원래의 테이블이 참조하고
    있는 중에 이러한 명령을 실행하면 예기치 못한 결과가 된다. 그러므로 이러한 문제를
    피하려면, FLUSH TABLES 명령을 실행하고 나서 하면 된다.

    MERGE 테이블을 만들 때 UNION(list-of-tables)를 사용하여 만들어야 하는데, MERGE 테이블 들어가는 UNION 문의 첫 번째 테이블과 마지막 테이블은 INSERT_METHOD를 지정할 수도 있다. INSERT_METHOD를 지정하지 않거나 NO를 지정하지 않으면, MERGE 테이블 에 INSERT 문을 실행하면 에러메시지를 받게된다.
    【예제】
    mysql> CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
    Query OK, 0 rows affected (0.03 sec)

    mysql> CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
    Query OK, 0 rows affected (0.01 sec)

    mysql> INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
    Query OK, 3 rows affected (0.02 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    mysql> INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
    Query OK, 3 rows affected (0.01 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    mysql> CREATE TABLE total (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20))
    -> TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
    Query OK, 0 rows affected (0.01 sec)

    mysql> SELECT * FROM total;
    +---+---------+
    | a | message |
    +---+---------+
    | 1 | Testing |
    | 2 | table |
    | 3 | t1 |
    | 1 | Testing |
    | 2 | table |
    | 3 | t2 |
    +---+---------+
    6 rows in set (0.00 sec)
    mysql>

    예제에서 컬럼 a는 PRIMARY KEY로 선언되었지만, UNIQUE하지 않은 것처럼 MERGE 테 이블은 MyISAM 테이블에서 데이터를 가져오기 때문에 UNIQUE를 강요할 수 없다.
    MERGE 테이블을 re-map하려면 다음과 같은 방법중의 하나를 사용한다.

    방법1) MERGE 테이블을 DROP하고 MERGE 테이블을 다시 만든다.
    방법2) ALTER TABLE tbl_name UNION(...)을 사용한다.
    방법3) '.MRG' 파일을 변경하고 MERGE 테이블에 대해 FLUSH TABLE을 실행한다.

    방법3의 쉘 프롬프트에서 '.MRG' 파일을 직접 다루는 방법은 다음 예와 같다.

    【예제】
    # cd /export/home/mysql/var/jijoeDB
    # rm total.MRG ☜ 고치기 위해서
    # vi total.MRG
    t1
    t2
    # chown mysql:mysql total.MRG
    # chmod 660 total.MRG
    # mysqladmin -p flush-tables

    mysql> SELECT * FROM total;
    +---+---------+
    | a | message |
    +---+---------+
    | 1 | Testing |
    | 2 | table |
    | 3 | t1 |
    | 1 | Testing |
    | 2 | table |
    | 3 | t2 |
    +---+---------+
    6 rows in set (0.00 sec)
    mysql>…
    작성자최고관리자 시간 02-01 조회 894

회원로그인

Copyright © 2001-2016 ITNANUM. All Rights Reserved..