カテゴリー: MySQL

MySQLデッドロックロックしているスレッドを殺す

2020年5月25日2020年5月31日 | by 優 | にコメントを残す

(none)> SELECT * FROM  sys.innodb_lock_waits\G

***************************[ 1. row ]***************************
wait_started                 | 2020-05-25 06:26:01
wait_age                     | 0:00:13
wait_age_secs                | 13
locked_table                 | `homestead`.`samples`
locked_index                 | PRIMARY
locked_type                  | RECORD
waiting_trx_id               | 47694
waiting_trx_started          | 2020-05-25 06:26:01
waiting_trx_age              | 0:00:13
waiting_trx_rows_locked      | 1
waiting_trx_rows_modified    | 0
waiting_pid                  | 1274
waiting_query                | insert into `samples` (`hoge_ ... at`) values (?, ?, ?, ?, ?, ?)
waiting_lock_id              | 47694:2144:3:1
waiting_lock_mode            | X
blocking_trx_id              | 47675
blocking_pid                 | 744 ←●注目
blocking_query               | <null>
blocking_lock_id             | 47675:2144:3:1
blocking_lock_mode           | X
blocking_trx_started         | 2020-05-24 16:54:51
blocking_trx_age             | 13:31:23
blocking_trx_rows_locked     | 40
blocking_trx_rows_modified   | 2
sql_kill_blocking_query      | KILL QUERY 744
sql_kill_blocking_connection | KILL 744
1 row in set
Time: 0.009s

(none)> SELECT * FROM sys.innodb_lock_waits\G

***************************[ 1. row ]***************************

wait_started | 2020-05-25 06:26:01

wait_age | 0:00:13

wait_age_secs | 13

locked_table | `homestead`.`samples`

locked_index | PRIMARY

locked_type | RECORD

waiting_trx_id | 47694

waiting_trx_started | 2020-05-25 06:26:01

waiting_trx_age | 0:00:13

waiting_trx_rows_locked | 1

waiting_trx_rows_modified | 0

waiting_pid | 1274

waiting_query | insert into `samples` (`hoge_ ... at`) values (?, ?, ?, ?, ?, ?)

waiting_lock_id | 47694:2144:3:1

waiting_lock_mode | X

blocking_trx_id | 47675

blocking_pid | 744 ←●注目

blocking_query | <null>

blocking_lock_id | 47675:2144:3:1

blocking_lock_mode | X

blocking_trx_started | 2020-05-24 16:54:51

blocking_trx_age | 13:31:23

blocking_trx_rows_locked | 40

blocking_trx_rows_modified | 2

sql_kill_blocking_query | KILL QUERY 744

sql_kill_blocking_connection | KILL 744

1 row in set

Time: 0.009s

逝ったれや！🐱

(none)> kill 744;
Query OK, 0 rows affected

1 2	(none)> kill 744; Query OK, 0 rows affected

@see[YouTube] IT速報よわよわエンジニア優さん LGBTの就職戦略トランス・X ジェンダー向け ( トランスジェンダー) フリーターから正社員インフラは2か月！ MySQLでロックを特定し、強制終了する …

の続きを読む

MySQL, SQL

SQL グループの中で最新の日付のデータを取得する MySQL

2020年5月16日2020年5月17日 | by 優 | にコメントを残す

●tb_user_section user_id section_id created 1 1 2020-05-11 20:00:00 1 2 2020-05-12 20:00:00 1 3 2020-05-13 20:00:00 2 1 2020-05-15 20:00:00 3 2 2020-05-18 20:00:00 3 4 2020-05-19 20:00:00 user_idのグループ …

の続きを読む

MySQL, SQL, PHP

Laravel SQLを実行ヒアドキュメントクロス集計

2020年5月6日2020年5月13日 | by 優 | にコメントを残す

手の込んだ集計などはSQLで取った方がシンプルになることも多いヒアドキュメントを利用して記述するのが良いですね🐱

<?php


namespace App\Services;

use App\Services\Service;
use Illuminate\Support\Facades\DB;

class GetsectionParentGoalService extends Service
{
    const MY_section_PARENT_INDEX = 0; // 集計部署取得クエリのINDEX

