Oracle MySQL Cloud Serviceのダッシュボードにパッチがアナウンスされていたのでパッチの適用を行いました。基本的な適用フローは以下の手順となります。

プリチェック → バックアップ(時間はDBサイズによる)→ パッチ適用 → パッチ適用完了
※バックアップはmysqldump(論理バックアップ)では無く、mysqlbackup(物理バックアップ)なので処理は高速です。

MySQL Cloud Serviceに関しては、こちらにてドキュメントとトライアルを確認する事が出来ます。
https://cloud.oracle.com/ja_JP/mysql
※ 2016年12月現在ではU.SとEMEAでのみ、MySQL Cloud Serviceを提供しているので検証環境、海外サービス展開、Enterprise版の機能検証に利用するのが宜しいかと思います。

■ パッチ適用前

mysql> select @@hostname,@@version,now();
+-----------------+-------------------------------------------+---------------------+
| @@hostname      | @@version                                 | now()               |
+-----------------+-------------------------------------------+---------------------+
| study01-mysql-1 | 5.7.15-enterprise-commercial-advanced-log | 2016-12-13 08:16:06 |
+-----------------+-------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 

1) パッチ適用前の状態

2) パッチ適用前の事前チェック

3) 事前チェック完了

4) パッチ適用開始による事前フルバックアップの自動実施

5) バックアップ完了と同時にパッチの適用開始

6) パッチ適用の完了

補足:バックアップの完了した時点でのダッシュボードでの履歴確認

■ パッチ適用中のバックグラウド処理

1) MySQL5.7.16のバイナリーを取得

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   15317     1  0 08:16 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   16372 15317  1 08:16 ?        00:00:01 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   17135 17122  0 08:19 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/msaas-provisioning-utils.sh && fetchArtifact /tmp/patch20161213-081909/msaas-installer-patch.zip  https://em2.storage.oraclecloud.com/v1/backups-jimops/jcs MSCS/5.7.16/MySQL_Binaries-5.7.16.zip
oracle   17183 15032  0 08:19 pts/0    00:00:00 grep mysql

