データベース自作PCサーバー作成 6

# リポジトリ追加
$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

# MariaDB インストール
$ dnf -y remove MariaDB-*
$ dnf clean packages;
$ dnf -y install \
  MariaDB-server \
  MariaDB-client

# MariaDB 自動起動設定と起動
$ systemctl enable --now mariadb

# MariaDBへ接続確認
$ mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> exit # ← 終了
Bye

MariaDB [(none)]> show engines;

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# ---------------------------------------------------
#  アクセス許可設定
# ---------------------------------------------------
bind-address = 0.0.0.0
# ---------------------------------------------------

# ---------------------------------------------------
#  文字セット・コード設定
# ---------------------------------------------------
character-set-server=utf8mb4
collation-server=utf8mb4_bin
# ---------------------------------------------------

# ---------------------------------------------------
#  SQLモード設定
# ---------------------------------------------------
sql_mode = 'ANSI_QUOTES'
# ---------------------------------------------------

# ---------------------------------------------------
#  パフォーマンススキーマ有効化
# ---------------------------------------------------
performance_schema = ON
# ---------------------------------------------------

# ---------------------------------------------------
#  イベントスケジューラー設定
# ---------------------------------------------------
event-scheduler = ON
# ---------------------------------------------------

# ---------------------------------------------------
#  テーブル定義キャッシュサイズ
# ---------------------------------------------------
table_definition_cache = 1024
# ---------------------------------------------------

# ---------------------------------------------------
#  InnoDB バッファプールサイズ設定
#  [物理メモリ] x 0.64
# ---------------------------------------------------
innodb_buffer_pool_size=40G
# ---------------------------------------------------

# ---------------------------------------------------
#  InnoDB ログサイズ
# ---------------------------------------------------
innodb_log_file_size = 2G
# ---------------------------------------------------

# ---------------------------------------------------
# InnoDB データファイルサイズ設定
innodb_data_file_path = ib_data:1G:autoextend
# ---------------------------------------------------

# ---------------------------------------------------
#  InnoDB データインデックス分割設定
# ---------------------------------------------------
innodb_file_per_table = 1
# ---------------------------------------------------

# ---------------------------------------------------
#  パケット最大サイズ設定
# ---------------------------------------------------
max_allowed_packet = 32MB
# ---------------------------------------------------

# ---------------------------------------------------
#  最大コネクション数設定
# ---------------------------------------------------
max_connections = 5000
# ---------------------------------------------------

# ---------------------------------------------------
#  ヒープ領域サイズ設定
# ---------------------------------------------------
max_heap_table_size = 2GB
tmp_table_size = 2GB
# ---------------------------------------------------

# ---------------------------------------------------
#  接続タイムアウト設定
# ---------------------------------------------------
connect_timeout = 300
# ---------------------------------------------------

# ---------------------------------------------------
#  インデックス未使用でのJOIN時に使用するバッファ
# ---------------------------------------------------
join_buffer_size = 4G
# ---------------------------------------------------

# ---------------------------------------------------
#  スレッドキャッシュ保持最大数
# ---------------------------------------------------
thread_cache_size = 5000
# ---------------------------------------------------

# ---------------------------------------------------
#  名前解決無効
# ---------------------------------------------------
skip-name-resolve = 1
# ---------------------------------------------------

# ---------------------------------------------------
#  スレッドプール設定
# ---------------------------------------------------
thread_handling = pool-of-threads
thread_pool_size = 32
# ---------------------------------------------------

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.5]

$ systemctl restart mariadb

$ head ~/Downloads/page_views.csv
uuid,document_id,timestamp,platform,geo_location,traffic_source
1fd5f051fba643,120,31905835,1,RS,2
8557aa9004be3b,120,32053104,1,VN>44,2
c351b277a358f0,120,54013023,1,KR>12,1
8205775c5387f9,120,44196592,1,IN>16,2
9cb0ccd8458371,120,65817371,1,US>CA>807,2
2aa611f32875c7,120,71495491,1,CA>ON,2
f55a6eaf2b34ab,120,73309199,1,BR>27,2
cc01b582c8cbff,120,50033577,1,CA>BC,2
6c802978b8dd4d,120,66590306,1,CA>ON,2

