20191129のMySQLに関する記事は4件です。

投稿日：2019-11-29T23:42:02+09:00

Alibaba Cloud の POLARDB を試してみる（３）MySQL 8.0 互換版でパラレルクエリを試す

今年もアドベントカレンダーの季節がやってきました。

というわけで、この記事は MySQL Advent Calendar 2019 3 日目のエントリとなります。

昨日は meijik さんでした。

MySQLクライアントでの非同期処理概略（キムラデービーブログ）

そして、以下の記事の続きでもあります。

※本日の同日エントリもあります（Qiita Advent Calendar の仕様変更に振り回された結果…）。

Alibaba Cloud の POLARDB を試してみる（４）PostgreSQL 11 互換版起動＋パラレルスキャン編

今回は、Alibaba Cloud の ApsaraDB for POLARDB MySQL 8.0 互換版の注目の機能、パラレルクエリ（Parallel Query）を軽く試してみます。

パラレルクエリとは

簡単にいってしまうと「1 つの SQL の処理を内部的に分割して並列に行うもの」です。

近年、PostgreSQL ではバージョンアップのたびにパラレルクエリの対象となる SQL が増えてきていますが、MySQL（8.0）では主キーに対するCOUNT(*)など非常に限られた SQL のみの対応にとどまっていました。

MySQL 8.0.14でSELECT COUNT(*)が加速する！- 「innodb_parallel_read_threads」検証その１（なからなLife／atsuizo さん）

そんなこともあって、MySQLer おなじみの Percona Live 2019 でも 2 日目のキーノートで取り上げられたようです。

Percona Live 2019 in Texas Austin 現地レポ(Session Day 2) – Keynote（スマートスタイル TECH BLOG）

Alibaba Cloud のドキュメントでは、以下のページで説明されています。

Parallel query（POLARDB MySQL Database）
Examples of parallel query（POLARDB MySQL Database）
Restrictions on parallel query（POLARDB MySQL Database）

試してみた

インスタンスとして、（テスト用を除いて最小の）4 Core 16 GB（polar.mysql.x4.large）を選択して試しました。

結果として、小さなインスタンスでもそれなりに効果があることがわかりました。

データ準備

以下のような定義のテーブルに、前回同様 100 万行INSERTしました。

テーブル定義

mysql> CREATE DATABASE pqtest;
Query OK, 1 row affected (0.00 sec)

mysql> USE pqtest;
Database changed
mysql> CREATE TABLE pqtest1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dummy_str1 VARCHAR(512) NOT NULL, val1 INT NOT NULL, val2 INT NOT NULL);
Query OK, 0 rows affected (0.80 sec)

データINSERT（部分）

SET AUTOCOMMIT=0;
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
（中略）
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
COMMIT;

INSERT結果（部分）

mysql> SELECT * FROM pqtest1 ORDER BY id LIMIT 10;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+
| id | dummy_str1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | val1 | val2 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+
|  1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  332 |  440 |
|  2 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  202 |  691 |
|  3 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  848 |  165 |
|  4 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  281 |  912 |
|  5 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  714 |  834 |
|  6 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |   26 |  629 |
|  7 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |   68 |  450 |
|  8 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |   48 |  888 |
|  9 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  294 |  807 |
| 10 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  151 |  335 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+
20 rows in set (0.00 sec)

テスト１：`COUNT(*)`・`SUM()`・`AVG()`

まずはパラレルクエリ無効で実行してみます。