2) 展開

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   17242 17122  0 08:19 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/msaas-provisioning-utils.sh && source /u01/app/oracle/tools/mscs/mysql/vm-scripts/mysql-installation-utils.sh && extractMysql /tmp/patch-msaas-installers20161213-081909/mysql/*.tar.gz 5.7.16 /u01/bin oracle
oracle   17244 17243 11 08:19 ?        00:00:01 tar zxvf /tmp/patch-msaas-installers20161213-081909/mysql/mysql-installer-5.7.16.tar.gz --directory=/u01/bin
oracle   17289 15032  0 08:19 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

3) MySQLへのネットワークアクセスをOFFにして起動して、パッチを適用 (ホストにはアクセス可能)

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   17346     1  0 08:20 ?        00:00:00 /bin/sh ./bin/mysqld_safe --skip-networking
oracle   18416 17346 45 08:20 ?        00:00:02 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --skip-networking --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   18533 17122  0 08:20 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/mysql-installation-utils.sh && shutdownMysql oracle 1
oracle   18539 18533  0 08:20 ?        00:00:00 mysqladmin shutdown -uoracle
oracle   18582 15032  0 08:20 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

4) パッチ適用が完了してMySQLを再起動して完了

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   18591     1  0 08:20 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   19646 18591  3 08:20 ?        00:00:00 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   19883 15032  0 08:21 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

■ パッチ適用後

mysql> select @@hostname,@@version,now();
+-----------------+-------------------------------------------+---------------------+
| @@hostname      | @@version                                 | now()               |
+-----------------+-------------------------------------------+---------------------+
| study01-mysql-1 | 5.7.16-enterprise-commercial-advanced-log | 2016-12-13 08:23:18 |
+-----------------+-------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 

REST APIを利用したインスタンスの詳細確認
Path: /paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/applied

[admin@misc01 opc]$ cat temp.sh 
#!/bin/sh

curl -X GET -u "{user:password}" \
-H "X-ID-TENANT-NAME:{ServiceDomainID}" -H "Accept: application/json" \
https://psm.europe.oraclecloud.com/paas/api/v1.1/instancemgmt/{ServiceDomainID}/services/MySQLCS/instances/{InstanceName}/patches/applied
[admin@misc01 opc]$ 

以下、実行結果

[admin@misc01 opc]$ ./patchchk_opc_mysql.sh 
[{"backupStatus":"Available","additionalNote":"Apply Patch to Server.\nVersion Up to 5.7.16\nby admin","appliedBy":"opc_user","appliedDate":"2016-12-13T08:18:35.189+0000","totalTime":"2 min, 20 sec","patchId":"5.7.16-EE","patchDescription":"MySQL Server version 5.7.16","patchReleaseUrl":"https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-16.html","releaseDate":"2016-10-12T09:00:00.000+0000","resultMessage":"MySQL patching succeeded","lastStatus":"COMPLETED","lastStatusMessage":"MySQL patching succeeded","componentPatches":{"mysql":{"id":1201,"version":"5.7","releaseVersion":"5.7.16","displayName":"MySQL Server version 5.7.16","description":"MySQL Server version 5.7.16","zipBundles":{"MSAAS":{"id":1214,"md5sum":"ec534747c1e3d5d0f644ff3d9bd52115","provisioningObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","storageKey":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","releaseDate":"10/12/2016","releaseUrl":"https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-16.html","displayName":"MySQL Server version 5.7.16"}},"preserveFiles":[]}},"patchComponents":[{"id":1201,"component":"mysql","version":"5.7","md5sum":"ec534747c1e3d5d0f644ff3d9bd52115","provisioningObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","patchingObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","preserveFiles":[]}],"patchType":"Quarterly","patchCategory":"Upgrade","patchSeverity":"Normal","jobId":"3260506","displayName":"5.7.16","toVersion":"5.7.16","backupId":"90b6eda2-969d-4753-9ae6-e8d318760530","inProgress":false,"operationType":"None","id":77602,"patchingResult":{"patchingId":60654,"backupId":"90b6eda2-969d-4753-9ae6-e8d318760530","versionBeforeThisPatch":"mysql 5.7.15","strategy":"Rolling","releaseVersionBeforeThisPatch":"5.7.15","customRollbackId":"3260506_1481617115189","startDate":"2016-12-13T08:18:35.189+0000","endDate":"2016-12-13T08:20:55.660+0000","patchingStatus":"COMPLETED","resultMessage":"MySQL patching succeeded","additionalNote":"Apply Patch to Server.\nVersion Up to 5.7.16\nby admin","appliedBy":"opc_user","jobId":"3260506","completeLog":"","progressMessages":["8:18:35.161 AM Phase initialize started","8:18:35.339 AM Phase initialize completed","8:18:35.696 AM Phase backup started","8:19:06.850 AM Phase backup completed","8:19:07.178 AM Phase patch started","8:19:07.616 AM Phase patch-rex started","8:20:54.463 AM Phase patch-rex completed","8:20:54.984 AM Phase patch completed","8:20:55.360 AM Phase finalize started","8:20:55.532 AM Completed"]},"rollbackId":"77602","rollbackVersion":"mysql 5.7.15","currentPatchLevel":"mysql 5.7.16","progressMessages":["8:18:35.161 AM Phase initialize started","8:18:35.339 AM Phase initialize completed","8:18:35.696 AM Phase backup started","8:19:06.850 AM Phase backup completed","8:19:07.178 AM Phase patch started","8:19:07.616 AM Phase patch-rex started","8:20:54.463 AM Phase patch-rex completed","8:20:54.984 AM Phase patch completed","8
[admin@misc01 opc]$ 

確認:スレーブ→マスターの順番でアップグレードし、MySQL Cloud Serviceで稼働しているレプリケーションも問題無いことを確認済み

REST APIを利用してパッチ適用する場合は、以下のURLを参照下さい。
【書式】
1) Pre-Check

/paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/checks/{patchId}

http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/op-paas-api-v1.1-instancemgmt-%7BidentityDomainId%7D-services-MySQLCS-instances-%7BserviceName%7D-patches-checks-%7BpatchId%7D-put.html

2) Apply Patch

/paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/{patchId}

http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/op-paas-api-v1.1-instancemgmt-%7BidentityDomainId%7D-services-MySQLCS-instances-%7BserviceName%7D-patches-%7BpatchId%7D-put.html

ALL) Patches REST Endpoints
http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/api-Patches.html


MySQLで疑似的なマテリアライズド・ビューを作成

MySQLにはOracleで利用可能な、マテリアライズド・ビューは実装されてません。
その為、トリガーで対応したり、ワークテーブルを作成しておいて、定期的にデータを入れ替えるような処理で対応する必要があります。

過去の経験では、デイリー、ウイークリーランキングを作成する為に、以下のようにMySQLのEVENT SCHEDULE機能とREPLACE 構文を利用して変更されたデータを
定期的に入れ替える処理を行ないマテリアライズド・ビューの代わりに利用していました。
イベントは、イベントを作成してスケジュールします。但し、イベントスケジューラが有効になっていないかぎり実行されません。

メモ:EVENTの代わりに、LinuxのcronやWindows Task Schedulerでも良いかと思います。

■ 検証用にテーブルの作成
ここでは、City_MasterがオリジナルでCity_SnapShotはSNAPコピーとしています。
オリジナルのデータはMySQLのオフィシャルサイトからダウンロードしたWorldデータベースのCityテーブルのデータを利用しています。

root@localhost [world2]> CREATE TABLE `City_Master` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `Name` char(35) NOT NULL DEFAULT '',
    ->   `CountryCode` char(3) NOT NULL DEFAULT '',
    ->   `District` char(20) NOT NULL DEFAULT '',
    ->   `Population` int(11) NOT NULL DEFAULT '0',
    ->   `test` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `idx_CountryCode` (`CountryCode`),
    ->   KEY `idx_City2_Covering` (`ID`,`Name`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.08 sec)

root@localhost [world2]> insert into City_Master select * from City;
Query OK, 4079 rows affected (0.15 sec)
Records: 4079  Duplicates: 0  Warnings: 0


root@localhost [world2]> CREATE TABLE `City_SnapShot` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `Name` char(35) NOT NULL DEFAULT '',
    ->   `CountryCode` char(3) NOT NULL DEFAULT '',
    ->   `District` char(20) NOT NULL DEFAULT '',
    ->   `Population` int(11) NOT NULL DEFAULT '0',
    ->   `test` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `idx_CountryCode` (`CountryCode`),
    ->   KEY `idx_City2_Covering` (`ID`,`Name`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

root@localhost [world2]> 

root@localhost [world2]> REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master;
Query OK, 4079 rows affected (0.19 sec)
Records: 4079  Duplicates: 0  Warnings: 0

root@localhost [world2]> select * from City_Master where ID = 100;
+-----+---------+-------------+------------+------------+------+
| ID  | Name    | CountryCode | District   | Population | test |
+-----+---------+-------------+------------+------------+------+
| 100 | Paraná  | ARG         | Entre Rios |     207041 | NULL |
+-----+---------+-------------+------------+------------+------+
1 row in set (0.00 sec)

root@localhost [world2]> select * from City_SnapShot where ID = 100;
+-----+---------+-------------+------------+------------+------+
| ID  | Name    | CountryCode | District   | Population | test |
+-----+---------+-------------+------------+------------+------+
| 100 | Paraná  | ARG         | Entre Rios |     207041 | NULL |
+-----+---------+-------------+------------+------------+------+
1 row in set (0.00 sec)


■ マスターテーブルでデータを変更してみます。(207041->207045)した後に、
REPLACEコマンドでSnapShotテーブルへ変更を反映してみます。

メモ:PKが同じであれば、データを問題無くリプレースするかどうかの動作確認。

root@localhost [world2]> update City_Master set Population = 207045 where ID = 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [world2]> select * from City_Master where ID = 100;
+-----+---------+-------------+------------+------------+------+
| ID  | Name    | CountryCode | District   | Population | test |
+-----+---------+-------------+------------+------------+------+
| 100 | Paraná  | ARG         | Entre Rios |     207045 | NULL |
+-----+---------+-------------+------------+------------+------+
1 row in set (0.00 sec)

root@localhost [world2]> select * from City_SnapShot where ID = 100;
+-----+---------+-------------+------------+------------+------+
| ID  | Name    | CountryCode | District   | Population | test |
+-----+---------+-------------+------------+------------+------+
| 100 | Paraná  | ARG         | Entre Rios |     207041 | NULL |
+-----+---------+-------------+------------+------------+------+
1 row in set (0.00 sec)


root@localhost [world2]> REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master;         
Query OK, 4080 rows affected (0.10 sec)
Records: 4079  Duplicates: 1  Warnings: 0

root@localhost [world2]> select * from City_Master where ID = 100;
+-----+---------+-------------+------------+------------+------+
| ID  | Name    | CountryCode | District   | Population | test |
+-----+---------+-------------+------------+------------+------+
| 100 | Paraná  | ARG         | Entre Rios |     207045 | NULL |
+-----+---------+-------------+------------+------------+------+
1 row in set (0.01 sec)

root@localhost [world2]> select * from City_SnapShot where ID = 100;
+-----+---------+-------------+------------+------------+------+
| ID  | Name    | CountryCode | District   | Population | test |
+-----+---------+-------------+------------+------------+------+
| 100 | Paraná  | ARG         | Entre Rios |     207045 | NULL |
+-----+---------+-------------+------------+------------+------+
1 row in set (0.00 sec)

root@localhost [world2]> 

■ ここから、EVENTを有効にして定期的にデータが自動反映されるスケジュールを設定します。
オプションファイルへ追加


event-scheduler=ON

■ オプションファイルへの設定変更が反映されているかどうか確認。
メモ:SETコマンドで、動的にも設定可能。
event_schedulerが実行されている事を確認したら、EVENTを作成しスケジュールを設定します。


root@localhost [world2]> show variables like 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

root@localhost [world2]> show processlist;
+----+-----------------+-----------+--------+---------+------+------------------------+------------------+
| Id | User            | Host      | db     | Command | Time | State                  | Info             |
+----+-----------------+-----------+--------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost | NULL   | Daemon  |   53 | Waiting on empty queue | NULL             |
|  4 | root            | localhost | world2 | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------+--------+---------+------+------------------------+------------------+
2 rows in set (0.01 sec)

root@localhost [world2]> 



root@localhost [world2]> CREATE EVENT SNAP_SHOT_VIEW_City_Master_City_SnapShot
    ->    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    ->    DO 
    ->      REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master;
Query OK, 0 rows affected (0.00 sec)

root@localhost [world2]> show events;
+--------+------------------------------------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| Db     | Name                                     | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------+------------------------------------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| world2 | SNAP_SHOT_VIEW_City_Master_City_SnapShot | root@localhost | SYSTEM    | ONE TIME | 2016-06-10 15:23:01 | NULL           | NULL           | NULL   | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8mb4_general_ci |
+--------+------------------------------------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

root@localhost [world2]> SELECT * FROM information_schema.EVENTS\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: world2
          EVENT_NAME: SNAP_SHOT_VIEW_City_Master_City_SnapShot
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: REPLACE INTO City_SnapShot select ID,Name,CountryCode,District,Population,test from City_Master
          EVENT_TYPE: ONE TIME
          EXECUTE_AT: 2016-06-10 15:23:01
      INTERVAL_VALUE: NULL
      INTERVAL_FIELD: NULL
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: NULL
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2016-06-10 14:23:01
        LAST_ALTERED: 2016-06-10 14:23:01
       LAST_EXECUTED: NULL
       EVENT_COMMENT: 
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: utf8mb4_general_ci
1 row in set (0.00 sec)

root@localhost [world2]> 



■ イベントを設定し終わったので、データを変更すれば、
次回のスケジュール(2016-06-10 15:23:01)にはデータが変更される事になります。

データを一件変更してみます。

root@localhost [world2]> select * from City_Master where ID = 1;
+----+-------+-------------+----------+------------+------+
| ID | Name  | CountryCode | District | Population | test |
+----+-------+-------------+----------+------------+------+
|  1 | Kabul | AFG         | Kabol    |    1780000 | NULL |
+----+-------+-------------+----------+------------+------+
1 row in set (0.00 sec)

root@localhost [world2]> select * from City_SnapShot where ID = 1;
+----+-------+-------------+----------+------------+------+
| ID | Name  | CountryCode | District | Population | test |
+----+-------+-------------+----------+------------+------+
|  1 | Kabul | AFG         | Kabol    |    1780000 | NULL |
+----+-------+-------------+----------+------------+------+
1 row in set (0.00 sec)

root@localhost [world2]> update City_Master set Population = 1780001 where ID = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

■ データ反映の確認
2016-06-10 15:23:01にEVENTが実行されたので、
SnapShot側のテーブルのデータも変更されている事が確認出来ます。


root@localhost [world2]> select *,now() from City_Master where ID = 1;
+----+-------+-------------+----------+------------+------+---------------------+
| ID | Name  | CountryCode | District | Population | test | now()               |
+----+-------+-------------+----------+------------+------+---------------------+
|  1 | Kabul | AFG         | Kabol    |    1780001 | NULL | 2016-06-10 14:25:30 |
+----+-------+-------------+----------+------------+------+---------------------+
1 row in set (0.00 sec)

root@localhost [world2]> select *,now() from City_SnapShot where ID = 1;
+----+-------+-------------+----------+------------+------+---------------------+
| ID | Name  | CountryCode | District | Population | test | now()               |
+----+-------+-------------+----------+------------+------+---------------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 | NULL | 2016-06-10 14:25:36 |
+----+-------+-------------+----------+------------+------+---------------------+
1 row in set (0.00 sec)

root@localhost [world2]> 


root@localhost [world2]> select *,now() from City_SnapShot where ID = 1;
+----+-------+-------------+----------+------------+------+---------------------+
| ID | Name  | CountryCode | District | Population | test | now()               |
+----+-------+-------------+----------+------------+------+---------------------+
|  1 | Kabul | AFG         | Kabol    |    1780001 | NULL | 2016-06-10 15:25:16 |
+----+-------+-------------+----------+------------+------+---------------------+
1 row in set (0.00 sec)

root@localhost [world2]> 

■ 集計表の場合
まずは、JPNの人口を合計したテーブルを作成してデータを入力しておく。
メモ:テーブルにはPKが必要です。
メモ:実際の人口は1億2000万を超えています。

root@localhost [world2]> select CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode;
+-------------+-----------------+
| CountryCode | SUM(Population) |
+-------------+-----------------+
| JPN         |        77965107 |
+-------------+-----------------+


root@localhost [world2]> create table City_Summary (
    -> ID int(11) NOT NULL, 
    -> CountryCode char(3) NOT NULL DEFAULT '',
    -> Population int(11) NOT NULL DEFAULT '0',
    -> PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)


root@localhost [world2]> REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@localhost [world2]> select * from City_Summary;
+----+-------------+------------+
| ID | CountryCode | Population |
+----+-------------+------------+
|  1 | JPN         |   77965107 |
+----+-------------+------------+
1 row in set (0.00 sec)

root@localhost [world2]> 

■ 集計表へのデータ反映
東京の人口を10名増やして、EVENTを作成してスケジュールを設定しデータが反映されるか確認してみます。

root@localhost [world2]> update City_Master set Population = 7980240 where Name = 'tokyo';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [world2]> select * from City_Summary;
+----+-------------+------------+
| ID | CountryCode | Population |
+----+-------------+------------+
|  1 | JPN         |   77965107 |
+----+-------------+------------+
1 row in set (0.00 sec)

root@localhost [world2]> CREATE EVENT SNAP_SHOT_VIEW_City_Master_City_Summary
    ->     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE 
    ->     DO 
    ->     REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode;
Query OK, 0 rows affected (0.00 sec)

root@localhost [world2]> 



root@localhost [world2]> SELECT * FROM information_schema.EVENTS 
    -> where EVENT_NAME = 'SNAP_SHOT_VIEW_City_Master_City_Summary'\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: world2
          EVENT_NAME: SNAP_SHOT_VIEW_City_Master_City_Summary
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode
          EVENT_TYPE: ONE TIME
          EXECUTE_AT: 2016-06-10 15:07:09
      INTERVAL_VALUE: NULL
      INTERVAL_FIELD: NULL
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: NULL
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2016-06-10 14:57:09
        LAST_ALTERED: 2016-06-10 14:57:09
       LAST_EXECUTED: NULL
       EVENT_COMMENT: 
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: utf8mb4_general_ci
1 row in set (0.01 sec)

root@localhost [world2]> 

■ 集計データ結果確認
設定時間に確認してみると、集計データが反映されている事が確認出来ました。

root@localhost [world2]> select *,now() from City_Summary;
+----+-------------+------------+---------------------+
| ID | CountryCode | Population | now()               |
+----+-------------+------------+---------------------+
|  1 | JPN         |   77965107 | 2016-06-10 14:59:26 |
+----+-------------+------------+---------------------+
1 row in set (0.00 sec)

root@localhost [world2]> select *,now() from City_Summary;
+----+-------------+------------+---------------------+
| ID | CountryCode | Population | now()               |
+----+-------------+------------+---------------------+
|  1 | JPN         |   77965117 | 2016-06-10 15:07:31 |
+----+-------------+------------+---------------------+
1 row in set (0.00 sec)

root@localhost [world2]> 

■ 継続的、且つ定期的にEVENTを実行したい場合
上記の例は、1回のみ実行する方法でしたが、
一定間隔で,継続して定期的に実行するには、以下の様にEVENTを作ると良いです。


root@localhost [world2]> CREATE EVENT SNAP_SHOT_VIEW_City_Master_City_Summary
    -> ON SCHEDULE 
    -> EVERY 10 MINUTE 
    -> COMMENT 'サマリーテーブルを10分毎に更新します'
    -> DO
    -> REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode;
Query OK, 0 rows affected (0.00 sec)

root@localhost [world2]>  SELECT * FROM information_schema.EVENTS\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: world2
          EVENT_NAME: SNAP_SHOT_VIEW_City_Master_City_Summary
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: REPLACE INTO City_Summary select 1,CountryCode,SUM(Population) from City_Master where CountryCode='JPN' group by CountryCode
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 10
      INTERVAL_FIELD: MINUTE
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: 2016-06-10 15:46:29
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2016-06-10 15:46:29
        LAST_ALTERED: 2016-06-10 15:46:29
       LAST_EXECUTED: 2016-06-10 15:46:29
       EVENT_COMMENT: サマリーテーブルを10分毎に更新します
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: utf8mb4_general_ci
1 row in set (0.00 sec)

root@localhost [world2]> 

Oracleのマテリアライズド・ビュー程の柔軟性は無いですが、
こんな対応方法もあるかと思いますので、使えそうでしたら是非試してみて下さい。

メモ: イベントの削除
———————-
DROP EVENT イベント名;

Triggerを利用する場合の参考)
http://www.fromdual.ch/mysql-materialized-views
http://d.hatena.ne.jp/IT7C/20100730/1280501734

参考)
13.2.8 REPLACE 構文
https://dev.mysql.com/doc/refman/5.6/ja/replace.html
13.2.5.3 INSERT … ON DUPLICATE KEY UPDATE 構文
https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html
13.1.11 CREATE EVENT 構文
https://dev.mysql.com/doc/refman/5.6/ja/create-event.html


db tech showcase 2012
に参加してきました。DBエンジニアとしては、非常に有意義で楽しいセミナーでした。

DB Tech Showcase

前職ではDBのコンサルやDB Teamのマネージャーしていましたが、
現職ではマネージメントに専念していてここ2~3年程きちんと触れていませんでしたが、
久々にDB技術の変化と触れ合ってきました。
DBのパフォーマンスは、CPU、メモリー、ディスク、ネットワークがメインですが、
最近はCPU、64bit化によるメモリー効率改善、SSDなどの影響でI/Oパフォーマンス向上して、
ネットワークに影響が出ているようで、InfinibandがMS SQL,MYSQL,Oracleでも盛り上がっている感じでした。

DB Tech Showcaseメモ

SQL Server

○AlwaysON
━Availability Group
データベース単位
  共有ディスク無し
  複数DBフェイルオーバー
  複数のセカンダリーサーバー(Max:4台―但しACKが遅いのでレスポンス注意)
CheckPoint単位で同期(1分デフォルト) Primary & Secondary同時
   ①ログ→②メモリー→③Checkpoint→④データファイル
   MSベンチマークでは代替63M/秒
━FailOver Cluster
 インスタンス単位
  共有ディスク
  Windows2008R2+SQL 2012 複数サイト(サブネットを跨いだクラスタリング2012から可能)
Windows Server FailOver Cluster

━FailOver Cluster+Availability Groupの構成もあり。
 ━Availability Group  (同期レプリカ、自動フェールオーバー、リーダブルセカンダリー)
━Availability Group DR (複数レプリカー同期(SYNC)は2台まで。
  非同期(A-SYNC)レプリカ (A-SYNCなのでコミットを待たない)
━マルチサブネット対応
 DBCC TRACEON(3499, -1) Availability Groupの書き込みパフォーマンス調整。

 ━パフォーマンス確認(SQL Server: SQL Statistics オブジェクト)
http://msdn.microsoft.com/ja-jp/library/ms190911.aspx
http://msdn.microsoft.com/ja-jp/library/ms190732%28v=sql.90%29.aspx
http://www.confio.com/db-resources/sql-wait-types/writelog/

Batch Requests/sec
Log write waits
Logical Disk Read Bytes/sec (バッファーメモリー、ディスクI/O)

注意: 同期レプリカの場合、2台のDBログに書き込んでからACKをサーバーに返す為パフォーマンス要確認。

○InfiniBand and SSD
 ━Windows2012 SQL2012からSMB3.0に対応
  SMB3.0対応
  SMB Direct (RDMAを持つNetwork Adapterをサポート)→Infinibandディフォルト対応。
  今まで ①L1:NIC→L2→L3:CPU→L7 これから L1->L7へダイレクト(高速)
SMB Multi Channel対応(Auto Failover,Auto Detect)
http://www.atmarkit.co.jp/fnetwork/tokusyuu/61ib02/01.html
http://msdn.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx
http://www.mellanox.co.jp/
http://www.atmarkit.co.jp/fnetwork/tokusyuu/61ib02/01.html

デモ検証実測値)
Windows2012 6.5GB/Sec
SQL2012 4.2GB/Sec
IB Switch 3.8GB/Sec
SSD 2.5GB/Sec
  注:PCI Busの制限は要確認。(PCI-E: 4GB/Sec)
○SQL ServerとIndexの進化
 Column Store Index
特定行のカラムだけを取る。→ DistinctやGroup byが高速化
テーブルに対して1つだけ付与する事が出来る。(全ての列を含む1つインデックスを作成する事も可能)
 全てのデータをintに変換してHASHにして圧縮をかける(blobで保存)
 対応しているデータ型は特定されているので要確認。(int, real,datetime,money等) varcharは不可
CREATE COLUMNSTORE INDEX (Transact-SQL)
 http://msdn.microsoft.com/en-us/library/gg492153.aspx

 検証)
  Column Store Indexを利用しない場合
  41秒(Clustered Indexで処理)
(MAXDOP =0)
Column Store Indexを利用した場合
37秒 (MAXDOP =1)
Column Store Indexを利用した場合
7秒 (MAXDOP =0)
 注:ReadOnlyなので必要な場合はバッチで処理する。(tableはReadになる)
   パーティショニングに対応しているので、パーティション毎に作成して直近のデータのみバッチ作成等

○SQL Server Tuning Point
━SQL2012 Lock変更
SQL2012までは…….
(A) select * from T SCH-S
(B) alter table T SCH-M
(C) select * from T with(NOLOCK) SCH-S
(A) -> (C) -> (B) の順になりAとCの処理をBが待ち続ける。(DDL Starvation)
SQL2012以降は ……
FIFOアルゴリズム採用してSCH-SはSCH-Mをスキップしない。
  但しTraceFlag 617を設定する事でNOLOCKの処理を優先する事が出来る。(再起動不要)
━統計情報変更
自動更新の為の閾値
  テーブル基数(データ量)<=500件 500回のデータ変更で統計を更新   テーブル基数(データ量)> 500件 500回のデータ変更+20%の変更で統計更新
  (例) 10億件のデータがある場合
     10億X0.2+500= 200,000,500件の変更発生で統計情報更新
  Changes to automatic update statistics in SQL Server ? traceflag 2371 -> SQRT(データ件数 * 1000)
  http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
  http://sqlserverpedia.com/blog/sql-server-bloggers/new-sql-2008-r2-sp1-trace-flag-adjusts-autostats-threshold/
  (例) 1,000,000,000件データがある場合は、SQRT(1,000,000,000X1000) = 1,000,000の変更で統計更新
  注:SQL2012, SQL2008R2 SP1から対応
  sys.dm_db_stats_properties を利用してサンプル数、更新日時、更新回数などを確認可能。(次回の統計更新タイミングが予測可能)
  http://msdn.microsoft.com/en-us/library/jj553546%28v=sql.105%29.aspx
DBCC SHOW_STATISTICSでも確認出来たが利便性向上の為に動的VIEWに変更された。
  http://sasuke.main.jp/sqrt.html

━FORCESEEK
  オプティマイザーをオーバーライト
  データへのアクセス パスとしてインデックスのシーク操作のみを使用するようにクエリ オプティマイザを設定可能。
  クラスター化、非クラスター化インデックスに有効。
  http://msdn.microsoft.com/ja-jp/library/bb510478%28v=sql.105%29.aspx
forceseekにインデックスの指定は出来無かったが、2008R2からは指定出来るようになった。
 select * from table with (forceseek) -> select * from table with (forceseek(ix(c1,c2))

━クエリープラン拡張
クエリープラン収集機能拡張
  ~SQL2008 R2 Profiler, SQL Trace, SET STATISTICS PROFILE
条件:収集する or 収集しない
  収集負荷が高い、収集出来ない事が多い
SQL2008 R2 ~
  拡張イベントとして設定可能(条件設定可能:実行時間、CPUtime 2秒以上のクエリーなどのみを取る事が出来る)
  絞り込み可能

 ━ページ割り当て確認
  SQL Server 2012では、DBCC INDやDBCC EXTENTINFOといったアンドキュメンテッドのコマンドにとって代わる
  sys.dm_db_database_page_allocationsという名前の新しい動的管理関数が導入されました。
  基本8K
~ SQL2008 R2までは DBCC EXTENTINFO
SQL2012 ~
  sys.dm_db_database_page_allocation
  http://enterprisezine.jp/dbonline/detail/3950?p=2

 ━Cardinality Estimation
  Estimate Framework
クエリー実行に時間がかかる場合
  → カージナリティ見積もり不正、統計不正、インデックス不正
  推定実行プラン(estimate)は、実際の実行プランと違う場合があるので何が実歳に違うか確認する事が出来る。(1回実行後)
統計情報の不正を確認出来るが、FALSE POSITIVEも拾ってしまうので注意
  実行中は負荷が上る、過剰見積もり、並列クエリーのノイズ(スレッド)などに注意しておく。
  Troubleshooting Poor Query Performance: Cardinality Estimation
  http://msdn.microsoft.com/en-us/library/ms181034%28v=sql.105%29.aspx

━高パフォーマンスの負荷で実行されている SQL Server 2005 および SQL Server 2008 のチューニング オプション
トレース フラグ 834: バッファ プールに対する Microsoft Windows large-page allocations の使用 
http://support.microsoft.com/kb/920093/ja
トレース フラグ 2301: 詳細な意思決定サポートの最適化を有効にする
http://support.microsoft.com/kb/920093/ja

━sys.dm_os_wait_stats (Transact-SQL)
  http://msdn.microsoft.com/ja-jp/library/ms179984.aspx
order by wait_time_ms descで思い処理を調査出来る。
○SQL ServerとNUMA

━MAXDOPは物理コア数に合わせる。(AMDだけ注意 16->8 8->4で設定)
 最近のHYPER-THREADは改善されていて、ONにしておくとOLTPでのパフォーマンスが10%程度上がる。
 OLTPとNUMA
 8Socket + Hyper Thread ON 2400ユーザー/秒
 4Socket + Hyper Thread ON 1400ユーザー/秒
ディスク構成例:Data (SSD),Index(SSD),T-Log(Disk), tempDB(DISK)
 NUMA設定で0~3はNetworksにCPUが利用される。
 sys.dm_os_memory_node_access_stats
* Cross NUMA node memory access statistics
* Trace Flag 842 is needed
 Page life Expectancyは30分程度はあった方が望ましい。(無い場合はメモリー不足を確認)
 http://www.atmarkit.co.jp/fdb/rensai/10_drk/03/drk02.html
 How to Identify Microsoft SQL Server Memory Bottlenecks
 http://www.mssqltips.com/sqlservertip/2304/how-to-identify-microsoft-sql-server-memory-bottlenecks/
 Finding Implicit Column Conversions in the Plan Cache
 http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx
 Coreinfo v3.1(mapping between logical processors and the physical processor)
 http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx

その他、Always On
https://skydrive.live.com/?cid=DEC1C75BCA0DCEB2&id=DEC1C75BCA0DCEB2!11496

MYSQL

○MariaDB
http://mariadb.org/
http://enterprisezine.jp/dbonline/detail/4220
 better than MYSQL
Full Compatible with MYSQL
Bug-Free? (MYSQLのバグを見てFixしてからリリース)
 GPLv2
XtraDB = InnoDB
MYSQLと比較するとCheckpointの頻度が少ない。(DiskへのFlashが少ない)
OptimizerはMYSQL5.68より優れている。
 MariaDB5.3 GIS support
MyISAMを利用しているユーザーにはKey Cacheが1~64セグメント設定出来るのでお勧め。
 基本的には1つのメジャーバージョンは5年サポートする。
About XtraDB
https://kb.askmonty.org/en/about-xtradb/

○MYSQL5.6新機能
セミナー資料by 奥野さん
━レプリケーション
 GTIDを保持してどのスレーブが最新か確認し自動昇格可能
 change master to master_host=’~~~’, master_auto_position =1;
 mysqlfailoverツール(workbenchに付属→マスターの自動フェイルオーバー実行)
 http://b.l0g.jp/mysql/mysql565-gtid/
 http://dev.mysql.com/doc/workbench/en/mysqlfailover.html
 http://yoku0825.blogspot.jp/2012/10/mysqlfailovermysql-utilitieslinux.html

 クラッシュセーフスレーブ(ファイル→InnoDBにレプリケーション情報を持つ事が可能)
 mysqlbinlogコマンドによるバイナリーログバックアップ
 スレーブ利用するNIC指定
 マルチスレッドスレーブ

━InnoDB
 インデックス追加・削除がオンライン可能(Clustered Indexは別)
 *.idbファイルのImport/Export可能
 http://dev.mysql.com/doc/refman/5.6/en/flush.html
インデックス統計改善
 memcached Interface追加
 ログMAXサイズが512GBまで拡張可能
 FULLTEXTインデックスサポート
 ページサイズ指定(4KB,8KB,16KB)
バッファープールの内容を保存、再起動時の暖機運転不要
http://nippondanji.blogspot.jp/2012/10/mysql-56.html

━Optimizer
 Semi-Joinの最適化
 Explainの改善(select以外のDMLも確認可能)

 Order by ~ Limit句の改善
 オプティマイザートレース
 MRR (RowIDをバッファーに入れてソート処理)

━パーティショニング
 パーティション間でのJOIN可能
 パーティション作成可能数1024→8192へ。
 ロック改善(テーブルロックの対象がパーティション単位へ)

━その他
 セキュリティ (slaveのスタートにパスワード入力可能=パラメーターへの書き込み不要)
 OpenGIS
パフォーマンス確認、小数点以下へ対応
スケーラビリティ(R/W: MYSQL5.5比 → 151%改善 Read: NYSQL5.5比で234%改善)
Kernel_Mutexによる処理の分散
Flush改善(リソース状況により詳細調整可能)
http://www.mysqlperformanceblog.com/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/


Oracleのディフォルトユーザーなどの利用していないアカウントを無効にして権限の確認。

■ CREATE SESSION出来ないように設定

SQL> REVOKE CONNECT FROM DBSNMP;

取消しが成功しました。

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = ‘DBSNMP’;

USERNAME PROFILE ACCOUNT_STATUS
—————————— —————————— ——————————–
DBSNMP DEFAULT OPEN

■ アカウントの無効化

SQL> ALTER USER DBSNMP ACCOUNT LOCK PASSWORD EXPIRE;

ユーザーが変更されました。

■ アカウントがOPENになって無い事を確認

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = ‘DBSNMP’;

USERNAME PROFILE ACCOUNT_STATUS
—————————— —————————— ——————————–
DBSNMP DEFAULT EXPIRED & LOCKED

■ 権限の確認

SQL> SELECT LPAD(‘ ‘, 2*level) || granted_role “USER PRIVS”
2 FROM (
3 SELECT NULL grantee, username granted_role
4 FROM dba_users
5 WHERE username LIKE UPPER(‘DBSNMP’)
6 UNION
7 SELECT grantee, granted_role
8 FROM dba_role_privs
9 UNION
10 SELECT grantee, privilege
11 FROM dba_sys_privs)
12 START WITH grantee IS NULL
13 CONNECT BY grantee = prior granted_role;

USER PRIVS
—————————————————————————————————-
DBSNMP
SELECT ANY DICTIONARY

SQL>