VOYAGE GROUP　エンジニアブログ

voyagegroup_techのブログ
VOYAGE GROUPエンジニアブログです。

DB

2011年10月13日10:03
カテゴリ
DB

やっぱりioDriveは優秀だった！　MySQL編

こんにちは、DBAの三浦(@hironomiu)です。

ひょんなことからサービス利用前のioDrive(80G)で検証する機会を取れました。
（ioDrive自体は販売初期の頃からサービスで使っていたりします。）

fusion-powered-io
スペック情報等は下記のfusionioのサイトを参照頂ければと思います。
http://www.fusionio.com/platforms/iodrive/
 
今回は前々からMySQLのDiskとしてどのくらいのパフォーマンスが出るのか興味があったので軽く動かしてみました。
サービス利用前とあるように時間が相当限られていたのでioDrive(80G)に最適なOS、FileSystemの選定
カーネル、my.cnf等の細かいチューニングは行っておらず、情報の採取についても代表的なものしか行っていません。

比較の対象としてioDrive(80G)と同モデルのIAサーバでDiskをHDD（4玉 RAID1+0）のものと三浦の検証用のPCに挿したSSD（X25-E Extreme 64G）を用いてみました。
こちらも上記の通り、同一条件ではありませんので、改めて厳密な性能検証では無くioDriveスゲー的な記事である点をご了承下さい。

システムの主な設定
　・OSは全てCentOS5.5(64bit)
　・FileSystemはioDrive(80G)とSSD（X25-E Extreme 64G）はext3、HDD（4玉RAID1+0）はxfs
　・ext3にはfstabでnoatime,data=writebackを設定
　・I/Oスケジューラ（/sys/block/～/queue/schedulerはnoopで統一
　・I/Oキュー（/sys/block/～/queue/nr_requests）は1000で統一

MySQLの主な設定（全て統一）
　・バージョンは5.5.8
　・レプリケーションは行わない（スタンドアロン）
　・バイナリログの取得は行わない
　・innodb_buffer_pool_size=128M
　・skip_innodb_doublewrite
　・query_cache_size=0
　・innodb_read_io_threads= 4
　・innodb_write_io_threads= 4

読込み性能
まずは読込み性能　hdparmを各10回取得しました。
ioDrive(80G)
[1] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.49 MB/sec
[2] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.47 MB/sec
[3] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.53 MB/sec
[4] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.85 MB/sec
[5] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.90 MB/sec
[6] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.67 MB/sec
[7] Timing buffered disk reads: 1946 MB in 3.00 seconds = 648.60 MB/sec
[8] Timing buffered disk reads: 2036 MB in 3.00 seconds = 678.16 MB/sec
[9] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.72 MB/sec
[10] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.53 MB/sec
SSD（X25-E Extreme 64G）
[1] Timing buffered disk reads: 100 MB in 0.51 seconds = 197.56 MB/sec
[2] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.26 MB/sec
[3] Timing buffered disk reads: 100 MB in 0.50 seconds = 199.96 MB/sec
[4] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.40 MB/sec
[5] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.32 MB/sec
[6] Timing buffered disk reads: 100 MB in 0.50 seconds = 199.57 MB/sec
[7] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.51 MB/sec
[8] Timing buffered disk reads: 100 MB in 0.50 seconds = 199.67 MB/sec
[9] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.43 MB/sec
[10]Timing buffered disk reads: 100 MB in 0.50 seconds = 200.47 MB/sec
HDD（4玉RAID1+0）
[1] Timing buffered disk reads: 926 MB in 3.00 seconds = 308.63 MB/sec
[2] Timing buffered disk reads: 890 MB in 3.00 seconds = 296.49 MB/sec
[3] Timing buffered disk reads: 908 MB in 3.01 seconds = 301.95 MB/sec
[4] Timing buffered disk reads: 906 MB in 3.00 seconds = 301.70 MB/sec
[5] Timing buffered disk reads: 918 MB in 3.01 seconds = 305.21 MB/sec
[6] Timing buffered disk reads: 896 MB in 3.00 seconds = 298.20 MB/sec
[7] Timing buffered disk reads: 910 MB in 3.00 seconds = 302.98 MB/sec
[8] Timing buffered disk reads: 904 MB in 3.00 seconds = 300.97 MB/sec
[9] Timing buffered disk reads: 924 MB in 3.01 seconds = 307.06 MB/sec
[10] Timing buffered disk reads: 904 MB in 3.01 seconds = 300.75 MB/sec
平均（MB/sec）
ioDrive（80G）SSD（X25-E Extreme 64G）HDD（4玉RAID1+0）
674.792 200.415 302.394

