MySQLのハマりどころ
前回に引き続き、テーマは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)
UPDATE文ではWHERE句の書き方で速度が違う？
SQLのWHERE句で、複数のキーを指定して、データを取得する際、
1) SELECT * FROM testtable WHERE col1=1 and col2=1;
2) SELECT * FROM testtable WHERE (col1, col2) = (1, 1);
という書き方があり、どちらも全く同じ意味を指すものと思っていました。
実際、SELECT 文では、実行結果も、
mysql> CREATE TABLE testtable(col1 INT not null, col2 INT not null, col3 INT, KEY(col1, col2)); mysql> INSERT INT testtable (col1, col2) VALUES (1,1), (2,2), (3,3), (4,4), (5,5); mysql> SELECT * FROM testtable WHERE (col1, col2) = (1, 1); +------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | 1 | NULL | +------+------+------+
EXPLAIN の結果も、
mysql> DESC SELECT * FROM testtable WHERE (col1, col2) = (1, 1)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtable type: ref possible_keys: col1 key: col1 key_len: 8 ref: const,const rows: 1 Extra:
期待通り、インデックスを使用していて問題なさそうです。
UPDATE でも一見同じように動作しているように見えます。
mysql> UPDATE testtable SET col3=1 WHERE (col1, col2) = (1, 1); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
しかし、slow-queries (log-queries-not-using-indexes も有効にしてあります) を見ると
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 use wheretest; UPDATE testtable SET col3=1 WHERE (col1, col2) = (1, 1);
インデックスを使ってくれていないようです。(環境によってはRows_examined にも全行の行数が出たのですが、今回記事にするに当たっての追試では0 になっていました)
上の例では、全行検索しても5行程度なので実質的に速度差はありませんが、データを655360件に増やして実行してみると、結果は0秒、0.16秒となり、明らかな差が出てしまいました。
mysql> UPDATE testtable SET col3=1 WHERE col1 = 1 and col2 = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> UPDATE testtable SET col3=1 WHERE (col1, col2) = (1, 1); Query OK, 0 rows affected (0.16 sec) Rows matched: 1 Changed: 0 Warnings: 0
ストレージエンジンをInnoDB/MyISAM、MySQLのバージョンを 5.0.77/5.5.13 と変えてみたものの結果は変わりませんでした。
まだソースまでは確認していませんが、何か釈然としない結果になってしまいました。とりあえず、原因が分かるまでは (col1, col2) = (1, 1) の書き方は封印しています。
急激に落ちたネットワークのパフォーマンス
初回ということで、サービスイン直後に最初に陥った問題について紹介します。 結論としては、「ネットワーク周りの設定を見直そう」に尽きます。
低負荷のときには正常に動いているかのように見えていた環境が、ネットワーク負荷の高まりにより、急に動作しなくなることがよくあります。
Donutsの場合、まさにこの状況が発生しました。
このようなときは、まずNICの状況を把握し、NICの設定内容が使用している回線に合った設定になっているか、auto-neg、全二重/半二重、通信速度がプロバイダが指定している値と合っているか確認します。
NICの状態を確認する
最初に、ifconfigで現状を確認。
# ifconfig eth0 Link encap:Ethernet HWaddr xx:xx:xx:xx:xx:xx inet addr:192.168.xx.xx Bcast:192.168.1.255 Mask:255.255.255.0 inet6 addr: xxxx/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:1236623 errors:7434 dropped:7434 overruns:0 frame:0 TX packets:1145319 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:463793883 (442.3 MiB) TX bytes:469182630 (447.4 MiB)
ifconfig で errors, dropped, overruns, frame に0以外の数値が出ていれば、まず間違いなく、何かパラメータがおかしいです。(各項目の詳しい意味については他に任せますが、ルータのマニュアル等が詳しいです)
上記の場合、「packets:1236623 errors:7434 dropped:7434」となっており、何か起こっています。そこで、NICの設定状況を確認します。
NICの設定確認と変更を行う
現在の設定の確認と変更は ethtool で行います。
# ethtool eth0 Settings for eth0: Supported ports: [ TP ] Supported link modes: 10baseT/Half 10baseT/Full 100baseT/Half 100baseT/Full 1000baseT/Full Supports auto-negotiation: Yes Advertised link modes: Not reported Advertised auto-negotiation: No Speed: 100Mb/s Duplex: Full Port: Twisted Pair PHYAD: 1 Transceiver: internal Auto-negotiation: yes Supports Wake-on: g Wake-on: d Link detected: yes
プロバイダからは Autoneg は無効と指定されていたのに、yes になっていました。そのせいでDuplex が Full と Half でブレ、結果としてパケットロスが発生していたようです。
# ethtool -s eth0 speed 100 autoneg off duplex full
として、Autonegをoffに変更しましたた。しばらくしてから、もう一度 ifconfig でerrors がそれ以上増えていないことを確認し、一安心。
1000BASE-T の場合は仕様上 Auto-negotiation は常に on なので、
# ethtool -s eth0 speed 1000 autoneg off
などの設定はできません。
Offload処理をチェックする
ついでにOffload処理についても見ておきましょう。Offload処理は、CPUに代わってNICが処理を行うことでCPU負荷を下げる機能なのですが、環境によっては高負荷の時のパケットロスにつながることがあるようで、Donutsのサーバでは全てoff にしてあります。
# ethtool -k eth0 Offload parameters for eth0: rx-checksumming: off tx-checksumming: off scatter-gather: off tcp segmentation offload: off udp fragmentation offload: off generic segmentation offload: off generic-receive-offload: off
全てoffにする設定はこちら↓
# ethtool -K eth0 rx off tx off tso off
余談ですが、ethtool のオプションは、
小文字⇒内容確認、大文字⇒設定
である一方で、基本設定(generic options)だけは、
オプションなし⇒内容確認、「 -s 」⇒設定
であるのは私にはどうも覚えにくくて…
ネットワーク周りの設定を見直そう
このブログでは、株式会社Donutsのサーバ運用でハマった点を中心に紹介していきます。
Donutsではモバゲーで「暴走列伝 単車の虎」というソーシャルゲームを提供していて、現在月間11億PVがあります。
2011年1月末にリリースした時には月間300万PVであったアクセス数が、飛躍的に増える過程で発生した様々な問題を取り上げていくので、分野があちこち飛んで読みにくいかもしれませんが、その点はご容赦願います。
