Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード

2017-10-11

Amazon Linux に Sysbench をインストールする

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

参考

2017-09-18

sqlplus から Oracle Database に接続すると ORA-21561 で失敗する

事象

sqlplus から Oracle Database に接続すると ORA-21561 で失敗する。

% sqlplus awsuser/******@******.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 18 22:37:57 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-21561: OID generation failed

ORA-21561: OID生成に失敗しました

原因: 渡されたハンドルが有効でない可能性があります。

処置: env、svcハンドルの妥当性を確認してください。

ORA-19999からORA-24279

原因

  • /etc/hosts にホスト名のマッピングがない、もしくは間違っている。

Incorrect /etc/hosts mapping for the client machine or no mapping at all.

Sfaturi Oracle / Tips and tricks OracleDBA: ORA-21561 : OID generation failed

対処

  • ホスト名を確認する
% uname -n
******.***.foo.com
  • /etc/hosts に追記する
% sudo vi /etc/hosts
127.0.0.1	localhost ******.***.foo.com

環境


参考

Mac OS X に Oracle Instant Client をインストールする

ダウンロード


インストール

% unzip instantclient-basic-macos.x64-12.1.0.2.0.zip
Archive:  instantclient-basic-macos.x64-12.1.0.2.0.zip
  inflating: instantclient_12_1/BASIC_README
  inflating: instantclient_12_1/adrci
  inflating: instantclient_12_1/genezi
  inflating: instantclient_12_1/libclntsh.dylib.12.1
  inflating: instantclient_12_1/libclntshcore.dylib.12.1
  inflating: instantclient_12_1/libnnz12.dylib
  inflating: instantclient_12_1/libocci.dylib.12.1
  inflating: instantclient_12_1/libociei.dylib
  inflating: instantclient_12_1/libocijdbc12.dylib
  inflating: instantclient_12_1/libons.dylib
  inflating: instantclient_12_1/liboramysql12.dylib
  inflating: instantclient_12_1/ojdbc6.jar
  inflating: instantclient_12_1/ojdbc7.jar
  inflating: instantclient_12_1/uidrvci
  inflating: instantclient_12_1/xstreams.jar
% unzip instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
Archive:  instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
  inflating: instantclient_12_1/SQLPLUS_README
  inflating: instantclient_12_1/glogin.sql
  inflating: instantclient_12_1/libsqlplus.dylib
  inflating: instantclient_12_1/libsqlplusic.dylib
  inflating: instantclient_12_1/sqlplus
  • 移動する
% sudo mkdir -p /opt/oracle
% sudo mv instantclient_12_1 /opt/oracle
  • パスを通す
% vi .zshrc.local
export PATH=$PATH:/opt/oracle/instantclient_12_1

接続する

$ sqlplus awsuser/******@******.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL

2017-09-10

Oracle Database の PL/SQL を MySQL にどう移行するか

はじめに

Oracle Database の PL/SQLMySQL への移行方法を説明します(MySQL 5.0 からストアドプロシージャに対応している)。

AWS Schema Conversion Tool (SCT) という GUI デスクトップアプリケーション(Windwos/Mac OS X/Linux版)をインストールし、移行元の Oracle Database に接続して自動的にPL/SQLMySQL のルーチンに変換することができます。

SCT による変換方法は Oracle Database を Amazon Aurora に移行する方法 | Amazon Web Services ブログ をご覧ください。


移行先として適切なサービスを選ぶ

本題に入る前に Oracle Database からの移行先として MySQL、PostgreSQL、Redshift のどれが適切か説明します。

経験上、エンタープライズで Oracle Database を使っている場合は PostgreSQL が向いているケースが多いです。アクセスブロック数が少ないSQLのみで結合もネステッドループのみで問題ないシステムは MySQL が向いています。Exadata で OLTP(トランザクション) とOLAP(分析)を共存させている場合は PostgreSQL で OLTP、Redshift で OLAP(分析)という使い分けをオススメします。

MySQL が向いているケース
  • OLTP でシンプルでアクセスブロック数が少ないSQLが大半。
  • 結合はネステッドループのみで問題ない程度の軽い SQL のみ。
  • さらに結合も必要なく、キーのみにでアクセスするケースでは DynamoDB が向いています。
PostgreSQL が向いているケース
  • OLTP でも結合対象行数を絞れず HASH JOIN が必要な重いSQLが多い。
  • エンタープライズで Oracle Database を使っているシステムはこのケースが一番多い印象。
Redshift が向いているケース
  • Exadata で集計処理を投げていて Smart Scan を享受しているようなケースでは Redshift が向いている。
  • Exadata で OTLP(トランザクション処理)と分析の両方の用途で使っている場合は、OLTP は RDS で、分析は Redshift というように使い分けるアーキテクチャにする。

Oracle Database と MySQL のプロシージャ・ファンクションの構文の違い

SCT でプロシージャを変換した例

aws-database-migration-samples/generate_tickets.pls at master ? awslabs/aws-database-migration-samples ? GitHub を変換した例です。

  • Oracle Database(変換前)
procedure generate_tickets(P_event_id IN NUMBER) as
  CURSOR event_cur(P_ID NUMBER) IS
  SELECT id,location_id
  FROM   sporting_event
  WHERE  ID = P_ID;

  standard_price NUMBER(6,2);

BEGIN
  standard_price := DBMS_RANDOM.VALUE(30,50);

  FOR event_rec IN event_cur(P_event_id) LOOP
    INSERT /*+ APPEND */ INTO sporting_event_ticket(id,sporting_event_id,sport_location_id,seat_level,seat_section,seat_row,seat,ticket_price)
    SELECT sporting_event_ticket_seq.nextval
      ,sporting_event.id
      ,seat.sport_location_id
      ,seat.seat_level
      ,seat.seat_section
      ,seat.seat_row
      ,seat.seat
      ,(CASE
         WHEN seat.seat_type = 'luxury' THEN 3*standard_price
         WHEN seat.seat_type = 'premium' THEN 2*standard_price
         WHEN seat.seat_type = 'standard' THEN standard_price
         WHEN seat.seat_type = 'sub-standard' THEN 0.8*standard_price
         WHEN seat.seat_type = 'obstructed' THEN 0.5*standard_price
         WHEN seat.seat_type = 'standing' THEN 0.5*standard_price
      END ) ticket_price
    FROM sporting_event
       ,seat
    WHERE sporting_event.location_id = seat.sport_location_id
    AND   sporting_event.id = event_rec.id;
  END LOOP;
