がると申します。
以前伺ってたところでEC-CUBEを使っていたのですが(バージョンは1.4.…確か3かそこらだったと記憶しております)。
DBはMySQLを、Webサーバとは別立てで、それなりのパワーのモノを占有で使っていたのですが。

端的に言ってしまうと「何度かDBサーバが落ちました」。より正確には「コネクション数が設定の100を超えてしまい固まった」というのが正しいのですが。
結果的に、Webサーバ側からのDB connectが一切合切拒否られて、何度か、DBサーバのrestartの必要に駆られる事がありました。
数値は覚えてませんが…ユーザ数で万の一桁前半、アイテム数は万行かない程度だったように記憶してます…曖昧ですが。
そのあたりもあって私も色々見たのですが…正直、ちょっと如何なモノかと思われるようなSQLをものすごい量投げてました。

とりあえず直近には
・DBサーバのコネクション数の監視
・発行されているSQL文(特に時間がかかっているクエリ)のチェック
などが重要だと思うのですが…正直、もうちょっと基本設計を…と思わなくもありません。

とりあえず「そんな事もあったんだ」程度のニュアンスで書き込んでみます。

コネクション数は、通常同時接続ユーザ数と連動するので、サイト規模との兼ね合いもあるので難しいところですが…

1.x系は細切れSQLを投げていたのですかね?
最新版は、比較的コストの高い大物を投げているようです。
その大物が無駄に大きくて… 特に MySQL のは酷いですね。

コネクション数の問題は、同時接続の試験はやっていなかったので、気づかなかったです。
しかし、再起動云々という対処が可能となると、何か別の階層の予感もしますね。
コネクションを明示的に閉じなくて良いことになっているのに、実際には明示的に閉じないと駄目とかはありがちですから。

----------------
Seasoft
こちらでの投稿は、アイディア程度に留めさせていただいております。
個別案件の作業は有償で承っております。お気軽にご相談ください。

確かにそれは的を得ていると思います。
ただ、まぁ、数百とか１０００件以内の商品数なら、それでもそこそこには動いていますね。

できれば、ＤＢ設計から見直してもらえるとありがたいかなぁ、という気がしました。
前々から言ってるように、MySQLのほうは、かなりボーダーラインで動いてる気がします。

私の場合も、私の利用する予定のボリュームでは問題なさそうなんですよね。
特に PostgreSQL の方は、問題の度合いが軽そうだし。
なので、パフォーマンスチューニングからは一旦手を引きました。

ちなみに、DB設計で気になる点は、どのようなあたりでしょうか?
私はまだ、DBはあまり把握できていないのですが、気になるといえばPKが張られていないとかは嫌ですね。

----------------
Seasoft
こちらでの投稿は、アイディア程度に留めさせていただいております。
個別案件の作業は有償で承っております。お気軽にご相談ください。

そうですね。DB設計の改善には時間がかかると思いますので現段階のバージョンでパフォーマンスを上げたい場合（商品数1500以上）はnanasessさんの仰るとおり高速なハード等のインフラとpostgresqlは一つの選択肢でしょうね。

私もインフラの改善を行いたいと思っているのですが、費用面でなかなか先に進まず・・

がるです。

コネクション数に関してですが。私が見ていた限りでは
・ものすごく重いSQLを投げていたために、あるDB接続ハンドルにおいて「処理が終わらない」事象が発生
・上述が多数重なって(大抵この手のは、一度溜まり始めると一気に溜まります)、結果的にコネクション数を食いつぶした
感じでした。

個人的には。
テーブル設計もそうなのですが、それ以上に、投げるクエリがちょっとどんなもんかなぁ、と感じております。
大規模系開発の方とお話するときくらいしか頷いていただけないのですが。個人的には「原則としてjoin及び副次問い合わせ禁止」というスタンスなので。
…EC-CUBEが投げているSQLを見ている限りでは、ちょっとびっくりするくらいにネストしたクエリがあったりします。
正直、DBって、マシンスペックどんだけ上げてもクエリが重ければあんまり効果がでず、また、横に広げ出すともの凄いコストがかかりまくるので。個人的には「DBには出来るだけ仕事をさせない」設計が好みだったりはします。

まぁ開発スタンスは色々あってよいとは思うのですが。
個人的には「小規模を超えるものには向かないんじゃないかなぁ」と感じる次第です。

引用：

具体的に SQL を特定できれば, 多くの人が幸せになれるかもしれませんね.

EC-CUBE で構築した大規模事例をいくつか見ていますが, PV が多くなると, 商品一覧などの重い SQL よりもセッション処理が追いつかなくなる傾向が多いようでした.

