Hatena::ブログ(Diary)

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

2018-07-21

Amazon Redshift でロックを保持しているセッションとロック待ちしているセッションを確認する方法

Redshift でロックを保持しているセッションとロック待ちしているセッションlock_wait.sql で確認すると便利。マネジメントコンソールで Redshift の「クエリ」タブではロックを保持しているクエリは表示されるがロック待ちしているクエリは表示されない。


準備

  • テーブルを作成してレコードを insert する。
CREATE TABLE testtab1(id int primary key, name varchar(32));
INSERT INTO testtab1 (id, name) VALUES(1, 'foo');
INSERT INTO testtab1 (id, name) VALUES(2, 'bar');

検証

BEGIN;
UPDATE testtab1 SET name='fff' WHERE id=1;
UPDATE testtab1 SET name='bbb' WHERE id=2;

確認

  • ロックを保持しているセッションと待たされているセッションを確認する。
    • pid: 75517 は granted が True なのでロックを保持していて、pid:79568 は False なので待たされている。
    • 一行目の pid: 75517 は num_blocking から1セッションを待たせていて、pidlist から pid:79568 を待たされていることが分かる。
    • 三行目の pid:79568 は waiting から 2239秒(37分)待たされていることが分かる。
# \i lock_wait.sql
  xid   |  pid  | username | dbname | relation | schemaname | objectname |         mode          | granted | obj_type |         txn_start          | block_sec | block_min | block_hr | waiting | max_sec_blocking | num_blocking | pidlist
--------+-------+----------+--------+----------+------------+------------+-----------------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+---------
 863579 | 75517 | awsuser  | mydb   |   170038 | public     | testtab1   | ShareRowExclusiveLock | True    | relation | 2018-07-21 14:17:07.367098 |   2307.24 |     38.45 |     0.64 |         |          2239.24 |            1 | 79568★待たされているセッションの pid
 863579 | 75517 | awsuser  | mydb   |   170038 | public     | testtab1   | AccessShareLock       | True    | relation | 2018-07-21 14:17:07.367098 |   2307.24 |     38.45 |     0.64 |         |                  |              |
 863608 | 79568 | awsuser  | mydb   |   170038 | public     | testtab1   | ShareRowExclusiveLock | False   | relation | 2018-07-21 14:18:11.388639 |           |           |          | 2239.24★待たされている秒数 |                  |              |