もうちょっとioDriveが行くかなと思いましたが、それでも十分早いですね。

書込み性能
次に書込み性能　ddを1Gbyteで各10回取得しました。
ioDrive(80G)
[1] 1073741824 bytes (1.1 GB) copied, 1.16875 seconds, 919 MB/s
[2] 1073741824 bytes (1.1 GB) copied, 1.19023 seconds, 902 MB/s
[3] 1073741824 bytes (1.1 GB) copied, 1.19167 seconds, 901 MB/s
[4] 1073741824 bytes (1.1 GB) copied, 1.49715 seconds, 717 MB/s
[5] 1073741824 bytes (1.1 GB) copied, 1.45808 seconds, 736 MB/s
[6] 1073741824 bytes (1.1 GB) copied, 1.4612 seconds, 735 MB/s
[7] 1073741824 bytes (1.1 GB) copied, 1.42681 seconds, 753 MB/s
[8] 1073741824 bytes (1.1 GB) copied, 1.46143 seconds, 735 MB/s
[9] 1073741824 bytes (1.1 GB) copied, 1.46766 seconds, 732 MB/s
[10] 1073741824 bytes (1.1 GB) copied, 1.46032 seconds, 735 MB/s
SSD（X25-E Extreme 64G）
[1] 1073741824 bytes (1.1 GB) copied, 0.89152 seconds, 1.2 GB/s
[2] 1073741824 bytes (1.1 GB) copied, 1.64124 seconds, 654 MB/s
[3] 1073741824 bytes (1.1 GB) copied, 1.31282 seconds, 818 MB/s
[4] 1073741824 bytes (1.1 GB) copied, 1.39209 seconds, 771 MB/s
[5] 1073741824 bytes (1.1 GB) copied, 1.483 seconds, 724 MB/s
[6] 1073741824 bytes (1.1 GB) copied, 1.74863 seconds, 614 MB/s
[7] 1073741824 bytes (1.1 GB) copied, 2.06978 seconds, 519 MB/s
[8] 1073741824 bytes (1.1 GB) copied, 1.90353 seconds, 564 MB/s
[9] 1073741824 bytes (1.1 GB) copied, 1.41756 seconds, 757 MB/s
[10] 1073741824 bytes (1.1 GB) copied, 2.16436 seconds, 496 MB/s
HDD（4玉RAID1+0）
[1] 1073741824 bytes (1.1 GB) copied, 1.29015 seconds, 832 MB/s
[2] 1073741824 bytes (1.1 GB) copied, 1.31637 seconds, 816 MB/s
[3] 1073741824 bytes (1.1 GB) copied, 1.27612 seconds, 841 MB/s
[4] 1073741824 bytes (1.1 GB) copied, 1.42499 seconds, 754 MB/s
[5] 1073741824 bytes (1.1 GB) copied, 1.84404 seconds, 582 MB/s
[6] 1073741824 bytes (1.1 GB) copied, 1.92831 seconds, 557 MB/s
[7] 1073741824 bytes (1.1 GB) copied, 1.36545 seconds, 786 MB/s
[8] 1073741824 bytes (1.1 GB) copied, 1.28768 seconds, 834 MB/s
[9] 1073741824 bytes (1.1 GB) copied, 1.33781 seconds, 803 MB/s
[10] 1073741824 bytes (1.1 GB) copied, 1.29652 seconds, 828 MB/s
平均（MB/s）
ioDrive(80G)SSD（X25-E Extreme 64G）HDD（4玉RAID1+0）
786.5 711.7 763.3

