2016年5月2日月曜日

はじめてのMySQLへのPull-Request体験談

の前に、いくつかMySQLへのContributeの前提を。

MySQLへのコードの寄贈は

1. Oracleプロファイルを持っていないといけない。ダウンロードする時に "No thanks" に気付かないと作らされるアレ。
2. Oracle Contributor Agreement にサインしないといけない(OCAにサインしたかどうかがOracleプロファイルに紐付けられる…ので、Oracleプロファイルが必要みたい)

が前提になる。
これなしでいきなりPull-Requestしても多分蹴られる(ってか手違いで蹴られた)


前提について詳しくはこのへんの記事が読みやすくて良いと思う。




で、取り敢えず バグレポート を上げて、GitHubでも Pull-Request を出してみた。

予想としては
Hi, thank you for your contribution. Please confirm this code is submitted under the terms of the OCA (Oracle's Contribution Agreement) you have previously signed by cutting and pasting the following text as a comment:
"I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it."
Thanks

bug 79747: crc32 optimizations by grooverdan · Pull Request #68 · mysql/mysql-server

こんな感じで、「これコピペしてね」って言われると思ってたんだけどさにあらず。



Hi, thank you for submitting this pull request. In order to consider your code we need you to sign the Oracle Contribution Agreement (OCA). Please review the details and follow the instructions at http://www.oracle.com/technetwork/community/oca-486395.html
Please make sure to include your MySQL bug system user (email) in the returned form.
Thanks

Fix Bug#80833 by yoku0825 · Pull Request #102 · mysql/mysql-sys


(;・3・) アルェー、コピペしようにもコピペするべき文章が見当たらないぞー。

mysql/mysql-server と mysql/mysql-sys でbotが違うのかなぁと思いつつ、かるーく 梅ッシュ 最近のVerifiedおじさんことUmeshに「GitHubでPull-Request出したけど大丈夫ですかね?」的なジャブをかましてみるも、「大丈夫じゃね?」的な返答。

そして待つことしばし。


Hi, there was no response to our request to sign an OCA or confirm the code is submitted under the terms of the OCA. As such this request will be closed.
Thanks

Fix Bug#80833 by yoku0825 · Pull Request #102 · mysql/mysql-sys


Σ(゚д゚lll) confirm用のメッセージが表示されないまま「お前がconfirmしないからcloseするわー」ってなった!!!1


困った時の Lenkaちゃん 。
bugs.mysql.comにパッチを送り付けると、Lenkaちゃんから「受け取ったよ!」ってメールが来る(Lenkaちゃん宛てにbugs.mysql.comからメールが飛んでるっぽく、それを転送してくれるんだけど、どうもその時間差から察するに、自動転送ってわけではなさそう)ので、きっと彼女なら詳しいはず。

待つことしばし。


it looks like that for some reason I have not added (or it was somehow removed…) your GitHub name.
As a result the script did not know that you signed OCA.

( д ) ゚ ゚ ホワッ!?

そしてmysql-oca-botと同じアイコンの mysql-admin というアカウントによってreopenされるPull-Request。
https://github.com/mysql/mysql-sys/pull/102#issuecomment-215091765


その後は無事にbotによってbugs.mysql.comがアップデートされました。

Lenkaちゃんありがとう。



…ところでついさっき気付いたんだけど、俺、今まで一度も○racleさんにGitHubアカウント提出したことなかったや。。(今はOCAの段階で聞かれるのかしらん? 体験談求む)

取り敢えず困った時はLenkaちゃん。ありがとうございました(*-人-)

2016年4月26日火曜日

epelからyumでCactiをインストールするとcacti.sqlがない

久々にCactiをインストールしようとしたらハマった。
ハマったところは Cactiをインストール via yum - CentOS@さくらVPSで構築するサーバ管理・運用メモ を見て解決したんだけれど(ビバインターネット)2016年にもなってまだcacti.sql入ってないの馬鹿なの? とか思ってたらなんか変な動作してる。


なんだこれ。

# cat /etc/centos-release
CentOS release 6.6 (Final)

# yum install -y epel-release

# yum install -y cacti
..

# rpm -ql cacti | grep cacti.sql
/usr/share/doc/cacti-0.8.8b/cacti.sql

# ll /usr/share/doc/cacti-0.8.8b/cacti.sql
ls: cannot access /usr/share/doc/cacti-0.8.8b/cacti.sql: No such file or directory

# rpm -Vv cacti | grep cacti.sql
.........  d /usr/share/doc/cacti-0.8.8b/cacti.sql

epelからyumで突っ込むとcacti.sqlは無い。
でもrpm -Vでもmissingとも言われない。


# yum install -y epel-release yum-utils

# yumdownloader cacti

# yum install -y crontabs httpd mysql net-snmp net-snmp-utils php php-mysql php-snmp rrdtool perl
# rpm -i cacti-0.8.8b-7.el6.noarch.rpm

# rpm -ql cacti | grep cacti.sql
/usr/share/doc/cacti-0.8.8b/cacti.sql

# ll /usr/share/doc/cacti-0.8.8b/cacti.sql
-rw-r--r-- 1 root root 178349 Aug  7  2013 /usr/share/doc/cacti-0.8.8b/cacti.sql

# rpm -Vv cacti | grep cacti.sql
.........  d /usr/share/doc/cacti-0.8.8b/cacti.sql

yumdownloaderからのrpmでインストールするとフツーにあった(コンテナーは新しく立ち上げ直した)
えー、なにこれ。


# yum install -y epel-release yum-utils

# yumdownloader cacti
# yum install -y ./cacti-0.8.8b-7.el6.noarch.rpm

# rpm -ql cacti | grep cacti.sql
/usr/share/doc/cacti-0.8.8b/cacti.sql

# ll /usr/share/doc/cacti-0.8.8b/cacti.sql
ls: cannot access /usr/share/doc/cacti-0.8.8b/cacti.sql: No such file or directory

# rpm -Vv cacti | grep cacti.sql
.........  d /usr/share/doc/cacti-0.8.8b/cacti.sql

yumdownloaderからのyumコマンドだと消えるので、yumコマンドが悪さをしてるのはそうなんだろうけど。。
ナニコレ。

CentOS 6.6とCentOS 7.2(の吊るしのDockerイメージ)で確認。


【2016/04/26 13:42】

教えてもらいました! ビバインターネット!





# yum install -y epel-release

# diff -C1 /etc/yum.conf{.orig,}
*** /etc/yum.conf.orig  2016-04-26 04:44:45.807999999 +0000
--- /etc/yum.conf       2016-04-26 04:44:55.390999997 +0000
***************
*** 12,14 ****
  distroverpkg=centos-release
! tsflags=nodocs

--- 12,14 ----
  distroverpkg=centos-release
! #tsflags=nodocs

# yum install -y cacti

# rpm -ql cacti | grep cacti.sql
/usr/share/doc/cacti-0.8.8b/cacti.sql

# ll /usr/share/doc/cacti-0.8.8b/cacti.sql
-rw-r--r-- 1 root root 178349 Aug  7  2013 /usr/share/doc/cacti-0.8.8b/cacti.sql

やった!! ありがとうございます!!

2016年4月1日金曜日

舞奈たんとは何なのか

4/1ですが大真面目なはなし(?)


俺が最初に言い出したのは多分この時。



日本MySQLユーザ会  略称MyNA(まいな)と日本MySQLユーザ会MariaDB分科会 略称MariNA(まりな)、擬人化されてないのおかしくね? みたいなネタでした(誰だったか忘れたけれど誰かが言ってたのをネタにしたと記憶している)


【2016/04/27 12:42】
代表えもんだったようです。







それ以降は割としょっちゅう言ってた気がします。




あれから2年が経ち…カッとしてクラウドワークスで発注しました。
今でもなんであんなにムキになって舞奈たんを擬人化したかったのか良くわかりません。
なお、舞奈たんは「マイナくん(仮)」の擬人化であってMySQLの擬人化ではないので、緑色です。

最初は 第2回 MySQL・PostgreSQLユーザーグループ(MyNA・JPUG)合同DB勉強会 に間に合うようなスケジュールを考えていたんですが(「MySQLといかMyNAのPostgreSQLというかJPUGに対する優位点? 萌えキャラがいることですよ!」って言いたかった)、依頼してたデザイナーさんと連絡が取れなくなるなどクラウドソーシングの闇をいくつか見たあとで、最終的に キヨイチ さんに受注していただきました(*-人-)





( ´-`).oO(現在もう候補画像は見られませんが、当時1案と呼ばれていたものが僅差で勝ちました。というわけでこれが舞奈たん。




この手のネタは人によって好き嫌い分かれそうですが、まあかつて↓こんなことやってた人たちなので大丈夫だろうとか思ってましたし思ってます。

萌え萌えなMySQL互換オープンソースデータベース「MoSQL」 - GIGAZINE


もともと、俺が会社目的でも個人目的でもスライドなりステッカーなりサイトなり好きなように使えるように発注したものなので、ライセンスはとてもゆるい です。
値段ぶんくらいは使い倒したいと思っているのでみなさんも協力してくだしあ。

ちなみに「どこからその予算出てるの?」と聞かれることが何度かありましたが、俺のお小遣いです


何が言いたいかと言うと今月のクレジットカードの引き落としがくぁwせdrftgyふじこlp

2016年3月30日水曜日

SHOW TABLE STATUSのData_lengthとかIndex_lengthとかData_freeの値をぼんやり考える

InnoDBの場合。MyISAMは全くアテにならなかった(少なくとも5.7.11では)
InnoDBでも所詮統計情報なので完全にアテになる訳じゃないのはお約束。

テスト。


mysql57> CREATE TABLE t1 (val char(250));
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t1 SET val= '';
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t1 SELECT * FROM t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

..

mysql57> INSERT INTO t1 SELECT * FROM t1;
Query OK, 131072 rows affected (1.67 sec)
Records: 131072  Duplicates: 0  Warnings: 0

ysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.00 sec)

mysql57> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 257368
 Avg_row_length: 312
    Data_length: 80330752
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2016-03-30 19:14:45
    Update_time: 2016-03-30 19:24:46
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=Dynamic
        Comment:
1 row in set (0.01 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     257368 | 76.61 MiB   | 0 bytes      | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.01 sec)

SHOW TABLE STATUSだと見にくいのでi_s使って取ってみる。
innodb_autoextend_incrementはシステムテーブルスペース(ibdata1)の自動拡張単位で、.ibdファイルの場合は最大で4MB(らしい)

https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_autoextend_increment

100行ずつINSERTしながら100万行くらいまで様子を見てみたけど、Data_freeはだいたい4MB~7MBの間を彷徨っている様子。4MBまでは利用可能領域を使って、それを割るとautoextendするっぽい。

行を半分くらい消してみる。


mysql57> DELETE FROM t1 WHERE rand() < 0.5;
Query OK, 130891 rows affected (1.00 sec)

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.00 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     131354 | 76.61 MiB   | 0 bytes      | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)

Data_lengthは変わらず、Data_freeも変わらず。.ibdファイルのサイズももちろん変わらない。


mysql57> INSERT INTO t1 SELECT * FROM t1 WHERE rand() < 0.5;
Query OK, 65675 rows affected (0.77 sec)
Records: 65675  Duplicates: 0  Warnings: 0

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.00 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     193161 | 76.42 MiB   | 0 bytes      | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)

消したぶんより少ない行をINSERTすると、Data_lengthは伸びない(= 空きページが再利用されている)けどData_freeも変わらない。謎い(deleteした時にdata_freeをインクリメントしてた気がするんだけど見付けられない。気のせいだったのか)
パージスレッドのご機嫌なのかなぁと思ってinnodb_fast_shutdown= 0にして再起動してみたけど変わらない。WHEREなしのDELETEでどかんと行くとData_freeに全部計上されるんだけどなぁ。


mysql57> SET GLOBAL innodb_fast_shutdown= 0;
Query OK, 0 rows affected (0.00 sec)

mysql57> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

$ mysqld_multi start 57


Index_lengthも同じ様子。

mysql57> ALTER TABLE t1 ADD KEY (val);
Query OK, 0 rows affected (9.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.01 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     191498 | 76.44 MiB   | 58.98 MiB    | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)

mysql57> DELETE FROM t1 WHERE rand() < 0.5;
Query OK, 98257 rows affected (1.94 sec)

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.02 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|      92680 | 76.44 MiB   | 58.98 MiB    | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)


大体、storage/innobase/handler/i_s.ccから上手く見つけられないんだけどどこから見つければいいんだっけ。。:(;゙゚'ω゚'):

2016年3月8日火曜日

MySQL <= 5.7.10からMySQL >= 5.7.11に乗り換えるだけでdefault_password_lifetimeの呪縛から逃れられる理由

見直したらほぼ 日々の覚書: MySQL 5.7.4で導入されたdefault_password_lifetimeがじわじわくる(MySQL 5.7.11でFIX!!) に書いてあったんだけど、



default_password_lifetime はユーザー作成時には何もせず、ユーザーがログインするたびにpassword_last_changedと比較するので、default_password_lifetime= 360でユーザーを作っちゃっても今の値が0なら特に何もする必要はないです

文字コードの話でいうと、character_set_serverのデフォルトにあたるものが置き換わっただけで、テーブル単位で既に指定されている文字コードは変わらない、というのと同じ感じ。

default_password_lifetimeは「ユーザーを作ってからn日後にEXPIREする」ではなくて(変数の名前からするとそんな動作しそうなんだけど)、「ログイン試行時にpassword_lifetimeが明示的に決められていない場合、password_last_changedと現在時刻を比較して、n日以上経過してたらエラーを返す」ためのパラメーター。


mysql57> SELECT user, host, password_expired, password_last_changed, password_lifetime FROM user;
+-----------+-----------+------------------+-----------------------+-------------------+
| user      | host      | password_expired | password_last_changed | password_lifetime |
+-----------+-----------+------------------+-----------------------+-------------------+
| root      | localhost | N                | 2016-02-07 21:31:49   |              NULL |
| mysql.sys | localhost | N                | 2016-02-07 21:31:49   |              NULL |
+-----------+-----------+------------------+-----------------------+-------------------+
2 rows in set (0.00 sec)

関係してくるカラムはこのへん。

まず、password_lifetimeが NULLならuse_default_password_lifetimeのフラグが立つ。NULL以外の場合(EXPIREする日付が入る)はuse_default_password_lifetimeのフラグは降りる。これはacl_loadの中なので、mysqldが起動した時やFLUSH PRIVILEGESの時にこの処理を通る。

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_auth_cache.cc#L1764-L1798


それから認証時のcheck_password_lifetime。

1) password_expiredが'Y'ならreturn false
2) use_default_password_lifetimeフラグが降りてたらpassword_lifetimeと現在時刻を比較。過ぎてたらtrue
3) フラグが降りてなかったらpassword_last_changedと現在時刻とdefault_password_lifetimeで比較。過ぎてたらtrue

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_authentication.cc#L1988-L2028


check_password_lifetimeの呼び出し元では、password_expiredが'Y'またはcheck_password_lifetimeの戻り値がtrueならEXPIREされているとしてER_MUST_CHANGE_PASSWORD_LOGIN。

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_authentication.cc#L2358-L2381


CREATE USER時に通るのはmysql_create_userだけれど、ここは特にdefault_password_lifetimeはチェックしてない。ALTER USERとSET PASSWORDの時だけ変数を参照してるくらい。

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_user.cc#L1232


5.7.11の時点で、という感じなので、5.8以降で再び仕掛けてくる時はどうなるか知らない。

2016年3月2日水曜日

複数のテーブルのON UPDATE current_timestampなカラムの値を揃える方法を考える

タイトルで何を言ってるのか我ながら良くわからない。。


mysql56> SELECT * FROM t1 JOIN t2 USING(num);
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
| num | val   | created             | updated             | val    | created             | updated             |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
|   1 | one   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | eins   | 2016-03-01 15:38:44 | 2016-03-01 16:40:29 |
|   2 | two   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zwei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:33 |
|   3 | three | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | drei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:36 |
|   4 | four  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | vier   | 2016-03-01 15:38:44 | 2016-03-01 16:40:59 |
|   5 | five  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | funf   | 2016-03-01 15:38:44 | 2016-03-01 16:41:05 |
|   6 | six   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sechs  | 2016-03-01 15:38:44 | 2016-03-01 16:41:11 |
|   7 | seven | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sieben | 2016-03-01 15:38:44 | 2016-03-01 16:41:16 |
|   8 | eight | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | acht   | 2016-03-01 15:38:44 | 2016-03-01 16:41:27 |
|   9 | nine  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | neun   | 2016-03-01 15:38:44 | 2016-03-01 16:41:34 |
|  10 | ten   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zehn   | 2016-03-01 15:38:44 | 2016-03-01 16:41:46 |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
10 rows in set (0.00 sec)


t1.updated, t2.updatedはそれぞれDATETIME ON UPDATE CURRENT_TIMESTAMPなカラム。
やりたいことは、

mysql56> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:35 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:40 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.01 sec)

この時点でt1.updatedとt2.updatedを同じ時刻にすること。



1. リテラル渡す

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

はじめっからUPDATE ON current_timestampなんてつけないでほしかった。


2. 1ステートメントで更新する

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 JOIN t2 USING(num) SET t1.val= 'updated', t2.val= 'updated' WHERE t1.num = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

CURRENT_TIMESTAMP()はNOW()のシノニムなので、そのまま使うと「ステートメント開始時点の現在時刻」を返す。
という訳で1ステートメントなら開始時刻は1つに定まる。

どう考えても綺麗にJOINできないテーブルとかあるしINSERTやDELETEが混じったトランザクションで詰むので却下。


3. timestamp変数使う

さらに、SET TIMESTAMP ステートメントによって、NOW() で返された値は影響を受けますが、SYSDATE() で返された値は影響を受けません。つまり、バイナリログのタイムスタンプ設定は、SYSDATE() の呼び出しに影響しないことを意味します。タイムスタンプをゼロ以外の値に設定すると、後続の NOW() が起動されるたびに、その値が返されます。タイムスタンプをゼロに設定すると、この効果が取り消され、再度 NOW() が現在の日付と時間を返すようになります。

http://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now

というわけでこうじゃ。


mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= @@timestamp;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql56> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= 0;
Query OK, 0 rows affected (0.00 sec)

コミットした後は(前でもいいけど)timestamp変数を0に戻しておかないと、そのコネクションのNOW()がいつまでもSET timestampした時点の時刻を返すようになる。


…はじめっからUPDATE ON current_timestampなんてつけないでほしかった。

2016年3月1日火曜日

ORDER BY RAND()はしたくないけどそれなりにランダムな結果セットを返すいくつかの方法を考える

ORDER BY RAND()といえば、「結果セットをランダムにソートし、LIMITと組み合わせることでランダムに指定件数をピックアップしたかのように見える」黒魔術。

( ´-`).oO(そういえばこれも ORDER BY FIELD と一緒で構文だと思っていた人がいたな。。


これもまあRAND()関数を使ってるだけなので、select_listに放り込めば何やってるかわかりやすい。

mysql56> SELECT num, val, RAND() AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 94164 | e8d2546088e6be7ff164964c7a07bdb3 | 0.000012977980089353379 |
|  4354 | 46d0671dd4117ea366031f87f3aa0093 |  0.00001926440747386255 |
| 11573 | 2d6304a207cd9469f776e651e81ed7f8 | 0.000023321248612665803 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.09 sec)


余談だけど、RAND()関数は引数にシード値を取れる(引数を取らない場合はテキトーにシード値が設定される)ので、ランダムっぽいけど再現可能な並び順を作り出すこともできる。

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.08 sec)

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