END;
  • MySQL(変換後)
    • コメントが入っている箇所は Oracle Database の構文に相当するものが MySQL になかった箇所で、この部分は自分でワークアラウンドを記述する必要があります。
CREATE PROCEDURE DMS_SAMPLE.GENERATE_TICKETS(IN par_P_EVENT_ID DOUBLE)
BEGIN
    DECLARE var$ID DOUBLE;
    DECLARE var$LOCATION_ID DOUBLE;
    DECLARE par_P_ID DOUBLE;
    DECLARE var_standard_price DECIMAL (6, 2);
    DECLARE done INT DEFAULT FALSE;
    DECLARE event_cur CURSOR FOR SELECT
        ID,a
        FROM SPORTING_EVENT
        WHERE ID = par_P_ID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done := TRUE
    /*
    [340 - Severity CRITICAL - MySQL doesn't support the SYS.DBMS_RANDOM.VALUE(NUMBER,NUMBER) function. Create a user-defined function.]
    standard_price := DBMS_RANDOM.VALUE(30,50)
    */;
    SET par_P_ID := par_P_event_id;
    OPEN event_cur;

    read_label:
    LOOP
        FETCH event_cur INTO var$ID, var$LOCATION_ID;

        IF done THEN
            LEAVE read_label;
        END IF;
        INSERT INTO SPORTING_EVENT_TICKET
            (ID, SPORTING_EVENT_ID, SPORT_LOCATION_ID, SEAT_LEVEL, SEAT_SECTION, SEAT_ROW, SEAT, TICKET_PRICE)
            SELECT
                aws_oracle_ext.sequence$nextval('SPORTING_EVENT_TICKET_SEQ', 'DMS_SAMPLE'), SPORTING_EVENT.ID, SEAT.SPORT_LOCATION_ID, SEAT.SEAT_LEVEL, SEAT.SEAT_SECTION, SEAT.SEAT_ROW, SEAT.SEAT, (CASE WHEN SEAT.SEAT_TYPE = 'luxury' THEN 3 * var_standard_price WHEN SEAT.SEAT_TYPE = 'premium' THEN 2 * var_standard_price WHEN SEAT.SEAT_TYPE = 'standard' THEN var_standard_price WHEN SEAT.SEAT_TYPE = 'sub-standard' THEN 0.8 * var_standard_price WHEN SEAT.SEAT_TYPE = 'obstructed' THEN 0.5 * var_standard_price WHEN SEAT.SEAT_TYPE = 'standing' THEN 0.5 * var_standard_price END) AS ticket_price
                FROM SPORTING_EVENT, SEAT
                WHERE SPORTING_EVENT.LOCATION_ID = SEAT.SPORT_LOCATION_ID AND SPORTING_EVENT.ID = var$ID;
    END LOOP;
    CLOSE event_cur;