    /**
     * 集計対象部署の半期のKPIを返却
     *
     * @param int $sectionParentId
     * @param int $year
     * @param string $period
     * @return object(stdClass)
     */
    public function execute($sectionParentId, $year, $period)
    {
        $sql = <<< SQL
            SELECT
              SUM(kpi1) AS kpi1,
              SUM(kpi2) AS kpi2,
              SUM(kpi3) AS kpi3,
              SUM(kpi4) AS kpi4,
              SUM(kpi5) AS kpi5,
              SUM(kpi6) AS kpi6,
              SUM(kpi7) AS kpi7,
              SUM(kpi8) AS kpi8,
              SUM(kpi9) AS kpi9,
              SUM(kpi10) AS kpi10,
              SUM(kpi11) AS kpi11,
              SUM(kpi12) AS kpi12
            FROM
              tb_section_kpi
            WHERE tb_section_kpi.section_id IN
            (
              SELECT DISTINCT
                tb_section.section_id
              FROM
                tb_section
                  JOIN
                tb_section_parent
                  ON tb_section.section_parent_id = ?
              WHERE
                tb_section.deleted = 0
              AND
                tb_section.year = ?
              AND
                tb_section.period = ?
            )
SQL;

        $result = DB::select($sql, [
                                    $sectionParentId,    // ON tb_section.section_parent_id = {$sectionParentId}
                                    $year,               // tb_section.year = '{$year}'
                                    $period,             // tb_section.period = '{$period}'
                                ]);

        return $result[self::MY_section_PARENT_INDEX];
    }
}

<?php

namespace App\Services;

use App\Services\Service;

use Illuminate\Support\Facades\DB;

class GetsectionParentGoalService extends Service