なおこの場合、「そのシードで何番目に生成された乱数か」がキモになるので、WHERE句評価後 *何番目に* その行がフェッチされるかでrand_valの値は変わる。WHERE句を評価した後に、取り出したレコードの順番にRAND()を計算してソートするからだ。


mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num > 30000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78089 | 67ebeaa4f6391a89d2b629860fff2c9d | 0.000006978400058092922 |
| 64076 | 6a3f8e5443504151a7306f2a13fae303 |  0.00004350300882337895 |
| 30140 | 8befb4efe8ce6cdf0e1a84974d452a9f |  0.00004702340815870409 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num < 80000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.10 sec)


だから本当に再現可能なランダムっぽい何かをしたい場合、(あるなら)サロゲートキーを渡してゴニョる方がいい気がする。これなら常に「そのシードで1番目の乱数」を使うことになるので、「結果セットの先頭から何番目にあるか」が変わってもRAND()関数の戻す値は変わらない(はず) 並び順をズラす場合、サロゲートキーに何かしら足してやったりかけてやったりすればいい。

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num < 90000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.08 sec)

mysql56> SELECT num, val, RAND(num * 2) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 25517 | 362f278d9150aaf7894f586b5682de06 |  0.00001504737885207625 |
| 43266 | faeddbfcef4331221e71ed4186e0c65b | 0.000025210902118320486 |
| 61015 | 67f68835939b9fa291a4e417312b4ec1 |  0.00003538932654577245 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)


