MySQLのハマりどころ - 株式会社Donuts

前回に引き続き、テーマはMySQLです。

パラメータのチューニングは、
「MySQL チューニング」で検索すれば、山ほど出てくるので、
そちらに譲るとして、もっと初歩的なところに触れたいと思います。
(特に、http://dsas.blog.klab.org/archives/50860867.html には私も大変お世話になりました)

skip-resolve-nameは無効にしよう

まずは、my.cnfのパラメータ「skip-name-resolve」についてです。
以下は、Donutsで使っている標準的な設定で、一番下の行にあります。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

log-slow-queries
long_query_time=1
#log-queries-not-using-indexes
#log=/var/lib/mysql/query.log

# server-id=40
# log-bin=/var/lib/mysql_log/binlog
# log-slave-updates
# expire_logs_days = 1

max_connections=100
thread_cache_size=100

key_buffer_size=64M
myisam_sort_buffer_size=1M
sort_buffer_size=1M
read_buffer_size=1M

innodb_file_per_table
innodb_log_file_size=2000M
innodb_log_buffer_size=32M


innodb_buffer_pool_size=4000M
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
#innodb_flush_method=O_DIRECT
skip-innodb_doublewrite
skip-innodb_checksums 

skip-name-resolve

以前から、MySQLへの新規接続が時々、遅いWebサーバーがあり、
疑問に思っていたところ、状況がわかってきました。

DBサーバーのhostsファイルに、
IPアドレスが書かれているWebサーバーは接続が早く、
IPアドレスが書かれていないWebサーバーは接続が遅かったのです。

そして、根本的な原因は、skip-resolve-nameを設定していたことでした。

skip-resolve-nameは、接続を受けた際に、
認証の目的でクライアントのIPアドレスを逆引きする機能です。

接続が遅かった原因は、hostsファイルにIPアドレスが書かれていなかったため、
DNSサーバーで名前解決をしようとしていたことによるものでした。
詳細はこちら: http://dev.mysql.com/doc/refman/5.5/en/dns.html

あまりに基本的すぎるためか、このことについて書いてある情報源が少ないです。
しかし、大量の接続を行う場合は、これを無効にしないと話になりません。
自前でDNSサーバーを運用していれば、まだ良いのですが、
プロバイダのDNSサーバーを使用している場合は、大迷惑。

最終的にはアクセスを禁止されて、「なぜか、DBサーバーに接続できない」事件に発展します。
なぜ、このような機能がデフォルトで有効なのか理解に苦しみます。

MySQL5.0で、無意味な文言が記録される

クエリのチューニングには欠かせない slowqueryのファイルに時々、SQL文が記録されず、

# administrator command: Prepare Execute;

という文言だけが記録されることがあるようです。
MySQL 5.0 だけで 5.5 では起きていないので、早くバージョンアップする必要があるということかもしれませんが。

また、他にもlong_query_time に1秒以下を指定できなかったりもするので、やはり、バージョンアップしたいところです。
(Zend_Db_Profilerを使用したプロファイリングについては次回紹介します)。

ちなみに、Donutsでは、最初に使用し始めたMySQLのバージョンが、CentOS5のデフォルトの5.0.77 であったため、
いまだに5.0.77を使用しているDBサーバーがあります。

MySQL5.5で、InnoDBがtmpfs上で動作しない

ところが、MySQL5.5に移行することに踏み切れない理由がありました。
tmpfs上でInnoDBを動かそうとすると、MySQLが落ちてしまうのです。
http://bugs.mysql.com/bug.php?id=58421

tmpfs にはasynch I/Oが無いのに、asynch I/Oでアクセスしようとして、
Kernelに怒られ、落ちてしまうようです。
1年近く前に、BugDBに挙がっているので、バグフィックスが待ち遠しいところです。

MySQL5.5では、unsingedカラムの更新に注意

MySQL 5.5では、unsingedを指定してあるカラムを更新するupdate 文で、
式の評価途中、負になる場合、エラーとなってしまうようです。
（以下の例では、「col1 - 2」の部分が負になります。）

mysql> create table testtable2 (col1 int unsigned);
mysql> insert into testtable2 (col1) values (1);
mysql> update testtable2 set col1=col1-2+10;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`t`.`testtable2`.`col1` - 2)'

5.0 では何事もなく更新されるのですが…
式の評価途中、負になる場合は、明示的にキャストしてやる必要があるようです。

mysql> update testtable2 set col1=cast(col1 as signed)-2+10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testtable2;
+------+
| col1 |
+------+
|    9 |
+------+
1 row in set (0.00 sec)