シーケンシャルな書込みでは差が見受けられませんでした。

ランダムI/0（bonnie++）についてはパスしました。

次にmysqlをインストールし、「JdbcRunner - 汎用データベース負荷テストツール」を使用させて頂いて性能を取得しました。
結果の見方については下記の製作者：Sadao Hiratsukaさんのサイトを参照頂ければと思います。
http://hp.vector.co.jp/authors/VA052413/jdbcrunner/

テストシナリオはtpccで下記設定で各4回の結果を採用しました。
Warmup time          : 10 sec
Measurement time : 60 sec
Number of tx types : 5
Number of agents : 200
Connection pool size : 200
Statement cache size : 40
Auto commit : false
Sleep time : 0,0,0,0,0 msec

今回結果はTotal tx countを抜粋しました。
ioDrive(80G)
[Total tx count] 3495,3614,358,331,328 tx
[Total tx count] 3594,3577,375,368,354 tx
[Total tx count] 3717,3665,401,393,358 tx
[Total tx count] 3509,3459,361,369,355 tx
SSD（X25-E Extreme 64G）
[Total tx count] 1524,1472,137,146,152 tx
[Total tx count] 1591,1558,182,163,144 tx
[Total tx count] 1619,1461,147,175,133 tx
[Total tx count] 1532,1357,122,172,158 tx
HDD（4玉RAID1+0）
[Total tx count] 2889,2772,276,282,282 tx
[Total tx count] 2785,2822,283,284,282 tx
[Total tx count] 2866,2818,266,274,280 tx
[Total tx count] 2735,2700,283,300,249 tx

こう見るとHDD（4玉RAID1+0）も結構がんばってるな～と見えるんですが、下記はjdbcrunnerで検証中のmpstatの抜粋です。（文字数の都合でSSD（X25-E Extreme 64G）は省略しました。）

ioDrive(80G)
18時34分42秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分43秒 all 13.23 0.00 2.66 3.99 0.25 1.00 0.00 78.87 19248.51
18時34分43秒 0 7.92 0.00 0.99 2.97 0.00 0.00 0.00 88.12 992.08
18時34分43秒 1 19.80 0.00 2.97 4.95 0.00 0.99 0.00 71.29 740.59
18時34分43秒 2 14.00 0.00 2.00 5.00 0.00 1.00 0.00 78.00 704.95
18時34分43秒 3 17.82 0.00 1.98 5.94 0.00 1.98 0.00 72.28 786.14
18時34分43秒 4 17.00 0.00 7.00 3.00 2.00 3.00 0.00 68.00 11339.60
18時34分43秒 5 4.90 0.00 2.94 1.96 0.98 0.98 0.00 88.24 2635.64
18時34分43秒 6 4.00 0.00 0.00 1.00 0.00 0.00 0.00 95.00 0.00
18時34分43秒 7 16.16 0.00 2.02 4.04 0.00 1.01 0.00 76.77 626.73
18時34分43秒 8 10.89 0.00 2.97 3.96 0.00 0.99 0.00 81.19 350.50
18時34分43秒 9 15.15 0.00 1.01 4.04 0.00 0.00 0.00 79.80 0.99
18時34分43秒 10 28.71 0.00 4.95 8.91 0.00 1.98 0.00 55.45 1074.26
18時34分43秒 11 3.96 0.00 3.96 2.97 0.00 0.00 0.00 89.11 0.00