(3 rows)
/**********************************************************************************************
Purpose: Return instances of table filter for all or a given table in the past 7 days
Columns:
pid: 				Process/Session Id
username:			User name
dbname:				Database
relation:			Object id
schemaname: 		Schema
objectname:			Object Name
mode:				Lock Mode (AcessShareLock, AccessExclusiveLock, etc)
granted:			Granted (True or False)
obj_type:			Type of Object
txn_start:			Start Time of the transaction that asked for the lock
block_sec:			Seconds Holding the Lock
block_min:  		Minutes Holding the lock
block_hr: 			Hours Holding the lock
waiting:			Seconds waiting for the lock	
max_sec_blocking:	Peak of seconds blocking other sessions
num_blocking:		Number of sessions blocked by this lock
pidlist:			List of Sessions being blocked by this lock

f:id:yohei-a:20180721235803p:image:w640


参考

2018-07-16

Amazon Redshift に重いクエリを同時多重実行しながら性能情報を見る

Redshift に負荷をかける

EC2gitpsql、pgbench をインストールする
$ sudo yum -y install postgresql96
$ sudo yum -y install postgresql96-contrib
$ sudo yum -y install git
Redshift で負荷かけの準備を行う
  • EC2 から Redshift にスーパーユーザーで接続する
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
  • ベンチマーク用のグループとユーザーを作成する
    • キューの同時実行数を超えると情報取得クエリもキューで待たされるため、負荷かけ実行ユーザーを別に作成してキューを分ける
# create group bench;
# create user benchuser password '********' in group bench;
# ALTER USER benchuser SET enable_result_cache_for_session = off;
# \q
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=benchuser dbname=mydb port=5439"
  • テーブルを作成する
# CREATE TABLE lineorder 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
);
  • データをロードする
# copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' 
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
gzip compupdate off region 'us-west-2';
# \q
Redshift に pgbench で負荷をかける
  • lineorder.sql を作成する
$ cat << EOF > lineorder.sql
select count(a.*) from lineorder a, lineorder b;
EOF
  • pgbench で lineorder.sql を10並列で合計50回実行する
$ pgbench -r -c 10 -j 10 -n -t 50 -f lineorder.sql -U awsuser -h ds28xl4n.******.us-west-2.redshift.amazonaws.com -d mydb -p 5439
  • running_queues.sql で実行中のクエリのPIDを確認して cancel すると実行中のクエリが終了して、キュー待ちしていたクエリが実行される様子を見る事ができる。
# cancel 44143;

性能情報の見え方

マネジメントコンソール
  • マネジメントコンソールの「クエリ」タブにはキュー待ちのクエリは表示されない。
  • マネジメントコンソールの「クエリ」タブの「実行時間」はキュー待ち時間を含む時間、「開始時刻」はクエリが発行された時刻(キュー待ちが終わって実行フェーズに変わった時刻ではなくクエリが発行された時刻)が表示される。

f:id:yohei-a:20180721205206p:image:w640

システムテーブル・システムビュー
  • スーパーユーザーでログインする(全ユーザーの情報を見るため)
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
  • running_queues.sql でキュー待ちと実行中のクエリを確認する
    • state が Queue だとキュー待ち、Run だと実行中。q_sec がキュー待ち時間(秒)、exe_sec が実行時間(秒)。
    • CPU時間(cpu_sec)が exe_sec より長いことがあるのはクエリマルチプロセッサで並列実行された時間の合計のためと思われる。
    • read_mb はディスク読込み量、spill_mb はディスク書込み量。
ds28xl4n-r awsuser 00:10 =# \i running_queues.sql
   user    |  pid  |  xid   | query | q | slt |        start        | state | q_sec | exe_sec | cpu_sec | read_mb | spill_mb | ret_rows |    nl_rows     |                                            sql                                             | alert
-----------+-------+--------+-------+---+-----+---------------------+-------+-------+---------+---------+---------+----------+----------+----------------+--------------------------------------------------------------------------------------------+-------
 benchuser | 34229 | 762470 | 89556 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  267373 |     128 |          |          | 37471050468695 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34230 | 762472 | 89557 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  253425 |     128 |          |          | 35415740526019 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34231 | 762474 | 89558 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  274471 |     128 |          |          | 38407248173760 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34228 | 762476 | 89559 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  255883 |     128 |          |          | 35736062097462 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34232 | 762478 | 89560 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  250486 |     128 |          |          | 34941414838224 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34233 | 762480 | 89561 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34235 | 762482 | 89562 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34234 | 762484 | 89563 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34236 | 762486 | 89564 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34237 | 762487 | 89565 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 awsuser   | 34063 | 765301 | 90186 | 7 |   1 | 2018-07-17 00:10:57 | Run   |     0 |       0 |       0 |       0 |          |          |                | /***************************************************************************************** |
(11 rows)

Time: 1024.246 ms
  • lock_wait.sql でロック待ち状況を確認する
    • objectname が対象オブジェクト、mode がロックモード、txn_start がトランザクション開始時刻。
    • block_sec、block_min、block_hr はロックを保持している秒、分、時間。
    • waiting はロック待ち時間(秒)。
    • num_blockingは待たされているセッション数、pidlist は待たされているセッションの pid。
ds28xl4n-r awsuser 21:42 =# \i lock_wait.sql
  xid   |  pid  | username  | dbname | relation | schemaname | objectname |      mode       | granted | obj_type |         txn_start          | block_sec | block_min | block_hr | waiting | max_sec_blocking | num_blocking | pidlist
--------+-------+-----------+--------+----------+------------+------------+-----------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+---------
 762484 | 34234 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.911033 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762478 | 34232 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.905794 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762482 | 34235 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.909771 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762474 | 34231 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.90356  |    197.83 |       3.3 |     0.05 |         |                  |              |
 762476 | 34228 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.904795 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762486 | 34236 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.912798 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762480 | 34233 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.907442 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762487 | 34237 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.914389 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762470 | 34229 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.900309 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762472 | 34230 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.90117  |    197.83 |       3.3 |     0.05 |         |                  |              |
(10 rows)

Time: 40773.823 ms
  • top_queries.sql で実行時間の長いクエリを調べる
    • クエリテキストで集計した実行時間の長いトップ50が表示される。
    • n_qry が実行回数、min/max/avg が実行時間の最小/最大/平均で total が実行時間の合計。
    • max_query_id がクエリIDが最大のもので、last_run がクエリが最後に実行された日付。
ds28xl4n-r awsuser 00:48 =# \i top_queries.sql
  db  | n_qry |                                     qrytext                                      |   min   |   max   |   avg   |  total  | max_query_id |  last_run  | aborted |    event
------+-------+----------------------------------------------------------------------------------+---------+---------+---------+---------+--------------+------------+---------+-------------
 mydb |     1 | select count(a.*) from lineorder a, lineorder b;                                 | 1603.00 | 1603.00 | 1603.00 | 1603.00 |        80506 | 2018-07-16 |       1 | Nested Loop
 mydb |     1 | select * from STV_EXEC_STATE ORDER BY segment, step, slice;                      |  305.00 |  305.00 |  305.00 |  305.00 |        80640 | 2018-07-16 |       0 |
 mydb |     1 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' credentials '' gzi |  179.00 |  179.00 |  179.00 |  179.00 |        85025 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |  100.00 |  100.00 |  100.00 |  100.00 |        89635 | 2018-07-16 |       0 | Nested Loop
 mydb |     1 | select * from lineorder;                                                         |   53.00 |   53.00 |   53.00 |   53.00 |        89549 | 2018-07-16 |       1 |
 mydb |     1 | /******************************************************************************* |   50.00 |   50.00 |   50.00 |   50.00 |        89648 | 2018-07-16 |       0 |
 mydb |     2 | /******************************************************************************* |    2.00 |   46.00 |   24.00 |   48.00 |        90184 | 2018-07-17 |       0 |
 mydb |     1 | /******************************************************************************* |   41.00 |   41.00 |   41.00 |   41.00 |        89582 | 2018-07-16 |       0 |
 mydb |     5 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.s |    1.00 |   29.00 |    6.60 |   33.00 |        87441 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |   20.00 |   20.00 |   20.00 |   20.00 |        89626 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |   20.00 |   20.00 |   20.00 |   20.00 |        89610 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_QUEUE_INFO;                                              |   17.00 |   17.00 |   17.00 |   17.00 |        85321 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_REPORT;                                                  |   17.00 |   17.00 |   17.00 |   17.00 |        82789 | 2018-07-16 |       1 |
 mydb |     1 | select trim(database) as DB, count(query) as n_qry, max(substring (qrytext,1,80) |   15.00 |   15.00 |   15.00 |   15.00 |        82847 | 2018-07-16 |       0 |
 mydb |     1 | select * from admin.v_check_wlm_query_time;                                      |   12.00 |   12.00 |   12.00 |   12.00 |        85257 | 2018-07-16 |       0 |
 mydb |     2 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.s |    0.00 |   11.00 |    5.50 |   11.00 |        85266 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVV_QUERY_STATE;                                                   |   11.00 |   11.00 |   11.00 |   11.00 |        82820 | 2018-07-16 |       0 |
 mydb |     1 | SELECT date_trunc('hour',a.service_class_start_time) AS hour, a.service_class, b |   10.00 |   10.00 |   10.00 |   10.00 |        89658 | 2018-07-16 |       0 |
 mydb |     6 | /******************************************************************************* |    1.00 |    1.00 |    1.00 |    6.00 |        90186 | 2018-07-17 |       0 |
 mydb |     1 | SELECT *, DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF FROM STL_QUERY WHERE QUERY |    6.00 |    6.00 |    6.00 |    6.00 |        82812 | 2018-07-16 |       0 |
 mydb |     1 | SELECT s.process AS pid ,date_Trunc ('second',s.starttime) AS S_START ,datediff( |    6.00 |    6.00 |    6.00 |    6.00 |        82831 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |    6.00 |    6.00 |    6.00 |    6.00 |        89606 | 2018-07-16 |       0 |
 mydb |     2 | select * from STV_EXEC_STATE ;                                                   |    0.00 |    5.00 |    2.50 |    5.00 |        82795 | 2018-07-16 |       0 |
 mydb |     1 | select * from stl_query;                                                         |    5.00 |    5.00 |    5.00 |    5.00 |        82816 | 2018-07-16 |       0 |
 mydb |     1 | padb_fetch_sample: select * from lineorder                                       |    5.00 |    5.00 |    5.00 |    5.00 |        85039 | 2018-07-16 |       0 | Filter
 mydb |    50 | select count(1) from lineorder;                                                  |    0.00 |    2.00 |    0.08 |    4.00 |        80493 | 2018-07-16 |       0 |
 mydb |     2 | /* query showing queries which are waiting on a WLM Query Slot */ SELECT w.query |    0.00 |    1.00 |    0.50 |    1.00 |        90180 | 2018-07-17 |       0 |
 mydb |     1 | select * from STV_INFLIGHT;                                                      |    1.00 |    1.00 |    1.00 |    1.00 |        82788 | 2018-07-16 |       0 |
 mydb |     1 | SELECT TRIM(DATABASE) AS DB, w.query, SUBSTRING(q.querytxt,1,100) AS querytxt, w |    1.00 |    1.00 |    1.00 |    1.00 |        89655 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |    1.00 |    1.00 |    1.00 |    1.00 |        89597 | 2018-07-16 |       0 |
 mydb |     4 | select * from stv_wlm_query_state;                                               |    0.00 |    1.00 |    0.25 |    1.00 |        87438 | 2018-07-16 |       0 |
 mydb |     1 | select count(*) from lineorder;                                                  |    0.00 |    0.00 |    0.00 |    0.00 |        89553 | 2018-07-16 |       0 |
 mydb |     1 | /* Query showing information about sessions with currently running queries */ SE |    0.00 |    0.00 |    0.00 |    0.00 |        89592 | 2018-07-16 |       0 |
(33 rows)

Time: 531.186 ms
  • top_queries_and_cursors.sql で実行時間の長いクエリを調べる
    • クエリテキストで集計した実行時間の長いトップ50が表示される。
    • top_queries.sql と似ているが、CPU時間(cpu)、CPU使用率(cpupct)、ディスク読込み量(mb_read)、一時領域使用量(spill)など情報量が多い。
