Upgrade to Pro — share decks privately, control downloads, hide ads and more …

images_with_mysql_merit_demerit_and_troubleshooting

mamy1326_2
October 25, 2017

 images_with_mysql_merit_demerit_and_troubleshooting

PHPカンファレンス 2017で話す予定だった「MySQLで画像を扱うメリット・デメリットと特殊パターン」を改題し、「MySQLで画像 を扱うメリット・デメリットと障害・解決事例」として、第119回 PHP勉強会@東京で登壇した際のスライドです。

mamy1326_2

October 25, 2017
Tweet

Other Decks in Programming

Transcript

  1. ࣗݾ঺հ Name ɿ·Έ΍ͳ͓͖ ॴଐɹ ɿInnovator Japan Inc. Twitterɿ@mamy1326 - ීஈ͸PHPॻ͍ͯ·͢

    - 2017೥1݄ʹॳΊͯmy.cnfΛ৮Δ - ࠓ೥1೥ MySQLΛझຯʹͯ͠ΈΔ
  2. mysql> show create table bukken\G *************************** 1. row *************************** Table:

    bukken Create Table: CREATE TABLE `bukken` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `gaikan_01` mediumblob NOT NULL, `gaikan_02` mediumblob, `gaikan_03` mediumblob, `gaikan_04` mediumblob, `gaikan_05` mediumblob, `gaikan_06` mediumblob, `gaikan_07` mediumblob, `gaikan_08` mediumblob, `gaikan_09` mediumblob, `gaikan_10` mediumblob, `madori_01` mediumblob NOT NULL, `naiso_01` mediumblob NOT NULL, `naiso_02` mediumblob, `naiso_03` mediumblob, `naiso_04` mediumblob, `naiso_05` mediumblob, `naiso_06` mediumblob, `naiso_07` mediumblob, `naiso_08` mediumblob, `naiso_09` mediumblob, `naiso_10` mediumblob, `syuhen_01` mediumblob NOT NULL, `syuhen_02` mediumblob, `syuhen_03` mediumblob, `syuhen_04` mediumblob, `syuhen_05` mediumblob, `syuhen_06` mediumblob, `syuhen_07` mediumblob, `syuhen_08` mediumblob, `syuhen_09` mediumblob, `syuhen_10` mediumblob, `panorama` mediumblob, `gaikan_s_01` mediumblob NOT NULL, `gaikan_s_02` mediumblob, `gaikan_s_03` mediumblob, `gaikan_s_04` mediumblob, `gaikan_s_05` mediumblob, `gaikan_s_06` mediumblob, `gaikan_s_07` mediumblob, `gaikan_s_08` mediumblob, `gaikan_s_09` mediumblob, `gaikan_s_10` mediumblob, `madori_s_01` mediumblob NOT NULL, `naiso_s_01` mediumblob NOT NULL, `naiso_s_02` mediumblob, `naiso_s_03` mediumblob, `naiso_s_04` mediumblob, `naiso_s_05` mediumblob, `naiso_s_06` mediumblob, `naiso_s_07` mediumblob, `naiso_s_08` mediumblob, `naiso_s_09` mediumblob, `naiso_s_10` mediumblob, `syuhen_s_01` mediumblob NOT NULL, `syuhen_s_02` mediumblob, `syuhen_s_03` mediumblob, `syuhen_s_04` mediumblob, `syuhen_s_05` mediumblob, `syuhen_s_06` mediumblob, `syuhen_s_07` mediumblob, `syuhen_s_08` mediumblob, `syuhen_s_09` mediumblob, `syuhen_s_10` mediumblob, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) ͑ʁ
  3. ςʔϒϧߏ੒  mysql> show create table bukken\G ****************** 1. row

    ****************** Table: bukken Create Table: CREATE TABLE `bukken` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, ⾣ʮͪΌΜͱʯ֬ೝͯ͠ΈΔ
  4. ςʔϒϧߏ੒  `gaikan_01` mediumblob NOT NULL, `gaikan_02` mediumblob, `gaikan_03` mediumblob,

    `gaikan_04` mediumblob, `gaikan_05` mediumblob, `gaikan_06` mediumblob, `gaikan_07` mediumblob, `gaikan_08` mediumblob, `gaikan_09` mediumblob, `gaikan_10` mediumblob,
  5. ςʔϒϧߏ੒  `madori_01` mediumblob NOT NULL, `naiso_01` mediumblob NOT NULL,

    `naiso_02` mediumblob, `naiso_03` mediumblob, `naiso_04` mediumblob, `naiso_05` mediumblob, `naiso_06` mediumblob, `naiso_07` mediumblob, `naiso_08` mediumblob, `naiso_09` mediumblob, `naiso_10` mediumblob,
  6. ςʔϒϧߏ੒  `syuhen_01` mediumblob NOT NULL, `syuhen_02` mediumblob, `syuhen_03` mediumblob,

    `syuhen_04` mediumblob, `syuhen_05` mediumblob, `syuhen_06` mediumblob, `syuhen_07` mediumblob, `syuhen_08` mediumblob, `syuhen_09` mediumblob, `syuhen_10` mediumblob, `panorama` mediumblob,
  7. ςʔϒϧߏ੒  `gaikan_s_01` mediumblob NOT NULL, `gaikan_s_02` mediumblob, `gaikan_s_03` mediumblob,

    `gaikan_s_04` mediumblob, `gaikan_s_05` mediumblob, `gaikan_s_06` mediumblob, `gaikan_s_07` mediumblob, `gaikan_s_08` mediumblob, `gaikan_s_09` mediumblob, `gaikan_s_10` mediumblob,
  8. ςʔϒϧߏ੒  `madori_s_01` mediumblob NOT NULL, `naiso_s_01` mediumblob NOT NULL,

    `naiso_s_02` mediumblob, `naiso_s_03` mediumblob, `naiso_s_04` mediumblob, `naiso_s_05` mediumblob, `naiso_s_06` mediumblob, `naiso_s_07` mediumblob, `naiso_s_08` mediumblob, `naiso_s_09` mediumblob, `naiso_s_10` mediumblob,
  9. ςʔϒϧߏ੒  `syuhen_s_01` mediumblob NOT NULL, `syuhen_s_02` mediumblob, `syuhen_s_03` mediumblob,

    `syuhen_s_04` mediumblob, `syuhen_s_05` mediumblob, `syuhen_s_06` mediumblob, `syuhen_s_07` mediumblob, `syuhen_s_08` mediumblob, `syuhen_s_09` mediumblob, `syuhen_s_10` mediumblob, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7687528 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
  10. ςʔϒϧߏ੒  `syuhen_s_01` mediumblob NOT NULL, `syuhen_s_02` mediumblob, `syuhen_s_03` mediumblob,

    `syuhen_s_04` mediumblob, `syuhen_s_05` mediumblob, `syuhen_s_06` mediumblob, `syuhen_s_07` mediumblob, `syuhen_s_08` mediumblob, `syuhen_s_09` mediumblob, `syuhen_s_10` mediumblob, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7687528 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) όΠφϦσʔλ อଘ༻ΧϥϜ
  11. ը૾Λ3%#ͷ৚݅ ⾣ը૾ $ ls -lh gaikan* -rw-r--r-- 1 vagrant vagrant

    612K 9݄ 18 17:53 gaikan_01.png -rw-r--r-- 1 vagrant vagrant 27K 9݄ 18 17:51 gaikan_s_01.jpg ⾣*/4&35৚݅ ɾϝΠϯը૾ɹɹɿ,#ຕ ɾαϜωΠϧը૾ɿ,#ຕ ɾϨίʔυ*/4&35
  12. ը૾Λ3%#ͷ݁Ռ $ php db_img.php ## insert image ..1..2..3..4..5..6..7..8..9..10 total :9.1205940247ඵ

    avarage:0.9120594025ඵ ⾣݁Ռ ɾϨίʔυॻ͖ࠐΈɿTFD ɾϨίʔυॻ͖ࠐΈɿTFD
  13. ϑΝΠϧ໊͚ͩ3%#ͷ৚݅ ⾣ը૾ $ pwd /var/www/html/img $ ls -lh ߹ܭ 640K

    -rw-r--r-- 1 apache apache 612K 10݄ 1 06:34 gaikan_01.png -rw-r--r-- 1 apache apache 27K 10݄ 1 06:34 gaikan_s_01.jpg ⾣*/4&35৚݅ ɾը૾͸ϑΝΠϧ໊ͷΈ ɾϨίʔυ*/4&35
  14. ϑΝΠϧ໊͚ͩ3%#ͷ݁Ռ $ php db_img_name.php ## insert image ..1..2..3..4..5..6..7..8..9..10 total :0.0100040436ඵ

    avarage:0.0010004044ඵ ⾣݁Ռ ɾϨίʔυॻ͖ࠐΈɿTFD ɾϨίʔυॻ͖ࠐΈɿTFD
  15. ࣮ࡍͷ༰ྔ ⾣ը૾Λ3%# # pwd /var/lib/mysql/mamy_test [root@localhost mamy_test]# ll bukken_*.ibd -rw-r-----

    1 mysql mysql 213909504 10݄ 1 06:17 bukken_img.ibd -rw-r----- 1 mysql mysql 98304 10݄ 1 06:58 bukken_name.ibd
  16. ࣮ࡍͷ༰ྔ ⾣ը૾Λ3%# # pwd /var/lib/mysql/mamy_test [root@localhost mamy_test]# ll bukken_*.ibd -rw-r-----

    1 mysql mysql 213909504 10݄ 1 06:17 bukken_img.ibd -rw-r----- 1 mysql mysql 98304 10݄ 1 06:58 bukken_name.ibd ࠩ͸ྺવ