18時34分43秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分44秒 all 12.24 0.00 3.08 4.08 0.25 0.83 0.00 79.52 19355.00
18時34分44秒 0 4.00 0.00 1.00 2.00 0.00 0.00 0.00 93.00 1001.00
18時34分44秒 1 18.81 0.00 2.97 5.94 0.00 0.00 0.00 72.28 675.00
18時34分44秒 2 12.87 0.00 2.97 4.95 0.00 0.99 0.00 78.22 578.00
18時34分44秒 3 14.14 0.00 2.02 4.04 0.00 0.00 0.00 79.80 610.00
18時34分44秒 4 13.86 0.00 7.92 2.97 1.98 3.96 0.00 69.31 11852.00
18時34分44秒 5 4.00 0.00 5.00 3.00 0.00 0.00 0.00 88.00 2403.00
18時34分44秒 6 0.99 0.00 0.99 1.98 0.00 0.00 0.00 96.04 0.00
18時34分44秒 7 17.82 0.00 1.98 5.94 0.99 0.99 0.00 72.28 537.00
18時34分44秒 8 17.00 0.00 2.00 6.00 0.00 1.00 0.00 74.00 580.00
18時34分44秒 9 14.71 0.00 2.94 2.94 0.00 0.98 0.00 78.43 409.00
18時34分44秒 10 25.25 0.00 5.05 10.10 0.00 1.01 0.00 58.59 706.00
18時34分44秒 11 3.03 0.00 2.02 2.02 0.00 0.00 0.00 92.93 0.00

18時34分44秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分45秒 all 13.41 0.00 3.25 4.83 0.25 0.67 0.00 77.60 21473.00
18時34分45秒 0 2.00 0.00 0.00 4.00 0.00 0.00 0.00 94.00 1001.00
18時34分45秒 1 16.00 0.00 2.00 7.00 0.00 1.00 0.00 74.00 514.00
18時34分45秒 2 19.19 0.00 4.04 7.07 0.00 1.01 0.00 68.69 764.00
18時34分45秒 3 18.81 0.00 1.98 3.96 0.00 0.99 0.00 74.26 543.00
18時34分45秒 4 10.00 0.00 9.00 3.00 3.00 3.00 0.00 72.00 13967.00
18時34分45秒 5 3.00 0.00 5.00 1.00 0.00 0.00 0.00 91.00 2407.00
18時34分45秒 6 4.04 0.00 0.00 3.03 0.00 0.00 0.00 92.93 0.00
18時34分45秒 7 19.19 0.00 2.02 5.05 0.00 0.00 0.00 73.74 539.00
18時34分45秒 8 15.00 0.00 2.00 6.00 0.00 1.00 0.00 76.00 493.00
18時34分45秒 9 19.19 0.00 2.02 5.05 0.00 1.01 0.00 72.73 623.00
18時34分45秒 10 31.31 0.00 4.04 7.07 0.00 1.01 0.00 56.57 629.00
18時34分45秒 11 4.00 0.00 5.00 4.00 0.00 0.00 0.00 87.00 0.00

18時34分45秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分46秒 all 12.90 0.00 3.00 4.49 0.25 0.75 0.00 78.62 20980.81
18時34分46秒 0 11.11 0.00 0.00 0.00 0.00 0.00 0.00 88.89 1011.11
18時34分46秒 1 16.00 0.00 3.00 7.00 0.00 0.00 0.00 74.00 776.77
18時34分46秒 2 15.15 0.00 2.02 7.07 0.00 1.01 0.00 74.75 632.32
18時34分46秒 3 15.15 0.00 2.02 6.06 0.00 1.01 0.00 75.76 741.41
18時34分46秒 4 10.00 0.00 8.00 2.00 2.00 3.00 0.00 75.00 12580.81
18時34分46秒 5 4.00 0.00 2.00 1.00 0.00 1.00 0.00 92.00 2574.75
18時34分46秒 6 6.00 0.00 1.00 2.00 0.00 0.00 0.00 91.00 0.00
18時34分46秒 7 16.67 0.00 3.92 6.86 0.00 0.98 0.00 71.57 572.73
18時34分46秒 8 20.00 0.00 3.00 5.00 0.00 1.00 0.00 71.00 754.55
18時34分46秒 9 16.00 0.00 3.00 6.00 0.00 0.00 0.00 75.00 788.89
18時34分46秒 10 21.57 0.00 4.90 9.80 0.00 0.98 0.00 62.75 540.40
18時34分46秒 11 2.97 0.00 3.96 0.99 0.00 0.00 0.00 92.08 0.00
HDD（4玉RAID1+0）
16時34分27秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分28秒 all 4.58 0.00 1.00 30.62 0.08 1.16 0.00 62.56 7223.76
16時34分28秒 0 0.99 0.00 0.00 2.97 0.00 0.00 0.00 96.04 991.09
16時34分28秒 1 5.94 0.00 0.99 41.58 0.00 1.98 0.00 49.50 449.50
16時34分28秒 2 0.00 0.00 0.99 0.99 0.00 0.00 0.00 98.02 0.00
16時34分28秒 3 6.00 0.00 1.00 54.00 1.00 1.00 0.00 37.00 455.45
16時34分28秒 4 6.93 0.00 1.98 51.49 0.00 0.99 0.00 38.61 434.65
16時34分28秒 5 7.00 0.00 1.00 40.00 0.00 2.00 0.00 50.00 455.45
16時34分28秒 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
16時34分28秒 7 3.00 0.00 1.00 26.00 0.00 1.00 0.00 69.00 230.69
16時34分28秒 8 1.00 0.00 0.00 4.00 0.00 0.00 0.00 95.00 1657.43
16時34分28秒 9 6.06 0.00 1.01 36.36 0.00 1.01 0.00 55.56 377.23
16時34分28秒 10 12.00 0.00 4.00 72.00 0.00 4.00 0.00 8.00 1692.08
16時34分28秒 11 6.93 0.00 1.98 36.63 0.00 1.98 0.00 52.48 481.19