さて本題。
ORDER BY RAND()はWHERE句でフィルターされた後の行全てに対してRAND()関数を適用し、その結果でソートするので、WHERE句でフィルターした後の行が多ければ多いほど重くなるし、WHERE句で十分フィルターが聞いてもUsing temporaryに落ちる。

昔からよく言われることではあるが、アプリケーション側で乱数を作ってWHERE句に指定するのはよくあるやり方だ。その場合、ORDER BY FIELD() はやっぱり役に立つかもしれない。
アプリケーション側と言いながらSQLでやってるのは気にしない。

mysql56> SELECT MAX(num) FROM t1 INTO @max_num;
Query OK, 1 row affected (0.01 sec)

mysql56> SET @r1 := CAST(@max_num * RAND() + 1 AS signed), @r2 := CAST(@max_num * RAND() + 1 AS signed), @r3 := CAST(@max_num * RAND() + 1 AS signed);
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num IN (@r1, @r2, @r3) ORDER BY FIELD(num, @r1, @r2, @r3);
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 68966 | 3ccf50b9d73ea11f758bd030e5ac593f |
| 51533 | 2f6a0826437abc6688b22dfd89d783c0 |
| 50767 | 8deda01d4df57c1d3cb6b1e4a0391fbe |
+-------+----------------------------------+
3 rows in set (0.01 sec)