{

const MY_section_PARENT_INDEX = 0; // 集計部署取得クエリのINDEX

/**

* 集計対象部署の半期のKPIを返却

* @param int $sectionParentId

* @param int $year

* @param string $period

* @return object(stdClass)

public function execute($sectionParentId, $year, $period)

{

$sql = <<< SQL

SELECT

SUM(kpi1) AS kpi1,

SUM(kpi2) AS kpi2,

SUM(kpi3) AS kpi3,

SUM(kpi4) AS kpi4,

SUM(kpi5) AS kpi5,

SUM(kpi6) AS kpi6,

SUM(kpi7) AS kpi7,

SUM(kpi8) AS kpi8,

SUM(kpi9) AS kpi9,

SUM(kpi10) AS kpi10,

SUM(kpi11) AS kpi11,

SUM(kpi12) AS kpi12

FROM

tb_section_kpi

WHERE tb_section_kpi.section_id IN

(

SELECT DISTINCT

tb_section.section_id

FROM

tb_section

JOIN

tb_section_parent

ON tb_section.section_parent_id = ?

WHERE

tb_section.deleted = 0

AND

tb_section.year = ?

AND

tb_section.period = ?

)

SQL;

$result = DB::select($sql, [

$sectionParentId, // ON tb_section.section_parent_id = {$sectionParentId}

$year, // tb_section.year = '{$year}'

$period, // tb_section.period = '{$period}'

]);

return $result[self::MY_section_PARENT_INDEX];

}

終端文字の後に１行空行を入れることこの場合だとSQL; 終端文字の行の前後に文字が入らないようにする →左端にくっつきます。結果はarray+stdClassで取得されますヒアドキュメント内の変数・int型は{ …

の続きを読む

MySQL

MySQL xxx doesn’t have a default value

2020年4月14日2020年4月17日 | by 優 | にコメントを残す

MySQL5.6以降から規約が厳しくなった。 SQLを改善する MySQLの設定で緩める今回はMySQLの設定を緩めることで対応 /etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
↓
sql_mode=NO_ENGINE_SUBSTITUTION

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

↓

sql_mode=NO_ENGINE_SUBSTITUTION

対応

$ systemctl restart mysqld

1	$ systemctl restart mysqld

[YouTube] IT速報よわよわ …

の続きを読む

MySQL, RDB, SQL

MySQL5.7 いいね機能のSQL

2019年12月21日2019年12月27日 | by 優 | にコメントを残す

SQLの実行順序おさらい SELECT 列の抽出対象 FROM テーブル名 WHERE 行の絞り込み条件 GROUP BY hoge_colum HAVING 100 > amount Aテーブルの列と行を切り出しイメージをする Bテーブルの列と行を切り出しイメージをする ONで結合条件を指定 WHEREで行を絞りこむ GROUP BYでグループ化 …

の続きを読む

MySQL, RDB, チューニング, Linux

MySQL SHOWによる解析とチューニング

2019年4月2日2019年5月29日 | by 優 | にコメントを残す

MySQLのチューニング調査依頼で時短で終わるようにバージョン CentOS7 MySQL5.6 DB, テーブルのDBエンジンの確認データベースの確認

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| exampledb       |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| exampledb |

+--------------------+

4 rows in set (0.00 sec)

デフォルトのエンジンの確認

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

…

の続きを読む

MySQL, AWS

AWSでのMySQLデータ生存戦略

2018年11月28日2019年11月28日 | by 優 | にコメントを残す

MySQL on EC2 EFSをマウント mysqldumpのスナップショットを定期で取ってEFSに出力バイナリログを–result-file=で指定してEFSに出力合わせて、 Cronを5秒ずつ程度走らせてバイナリログをS3に同期してバックアップ CloudWatchにもログを出力 MySQL on RDS 5分毎にSnapshotが走っている仕様 …

の続きを読む

MySQL, RDB, チューニング

コンシューマでJOIN禁止, 固定長DBまとめ

2018年2月7日2019年2月7日 | by 優 | にコメントを残す

コンシューマ世界でのJOIN禁止、固定長ルールのまとめ私 JOIN禁止の世界は知らない(1テーブルに1億以上のレコードを扱ったことがない) 原則として正規化できるものは正規化してJOINを行い、正規化する必要のないものは正規化しないスタンス 1テーブルはどんなに大きくても20カラム限界がセオリー →正規化できるのではないか、テーブルをわける 1テーブルが大きく正規化されずに開発されたサービスの保 …

の続きを読む

MySQL, SQL, チューニング

SQL インデックスが効かない検索

2018年1月25日2019年9月8日 | by 優 | にコメントを残す

基本戦略テーブルを正規化する JOIN(INNER JOIN)を有効に活用する非正規化でテーブルを巨大にしないすべてのテーブルを正規化することは出来ない出来るテーブルのみ正規化する EXPLAINで計測する、INDEXが有効に動いているか確認するインデックスのデメリット SELECT性能がBtreeインデックスによって向上するが、UPDATEでINDEXが再生 …

の続きを読む

MySQL, SQL, トラブルシューティング

MySQL デッドロック対処

2017年12月4日2019年1月21日 | by 優 | にコメントを残す

MySQLのデッドロック対処おまけでギャップロック寄稿しました。今回はトランザクションでよくありがちなデッドロックのご紹介。たすきがけのデッドロックよくデッドロックはなんぞや？といった時に提示されるパターンです。

CREATE TABLE a_table(
	a_id       INT UNSIGNED PRIMARY KEY,
	point      INT UNSIGNED,
    value      VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO a_table(a_id, point, value) VALUES
	(1, 100, "a");
	



CREATE TABLE b_table(
	b_id       INT UNSIGNED PRIMARY KEY,
	point      INT UNSIGNED,
    value      VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO b_table(b_id, point, value) VALUES
	(1, 100, "a");

CREATE TABLE a_table(

a_id INT UNSIGNED PRIMARY KEY,

point INT UNSIGNED,

value VARCHAR(255)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO a_table(a_id, point, value) VALUES

(1, 100, "a");

CREATE TABLE b_table(

b_id INT UNSIGNED PRIMARY KEY,

point INT UNSIGNED,

value VARCHAR(255)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO b_table(b_id, point, value) VALUES

(1, 100, "a");

…

の続きを読む

MySQL, Linux

Percona XtraBackup ロールフォワード

2017年12月2日2017年12月3日 | by 優 | にコメントを残す

Percona XtraBackup mysqldumpリストア時間比較おまけでロールフォワード寄稿しました。どっちを使う？ XtraBackup レプリケーションが行われているシステム。 WEBサービス。1秒も止められないようなもの。1秒で利益や損失が発生するサービス。 XtraBackup, mysqldumpの併用しておく。 mysqldump 業務アプリ。深夜や早朝、お …

の続きを読む

MySQL, チューニング, Linux

MySQL 5.6 ファイルディスクリプタカーネルチューニング CentOS7

2017年10月26日2019年1月25日 | by 優 | にコメントを残す

MySQL5.6 ファイルディスクリプタチューニング CentOS7 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 10140) [Warning] Buffered warning: Changed limits: max_con …

の続きを読む