16時34分28秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分29秒 all 4.50 0.00 1.00 43.30 0.00 1.08 0.00 50.12 7258.00
16時34分29秒 0 0.00 0.00 0.00 1.00 0.00 0.00 0.00 99.00 1001.00
16時34分29秒 1 3.96 0.00 0.99 64.36 0.00 0.00 0.00 30.69 268.00
16時34分29秒 2 0.00 0.00 0.00 4.00 0.00 0.00 0.00 96.00 0.00
16時34分29秒 3 6.93 0.00 0.99 62.38 0.00 1.98 0.00 27.72 461.00
16時34分29秒 4 6.12 0.00 2.04 78.57 0.00 1.02 0.00 12.24 459.00
16時34分29秒 5 7.00 0.00 1.00 69.00 0.00 2.00 0.00 21.00 626.00
16時34分29秒 6 0.00 0.00 0.00 4.00 0.00 0.00 0.00 96.00 0.00
16時34分29秒 7 4.95 0.00 0.99 53.47 0.00 1.98 0.00 38.61 351.00
16時34分29秒 8 0.00 0.00 0.00 4.00 0.00 1.00 0.00 95.00 1740.00
16時34分29秒 9 4.95 0.00 0.99 39.60 0.00 0.99 0.00 53.47 216.00
16時34分29秒 10 11.11 0.00 3.03 77.78 0.00 4.04 0.00 4.04 1719.00
16時34分29秒 11 9.00 0.00 1.00 64.00 0.00 1.00 0.00 25.00 417.00

16時34分29秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分30秒 all 4.00 0.00 0.75 45.80 0.00 1.00 0.00 48.46 6368.00
16時34分30秒 0 0.00 0.00 0.00 6.00 0.00 0.00 0.00 94.00 1001.00
16時34分30秒 1 3.00 0.00 1.00 56.00 0.00 1.00 0.00 39.00 253.00
16時34分30秒 2 1.00 0.00 0.00 3.00 0.00 0.00 0.00 96.00 0.00
16時34分30秒 3 6.12 0.00 1.02 52.04 0.00 1.02 0.00 39.80 383.00
16時34分30秒 4 5.88 0.00 0.98 78.43 0.00 2.94 0.00 11.76 473.00
16時34分30秒 5 8.91 0.00 2.97 73.27 0.00 1.98 0.00 12.87 1030.00
16時34分30秒 6 0.00 0.00 0.00 6.00 0.00 0.00 0.00 94.00 0.00
16時34分30秒 7 5.05 0.00 0.00 50.51 0.00 1.01 0.00 43.43 301.00
16時34分30秒 8 0.00 0.00 0.00 8.91 0.00 0.00 0.00 91.09 1528.00
16時34分30秒 9 4.04 0.00 0.00 59.60 0.00 0.00 0.00 36.36 306.00
16時34分30秒 10 7.77 0.00 1.94 71.84 0.00 2.91 0.00 15.53 731.00
16時34分30秒 11 6.00 0.00 1.00 80.00 0.00 2.00 0.00 11.00 365.00