END;


比較

CREATE PROCEDURE Statement

#OracleMySQL
1CREATE OR REPLACE PROCEDURE DROP PROCEDURE IF EXISTS and CREATE PROCEDURE
2param IN / OUT / IN OUT datatype Parameter definition IN / OUT / INOUT param datatype(length)
3IS / AS Removed
4Variable declaration is before BEGIN Variable declaration is after BEGIN
5END sp_name END

CREATE FUNCTION Statement

  • Converting user-defined functions from Oracle to MySQL:
#Oracle MySQL
1CREATE OR REPLACE FUNCTION DROP FUNCTION IF EXISTS and CREATE FUNCTION
2param IN / OUT / IN OUT datatype Parameter definition param datatype(length)
3RETURN datatype Return value RETURNS datatype(length)
4IS / AS Removed
5Variable declaration is before BEGIN Variable declaration is after BEGIN
6END func_name END

PL/SQL Statements

#OracleMySQL
1variable datatype := value Variable declaration DECLARE variable datatype DEFAULT value
2variable := value Assignment statement SET variable = value
3CURSOR cur (params) IS SELECT Cursor declaration DECLARE cur CURSOR FOR SELECT
4Variable and cursor declarations can be mixed in any order Variable declarations must be before cursor and handlers
5FOR rec IN cursor LOOP Cursor loop OPEN cursor WHILE-FETCH-CLOSE
6IF THEN ELSIF ELSE END IF IF statement IF THEN ELSEIF ELSE END IF
7WHILE condition LOOP sql END LOOP A loop statement WHILE condition DO sql END WHILE
8EXIT WHEN condition Exit from a loop IF condition THEN LEAVE label END IF
  • EXCEPTION block:
#Oracle MySQL
1BEGIN stmts EXCEPTION … END Exception block structure BEGIN DECLARE HANDLER … stmts END
2WHEN DUP_VAL_ON_INDEX Duplicate key DECLARE EXIT HANDLER FOR SQLSTATE '23000'
3WHEN NO_DATA_FOUND No rows found DECLARE EXIT HANDLER FOR NOT FOUND
4WHEN OTHERS All exceptions DECLARE EXIT HANDLER FOR SQLEXCEPTION
http://www.sqlines.com/oracle-to-mysql#plsql-statements
  • パッケージ変数は MySQL ではセション変数(@)で代用できる。
  • 結果セットの返却は MySQL では SELECT 文を書くだけ。
  • %TYPE および %ROWTYPE データ型定義は MySQL にはない。

