Hatena::ブログ(Diary)

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

2018-07-31

PostgreSQL 10 の宣言的パーティションについて

特徴

  • CREATE TABLE 文でパーティショニングが構築可能に
  • ALTER TABLE 文で、子テーブルの追加/除去が可能
  • 階層的なパーティショニングも可能
  • テーブルに外部サーバテーブル(FDW)を使用可能
  • INSERT の高速化
  • テーブル継承の機能を使用して実装されている

制約

参考

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-06-12

MySQL on CentOS 6.9 に sysbench でベンチマークをかけてみる

ファイルシステムの初期化

  • ファイルシステムを初期化する
# mkdir /nvme0n1 /e32000
# fdisk -l
# mkfs.ext4 /dev/nvme0n1
# mkfs.ext4 /dev/xvdb
# mount /dev/nvme0n1 /nvme01
# mount /dev/xvdb /e32000

インストール

  • MySQL の yum リポジトリを追加する
# yum -y install http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
# yum -y install mysql-community-server
# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
# sudo yum -y install sysbench
# yum -y install strace 
# mysql -u root
  • データベースを作成する
mysql> create database mydb;
  • テーブルを作成してデータをロードする
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-user=root \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
  • datadir 配下のファイルをコピーしておく
# service mysqld stop
# cp -pr /var/lib/mysql /nvme01/
# cp -pr /var/lib/mysql /dev/shm/
# service mysqld start
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-db=mydb \
 --mysql-user=root \
 --time=30 \
 --db-ps-mode=disable \
 --threads=1 \
 run