16時34分30秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分31秒 all 4.41 0.00 1.08 40.85 0.00 1.00 0.00 52.66 7587.00
16時34分31秒 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1001.00
16時34分31秒 1 4.95 0.00 0.99 60.40 0.00 0.99 0.00 32.67 317.00
16時34分31秒 2 0.00 0.00 0.00 9.00 0.00 0.00 0.00 91.00 0.00
16時34分31秒 3 5.94 0.00 0.99 50.50 0.00 0.99 0.00 41.58 452.00
16時34分31秒 4 6.06 0.00 2.02 55.56 0.00 2.02 0.00 34.34 564.00
16時34分31秒 5 14.14 0.00 4.04 69.70 0.00 3.03 0.00 9.09 1712.00
16時34分31秒 6 0.00 0.00 0.00 14.14 0.00 0.00 0.00 85.86 0.00
16時34分31秒 7 4.95 0.00 0.99 53.47 0.00 0.99 0.00 39.60 456.00
16時34分31秒 8 0.00 0.00 0.00 2.02 0.00 0.00 0.00 97.98 1818.00
16時34分31秒 9 5.00 0.00 1.00 60.00 0.00 2.00 0.00 32.00 386.00
16時34分31秒 10 6.19 0.00 0.00 53.61 0.00 0.00 0.00 40.21 487.00
16時34分31秒 11 6.00 0.00 2.00 63.00 0.00 0.00 0.00 29.00 390.00
iowait 値を見る限り明らかにioDrive(80G)には余裕があるように見受けられます。
今回は時間切れでここまでとなりますが、ioDrive(80G)に関してはまだまだ余力があり、
チューニング次第で更に性能向上が見込めれるだけの様相は伺い知れました。
また触れる機会がありましたら次回はチューニングを行ってみて結果をBlogにあげてみたいと思います。
2010年08月10日17:40
カテゴリ
DB

DWHで類似度計算

 こんにちは、ECナビ　システム本部　システムソリューショングループの大竹(@tokosa)です。

ECナビではDWHのNetezzaを使用し、データマートを構築しております。

Netezzaに関しては、日本ネティーザのサイトをご覧下さい。

実際Netezzaの性能がどの位なのか？何が出来るのかをご紹介します。

Netezzaを使用して２つの集合の類似度を計る

２つの集合の類似度を計る方法としてTanimoto係数(Jaccard係数の拡張)があります。

式は下記の通り。

==================================================================
Tb = Nc/ (Na + Nb - Nc)
==================================================================
Tb：Tanimoto係数 （二値データ）
Na：Aのアイテム数 
Nb：Bのアイテム数 
Nc：共通集合

Tbは0～1の間で0に近ければ類似度が低く1に近づく程類似度が高くなります。

では、実際にDBで集計を行います。

テーブル構成

NETEZZA => d PUR_TEST
    Attribute    |          Type          | Modifier | Default Value 
-----------------+------------------------+----------+---------------
 USER_ID         | INTEGER                |          | 
 ITEM_ID         | INTEGER                |          | 
 DATE            | DATE                   |          | 
distribute key (USER_ID)
レコード数：50万
集計対象数(アイテム数)：1000

テンポラリーテーブルは作成せず、自己結合でSQLを実行させます。

１．Nc=アイテム共通集合をもとめるSQL
  select m.ITEM_ID as MY_ITEM,o.ITEM_ID as OTHER_ITEM ,COUNT(distinct(o.USER_ID)) as match
  from PUR_TEST m INNER JOIN PUR_TEST o on m.ITEM_ID != o.ITEM_ID
  and m.USER_ID =o.USER_ID 
  group by m.ITEM_ID,o.ITEM_ID