# PHPインストール
$ dnf -y install php php-mysqli

# PHP バージョン確認
$ php -v
PHP 7.2.24 (cli) (built: Oct 22 2019 08:28:36) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies

# MySQLi利用可能確認
$ php -i|grep mysqli
/etc/php.d/30-mysqli.ini,
mysqli
mysqli.allow_local_infile => Off => Off
mysqli.allow_persistent => On => On
mysqli.default_host => no value => no value
mysqli.default_port => 3306 => 3306
mysqli.default_pw => no value => no value
mysqli.default_socket => /var/lib/mysql/mysql.sock => /var/lib/mysql/mysql.sock
mysqli.default_user => no value => no value
mysqli.max_links => Unlimited => Unlimited
mysqli.max_persistent => Unlimited => Unlimited
mysqli.reconnect => Off => Off
mysqli.rollback_on_cached_plink => Off => Off
API Extensions => mysqli,pdo_mysql

scp ~/Downloads/page_views.csv 接続ユーザー名@mariadb1.vlue.io:/data

CREATE DATABASE `mytest` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';

CREATE TABLE "page_views" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "uuid" char(14) COLLATE utf8mb4_bin NOT NULL,
  "document_id" int(11) NOT NULL,
  "platform" tinyint(4) DEFAULT NULL,
  "geo_location" varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  "traffic_source" tinyint(4) DEFAULT NULL,
  "created_at" timestamp NULL DEFAULT NULL,
  "updated_at" timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY ("id","uuid","document_id") USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2000250001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

<?php

# ----------------------------------------------------------------------------
#  設定
# ----------------------------------------------------------------------------

# バルクインサートサイズ
$BULK_SIZE = 350000;

# CSVファイルパス
$CSV_FILE_PATH = '/data/page_views.csv';

# SQLファイル出力先パス
$SQL_OUTPUT_PATH = '/root/page_views';


# ----------------------------------------------------------------------------
#  メイン処理
# ----------------------------------------------------------------------------

# ファイルオープン
$fp = @fopen($CSV_FILE_PATH, "r");

if ($fp) {

    # カウンタ初期化
    $i = 0;
    $cnt = 0;

    # 挿入データバッファ初期化
    $buff = [];

    # 計測開始
    $start = microtime(true);

    # 1行ずつCSV解析をして読み込み
    while ($line = fgetcsv($fp)) {

        $i++;

        # 1行目はヘッダーなのでスキップ
        if ($i === 1) {
            continue;
        }

        # バッファへデータ追加
        $buff[] = $line;

        if (sizeof($buff) === $BULK_SIZE) {

            $values = [];

            foreach ($buff as $item) {
                $values[] = '  (' . implode(', ', [
                        "'" . $item[0] . "'"
                        , $item[1]
                        , $item[3]
                        , "'" . $item[4] . "'"
                        , $item[5]
                        , "'" . date("Y-m-d H:i:s", $item[2] + 1465876799998 / 1000) . "'"
                        , "CURRENT_TIMESTAMP()"
                    ]) . ')';
            }

            $query = implode(PHP_EOL, [
                PHP_EOL
                , " INSERT IGNORE INTO `page_views` ("
                , "     `uuid`"
                , "   , `document_id`"
                , "   , `platform`"
                , "   , `geo_location`"
                , "   , `traffic_source`"
                , "   , `created_at`"
                , "   , `updated_at`"
                , " )"
                , " VALUES"
                , implode(',' . PHP_EOL, $values)
            ]);

            file_put_contents($SQL_OUTPUT_PATH . '/insert_' . str_pad($cnt, 6, 0, STR_PAD_LEFT) . '.sql', $query);

            # カウンターインクリメント
            $cnt++;

            # 挿入データバッファ初期化
            $buff = [];
        }
    }

    if (sizeof($buff) > 0) {

        $values = [];

        foreach ($buff as $item) {
            $values[] = '  (' . implode(', ', [
                    "'" . $item[0] . "'"
                    , $item[1]
                    , $item[3]
                    , "'" . $item[4] . "'"
                    , $item[5]
                    , "'" . date("Y-m-d H:i:s", $item[2] + 1465876799998 / 1000) . "'"
                    , "CURRENT_TIMESTAMP()"
                ]) . ')';
        }

        $query = implode(PHP_EOL, [
            PHP_EOL
            , " INSERT IGNORE INTO `page_views` ("
            , "     `uuid`"
            , "   , `document_id`"
            , "   , `platform`"
            , "   , `geo_location`"
            , "   , `traffic_source`"
            , "   , `created_at`"
            , "   , `updated_at`"
            , " )"
            , " VALUES"
            , implode(',' . PHP_EOL, $values)
        ]);

        file_put_contents($SQL_OUTPUT_PATH . '/insert_' . str_pad($cnt, 6, 0, STR_PAD_LEFT) . '.sql', $query);


    }


}