パラレル無効

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (9.04 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (0.41 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 912368
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

1 回目がバッファプールに載っていない状態、2 回目が載っている状態です。

次は 2 並列のパラレルクエリを試してみます。

2並列

mysql> SET max_parallel_degree = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET force_parallel_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (5.25 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (0.21 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: <gather2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 912368
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 456184
     filtered: 100.00
        Extra: Parallel scan (2 workers)
2 rows in set, 1 warning (0.00 sec)

実行計画が変わり、2 倍近く高速になりました。

続いて 4 並列。

4並列

mysql> SET max_parallel_degree = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> SET force_parallel_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (3.51 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (0.22 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: <gather2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 912368
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 228092
     filtered: 100.00
        Extra: Parallel scan (4 workers)
2 rows in set, 1 warning (0.00 sec)

バッファプールに載っていない状態ではさらに速くなりました。

`GROUP BY`・`ORDER BY`・`LIMIT`

続いて、COUNT(*)・SUM()にGROUP BY・ORDER BY・LIMITを組み合わせてみます。

まずはパラレルクエリ無効で。

パラレル無効

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (10.15 sec)

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (0.83 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 912368
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

今度はパラレルクエリを 4 並列だけ試してみます。

4並列

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (3.34 sec)

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (0.39 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: <gather2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 912368
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 228092
     filtered: 100.00
        Extra: Parallel scan (4 workers); Using temporary; Using filesort
2 rows in set, 1 warning (0.00 sec)

先の例と同じような結果になりました。

注意点

先に記した Alibaba Cloud のドキュメント 3 つ目のページに書かれていますが、以下のようなケースではパラレルクエリが適用されません。

テーブル内の行数が 20,000 未満
システムテーブルや一時テーブル
SELECT... FOR UPDATE・SELECT... FOR SHARE
フルテキストインデックスでの検索
ストアドプロシージャや UDF
再帰 CTE やウィンドウ関数
GIS / XML 関数
GROUP BY WITH ROLLUP
ロック関数
B+ ツリー以外のインデックスでの検索やインデックスマージ
トランザクション分離レベルSERIALIZABLEでの SQL 実行
CPU コアの 4 倍以上のスレッドを使うパラレルクエリ

次回は…

LIKE検索など別ケースのパラレルクエリを試す予定です。

MySQL Advent Calendar 2019 明日（4 日目）は tmtms さんです。

　 Tweet
Qiitaで続きを読む

投稿日：2019-11-29T22:55:06+09:00

Docker ComposeでLaravel6.5の環境を作成

少しDockerComposeについて勉強してみたので
試しにLaradockを使わずにDocker ComposeでLaravelの環境構築をしてみます

作成する環境

Laravel 6.5.2
php7.3
mysql 8.0.18
nginx 1.17

ディレクトリ構成

project
├ docker-compose.yml
├ docker
|　 ├ php
│ 　│　 ├ php.ini
│ 　│ 　└ Dockerfile
│　 ├ nginx
│ 　│ 　└ default.conf
│ 　└ mysql
│ 　 　 ├ conf.d
│ 　 　 │  └ default_authentication.cnf
│ 　  　└ data
└ src

PHP用のDockerfileを作成

docker/php/Dockerfile

FROM php:7.3-fpm
COPY php.ini /usr/local/etc/php/

RUN apt-get update \
  && apt-get install -y zlib1g-dev libzip-dev mariadb-client \
  && docker-php-ext-install zip pdo_mysql

#Composer install
RUN php -r "copy('https://getcomposer.org/installer', 'composer-setup.php');"
RUN php -r "if (hash_file('sha384', 'composer-setup.php') === 'a5c698ffe4b8e849a443b120cd5ba38043260d5c4023dbf93e1558871f1f07f58274fc6f4c93bcfd858c6bd0775cd8d1') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;"
RUN php composer-setup.php
RUN php -r "unlink('composer-setup.php');"
RUN mv composer.phar /usr/local/bin/composer
# rootでのcomposerコマンド実行を許可
ENV COMPOSER_ALLOW_SUPERUSER 1
# composerのグローバルパッケージがインストールされるディレクトリの指定
ENV COMPOSER_HOME /composer
# composerのパスを通す
ENV PATH $PATH:/composer/vendor/bin
# laravelインストール
RUN composer global require "laravel/installer"

RUN curl -SL https://deb.nodesource.com/setup_13.x | bash
RUN apt-get install -y nodejs && \
  npm install -g npm@latest && \
  npm install -g @vue/cli

WORKDIR /var/www

php.iniの設定

docker/php/php.ini

; timezone
date.timezone = Asia/Tokyo

; error reporing
log_errors = On
error_log = /dev/stderr
display_errors = Off

; mbstring
mbstring.language = "Japanese"

timezoneを東京に
errorログを標準出力エラーに設定
デフォルトで使用する言語を日本語に設定

nginxの設定ファイルを記述

docker/nginx/default.conf

server {
  listen 80;

  root  /var/www/public;
  index index.php;

  location / {
    try_files $uri $uri/ /index.php$is_args$args;
  }

  location ~ \.php$ {
    fastcgi_split_path_info ^(.+\.php)(/.+)$;
    fastcgi_pass   php-fpm:9000;　
    fastcgi_index  index.php;

    include        fastcgi_params;
    fastcgi_param  SCRIPT_FILENAME $document_root$fastcgi_script_name;
    fastcgi_param  PATH_INFO $fastcgi_path_info;
  }
}

fastcgi_pass php-fpm:9000;のphp-fpmにはdocker-compose.ymlで定義したサービス名を指定します。

mysql

MySQL8系のデフォルト認証形式を変更するための設定ファイルを作成します

docker/mysql/conf.d/default_authentication.cnf

[mysqld]
default_authentication_plugin= mysql_native_password

続いて、mysqlのデータを永続化するためのディレクトリを作成します

$ mkdir /docker/mysql/data

docker-compose.ymlの作成

docker-compose.yml

version: '3'
services:
  nginx:
    image: nginx:1.17-alpine # nginxのimageにnginx:1.17-alpineを指定
    container_name: "nginx"
    ports:
      - "8080:80" # ホストの8080ポートでnginxコンテナの80にアクセス
    volumes:
      - ./src:/var/www # ホストのsrcをnginxコンテナの/var/wwwにマウント
      - ./docker/nginx/default.conf:/etc/nginx/conf.d/default.conf # 作成したdefault.confを/etc/nginx/conf.d/default.confにマウント
    depends_on: # コンテナの依存関係を定義する
      - php-fpm # php-fpmの起動後にnginxを起動
  php-fpm:
    build: ./docker/php # ./docker/php/Dockerfileをbuildしてイメージ作成
    container_name: "php-fpm"
    volumes:
      - ./src:/var/www # ホストのsrcをphp-fpmコンテナの/var/wwwにマウント
    links: # コンテナと他のサービスを繋げる
      - db
    depends_on:
      - db # dbの起動後にphp-fpmを起動
  db:
    image: mysql:8.0.18 # mysqlのimageにmysql:8.0.18を指定
    container_name: "db"
    volumes:
      - ./docker/mysql/conf.d:/etc/mysql/conf.d # mysqlのせってファイルをマウント
      - ./docker/mysql/data:/var/lib/mysql # mysqlのデータ永続化
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: hogehoge
      MYSQL_USER: hoge
      MYSQL_PASSWORD: hoge
      MYSQL_ROOT_PASSWORD: root
      TZ: "Asia/Tokyo"
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

コンテナ起動

$ docker-compose build
$ docker-compose up -d

laravelのプロジェクトファイル作成

$ docker-compose exec php-fpm composer create-project --prefer-dist "laravel/laravel=6.5.2" .

確認

これでhttp://localhost:8080にアクセスするとlaravelのページが表示されます

　laravelの認証画面まで作ってみる

$ docker-compose exec php-fpm composer require laravel/ui
$ docker-compose exec php-fpm php artisan ui vue --auth
$ docker-compose exec php-fpm npm run dev

php artisan make:auth　をしたら怒られました、、、laravel6で変更されたんですね

このためにDockerfileで下記を書いています

RUN curl -SL https://deb.nodesource.com/setup_13.x | bash
RUN apt-get install -y nodejs && \
  npm install -g npm@latest && \
  npm install -g @vue/cli

再度確認

http://localhost:8080/register
無事ユーザー登録ページが表示されました

登録してみる

怒られた、、、

laravelの.envでDBの設定を忘れていたので設定

src/.env

DB_CONNECTION=mysql
DB_HOST=db
DB_PORT=3306
DB_DATABASE=hogehoge
DB_USERNAME=hoge
DB_PASSWORD=hoge

DB_HOST
DB_DATABASE
DB_USERNAME
DB_PASSWORD
をdocker-compose.ymlで定義したものに変更してください
DB_HOSTはDBコンテナのサービス名です

認証用のテーブルも作り忘れていたので作成

$ docker-compose exec php-fpm php artisan migrate

再度作成！

いけた！！！

まだまだDocker勉強中で間違っている所があるかと思いますので
ご指摘ありましたらよろしくお願いいたします?‍♂️

　 Tweet
Qiitaで続きを読む

投稿日：2019-11-29T14:28:39+09:00

MySQL構築

Hadoop との比較用に MySQL を構築しました。

VM 構築

Vagrant.configure("2") do |config|
  config.vm.box = "ubuntu/bionic64"
  config.vm.synced_folder "./share", "/home/vagrant/share", owner: "vagrant", group: "vagrant"

  #--- MySQL 構築 ---#
  config.vm.define "mysql" do | mysql |
    mysql.disksize.size = '90GB'
    mysql.vm.provider "virtualbox" do |vb|
      vb.memory = 8192
    end
    mysql.vm.hostname = "mysql"
    mysql.vm.network "private_network", ip: "192.168.33.31"
    mysql.vm.provision :hosts, :sync_hosts => true
  end
  #--- MySQL 構築 ---#
end

MySQL インストール

全て y にしました。
パスワードの強度は 0 にしました。

$ sudo apt update
$ sudo apt install mysql-server -y
$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.


Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

MySQL 設定

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
$ sudo systemctl restart mysql
$ sudo vi /etc/mysql/conf.d/mysqldump.cnf
$ sudo vi /etc/mysql/conf.d/mysql.cnf

# [mysqld] 配下に追記
character-set-server = utf8
default_password_lifetime = 0

# [mysqldump] 配下に追記
default-character-set=utf8

# [mysql] 配下に追記
default-character-set=utf8

MySQL 動作確認

$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

　 Tweet
Qiitaで続きを読む

投稿日：2019-11-29T01:26:29+09:00

nodeを使ってmysqlに接続する

nodeでmysqlに接続するには

// requireの設定
const mysql = require('mysql');

// MySQLとのコネクションの作成
const connection = mysql.createConnection({
  host : 'localhost',
  user : 'root',
  database: 'testdatabase'
});

// 接続
connection.connect();

// userdataの取得
connection.query('SELECT * from userdata;', function (err, rows, fields) {
  if (err) { console.log('err: ' + err); } 

  console.log('name: ' + rows[0].name);
  console.log('id: ' + rows[0].id);

});

// userdataのカラムを取得
connection.query('SHOW COLUMNS FROM userdata;', function (err, rows, fields) {
  if (err) { console.log('err: ' + err); }

  console.log(rows[0].Field);
  console.log(rows[1].Field);
});

// 接続終了
connection.end();

　 Tweet
Qiitaで続きを読む

20191129のMySQLに関する記事は4件です。

Alibaba Cloud の POLARDB を試してみる（３）MySQL 8.0 互換版でパラレルクエリを試す

パラレルクエリとは

試してみた

データ準備

テスト１：`COUNT(*)`・`SUM()`・`AVG()`

`GROUP BY`・`ORDER BY`・`LIMIT`

注意点

次回は…

Docker ComposeでLaravel6.5の環境を作成

作成する環境

ディレクトリ構成

PHP用のDockerfileを作成

php.iniの設定

nginxの設定ファイルを記述

mysql

docker-compose.ymlの作成

コンテナ起動

laravelのプロジェクトファイル作成

確認

laravelの認証画面まで作ってみる

再度確認

登録してみる

laravelの.envでDBの設定を忘れていたので設定

認証用のテーブルも作り忘れていたので作成

再度作成！

MySQL構築

VM 構築

MySQL インストール

MySQL 設定

MySQL 動作確認

nodeを使ってmysqlに接続する

日付別

タグ一覧

20191129のMySQLに関する記事は4件です。

Alibaba Cloud の POLARDB を試してみる（３）MySQL 8.0 互換版でパラレルクエリを試す

パラレルクエリとは

試してみた

データ準備

テスト１：COUNT(*)・SUM()・AVG()

GROUP BY・ORDER BY・LIMIT

注意点

次回は…

Docker ComposeでLaravel6.5の環境を作成

作成する環境

ディレクトリ構成

PHP用のDockerfileを作成

php.iniの設定

nginxの設定ファイルを記述

mysql

docker-compose.ymlの作成

コンテナ起動

laravelのプロジェクトファイル作成

確認

laravelの認証画面まで作ってみる

再度確認

登録してみる

laravelの.envでDBの設定を忘れていたので設定

認証用のテーブルも作り忘れていたので作成

再度作成！

MySQL構築

VM 構築

MySQL インストール

MySQL 設定

MySQL 動作確認

nodeを使ってmysqlに接続する

日付別

タグ一覧

テスト１：`COUNT(*)`・`SUM()`・`AVG()`

`GROUP BY`・`ORDER BY`・`LIMIT`

　laravelの認証画面まで作ってみる