dtb_session を UPDATE するだけの簡単な SQL ですが, ロードバランサで冗長化された WEB サーバーからの PV を捌き切るのは簡単ではないようです.

# dtb_session のみを別の DB で処理することで対応していました

引用：

複雑なクエリほど I/O を使うので, ATA な低速ディスクと SAS や Fibre Channel 接続用のディスクなどで, それぞれベンチマークをとってみてはいかがでしょう？

# 正直ではなくてすみません...

どの部分と言われると、もしかすると回答は「全体的に」となってしまいそうな気がします。

たとえば、カテゴリ一覧を拾う、などといったＳＱＬはあたりまえですがそんなに複雑なわけはありませんので、そこは突っ込まれても困るのですが。

すくなくとお、ＳＱＬ投げる直前でvar_dumpなどとやって見ていますが「すげぇな・・・これ」と感じるところが多々ございます。
すみません「ここです」と明確に言えません、あまいに多岐にわたっているので。

ただ、何度もいいますが小規模なら十分使えると認識しております。
できれば、MySQLは特例としての動作環境にとどめたほうがいいかもしれませんね。

がる様

コネクション数の件、理解できました。
しかし、それって PHP は手を離しているはずなので、(MySQL内のセッションが残るのは仕方が無いとして)MySQLもコネクションを開放してほしい気がしますが、そういうものではないのかなぁ…
その状況下では、コネクションを張れたところで、処理で詰まるので、根本的な解決にはなら無そうですが。


> テーブル設計もそうなのですが、それ以上に、投げるクエリがちょっとどんなもんかなぁ、と感じております。

大いに賛同します。


> 大規模系開発の方とお話するときくらいしか頷いていただけないのですが。個人的には「原則としてjoin及び副次問い合わせ禁止」というスタンスなので。

ここは逆ですね。

以前は私も結合・副問い合わせはほとんど使っていませんでした。それが、1000行を超えるSQL文をWEBからリアルタイムで投げる銀行の情報系システムを扱ってから考えが変わりました。負荷を掛けるならDBだと。

しかし、その辺って、商用DBとオープンソースのDBで、差がつくんですよね…

----------------
Seasoft
こちらでの投稿は、アイディア程度に留めさせていただいております。
個別案件の作業は有償で承っております。お気軽にご相談ください。

がるです。
なんか久しぶりに書き込み応酬しておりますｗ

まず、SQLの特定ですが。
「EC-CUBE使ってた現場から離れてしまった ＆ その現場でも使わなくなってしまった」ので、ちとわざわざ環境作り直してまで、という元気はないので資料がない、という状況なので、「これ」とは申し上げられないのですが。
ただ、印象値としては「一つ二つではなくて色々と困ったクエリが多発していた」ように記憶しています。

コネクション関連なのですが。
どうやら「あるconnectionから発行されたSQLが完了するまでは解放しない」ような挙動をしているように感じられました(まぁ結果かえさにゃいかんのでそうだろうとは思うのですが)。
やはり「重いクエリがいかん」とか、どうしても思ってしまうんですがどんなもんなんですかねぇ。

で。
> 複雑なクエリほど I/O を使うので
> 負荷を掛けるならDBだと。
についてはまぁ色々な見地があろうかと思うのですが。
個人的には「DBには負荷をかけちゃいけない 負荷かけるのはWebサーバ側」というスタンスを取る事が多いです。
端的に書くと。Webサーバをクラスタリング化しても、プログラムさえちゃんと考慮されていれば、３台にしたら１台の３倍程度のお値段で片付くのですが。
DBサーバをクラスタリング化し始めると、桁が２つ３つずれこんでくるので orz
# OracleとDB2でクラスタリングしたくて見積もり依頼出したときの、あまりの指数っぷりに本気でどん引きした記憶があります。
# １台横にのばすと、本気で０が一つ追加くらいの勢いでした orz

多分この辺って
> しかし、その辺って、商用DBとオープンソースのDBで、差がつくんですよね…
が非常に大きな部分だとは思うのですが…ただ、裏側の機構(データの整合性の取り方)を考えると、どうしてもそこのコストの高さが気になってしまって(苦笑
あと。OSSのDBMSでも「ミドルウェアとして有料のソフトを使う」なんて事も少なからず出てくる事を考えると…「Webサーバに負荷かけて横にのばしたほうが安いよねぇ」と考えてしまいます(笑

まぁぶっちゃけあっしは現在使ってないのでいいっちゃぁいいのですが。
願わくば、もう少しスケーラビリティのある設計とかプログラミングとかなされてればなぁ、とか思ってみなくもありません。