実際問題、auto_increment(の値とは限らないけれど)の値が一貫して抜けがないことは期待できない(innodb_autoinc_lock_mode= 0だってDELETEすれば抜ける訳で、= 1ならトランザクションをロールバックしたりDuplicate Key Errorを起こすだけでautoincは進む)ので、こんな風に書くしかなかろうか。

mysql56> SELECT MAX(num) FROM t1 INTO @max_num;
Query OK, 1 row affected (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+------+----------------------------------+
| num  | val                              |
+------+----------------------------------+
| 8564 | 621eb0b827c09dd1804e87bd74f79383 |
+------+----------------------------------+
1 row in set (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 67575 | fd85263468f2e1315a31116cf7b12a00 |
+-------+----------------------------------+
1 row in set (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 12181 | b75867b590e9a1a38ceaea9f8cb9cf45 |
+-------+----------------------------------+
1 row in set (0.00 sec)

INで仕留めた場合でもLIMIT 1で仕留めた場合でも、SQLのレイヤーでは重複(たとえばnum = 1が2回引っかかる)や空振り(その条件にマッチするレコードが1件もない)を検出しないので、それが嫌ならアプリケーション側で重複判定をする必要がある。それに、必要な件数が揃うまで複数回クエリーを投げる必要があるので、それが嫌な感じはもちろんする(とはいえ、速度的には大概の場合お釣りがくる)。あとはサロゲートキーが極端に偏ってるとこれは死ぬかも知れない。1, 2, 3, .., 1000の次が10万だったりすると、max_num * rand()を超えるレコードはかなりの高確率でnum= 10万だ。


もう一つ、WHERE RAND()という手法もある。`tail -f .. | perl -nle 'if (rand() < 0.01) {print}'` って話を聞いて思い付いただけだけれども。

mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 99966 | 44d3377fd88bc32cd46acd38d716abd3 |
| 99899 | a9cfebcdb4e20ed975e82b7fd877693f |
| 99790 | 7aab6ba599620439ed28d3cee272c3af |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 99954 | 758f0bc5e561543556e9f4e0d23335cc |
| 99663 | 10ef53cc7b761466d851d05dda6b82e3 |
| 99650 | 02aecc1719dc308a7efac6064861cf93 |
+-------+----------------------------------+
3 rows in set (0.00 sec)


DESCにしてるのは趣味なのでASCでもいいかも知れない(けど、古いものからやるよりは新しいものから引いた方がバッファプール効率がいい) もうちょっと分散させたい場合はRAND()と比較する定数の値を小さくすれば良くて、1/100でLIMIT 3なら、期待値として直近300行をhandler_read_prevして、その中から3件が選ばれる。飽くまで1クエリーで1つのテーブルから取ってくるので、重複はない。が、確率で取ってくることになるのであまりタイトなサンプリングをすると空振り(LIMITで指定した件数が集まらない)はあり得る。あと、この形はサロゲートキーに依存せずインデックスさえあれば好きなインデックスでORDER BYできる。

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 312   |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)


ほぼ期待値通り。 OORDEER BY RAND()とWHEREで3回引くケースはこんな感じだった。

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 1      |
| Handler_read_key      | 1      |
| Handler_read_last     | 0      |
| Handler_read_next     | 0      |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 3      |
| Handler_read_rnd_next | 200002 |
+-----------------------+--------+
7 rows in set (0.00 sec)

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 4     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)


直近のPRIMARY KEYはバッファプールに載ってる(INSERTされたままバッファプールに残ってる)率が高い気がするので、偏りが許せるなら実用的だと思う(し、本当に完全ランダムでなきゃいけないならRAND()関数とか使っちゃいけない気がする) これだけ件数が絞れてれば、FROM句に閉じ込めてORDER BY RAND()しても許せなくはないくらいのパフォーマンスのはず。パッと見、numでORDER BYされてるとは思えないような感じに仕上がる(かもしれない)

mysql56> SELECT * FROM (SELECT num, val FROM t1 WHERE rand() < 1/3000 ORDER BY num DESC LIMIT 3) AS dummy ORDER BY RAND();
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 96640 | 3076ef0ad4d1e7c6dec15fb4541b6997 |
| 95735 | 3b2016665210c18767dfe611b76ffbea |
| 97248 | 32d32773f19f2f421ebc2d41da4ef5a9 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> show profile cpu;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000046 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000005 | 0.000000 |   0.000000 |
| init                           | 0.000008 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000348 | 0.000000 |   0.000000 |
| checking permissions           | 0.000016 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000195 | 0.000000 |   0.000000 |
| init                           | 0.000019 | 0.000000 |   0.000000 |
| System lock                    | 0.000018 | 0.000000 |   0.000000 |
| optimizing                     | 0.000004 | 0.000000 |   0.000000 |
| optimizing                     | 0.000019 | 0.000000 |   0.000000 |
| statistics                     | 0.000035 | 0.000000 |   0.000000 |
| preparing                      | 0.000019 | 0.000000 |   0.000000 |
| Sorting result                 | 0.000003 | 0.000000 |   0.000000 |
| statistics                     | 0.000003 | 0.000000 |   0.000000 |
| preparing                      | 0.000006 | 0.000000 |   0.000000 |
| Creating tmp table             | 0.000022 | 0.000000 |   0.000000 |
| Sorting result                 | 0.000004 | 0.000000 |   0.000000 |
| executing                      | 0.000014 | 0.000000 |   0.000000 |
| Sending data                   | 0.000012 | 0.000000 |   0.000000 |
| executing                      | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.001889 | 0.001999 |   0.000000 |
| Creating sort index            | 0.000045 | 0.000000 |   0.000000 |
| end                            | 0.000002 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000025 | 0.000000 |   0.000000 |
| end                            | 0.000009 | 0.000000 |   0.000000 |
| query end                      | 0.000007 | 0.000000 |   0.000000 |
| closing tables                 | 0.000002 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000004 | 0.000000 |   0.000000 |
| closing tables                 | 0.000010 | 0.000000 |   0.000000 |
| freeing items                  | 0.000176 | 0.001000 |   0.000000 |
| cleaning up                    | 0.000041 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
31 rows in set, 1 warning (0.00 sec)

サロゲートキーへの依存度はmax_num * RAND()より小さいけど、なんかどこかに落とし穴がありそうだよなぁ。。

( ´-`).oO(query_cache_type= DEMANDなんだけど、これ切れば300usくらい速くなるな。。


最後に、ランダムピックしたものをそもそもキャッシュする方法も考え付いた。そもそも元の行数が少なければ、ORDER BY RAND()でも戦えるんじゃないか、って話。
キャッシュは定期的に更新(というかスワップ)してやればいい。

mysql56> CREATE TABLE rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000;
Query OK, 10000 rows affected (0.17 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
|  2533 | 4de754248c196c85ee4fbdcee89179bd |
| 78915 | d3272a819b09ced96c69e22f183cc88e |
| 16351 | dcb8e02b8527b08dbd8acb146bccc612 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> CREATE TABLE tmp_rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000;
Query OK, 10000 rows affected (0.14 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql56> RENAME TABLE rand_cache TO old_rand_cache, tmp_rand_cache TO rand_cache;
Query OK, 0 rows affected (0.01 sec)

mysql56> DROP TABLE old_rand_cache;
Query OK, 0 rows affected (0.01 sec)

mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 98937 | 73a3320fa46a5e4fad268056af61cd42 |
| 54927 | a11d83c11ed8c95a32b3628a762cf41f |
| 38444 | d3e8129138f2c76dc6e4048281160fe0 |
+-------+----------------------------------+
3 rows in set (0.01 sec)


何も考えないORDER BY RAND()はORDER BY RAND()で、「空振りが無い(WHERE句でフィルターした結果がLIMIT未満でなければ)」、「重複がない」、「サロゲートキーに依存しない」というメリットもあるのだなぁと思った。そりゃあ使いたがる人多くてもわかるは。