SCTを使った変換手順例

$ git clone https://github.com/awslabs/aws-database-migration-samples.git
$ sudo rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
$ sudo rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
$ rpm -ql oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64
/usr/bin/sqlplus64
/usr/lib/oracle/12.2/client64/bin/sqlplus
/usr/lib/oracle/12.2/client64/lib/glogin.sql
/usr/lib/oracle/12.2/client64/lib/libsqlplus.so
/usr/lib/oracle/12.2/client64/lib/libsqlplusic.so
$ rpm -ql oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64
/usr/lib/oracle/12.2/client64/bin/adrci
/usr/lib/oracle/12.2/client64/bin/genezi
/usr/lib/oracle/12.2/client64/lib/libclntsh.so.12.1
/usr/lib/oracle/12.2/client64/lib/libclntshcore.so.12.1
/usr/lib/oracle/12.2/client64/lib/libipc1.so
/usr/lib/oracle/12.2/client64/lib/libmql1.so
/usr/lib/oracle/12.2/client64/lib/libnnz12.so
/usr/lib/oracle/12.2/client64/lib/libocci.so.12.1
/usr/lib/oracle/12.2/client64/lib/libociei.so
/usr/lib/oracle/12.2/client64/lib/libocijdbc12.so
/usr/lib/oracle/12.2/client64/lib/libons.so
/usr/lib/oracle/12.2/client64/lib/liboramysql12.so
/usr/lib/oracle/12.2/client64/lib/ojdbc8.jar
/usr/lib/oracle/12.2/client64/lib/xstreams.jar
  • 環境変数を設定する。
$ vi ~.bashrc
export PATH=$PATH:/usr/lib/oracle/12.2/client64/bin
export NLS_LANG=American_America.UTF8
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/12.2/client64/lib
$ sudo yum -y install git
  • スクリプトを入手する。
$ git clone https://github.com/awslabs/aws-database-migration-samples.git
  • ソースの Oracle Database にスキーマとオブジェクトを作成する。