２．Naのアイテム数
  select item_id,count(distinct(user_id)) NA from pur_test group by item_id

3．Nbのアイテム数
   select item_id,count(distinct(user_id)) NB from pur_test group by item_id

4.1～3の結果をTanimoto係数の公式へ。

 select MY_ITEM,NA,OTHER_ITEM,NB,NC,trunc(cast(NC as dec)/(NA+NB-NC),2) as T from (
   select m.ITEM_ID as MY_ITEM,o.ITEM_ID as OTHER_ITEM ,COUNT(distinct(o.USER_ID)) as NC
   from PUR_TEST m INNER JOIN PUR_TEST o on m.ITEM_ID != o.ITEM_ID
   and m.USER_ID =o.USER_ID 
   group by m.ITEM_ID,o.ITEM_ID
 ) ma,
 (select ITEM_ID,count(distinct(user_id)) NA from pur_test group by item_id) na,
 (select ITEM_ID,count(distinct(user_id)) NB from pur_test group by item_id) nb 
 WHERE 
 ma.MY_ITEM =na.ITEM_ID AND
 ma.OTHER_ITEM =nb.ITEM_ID

単純に計算量はO(n＾2)です。

アイテム数が1000位なら一瞬です。

実行時間================
real    0m6.680s
user    0m0.156s
sys     0m0.031s

アイテム数が10000超えると。。

実行時間================
real    29m53.769s
user    1m10.785s
sys     0m3.777s

まとめ

実際にはテンポラリテーブルを作成するべきです、アイテム毎のアイテム数(Na ,Nb) は先に集計しておくと良いでしょう。

Netzzaは自己結合も弱いのでこちらは別にするべきです。

今回はNetezzaの性能計る為、こんなSQLを書いています。

Netezzaにもstddev(標準偏差)、stddev_pop(母標準偏差)、variance(式の分散)、var_pop(母分散)などなど

解析関数が用意されています。協調フィルタリングの相関係数法もできますね。

タグ ：
2010年06月30日14:34
カテゴリ
DB

MySQL InnoDBでのネクストキーロックの落とし穴

はじめまして、株式会社ECナビ システム本部 情報システムグループの三浦と申します。

私は主にデータベースの運用、管理を行っています。

ECナビでは様々なサービスを展開しています。そしてそれと同じ数と言っても良い程のデータベースが稼動しています。

リレーショナルデータベースがメインでサービスを支えていますが、それを補う形でキーバリューストア的なデータベースも多数存在しています。

メインで活躍しているリレーショナルデータベースは用途によりOracle、MySQL、Netezza等と多岐に渡っています。

今回はMySQL InnoDBで実装されているネクストキーロックの落とし穴をデッドロックと絡めて説明したいと思います。

評価環境のMySQLのバージョンは5.1.39、トランザクション分離レベルはデフォルトのREPEATABLE READ、InnoDB Pluginは未導入にて今回は行いました。

下記のテーブル、データにて実施します。

mysql> SHOW CREATE TABLE HOGE\G;
*************************** 1. row ***************************
       Table: HOGE
Create Table: CREATE TABLE `HOGE` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `COL_INT` int(11) NOT NULL,
  `COL_CHAR` char(1) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `COL_INT` (`COL_INT`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SELECT * FROM HOGE;
+----+---------+----------+
| ID | COL_INT | COL_CHAR |
+----+---------+----------+
|  1 |      35 | A        |
|  2 |      40 | B        |
|  3 |      20 | C        |
|  4 |      25 | D        |
|  5 |      50 | A        |
|  6 |      10 | B        |
|  7 |      45 | C        |
|  8 |      15 | D        |
|  9 |      30 | A        |
+----+---------+----------+
9 rows in set (0.00 sec)

テストケース１

テーブル「HOGE」に存在するレコードをDELETEした後に新規レコードをテーブル「HOGE」に INSERT

トランザクションAトランザクションB
mysql> DELETE FROM HOGE WHERE COL_INT = 25;
Query OK, 1 row affected (0.00 sec)		 
 mysql> DELETE FROM HOGE WHERE COL_INT = 45;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 44,'B');