# ファイルクローズ
fclose($fp);

echo "completed generate insert sql process." . PHP_EOL;

# EOF

<?php

# ----------------------------------------------------------------------------
#  メイン処理
# ----------------------------------------------------------------------------

# プロセス数上限
$PROC_LIMIT = 10;

# カウンタ初期化
$cnt = 0;

# 実行プロセスID配列初期化
$pids = [];

# 計測開始
$start = microtime(true);

while ($cnt <= 5812) {

    # 実行プロセスリスト初期化
    $update_pids = [];

    # 実行中プロセス更新
    foreach ($pids as $pid) {

        # 実行結果初期化
        $output = [];

        # 実行コマンド作成
        $cmd = "ps h " . $pid;

        # 実行
        exec($cmd, $output);

        # 実行プロセスリストへ追加
        if (sizeof($output) === 1) {
            $update_pids[] = $pid;
        } else {
            echo sprintf('%0.10f', microtime(true) - $start) . PHP_EOL;
        }

    }

    $pids = $update_pids;

    # 実行中のプロセスが上限に達している場合スキップ
    if (sizeof($pids) >= $PROC_LIMIT) {
        usleep(1);
        continue;
    }

    # 連番作成
    $num = str_pad($cnt, 6, 0, STR_PAD_LEFT);

    # 実行コマンド作成
    $cmd = "php insert.php $num > /dev/null & echo $!";

    # 実行結果初期化
    $output = [];

    # インサートプロセス実行
    exec($cmd, $output);

    # PID追加
    $pids[] = $output[0];

    # インクリメント
    $cnt++;

}

echo "completed insert process." . PHP_EOL;

# EOF

<?php

# ----------------------------------------------------------------------------
#  設定
# ----------------------------------------------------------------------------

# 接続先ホスト
$HOSTNAME = 'localhost';

# 接続ユーザー名
$USERNAME = 'root';

# 接続パスワード
$PASSWORD = 'password';

# 接続データベース
$DBNAME = 'mytest';

# SQLファイルパス
$SQL_PATH = '/root/page_views';

# 連番取得
$num = $argv[1];

# データベース接続
$mysqli = new mysqli($HOSTNAME, $USERNAME, $PASSWORD, $DBNAME);

# データベース接続エラー処理
if ($mysqli->connect_error) {
    echo $mysqli->connect_error;
    exit();
}

# キャラセット設定
$mysqli->set_charset("utf8mb4");

# SQL取得
$query = file_get_contents($SQL_PATH . '/insert_' . $num . '.sql');

# SQL実行
$mysqli->query($query);

// DB接続を閉じる
$mysqli->close();

# EOF

$ php run_insert.php > insert.log &

$ tail -f insert.log

$ ps -aux | grep php

MariaDB [(none)]> use mytest;
MariaDB [mytest]> SELECT COUNT(*) FROM page_views;

$ wc -l /data/page_views.csv
2034275449 page_views.csv

10890.9318001270
10892.8104751110
10893.5190300941
10895.9931759834
10898.3707320690
10901.6219120026
10905.0063550472
10906.6536550522
10907.3238840103
10910.6990981102
10910.9942960739
10913.0309159756
10913.8176531792
10916.4012670517
10918.7953150272
10922.3823790550
completed insert process.

SELECT
( SELECT count( DISTINCT document_id ) FROM page_views b WHERE a.document_id < b.document_id ) + 1 rank 
FROM
page_views a 
ORDER BY
rank ASC