ds28xl4n-r awsuser 00:58 =# \i top_queries_and_cursors.sql
  db  | n_qry |                                                         qrytext                                                          |   max   |   avg   |  total  | queue |  cpu  | cpupct | spill | mb_read | rows_ret | max_query |  last_run  | aborted | alert
------+-------+--------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------+-------+--------+-------+---------+----------+-----------+------------+---------+-------
 mydb |     1 | select count(a.*) from lineorder a, lineorder b;                                                                         | 1603.00 | 1603.00 | 1603.00 |     6 | 64935 |  63.29 |       |     128 |          |     80506 | 2018-07-16 |       1 | NL
 mydb |     1 | select * from STV_EXEC_STATE ORDER BY segment, step, slice;                                                              |  305.00 |  305.00 |  305.00 |       |       |        |       |         |          |     80640 | 2018-07-16 |       0 |
 mydb |     1 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' credentials '' gzip compupdate off region 'us-west-2';     |  179.00 |  179.00 |  179.00 |     6 |  1809 |  14.42 |       |       0 |          |     85025 | 2018-07-16 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Returns the hig |  100.00 |  100.00 |  100.00 |     7 |   122 |   7.24 |       |       0 |          |     89635 | 2018-07-16 |       0 | NL
 mydb |     1 | /********************************************************************************************** Purpose: Return Alerts f |   68.00 |   68.00 |   68.00 |     7 |    17 |  20.58 |       |       0 |          |     90369 | 2018-07-17 |       0 | NL
 mydb |     2 | /********************************************************************************************** WLM QMR Rule Candidates  |   50.00 |   28.50 |   57.00 |     7 |     1 |   0.05 |       |       0 |          |     90365 | 2018-07-17 |       0 |
 mydb |     1 | select * from lineorder;                                                                                                 |   53.00 |   53.00 |   53.00 |     7 |    56 |   1.74 |       |    1850 | 23561221 |     89549 | 2018-07-16 |       1 |
 mydb |     2 | /********************************************************************************************** Purpose: Returns the per |   46.00 |   24.00 |   48.00 |     7 |       |        |       |       0 |          |     90184 | 2018-07-17 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return instance |   41.00 |   41.00 |   41.00 |     7 |       |        |       |       0 |          |     89582 | 2018-07-16 |       0 |
 mydb |     3 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.slot_count as slt, date_trunc('second',q. |   29.00 |   10.33 |   31.00 |     6 |     1 |   0.37 |       |       0 |          |     82846 | 2018-07-16 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return the top  |   20.00 |   20.00 |   20.00 |     7 |       |        |       |       0 |          |     89626 | 2018-07-16 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return instance |   20.00 |   20.00 |   20.00 |     7 |    18 |  22.67 |       |       0 |          |     89610 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_QUEUE_INFO;                                                                                      |   17.00 |   17.00 |   17.00 |     7 |       |        |       |       0 |          |     85321 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_REPORT;                                                                                          |   17.00 |   17.00 |   17.00 |       |       |        |       |         |          |     82789 | 2018-07-16 |       1 |
 mydb |     1 | select trim(database) as DB, count(query) as n_qry, max(substring (qrytext,1,80)) as qrytext, min(run_seconds) as "min"  |   15.00 |   15.00 |   15.00 |     6 |       |        |       |       0 |          |     82847 | 2018-07-16 |       0 |
 mydb |     1 | select * from admin.v_check_wlm_query_time;                                                                              |   12.00 |   12.00 |   12.00 |       |       |        |       |         |          |     85257 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVV_QUERY_STATE;                                                                                           |   11.00 |   11.00 |   11.00 |     6 |       |        |       |       0 |          |     82820 | 2018-07-16 |       0 |
 mydb |     1 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.service_class AS class ,w.slot_count AS s |   11.00 |   11.00 |   11.00 |     6 |       |        |       |       0 |          |     82837 | 2018-07-16 |       0 |
 mydb |     1 | SELECT date_trunc('hour',a.service_class_start_time) AS hour, a.service_class, b.condition AS service_class_condition, C |   10.00 |   10.00 |   10.00 |     7 |       |        |       |         |      202 |     89658 | 2018-07-16 |       0 |
 mydb |     1 | SELECT *, DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF FROM STL_QUERY WHERE QUERYTXT LIKE 'SELECT * FROM%' ORDER BY START |    6.00 |    6.00 |    6.00 |       |       |        |       |         |          |     82812 | 2018-07-16 |       0 |
 mydb |     6 | /********************************************************************************************** Purpose: Return the Curr |    1.00 |    1.00 |    6.00 |     7 |     1 |   0.41 |       |       0 |          |     90186 | 2018-07-17 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return commit q |    6.00 |    6.00 |    6.00 |     7 |       |        |       |       0 |          |     89606 | 2018-07-16 |       0 |
 mydb |     1 | SELECT s.process AS pid ,date_Trunc ('second',s.starttime) AS S_START ,datediff(minutes,s.starttime,getdate ()) AS conn_ |    6.00 |    6.00 |    6.00 |       |       |        |       |         |          |     82831 | 2018-07-16 |       0 |
 mydb |     2 | select * from STV_EXEC_STATE ;                                                                                           |    5.00 |    2.50 |    5.00 |       |       |        |       |         |          |     82795 | 2018-07-16 |       0 |
 mydb |     1 | padb_fetch_sample: select * from lineorder                                                                               |    5.00 |    5.00 |    5.00 |     6 |   136 |  98.42 |       |   25890 |          |     85039 | 2018-07-16 |       0 |
 mydb |     1 | select * from stl_query;                                                                                                 |    5.00 |    5.00 |    5.00 |       |       |        |       |         |          |     82816 | 2018-07-16 |       0 |
 mydb |    50 | select count(1) from lineorder;                                                                                          |    2.00 |    0.08 |    4.00 |     6 |     1 |  85.02 |       |      20 |          |     80493 | 2018-07-16 |       0 |
 mydb |     2 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.slot_count as slt, date_trunc('second',q. |    1.00 |    1.00 |    2.00 |     7 |       |        |       |       0 |          |     87441 | 2018-07-16 |       0 |
 mydb |     2 | /********************************************************************************************** Purpose: Return the top  |    1.00 |    1.00 |    2.00 |     7 |       |        |       |       0 |          |     90337 | 2018-07-17 |       0 |
 mydb |     1 | SELECT TRIM(DATABASE) AS DB, w.query, SUBSTRING(q.querytxt,1,100) AS querytxt, w.queue_start_time, w.service_class AS cl |    1.00 |    1.00 |    1.00 |       |       |        |       |         |          |     89655 | 2018-07-16 |       0 |
 mydb |     1 | select * from STV_INFLIGHT;                                                                                              |    1.00 |    1.00 |    1.00 |       |       |        |       |         |          |     82788 | 2018-07-16 |       0 |
 mydb |     2 | /* query showing queries which are waiting on a WLM Query Slot */ SELECT w.query ,substring(q.querytxt,1,100) AS querytx |    1.00 |    0.50 |    1.00 |     7 |     1 |   0.23 |       |       0 |          |     90180 | 2018-07-17 |       0 |
 mydb |     4 | select * from stv_wlm_query_state;                                                                                       |    1.00 |    0.25 |    1.00 |       |       |        |       |         |          |     87438 | 2018-07-16 |       0 |
 mydb |     1 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.service_class AS class ,w.slot_count AS s |    0.00 |    0.00 |    0.00 |     7 |     1 |   1.70 |       |       0 |          |     85266 | 2018-07-16 |       0 |
 mydb |     1 | select count(*) from lineorder;                                                                                          |    0.00 |    0.00 |    0.00 |       |       |        |       |         |          |     89553 | 2018-07-16 |       0 |
 mydb |     1 | /* Query showing information about sessions with currently running queries */ SELECT s.process AS pid ,date_Trunc ('seco |    0.00 |    0.00 |    0.00 |       |       |        |       |         |          |     89592 | 2018-07-16 |       0 |
(36 rows)

Time: 1141.186 ms
# select a.userid, a.query, a.total_queue_time, a.total_exec_time, a.exec_start_time, a.exec_end_time, substring(b.text,1,100)
from stl_wlm_query a, stl_querytext b
where a.query = b.query
order by a.total_exec_time desc
limit 10;

 userid | query | total_queue_time | total_exec_time |      exec_start_time       |       exec_end_time        |                                              substring
--------+-------+------------------+-----------------+----------------------------+----------------------------+------------------------------------------------------------------------------------------------------
    100 | 80506 |                0 |      1602831743 | 2018-07-16 18:51:06.926251 | 2018-07-16 19:17:49.757994 | select count(a.*) from lineorder a, lineorder b;
    102 | 85025 |                0 |       179448651 | 2018-07-16 20:12:48.089068 | 2018-07-16 20:15:47.537719 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder'\ncredentials ''\ngzip compupdate off r
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | /**********************************************************************************************\nPur
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 |  results can be used to fine tune WLM queues which contain too many or too few slots\nresulting in W
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 |  in the WLM configuration file.\nmax_wlm_concurrency: Current actual concurrency level of the servic
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | time.\nmax_slots_ts: Most recent time at which the max_service_class_slots occurred.\nlast_queued_ti
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | Since generate_series is unsupported in Redshift, this uses an unelegant method to generate a dt\nse
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | .\n- If SVL_QUERY_REPORT has < 604800 rows you may want to substitue SVL_QUERY_REPORT for another ta
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | un after period of heaviest query activity\nHistory:\n2015-08-31 chriz-bigdata created\n************
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | dt_series AS (select sysdate - (n * interval '1 second') as dt from (select row_number() over () as
(10 rows)

Time: 258.192 ms
# select a.userid, a.query, a.total_queue_time, a.total_exec_time, a.queue_start_time, a.queue_end_time, substring(b.text,1,100)
from stl_wlm_query a, stl_querytext b
where a.query = b.query
order by a.total_queue_time desc
limit 10;

 userid | query | total_queue_time | total_exec_time |      queue_start_time      |       queue_end_time       |                                              substring
--------+-------+------------------+-----------------+----------------------------+----------------------------+------------------------------------------------------------------------------------------------------
    100 | 80640 |        304428952 |               0 | 2018-07-16 19:20:38.746379 | 2018-07-16 19:25:43.175331 | select * from STV_EXEC_STATE ORDER BY segment, step, slice;
      1 | 79172 |                0 |          103599 | 2018-07-16 13:40:59.910911 | 2018-07-16 13:40:59.910911 | elect oid from pg_namespace where nspname='pg_internal'))
      1 | 79212 |                0 |           97704 | 2018-07-16 13:51:59.913595 | 2018-07-16 13:51:59.913595 | select count(distinct id) from pg_catalog.stv_tbl_perm where temp = false and db_id > 1 and id not i
      1 | 79220 |                0 |           17152 | 2018-07-16 13:53:00.02343  | 2018-07-16 13:53:00.02343  | select node_num,value from stv_fdisk_stats where name='blocks_allocated_total'
      1 | 79307 |                0 |           20577 | 2018-07-16 14:16:39.938937 | 2018-07-16 14:16:39.938937 | select sum(value) from stv_fdisk_stats where name = 'blocks_allocated_permanent'
      1 | 79267 |                0 |           96503 | 2018-07-16 14:05:59.917467 | 2018-07-16 14:05:59.917467 | elect oid from pg_namespace where nspname='pg_internal'))
      1 | 79259 |                0 |           94293 | 2018-07-16 14:03:11.801154 | 2018-07-16 14:03:11.801154 | elect oid from pg_namespace where nspname='pg_internal'))
      1 | 79314 |                0 |            6204 | 2018-07-16 14:17:48.023831 | 2018-07-16 14:17:48.023831 | Select * from pg_internal.redshift_auto_health_check_386769
      1 | 79493 |                0 |           16171 | 2018-07-16 15:02:00.041758 | 2018-07-16 15:02:00.041758 | select node_num,value from stv_fdisk_stats where name='blocks_allocated_total'
      1 | 79360 |                0 |            6354 | 2018-07-16 14:28:48.186254 | 2018-07-16 14:28:48.186254 | Select * from pg_internal.redshift_auto_health_check_386769
Time: 4216.293 ms

2018-07-07

コンテナ について

コンテナという言葉の意味

container

con-「共に」tain「つかんで離さない」-er「人、もの」

->何かをとどめようとするもの

-> 【名】入れもの、コンテナ

英単語 container の語源と意味 - Gogengo! - 英単語は語源でたのしく

コンテナ (英: container)とは、内部に物を納めるための容器のことである。コンテナーとも呼ばれる。

コンテナ - Wikipedia

コンピュータにおけるコンテナ

 商用OSの世界でも、SolarisゾーンやHP-UX Containersなどのように、コンピュータ資源を分離する「コンテナ技術」が発展していきました。ここで、コンテナという言葉が出てきます。コンテナとは船や列車などの貨物輸送で使われる容器です。一隻に大量のコンテナを積載して運ぶ貨物輸送船を思い浮かべて下さい。

 なぜ貨物でコンテナが利用されるのでしょう。容器を直方体の箱にすれば船の限られたスペースにすき間なく積み上げて効率よく配置でき、異なる種類のコンテナ同士で内容物が混ざるといったトラブルも避けられます。依頼主にも管理側にもメリットがあるからです。

 この「隙間なく積み上げて配置できること」と「内容物が混ざるトラブルが発生しないこと」という点が非常に重要です。コンピュータにおけるコンテナも同じ考え方です。コンピュータにおいて、一隻のコンテナ船はホストOSです。その上に載る大量のコンテナの群れには、それぞれさまざまなアプリプロセスが稼働しています。隙間なく積まれたコンテナの群れをよく目を凝らしてみてみると、コンテナ同士はぴったりとくっついて配置されていますが、お互いのコンテナは、となりのコンテナの内容物(プロセス)は知り得ません。すなわち、ホストOSであるコンテナ船から見た人は、コンテナという分離された空間がいくつも存在するかのように見えます。これが、分離された空間=コンテナと呼ばれるゆえんです。

 また、船の平らな甲板にコンテナが直接触れて置かれている点も重要です。平らであるということをコンピュータに置き換えると、ホストOSとコンテナが直接触れているということは、ホストOSとコンテナの間にはハイパーバイザーのような介在者がいないことになります。すなわち、コンテナで稼働するアプリプロセスは、ホストOSから直接起動されているのです。

第4回 「Dockerのクジラロゴ」が意味するもの (2/3) - ITmedia エンタープライズ

他の仮想化とコンテナの違い


コンテナの歴史

f:id:yohei-a:20180708124417p:image:w640

chroot が生まれた背景

 一般的にアプリなどの稼働テストにおいて、実際の本番環境のファイルなどを直接作成、変更、削除するのは、非常に危険です。chroot監獄は、このような本番環境で試すと危険をともなう場合と比べ、ソフトウェアの開発やテスト環境を安全に行うことができます。当時は非力な計算機を使って、BSDなどのUNIX向けにさまざまなソフトウェアの開発が研究機関において行われていました。このような「監獄」の考え方が出現したのは、ソフトウェア開発者が1台のマシンで本番環境をシミュレートして、効率的にかつ安定的に開発を行いたいというニーズが背景にあったためです。

f:id:yohei-a:20180708124941j:image

本番機と開発機を2重に持たず、本番機で開発するのは非常に危険。そこで、本番用のファイルシステムの一部を分離(隔離)するという考えが生まれた

第1回 Dockerと「昭和54年」の深〜い関係 - ITmedia エンタープライズ
chroot の応用例

chroot監獄は開発の場面だけでなく、システムファイルの破損や、操作ミスなどで誤ってファイルを削除してしまい、OSが起動不能になった場合に、インストールメディアレスキューモードを使ってOSを復旧させるシーンにおいても使われています。

 具体的には、インストールメディアで対象となるマシンを起動後、破損したOSのルートディレクトリを適当な名前のディレクトリ(/mnt/sysimageなど)にマウントすることに成功すれば、そのディレクトリchrootを実行することで、破損したOSのルートディレクトリに遷移できます。これにより、起動不能だったOSのコマンド群を使えるようになり、復旧作業を行うことができます。

 また、セキュリティの面においても「ハニーポット(蜜の壺/おとりサーバ)」と呼ぶクラッカー対策にchrootが利用されます。現在(2015年)では、chrootを使ったハニーポットに代わる新しいセキュリティ対策の仕組みがいろいろと研究されていますが、基本的にはchrootの考え方を踏襲しており、2015年現在でもフリーOSなどで利用されています。

f:id:yohei-a:20180708125240j:image

chrootの応用例。chrootファイルシステムの分離機能はソフトウェアの開発だけでなく、さまざまな分野で応用されている

第1回 Dockerと「昭和54年」の深〜い関係 - ITmedia エンタープライズ
FreeBSD Jail

2000年、オープンソース界隈であるホットなニュースが飛び交いました。

 chroot監獄を大幅に発展させた機能が、フリーOSで利用可能となったのです。その名も「FreeBSD Jail(フリー・ビーエスディ・ジェイル)」です。「あるディレクトリ配下をルートディレクトリに見せる」というファイルシステムを取り扱う概念に加え、さらにアプリプロセスIDも監獄ごとに分離できるようになりました。

 このFreeBSD Jailは、監獄の中からホストOS側を見ると、ホストOSの各種資源の一部分だけがあたかもすべての資源のように見せることができる画期的なものです。FreeBSD Jailはとても軽く、洗練されたアーキテクチャとして知られており、日本やロシアで根強い人気があるFreeBSDと呼ばれるBSD系のフリーOSにおける強力なOS空間の分離機能として発展し、2015年現在でも広く利用されています。

 その後FreeBSD Jailは、VIMAGEと呼ぶネットワークに関する分離機能も追加されました。

第4回 「Dockerのクジラロゴ」が意味するもの (1/3) - ITmedia エンタープライズ
Dockerが生まれた背景

Googleでのコンテナ

つまり私たちが利用するGoogleのすべてのサービスも、Googleの社内で使われているツールもすべて、すでにGoogleではDockerのようなコンテナ型仮想化技術の上で実行されているということのようです。

「We start over 2billion containers per week.」(私たちは毎週20億個以上のコンテナを起動している)とも書いてあり、Google内部ではすさまじい数のコンテナが実稼働していることになります。

いまDockerを中心にコンテナ型仮想化が話題になっていますが、Googleではすでにコンテナがあたりまえの技術になっている、ということなのでしょう。

no title

Fargate

f:id:yohei-a:20180708132118p:image:w640

f:id:yohei-a:20180708132124p:image:w640

f:id:yohei-a:20180708132112p:image:w640


Kubernetes

Kubernetesとは

Kubernetesオープンソースの「コンテナオーケストレーションツールです。公式サイトのトップページにおいてもそう明示されていますが、「コンテナオーケストレーション」には明確な定義がまだありません。そこで本連載では、コンテナオーケストレーションを「コンテナ型仮想化を本番環境で活用するために必要な機能を提供すること」と定義します。

(中略)

Kubernetesの起源

Kubernetesの起源をたどる際には、Googleエンジニアが執筆したサービス管理に関する有名な論文が参考になります。この論文では、「Googleのサービスは、クラスタマネジャー『Borg』上で動作するコンテナを用いて提供されており、Borgと同様の仕組み(+Borgにおける反省を踏まえた対応策)を備えたオープンソースソフトウェアとしてKubernetesを開発した」旨が記載されています。

Googleのサービスを支えている基盤と同様の仕組みを備えていることから、「KubernetesGoogleで既にその有効性を実証済みである」という意味で大きな注目を浴びたといえるでしょう。

「Kubernetes」とは何か――コンテナ型仮想化の本番利用に向けた課題:先行事例に学ぶKubernetes企業活用の現実(1) - @IT

Kubernetesは要するに、プロセッシングのリソース管理の問題などを解決するためのオーケストレーションソフトウェアです。

プロセッシングだとか、ハイアベイラビリティだとかを。Kubernetesと、あとKubernetesが動作の前提とするEtcdという分散キーバリューストアをペアにして解決していくというものになりますが。

no title

EKS


参考

  • コンテナの性能分析

2011年7月1日

米国の新興クラウドベンダである「DotCloud」が、ベータ期間を終了し、正式サービスを開始しました。

DotCloudの最大の特徴は、PHPPerlRubyJavaPythonNode.jsなど複数の言語と、MySQLPostgreSQL、Cassandra、MongoDBCouchDB、Redisなど複数のデータベースMemcached、RabbitMQ、Hadoopなどのさまざまなソフトウェアを開発者が自由に組み合わせてプラットフォームを構成することができ、それがクラウド上のPaaSとして提供されるという点です。

(中略)

シックスアパート宮川達彦氏が、4月にDotCloudへ転職したことブログで明らかにしていますが、そのエントリで宮川氏はこのように書いています。

I hope to see Perl deployments beat Ruby and Node.js on DotCloud some day :)

クラウド上の言語としてPerlが使えるプラットフォームはこれまでほとんどありませんでした。果たして、新しい世代のPaaSではどのような言語やデータベースが主流となるのでしょうか?

プログラミング言語やデータベースが選べる新世代PaaS「DotCloud」が正式サービス開始

1番のポイントは、PDBがいくつあってもインスタンスは1つだけというところです。PDBごとにメモリやプロセスを割り当てていたのでは、従来型のデータベースを複数作成する場合と同じで無駄なリソースを多く使用してしまいます。インスタンスを各PDBが共同利用することで、リソースを節約しようというのがOracle Multitenantの考え方なのです。こうして集約率を高め、さらにPDBとして独立性を持たせることで、スキーマ名の競合やセキュリティ見直しといった統合にありがちな課題まで、まとめて解決することができます。

徹底解説!Oracle Database 12cのすべて Vol.1 | アシスト

Webコンテナ(ウェブコンテナ、Web container)とは、Java Platform, Enterprise Edition (Java EE) アーキテクチャのWebコンポーネント規約を実装するソフトウェア[1]。Java Servletの実行環境となることからServletコンテナ(サーブレットコンテナ、Servlet container)とも呼ばれる。

この規約では、コンピュータセキュリティ、並列性、ライフサイクル管理、トランザクション処理、デプロイやその他のサービスを含むWebコンポーネントの実行環境を規定している。WebコンテナはJava EEプラットフォームAPIを利用したJSPコンテナとしての機能も提供する。

Webコンテナ - Wikipedia

データプレーン、コントロールプレーン

Docker container networking:overlay networkで出て来る。

Dockerのネットワークを理解するためのネットワーク技術入門 – PAYFORWARD

Docker EE networking features

The following two features are only possible when using Docker EE and managing your Docker services using Universal Control Plane (UCP):

Docker container networking:overlay network

上のスライドの黒塗り箇所が YouTube では見れる

2018-03-31

「Oracleの基本 〜データベース入門から設計/運用の初歩まで」の紹介

株式会社コーソルの 渡部さんOracle ACE)から献本いただいた「Oracleの基本 〜データベース入門から設計/運用の初歩まで」の紹介です。

一言で言うと新卒研修でデータベースの教科書として最適な本だと思います。

渡部さんとは JPOUG などのコミュニティ活動で以前から交流があり、コーソル社の社内勉強会にも何度かお邪魔したこともあります。Oracle 関連のイベントにお手伝いに来られた新卒ホヤホヤの方と挨拶したと思ったら数年後には第一線の現場で一緒に仕事させていただいたりしていましたが、ITの知識がなかった新卒や中途入社の方が数年後には一流のDBエンジニアとして第一線の現場やイベントの発表などで活躍されていて、データベース*1を勉強するには最もよい会社の一つだと思っていました。そのコーソル社のエンジニア育成で培われたノウハウが詰まった一冊だと思います。

敢えてアーキテクチャの説明を必要最低限に抑えて、Oracle Database をインストールして使いながら一通りの機能を使いながらウォークスルーする構成になっていて、コーソル社の育成の現場で培われたノウハウが詰まっていることが感じとれます。

初心者が資格取得から始めると暗記科目のようになってしまい、業務で一通り触れてからとなると1年以上はかかってしまったりしますが、本書は初心者が Oracle Database を最初に学ぶ上での高速道路になると思います。

また、ただの機能説明だけではなく著者陣の経験から得られたハマりポイントやなぜそうするかが散りばめられており、初心者ではなくても気づきがあるのではないかと思います。



オススメの読み方

Oracle Database を使ったことがない初心者の方が手を動かしながら通読するのがオススメです。ORACLE MASTER 取得や DB エンジニアを目指されているか方もまずはこの本を Oracle Database を使いながら通読するのが一番の近道ではないかと思います。


対象読者


一部引用

  • はじめに(P.3-4)

そこで,本書はOracle独自の用語の使用を最小限にとどめ,次の2点を考えながら執筆しました。

  • 平易な表現を用いてOracleを解説すること
  • データベースOracle,開発,設計,運用の基本について広くさまざまな視点から学べること

これらの解説ノウハウは,弊社コーソルの新人教育で培われたものです。コーソルの新人教育には,約2週間という短い期間で多くの新卒エンジニアORACLE MASTER Bronze DBAに合格させているという実績があります。さらに最近では,IT 未経験の新人エンジニアが,入社から2年半で,ORACLE MASTER Platinum(2日間の実技試験により認定されるORACLE MASTERの最上位資格)を取得するような例も出てきています。本書には,コーソルの新人エンジニアの生の声を反映させ,ノウハウを惜しみなく注ぎ込みました。

Oracleの基本 ?データベース入門から設計/運用の初歩まで:書籍案内|技術評論社
  • おわりに(P.353)

入門書という位置づけから、本書では、機能や使用方法に重点をおいて説明しました。よって、アーキテクチャについての説明を割愛しているところがあります。しかし、ひととおり、 Oracle を使えるようになったら、アーキテクチャにも目を向けてみてください。エンジニアであれば、「ソフトウェアがどうやって動いているか」ということに興味を持つのは自然なことですし、なにより、アーキテクチャを知っているかどうかで、理解度やスキルの伸びに大きな差が出てきます。


目次

はじめに
本書について
第1章 データベースを知る
1.1 なぜデータベースは必要なのか
1.2 リレーショナルデータベースの基礎
本書の構成
第2章 Oracleを使ってみる
2.1 データベースを構築する
2.2 データベースに接続する
2.3 データベースを起動/停止する
2.4 学習用ユーザーを作成する
2.5 テーブルとデータ操作の基本
第3章 より高度なデータ操作を学ぶ
3.1 データを複雑な条件で検索する
3.2 データを加工/集計する
3.3 NULLとIS NULL条件
3.4 SELECT文とSELECT文を組み合わせる
3.5 テーブルを結合する
3.6 データの表示画面にこだわる
3.7 トランザクションでデータを安全に更新する
第4章 データをより高速に/安全に扱うしくみ
4.1 検索処理を高速化するインデックス
4.2 SELECT文をシンプルにまとめるビュー
4.3 不正なデータの混入を防ぐ制約
4.4 連番を振り出すシーケンス
4.5 セキュリティ機構の基礎となるユーザー機能
4.6 ユーザー権限を制御する
第5章 テーブル設計の基本を知る
5.1 テーブル設計とは
5.2 第1ステップ‐概念設計
5.3 第2ステップ‐論理設計
5.4 第3ステップ‐物理設計
第6章 データベース運用/管理のポイントを押さえる
6.1 データベースにおける運用/管理の重要性
6.2 バックアップを取ってデータを守る
6.3 データベースのメンテナンス
6.4 データベースを監視する
6.5 ネットワーク環境/本番環境でOracleに接続する
6.6 トラブルに立ち向かうためには
おわりに
索引
著者略歴
監修者略歴

*1データベースを勉強するなら Oracle Database から入るのがオススメです

2018-03-10

MySQL InnoDB で大きなトランザクションを見つける

SHOW ENGINE INNODB STATUS の

で、History list length が大きいとMySQLインスタンス全体での UNDO のサイズが大きくなりロールバックにかかる時間が長くなっており、undo log entries が大きいトランザクションが UNDO ページを大量に使っている犯人だと類推できる。undo log entries は insert/update/delete などを行った行数(未コミット)でサイズではない。

MariaDB 5.5.27以降10未満(XtraDB)では Information Schema の INNODB_UNDO_LOGS. SIZE で undo log が使っているサイズを確認できる。


  • テーブルを作成する
> create table undo_test(
	col1 char(255),
	col2 char(255),
	col3 char(255),
	col4 char(255),
	col5 char(255)
);
  • 1行 insert する
> start transaction;
> insert into undo_test(col1) values('1');
> show engine innodb stauts\G
(中略)
------------
TRANSACTIONS
------------
TRANSACTIONS
------------
Trx id counter 111207046
Purge done for trx's n:o < 111206999 undo n:o < 0 state: running but idle
History list length 719 ★ history list length がインスタンス全体のUNDOページ数
LIST OF TRANSACTIONS FOR EACH SESSION:
(中略)
---TRANSACTION 111207036, ACTIVE 2 sec ★2秒経過
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1 ★1行insertしたので、undo log が 1になっている
MySQL thread id 8697(★スレッドID), OS thread handle 0x2b9eb4b47700, query id 1400669 27.0.3.145(★接続元IP) awsuser(★ユーザー名) delayed send ok done
  • 5行 insert する
> start transaction;
> insert into undo_test(col1) values('2');
> insert into undo_test(col1) values('3');
> insert into undo_test(col1) values('4');
> insert into undo_test(col1) values('5');
  • 行数を確認する
> select count(1) from undo_test;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)
> show engine innodb stauts\G
(中略)
------------
TRANSACTIONS
------------
Trx id counter 111207337
Purge done for trx's n:o < 111206999 undo n:o < 0 state: running but idle
History list length 808
LIST OF TRANSACTIONS FOR EACH SESSION:
(中略)
---TRANSACTION 111207036, ACTIVE 58 sec ★58秒経過
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 5 ★5行insertしたので、undo log が 5 になっている
MySQL thread id 8697, OS thread handle 0x2b9ef9e43700, query id 1400956 27.0.3.145 awsuser cleaned up
Trx read view will not see trx with id >= 111207290, sees < 111207036
> call mysql.rds_kill(8697); ★show engine innodb stauts の TRANSACTIONS に表示されていた MySQL thread id 
> show engine innodb status\G
(中略)
------------
TRANSACTIONS
------------
Trx id counter 111208582
Purge done for trx's n:o < 111206999 undo n:o < 0 state: running but idle
History list length 726
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 111208485, not started
MySQL thread id 8719, OS thread handle 0x2b9eb4a43700, query id 1402459 localhost rdsadmin cleaned up
---TRANSACTION 111205044, not started
MySQL thread id 8664, OS thread handle 0x2b9eb4bc9700, query id 1402491 27.0.3.145 awsuser System lock
show engine innodb status
---TRANSACTION 111208058, not started
MySQL thread id 11, OS thread handle 0x2b9eb37ed700, query id 1401866 localhost rdsadmin delayed send ok done
---TRANSACTION 111208576, not started
MySQL thread id 2, OS thread handle 0x2b9eb4a43700, query id 1402490 localhost rdsadmin delayed send ok done
トランザクションがなくなっている

参考

Global history and purge operations

In addition to every record having a reference to its previous version, there is also a global view of the history of the entire database, called the “history list”. As each transaction is committed, its history is linked into this global history list in transaction serialization (commit) order. The history list is used primarily for cleaning up after a transaction, once no existing read view still needs its history (all other transactions have completed).

In the background, InnoDB runs a continuous “purge” process which is responsible for two things:

  • Actually deleting delete-marked records2, if the current version of the record in the index at the time of purge is still delete-marked and bears the same transaction ID. (That is, the record hasn’t been re-inserted.)
  • Freeing undo log pages and unlinking them from the global history list to make them available for re-use.

InnoDB exposes the total amount of history present in the system as a “History list length”, which can be seen in SHOW ENGINE INNODB STATUS. This is the count of all database modifications present in the undo logs, in units of undo logs (which may contain a single record modification or many).

The basics of the InnoDB undo logging and history system – Jeremy Cole

XtraDB は Information Schema の INNODB_UNDO_LOGS. SIZE(Size in pages of the segment) で undo log が使っているサイズが分かる。MariaDB 5.5.27 で入って、10では削除されてる。

Information Schema INNODB_UNDO_LOGS Table

The Information Schema INNODB_UNDO_LOGS table is a Percona enchancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains information about the the InnoDB undo log, with each record being an undo log segment. It was removed in MariaDB 10.0.

It has the following columns:

  • ColumnDescription
  • TRX_ID: Unique transaction ID number, matching the value from the information_schema.INNODB_TRX table.
  • RSEG_ID: Rollback segment ID, matching the value from the information_schema.INNODB_RSEG table.
  • USEG_ID: Undo segment ID.
  • SEGMENT_TYPE: Indicates the operation type, for example INSERT or UPDATE.
  • STATE: Segment state: one of ACTIVE (contains active transaction undo log), CACHED, TO_FREE (insert undo segment can be freed), TO_PURGE (update undo segment won't be re-used and can be purged when all undo data is removed) or PREPARED (segment of a prepared transaction).
  • SIZE: Size in pages of the segment.
Information Schema INNODB_UNDO_LOGS Table - MariaDB Knowledge Base

ソースコード

  • mysql-5.6.10/storage/innobase/lock/lock0lock.cc
/*********************************************************************//**
Prints info of locks for all transactions.
@return FALSE if not able to obtain lock mutex
and exits without printing info */
UNIV_INTERN
ibool
lock_print_info_summary(
/*====================*/
        FILE*   file,   /*!< in: file where to print */
        ibool   nowait) /*!< in: whether to wait for the lock mutex */
{
        /* if nowait is FALSE, wait on the lock mutex,
        otherwise return immediately if fail to obtain the
        mutex. */
        if (!nowait) {
                lock_mutex_enter();
        } else if (lock_mutex_enter_nowait()) {
                fputs("FAIL TO OBTAIN LOCK MUTEX, "
                      "SKIP LOCK INFO PRINTING\n", file);
                return(FALSE);
        }

        if (lock_deadlock_found) {
                fputs("------------------------\n"
                      "LATEST DETECTED DEADLOCK\n"
                      "------------------------\n", file);

                if (!srv_read_only_mode) {
                        ut_copy_file(file, lock_latest_err_file);
                }
        }

        fputs("------------\n"
              "TRANSACTIONS\n"
              "------------\n", file);

        fprintf(file, "Trx id counter " TRX_ID_FMT "\n",
                trx_sys_get_max_trx_id());

        fprintf(file,
                "Purge done for trx's n:o < " TRX_ID_FMT
                " undo n:o < " TRX_ID_FMT " state: ",
                purge_sys->iter.trx_no,
                purge_sys->iter.undo_no);

        /* Note: We are reading the state without the latch. One because it
        will violate the latching order and two because we are merely querying
        the state of the variable for display. */

        switch (purge_sys->state){
        case PURGE_STATE_EXIT:
        case PURGE_STATE_INIT:
                /* Should never be in this state while the system is running. */
                ut_error;

        case PURGE_STATE_DISABLED:
                fprintf(file, "disabled");
                break;

        case PURGE_STATE_RUN:
                fprintf(file, "running");
                /* Check if it is waiting for more data to arrive. */
                if (!purge_sys->running) {
                        fprintf(file, " but idle");
                }
                break;

        case PURGE_STATE_STOP:
                fprintf(file, "stopped");
                break;
        }

        fprintf(file, "\n");

        fprintf(file,
                "History list length %lu\n",
                (ulong) trx_sys->rseg_history_len);

#ifdef PRINT_NUM_OF_LOCK_STRUCTS
        fprintf(file,
                "Total number of lock structs in row lock hash table %lu\n",
                (ulong) lock_get_n_rec_locks());
#endif /* PRINT_NUM_OF_LOCK_STRUCTS */
        return(TRUE);
}
  • mysql-5.6.10/storage/innobase/trx/trx0trx.cc
/**********************************************************************//**
Prints info about a transaction.
Caller must hold trx_sys->mutex. */
UNIV_INTERN
void
trx_print_low(
/*==========*/
        FILE*           f,
                        /*!< in: output stream */
        const trx_t*    trx,
                        /*!< in: transaction */
        ulint           max_query_len,
                        /*!< in: max query length to print,
                        or 0 to use the default max length */
        ulint           n_rec_locks,
                        /*!< in: lock_number_of_rows_locked(&trx->lock) */
        ulint           n_trx_locks,
                        /*!< in: length of trx->lock.trx_locks */
        ulint           heap_size)
                        /*!< in: mem_heap_get_size(trx->lock.lock_heap) */
{
        ibool           newline;
        const char*     op_info;

        ut_ad(mutex_own(&trx_sys->mutex));

        fprintf(f, "TRANSACTION " TRX_ID_FMT, trx->id);

        /* trx->state cannot change from or to NOT_STARTED while we
        are holding the trx_sys->mutex. It may change from ACTIVE to
        PREPARED or COMMITTED. */
        switch (trx->state) {
        case TRX_STATE_NOT_STARTED:
                fputs(", not started", f);
                goto state_ok;
        case TRX_STATE_ACTIVE:
                fprintf(f, ", ACTIVE %lu sec",
                        (ulong) difftime(time(NULL), trx->start_time));
                goto state_ok;
        case TRX_STATE_PREPARED:
                fprintf(f, ", ACTIVE (PREPARED) %lu sec",
                        (ulong) difftime(time(NULL), trx->start_time));
                goto state_ok;
        case TRX_STATE_COMMITTED_IN_MEMORY:
                fputs(", COMMITTED IN MEMORY", f);
                goto state_ok;
        }
        fprintf(f, ", state %lu", (ulong) trx->state);
        ut_ad(0);
state_ok:

        /* prevent a race condition */
        op_info = trx->op_info;

        if (*op_info) {
                putc(' ', f);
                fputs(op_info, f);
        }

        if (trx->is_recovered) {
                fputs(" recovered trx", f);
        }

        if (trx->declared_to_be_inside_innodb) {
                fprintf(f, ", thread declared inside InnoDB %lu",
                        (ulong) trx->n_tickets_to_enter_innodb);
        }

        putc('\n', f);

        if (trx->n_mysql_tables_in_use > 0 || trx->mysql_n_tables_locked > 0) {
                fprintf(f, "mysql tables in use %lu, locked %lu\n",
                        (ulong) trx->n_mysql_tables_in_use,
                        (ulong) trx->mysql_n_tables_locked);
        }

        newline = TRUE;

        /* trx->lock.que_state of an ACTIVE transaction may change
        while we are not holding trx->mutex. We perform a dirty read
        for performance reasons. */

        switch (trx->lock.que_state) {
        case TRX_QUE_RUNNING:
                newline = FALSE; break;
        case TRX_QUE_LOCK_WAIT:
                fputs("LOCK WAIT ", f); break;
        case TRX_QUE_ROLLING_BACK:
                fputs("ROLLING BACK ", f); break;
        case TRX_QUE_COMMITTING:
                fputs("COMMITTING ", f); break;
        default:
                fprintf(f, "que state %lu ", (ulong) trx->lock.que_state);
        }

        if (n_trx_locks > 0 || heap_size > 400) {
                newline = TRUE;

                fprintf(f, "%lu lock struct(s), heap size %lu,"
                        " %lu row lock(s)",
                        (ulong) n_trx_locks,
                        (ulong) heap_size,
                        (ulong) n_rec_locks);
        }

        if (trx->has_search_latch) {
                newline = TRUE;
                fputs(", holds adaptive hash latch", f);
        }

        if (trx->undo_no != 0) {
                newline = TRUE;
                fprintf(f, ", undo log entries "TRX_ID_FMT, trx->undo_no);
        }

        if (newline) {
                putc('\n', f);
        }

        if (trx->mysql_thd != NULL) {
                innobase_mysql_print_thd(f, trx->mysql_thd, max_query_len);
        }
}
  • mysql-5.6.10/storage/innobase/handler/ha_innodb.cc
/************************************************************************//**
Implements the SHOW ENGINE INNODB STATUS command. Sends the output of the
InnoDB Monitor to the client.
@return 0 on success */
static
int
innodb_show_status(
/*===============*/
        handlerton*     hton,   /*!< in: the innodb handlerton */
        THD*            thd,    /*!< in: the MySQL query thread of the caller */
        stat_print_fn*  stat_print)
{
        trx_t*                  trx;
        static const char       truncated_msg[] = "... truncated...\n";
        const long              MAX_STATUS_SIZE = 1048576;
        ulint                   trx_list_start = ULINT_UNDEFINED;
        ulint                   trx_list_end = ULINT_UNDEFINED;

        DBUG_ENTER("innodb_show_status");
        DBUG_ASSERT(hton == innodb_hton_ptr);

        /* We don't create the temp files or associated
        mutexes in read-only-mode */

        if (srv_read_only_mode) {
                DBUG_RETURN(0);
        }

        trx = check_trx_exists(thd);

        trx_search_latch_release_if_reserved(trx);

        innobase_srv_conc_force_exit_innodb(trx);

        /* We let the InnoDB Monitor to output at most MAX_STATUS_SIZE
        bytes of text. */

        char*   str;
        ssize_t flen, usable_len;

        mutex_enter(&srv_monitor_file_mutex);
        rewind(srv_monitor_file);

        srv_printf_innodb_monitor(srv_monitor_file, FALSE,
                                  &trx_list_start, &trx_list_end);

        os_file_set_eof(srv_monitor_file);

        if ((flen = ftell(srv_monitor_file)) < 0) {
                flen = 0;
        }

        if (flen > MAX_STATUS_SIZE) {
                usable_len = MAX_STATUS_SIZE;
                srv_truncated_status_writes++;
        } else {
                usable_len = flen;
        }

        /* allocate buffer for the string, and
        read the contents of the temporary file */

        if (!(str = (char*) my_malloc(usable_len + 1, MYF(0)))) {
                mutex_exit(&srv_monitor_file_mutex);
                DBUG_RETURN(1);
        }

        rewind(srv_monitor_file);

        if (flen < MAX_STATUS_SIZE) {
                /* Display the entire output. */
                flen = fread(str, 1, flen, srv_monitor_file);
        } else if (trx_list_end < (ulint) flen
                   && trx_list_start < trx_list_end
                   && trx_list_start + (flen - trx_list_end)
                   < MAX_STATUS_SIZE - sizeof truncated_msg - 1) {

                /* Omit the beginning of the list of active transactions. */
                ssize_t len = fread(str, 1, trx_list_start, srv_monitor_file);

                memcpy(str + len, truncated_msg, sizeof truncated_msg - 1);
                len += sizeof truncated_msg - 1;
                usable_len = (MAX_STATUS_SIZE - 1) - len;
                fseek(srv_monitor_file, flen - usable_len, SEEK_SET);
                len += fread(str + len, 1, usable_len, srv_monitor_file);
                flen = len;
        } else {
                /* Omit the end of the output. */
                flen = fread(str, 1, MAX_STATUS_SIZE - 1, srv_monitor_file);
        }

        mutex_exit(&srv_monitor_file_mutex);

        stat_print(thd, innobase_hton_name, (uint) strlen(innobase_hton_name),

        my_free(str);

        DBUG_RETURN(0);
}

参考