$ cd aws-database-migration-samples/oracle/sampledb/v1
$ sqlplus awsuser/******@******.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL
SQL> install-rds.sql
  • SCT でソースとターゲットを指定して、変換する。

補足


参考

2017-09-04

pgbench で RDS PostgreSQL のマスターに負荷をかけてリードレプリカのレプリケーションラグを計測する

pgbench とは

pgbenchとは

pgbenchはPostgreSQLに同梱されているシンプルなベンチマークツールです。最初のバージョンは筆者により作成され、日本のPostgreSQLメーリングリストで1999年に公開されました。その後pgbenchはcontribという付属追加プログラムとして、PostgreSQLのソースコードとともに配布されるようになりました。どのバージョンでPostgreSQLに取り込まれたのかはPostgreSQL付属のドキュメント(HISTORY)には書かれていないので定かではないのですが、コミットログを見ると、おそらく2000年にリリースされたPostgreSQL 7.0で導入されたと思われます。その後数多くの改良がたくさんの人によって行われ、現在に至っています。

(中略)

pgbenchが標準で実行するトランザクションはTPC-Bを想定しているものとはいえ、実際に使ってみると不都合なこともあります。特に問題なのは、上記ステップ4で、pgbench_branchesの行数がスケーリングファクタと同じ(つまりデフォルトでは10)しかないため、同時接続数が10を超えるとロック競合が発生して性能が出なくなるということです。現実のシステムではこのような設計は普通は行わないので、実際のシステムでの性能を推し量るという、ベンチマーク本来の目的にはあまりそぐわないことになります。

そこでpgbenchでは3と4の処理を省略したトランザクションのモードを用意しており、pgbenchを実行するときに"-N"を付けることによって実行できます。Webシステムのように、多数の同時接続を想定している場合は、こちらを使うことをお勧めします。

pgbenchの使いこなし | Let's Postgres

準備

RDS PostgreSQL のマスターとリードレプリカを作成する
EC2 から RDS PostgreSQL に接続してセットアップする
$ sudo yum -y install postgresql
$ sudo yum -y install postgresql-contrib
  • データベースのセットアップ(テーブル作成、データ投入)
$ pgbench -i -s 1000 -U awsuser -h ******.******.ap-northeast-1.rds.amazonaws.com -d mydb
  • テーブルを確認する
$ psql "host=******.*******.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=mydb port=5432"
Password:
mydb=> select * from pg_tables where schemaname='public';
 schemaname |    tablename     | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+------------------+------------+------------+------------+----------+-------------+-------------
 public     | pgbench_branches | awsuser    |            | t          | f        | f           | f
 public     | pgbench_tellers  | awsuser    |            | t          | f        | f           | f
 public     | pgbench_accounts | awsuser    |            | t          | f        | f           | f
 public     | pgbench_history  | awsuser    |            | f          | f        | f           | f
(4 rows)

ベンチマーク実施

  • マスターにトランザクションを発行して負荷をかける
    • N: pgbench_tellersとpgbench_branchesを更新しない。マスターでロック競合がボトルネックになるとトランザクションのスループットが伸びずレプリケーション意外のボトルネックで頭打ちになる可能性があるため。
    • r: ステートメント毎の平均レイテンシをレポートする
    • c: クライアント数
    • j: ワーカースレッド数
    • t: トランザクション数
    • U: DBユーザー名
    • d: データベース名
$ pgbench -r -c 10 -j 10 -t 10000 -U awsuser -h ******.******.ap-northeast-1.rds.amazonaws.com -d mydb 

(中略)

transaction type: TPC-B (sort of)
scaling factor: 1000
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = ***.****** (including connections establishing)
tps = ***.****** (excluding connections establishing)
statement latencies in milliseconds:

(中略)

	*.********	BEGIN;
	*.********	UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
	*.********	SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
	*.********	UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
	*.********	UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
	*.********	INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
	*.********	END;
  • リードレプリカでレプリケーションラグを確認する
    • マネジメントコンソールの「レプリケーションの詳細」でも確認できる
    • 以下の単位は秒
$ psql "host=******.******.ap-southeast-1.rds.amazonaws.com user=awsuser dbname=mydb port=5432"
Password:

mydb=> SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;

 date_part
-----------
       0
(1 row)

mydb=> SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;
 date_part
-----------
       0
(1 row)
  • マネジメントコンソールの「モニタリング」の「トランザクションログの生成」でトランザクションログの生成量を確認する。

参考

2017-08-26

Linux に psql をインストールする

psql(PostgreSQL のクライアントツール) は postgresql パッケージに含まれるので、yum で postgresql パッケージをインストールする。


インストール

$ sudo yum install postgresql

...

Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql92-libs-9.2.21-1.60.amzn1.x86_64.rpm      | 260 kB   00:00
(2/2): postgresql92-9.2.21-1.60.amzn1.x86_64.rpm           | 4.1 MB   00:00
  • 確認する。
$ rpm -qa|grep postgresql
postgresql92-9.2.21-1.60.amzn1.x86_64
postgresql92-libs-9.2.21-1.60.amzn1.x86_64
$ which psql
/usr/bin/psql

使ってみる

  • Amazon Redshift に接続してみる。
$ psql "host=***.us-east-1.redshift.amazonaws.com user=master dbname=dev port=5439"
Password:
psql (9.2.21, server 8.0.2)
WARNING: psql version 9.2, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

dev=#

環境

$ cat /etc/issue
Amazon Linux AMI release 2017.03
Kernel \r on an \m
$ uname -r
4.9.38-16.33.amzn1.x86_64

参考