結果

  • E300
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            101892
        write:                           29112
        other:                           14556
        total:                           145560
    transactions:                        7278   (242.57 per sec.)
    queries:                             145560 (★4851.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0013s
    total number of events:              7278

Latency (ms):
         min:                                    3.78
         avg:                                    4.12
         max:                                   22.54
         95th percentile:                        4.33
         sum:                                29979.87

Threads fairness:
    events (avg/stddev):           7278.0000/0.00
    execution time (avg/stddev):   29.9799/0.00
  • E32000
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            99582
        write:                           28452
        other:                           14226
        total:                           142260
    transactions:                        7113   (237.08 per sec.)
    queries:                             142260 (4741.55 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0007s
    total number of events:              7113

Latency (ms):
         min:                                    3.88
         avg:                                    4.21
         max:                                    7.83
         95th percentile:                        4.41
         sum:                                29979.78

Threads fairness:
    events (avg/stddev):           7113.0000/0.00
    execution time (avg/stddev):   29.9798/0.00
  • NVMe
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            152558
        write:                           43588
        other:                           21794
        total:                           217940
    transactions:                        10897  (363.17 per sec.)
    queries:                             217940 (★7263.49 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0027s
    total number of events:              10897

Latency (ms):
         min:                                    2.49
         avg:                                    2.75
         max:                                    4.85
         95th percentile:                        2.81
         sum:                                29972.98

Threads fairness:
    events (avg/stddev):           10897.0000/0.00
    execution time (avg/stddev):   29.9730/0.00
  • tmpfs
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            209902
        write:                           59972
        other:                           29986
        total:                           299860
    transactions:                        14993  (499.73 per sec.)
    queries:                             299860 (★9994.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0003s
    total number of events:              14993

Latency (ms):
         min:                                    1.89
         avg:                                    2.00
         max:                                    4.16
         95th percentile:                        2.07
         sum:                                29982.17

Threads fairness:
    events (avg/stddev):           14993.0000/0.00
    execution time (avg/stddev):   29.9822/0.00

システムコール(fsyncの所要時間)

  • コマンド
# strace -yy -fe fsync -Tttp <PID>
  • E300
[pid 28833] 15:16:59.322025 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001616>
[pid 28833] 15:16:59.340313 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001362>
[pid 28833] 15:16:59.358445 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001512>
  • NVMe
[pid 28510] 15:14:48.225248 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000204>
[pid 28510] 15:14:48.241499 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000194>
[pid 28510] 15:14:48.257621 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000224>
  • tmpfs
[pid  8264] 15:12:02.394681 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000029>
[pid  8264] 15:12:02.410769 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000029>
[pid  8264] 15:12:02.426758 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000030>
[pid  8264] 15:12:02.442635 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000030>

システムコール(集計)

  • コマンド
# strace -fcp <PID>
  • E300
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 55.57   51.989330       53487       972           io_getevents
 32.08   30.015424        3314      9057      1740 futex
 11.11   10.398417      142444        73           select
  1.01    0.945853      157642         6         5 restart_syscall
  0.15    0.142054          84      1686           fsync
  0.03    0.027272           0    199846           gettimeofday
  0.02    0.014865           0    103448           clock_gettime
  0.01    0.008399           0     65686         2 recvfrom
  0.01    0.007503           0     32842           sendto
  0.00    0.002924           0      8466           madvise
  0.00    0.001104           0      6570           sched_yield
  0.00    0.000734           0      1681           pwrite
  0.00    0.000233           1       339           pread
  0.00    0.000042           0       401           io_submit
  0.00    0.000020           3         6           read
  0.00    0.000000           0         6           open
  0.00    0.000000           0         5           close
  0.00    0.000000           0         1           stat
  0.00    0.000000           0         6           lstat
  0.00    0.000000           0         2           poll
  0.00    0.000000           0         9           lseek
  0.00    0.000000           0         3           mmap
  0.00    0.000000           0        22           mprotect
  0.00    0.000000           0         1           munmap
  0.00    0.000000           0         2         1 access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         5           fcntl
  0.00    0.000000           0         6           getcwd
------ ----------- ----------- --------- --------- ----------------
100.00   93.554174                431151      1749 total
  • NVMe
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 56.05   54.565652       37710      1447           io_getevents
 32.70   31.833832        3075     10353      1931 futex
 11.14   10.849351      139094        78           select
  0.03    0.027523           0    223938           gettimeofday
  0.02    0.022996        3833         6         5 restart_syscall
  0.02    0.016611           9      1892           fsync
  0.01    0.014254           0    115670           clock_gettime
  0.01    0.010720           0     73445         1 recvfrom
  0.01    0.007561           0     36722           sendto
  0.00    0.003592           0      9214           madvise
  0.00    0.002005           0      7359           sched_yield
  0.00    0.000726           0      1883           pwrite
  0.00    0.000205           0       447           pread
  0.00    0.000163           0       888           io_submit
  0.00    0.000000           0         6           read
  0.00    0.000000           0         6           open
  0.00    0.000000           0         5           close
  0.00    0.000000           0         1           stat
  0.00    0.000000           0         6           lstat
  0.00    0.000000           0         1           poll
  0.00    0.000000           0         9           lseek
  0.00    0.000000           0         3           mmap
  0.00    0.000000           0        22           mprotect
  0.00    0.000000           0         1           munmap
  0.00    0.000000           0         2         1 access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         5           fcntl
  0.00    0.000000           0         6           getcwd
------ ----------- ----------- --------- --------- ----------------
100.00   97.355191                483419      1939 total
  • tmpfs
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 55.82   41.885262       11926      3512           io_getevents
 33.08   24.819955        1427     17391      2800 futex
 11.02    8.271746      114885        72           select
  0.03    0.023314           0    230171           gettimeofday
  0.02    0.011618           0    117623           clock_gettime
  0.01    0.007673           0     74685         1 recvfrom
  0.01    0.006094           0     37342           sendto
  0.00    0.002555           0      8772           madvise
  0.00    0.001471           0      7508           sched_yield
  0.00    0.001067           0      3799           io_submit
  0.00    0.001000         167         6         5 restart_syscall
  0.00    0.000930           0      2012           pwrite
  0.00    0.000315           0      2100           fsync
  0.00    0.000000           0         1           close
  0.00    0.000000           0         1           poll
  0.00    0.000000           0         3           pread
  0.00    0.000000           0         1           access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         3           fcntl
------ ----------- ----------- --------- --------- ----------------
100.00   75.033000                505006      2807 total

起動と停止

  • 停止
# service mysqld stop
  • datadir の変更
# vi /etc/my.conf
#datadir=/var/lib/mysql
#datadir=/e32000/mysql
#datadir=/nvme01/mysql
datadir=/dev/shm/mysql

  • 起動
# service mysqld start

環境

  • 16vCPU
  • 122 GB Memory
  • CentOS 6.9 (2.6.32-696.16.1.el6.x86_64)
  • 5.6.40 MySQL Community Server (GPL)

参考

2018-06-11

MySQL の InnoDB テーブルの断片化の影響を調べてみた

準備

  • テーブルを作成して1千万件データをロードする
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=10000000 \
 --mysql-host=aurora01.******.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=****** \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
  • テーブルの件数と空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   3145728 |    9867937 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)
  • 断片化させるプロシージャを作成する
mysql> delimiter //
mysql> create procedure fragment_sbtest1(in x int, in y int)
begin
 while x < y do
  delete from mydb.sbtest1 where id = x;
  set x = x + 2;
 end while;
end
//
mysql> delimiter ;

クエリ実行時間を測定する

1千万件
  • 実行時間は1分15秒程度
mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|   10000000 |
+------------+
1 row in set (1 min 15.50 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|   10000000 |
+------------+
1 row in set (1 min 17.92 sec)
5百万件(断片化状態)
  • データを歯抜けに削除する
mysql> call fragment_sbtest1(1, 10000000);
  • テーブルの件数と空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   7340032 |    4922775 |
+--------------+------------+-----------+------------+
1 row in set (0.02 sec)
  • 実行時間は1分10秒程度でほぼ変化なし。
mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (1 min 8.51 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (1 min 12.69 sec)

5百万件(断片化後)
  • 断片化を解消する
mysql> alter table sbtest1 engine innodb;
Query OK, 0 rows affected (34.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • テーブルの件数と空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   4194304 |    5154227 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)
  • 実行時間は35秒程度と半分程度に短縮。
mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (35.31 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (34.81 sec)

環境

"select count(pad) from sbtest1" で実行時間を測定したインスタンスは以下の通り。性能差を測定するためミニマムな環境にした。

  • db.t2.small
  • query_cache_type: 0
  • innodb_buffer_pool_size: 67108864