WAIT状態		 
 mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 29,'C');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
トランザクションBがエラーとなったことを受け
Query OK, 1 row affected (7.51 sec)		 

テストケース２

テーブル「HOGE」に存在しないレコードをDELETEした後に新規レコードをテーブル「HOGE」に INSERT

トランザクションAトランザクションB
mysql> DELETE FROM HOGE WHERE COL_INT = 28;
Query OK, 1 row affected (0.00 sec)		 
 mysql> DELETE FROM HOGE WHERE COL_INT = 43;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 44,'B');
WAIT状態		 
 mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 29,'C');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
トランザクションBがエラーとなったことを受け
Query OK, 1 row affected (11.76 sec)		 

テストケース３

テストケース１と同じくテーブル「HOGE」に存在するレコードをDELETEした後に新規レコードをテーブル「HOGE」に INSERT

但し、COL_INTを非UNIQUEからUNIQUEに変更

mysql> SHOW CREATE TABLE HOGE\G;
*************************** 1. row ***************************
       Table: HOGE
Create Table: CREATE TABLE `HOGE` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `COL_INT` int(11) NOT NULL,
  `COL_CHAR` char(1) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `COL_INT` (`COL_INT`)　★UNIQUEに変更
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
トランザクションAトランザクションB
mysql> DELETE FROM HOGE WHERE COL_INT = 25;
Query OK, 1 row affected (0.00 sec)		 
 mysql> DELETE FROM HOGE WHERE COL_INT = 45;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 44,'B');
Query OK, 1 row affected (0.00 sec)		 
 mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 29,'C');
Query OK, 1 row affected (0.00 sec)
エラーとならず正常終了エラーとならず正常終了

まとめ

非UNIQUE、UNIQUE INDEXの違いだけでも動きが違いました。

また、トランザクション分離レベルがREAD COMMITTEDの場合ですとテストケース１、２共にトランザクション間で干渉は起こらず正常終了します。

このようにINDEXの種類やトランザクション分離レベル、今回は実施していませんがデータの並び等のその他の要素によっても動きは違ってきます。

今回のような単一行単位でのDELETE&INSERTのようなアプリケーションはどこにでもあると思います。

まずは複数のDML(Data Manipulation Language)でトランザクションを構成する場合にこういう動きがあることを念頭に置くことが大事だと思います。
タグ ：
記事検索
最新記事
第29回すくすくスクラム 〜スクラムプロジェクト逆計画ゲーム〜に会場提供しました
2011年の振り返りと2012年の抱負
エンジニアが参加して良かったデザインミニ塾
滅びの言葉をテストする
VOYAGE CUPのインターバル撮影動画を作ってみた
VOYAGE WiMAXボーイ(36才)をぷにょっとさせた
『リーンスタートアップについて平鍋さんに聞く会』を開催しました
「新卒エンジニアがこのさき生き残るには」
アジャイルプロセス協議会 第8回 西日本セミナーで基調講演してきました
グレートナビック風を動くようにした
月別アーカイブ
2012年01月
2011年12月
2011年11月
2011年10月
2011年09月
2011年08月
2011年06月
2011年04月
2011年02月
2011年01月
2010年12月
2010年11月
2010年10月
2010年09月
2010年08月
2010年07月
2010年06月
2010年05月
カテゴリ別アーカイブ
programming (8)
ネタ (14)
ガジェット (2)
performance (1)
tool (1)
お知らせ (13)
勉強会 (14)
PHP (7)
DB (3)
iPhoneアプリ (7)
自炊 (2)
R (5)
ad:tech Tokyo (1)
presentation (4)
ruby (4)
Rails (2)
イベント (4)
スマートフォン (2)
Project Management (1)
agile (2)
security (2)
color (1)
タグクラウド
QRコード
QRコード