- 締切済み
FreeBSD+PostgreSQLでありえないぐらい遅い（通常の30倍以上かかる）
こんにちは。 現在某KD○I系列のレンタルサーバー屋で専用サーバーを借りていますが PostgreSQLのデータINSERTの速度がありえないぐらい遅く サーバー屋にさいさん文句を言っていますが これが仕様ですといってらちがあきません。 どれぐらい遅いか？というと1万行のデータをphpPgAdminからインサートするのに4分近くかかります。 ちなみにうちで適当に作ったVMでの実行だと10秒程度、あまりにらちあかなくて頭着てロリポップで試してみても35秒でした （まぁロリポップはMySQLですが） なおデータが複雑だから、、、というわけではなく以下のようなシンプルな構成にしても同様です。 testid varchar(10) testvalue varchar(10) ネットワークやハードウェアの障害かとも思いましたが phpMyAdminからMySQLに流し込む分には5秒程度で終わりますし 特定のサーバーの問題かと思い ほかのサーバーでも試してもらいましたが結果は同じでした。 かなり文句を言って向こうのサポートと喧嘩状態になりながら 最終的に向こうからの回答は以下の感じで は？という感じです。 ********************（一部伏字） 検証結果にて判明致しましたが、仮想化環境ではディスク自体が 仮想化されている為か、INSERT の結果にはご指摘いただいたように非常に素早い結果を得ることが出来ました。これは、弊社にて別途ご提供中のVPS の＊＊＊＊＊で要したのは 5 秒前後という結果でございました。 しかしながら、仮想化ではない通常のサーバでは、Disk への書き込み部分が ボトルネックとなるようで、上位プランの筐体を利用しての動作検証でも、 数十秒程度の速度向上が得られた程度にて、著しい成果を得ることは、 出来ませんでした。 大変申し訳ございませんが、Postgre SQL の設定ではなくご提供しております オペレーティングシステムのファイルシステムを含めたデータ処理方法などに起因する動作結果となります為、弊社では本速度を仕様としてご案内させていただきます。 ******************** 1万件で4分が仕様だそうですので 50件インサートするのに約1秒かかり 現在構築中のアンケートシステムでは一人50問程度使うので 10人同時にアクセスしたら最大10秒待たされるわけで とても使い物になりません。 年間100万以上の予算を組んだサーバーでまさかロリポップにも劣る速度になるとはホント驚きです。 ルート権限もなくSSHでログインすることもできないのでログもろくに見られない状態なのですが 上記のような現象に心当たりがあったり解決方法をご存知の方おられませんでしょうか？ 本来であれば別のサーバー屋と契約したいところなのですが OPENの日時考えると他のサーバー屋で再契約するには社内の稟議の問題もあり難しいです。 なんで客である自分がこんなこと調べないといけないんだーと かなり憤ってますが そうはいってもサーバー屋が原因であれプロジェクト失敗すれば私の心証も悪くなってしまいますし なんとか避けたいです。 何か心当たりがあれば教えてください。 よろしくお願いします。
- yukitakao
- お礼率29% (58/197)
- PostgreSQL
- 回答数4
- ありがとう数7
- みんなの回答 （4）
- 専門家の回答
みんなの回答
- tom233
- ベストアンサー率17% (61/352)
>レンタルサーバー屋で専用サーバーを借りていますが >仮想化環境ではディスク自体が レンタルしているのは専用サーバか? それともVPSか? それとも専用サーバをレンタルしてその中で質問者が VPSを構築しているのか?
- t-okura
- ベストアンサー率75% (253/335)
PostgreSQL データベースで大量データの登録・削除を行う場合、 適時 vacuum や vacuum full しないと極端に遅くなりますよ。 動作検証のため、大量データの登録・削除を繰り返している ようでしたら、一度 vacuum full してみてはいかがでしょうか。
- JACK_TOSHI
- ベストアンサー率63% (12/19)
こんにちは、 僕は、pg_dumpのインサートに下記で解決しました。 ※ 余談ですが、pg_dumpのコピーコマンドでは遅延は発生しません。 postgresqlを停止して、fsyncを on → off 変更後postgresqlを起動する。 インサート完了後、off → on にしてpostgresqlを再起動して使用しています。
お礼
情報ありがとうございます。 ただ今回はPostgresに初期データを流し込むことではなく 動的にデータを登録するアンケートシステムの登録速度が問題なのでこの方法での対処は難しそうです。 しかし このファイルへの書き込みも遅い原因の一因なのは間違いないですが 上の1万行のSQL文って全部で500kbぐらいなんですよね。 いくら1万回にわけて書き込んでいるとは言え 500kb分のデータ書き込みにそこまでかかる、ってのも何か納得いかないものがあります。。。 SQLの文法除いた 純データは1行10バイト以内だし 1万行でも100kb インデックス作ったってせいぜい倍の200kbぐらいだろうし・・・
- qbr2
- ベストアンサー率50% (62/123)
ありえるとすれば、このあたりでしょうか。 http://www.fiberbit.net/user/kunyami/postgresql/parameter/wal/wal_sync_method.html phpPgAdminからの更新であれば、おそらく１行毎にコミットされているので、 毎回ディスクへの書き込みを実行していると遅くなるかもしれません。 同じ設定でも、仮想環境の場合はハードディスクへの書き込み自体を キャッシュしておいてディスク自体には遅延書き込みを行う場合があるので、 パフォーマンスが圧倒的に違う というのも納得できます。
お礼
ありがとうございます。 なるほど。 設定がどうかは確認できませんが 確かに作成したプログラムもトランザクションありで実行すると心なしか実行速度があがったように思われます。 高アクセスが予想され 最悪サーバーダウンなどで完了しなくても一部だけでも登録できれれば、と今回はあえてトランザクションは使わずに実装したのですが そういうことならトランザクション使う方法での実行も検討してみる価値がありますね。 ただトランザクション使っても心なし早くはなるけれど たかが50件のインサートに0.5秒ぐらいはかかってるので 改善は見られるものの根本的な解決をはかるには サーバー側に設定を変えてもらうよう検討してもらうのが一番ですが・・・ 情報ありがとうございました
関連するQ&A
- postgresqlサーバーのチューニングについて
現在、Access2010＋Postgresqlにてシステムを構築しており ADOにてデータベースからレコードの検索・追加・更新を行っております。 今回質問させて頂きましたのはpostgresqlのチューニング方法についてです。 現在、あるテーブルにWANからVPNを経由して接続した状態でSELECTを行うと、 結果が帰ってくるまでにおよそ6分程度かかるテーブルがあります。 （LAN内では4秒程度） 抽出するフィールドを*ではなく、1フィールドに限定しても約4分程度かかります。 そのテーブルには約30000件ほどレコードが格納されており SELECTする際は、bool型のフィールドをWHERE条件に入れています。 例）SELECT * FROM tbl_test WHERE f条件 = TRUE; しかし同じテーブルの別のvarchar型フィールドにLIKE演算子で検索を かけると、すぐ（0.44秒）に結果が帰ってきます。 例）SELECT * FROM tbl_test WHERE 名称 LIKE '%テスト%'; 尚、この場合の結果は20件ほどですので、結果が早いのは当然ですが なぜbool型を条件にした場合に6分もかかってしまうのでしょうか。 システムの都合上、WANからVPNを経由してアクセスする事が必須ですので LAN内の速度が速くてもWANからの速度が遅いのはNGなのです。 一応、Web上で紹介されている一般的なチューニング方法を参考にし postgresql.confの設定値は下記の通りに行いました。 ・shared_buffers → 1024MB ・max_connections → 100 ・effective_cache_size → 512MB ・random_page_count → 2 ・work_mem → 3MB 恐らく根本的にどこかの設定や設計がまずい為、このような結果に なっているのであろうと思うのですが、それを特定するに至りません。 見直すべき点やアドバイスなどがあれば教えて下さい。 ＜サーバースペック＞ OS：WindowsXP Pro SP3 メモリ：3GB CPU：Pen4 2.8GHz Postgresqlバージョン：8.3
- ベストアンサー
- PostgreSQL
- Access97からAccess2003に移行したときの問題点
VB4→VB6へ、SQLサーバ7→2000へ、Access97→2003へ移行を行っています。 旧システムでは、約5秒で完了する処理が、移行後は、2分51秒かかります。 どのような処理かというと、 1.SQLサーバのデータをSELECT 2.AccessのテーブルにINSERT（もしくは、UPDATE) 3.Accessのクエリ実行 4.Accessのレポート表示 です。 新旧比較してみたのですが、1.SQLサーバのデータのSelectは、差はありません。 2.AccessのテーブルにInsertでは、 Access97は、1秒、2003は、45秒です。 Updateとなると、Access97では、4秒、2003は、2分5秒となりました。 新システムの方が、遅くて性能が悪いようです。 どのように、チューニングしたらよいでしょうか？ また、原因等、ご存知でしたら、教えていただけないでしょうか？ よろしくお願いします。
- ベストアンサー
- その他(データベース)
- SQLの型について
ASP+SQL初心者、hiroと申します。 ASPからデータベースに 1'56.123 のような計測タイムなどを INSERTする場合、データベースの型は何にしたらよろしいのでしょうか？ 今の所、varchar型で試しているのですが、「型が一致しません」と なってしまいます。 char型にCONVERTしても、うまくいきませんでした（結果は同じ）。 以前は、分・秒・1/1000 のように3分割してINSERTしていましたが、 できれば、今回は１つでINSERTしたいと思っています。 よろしくお願いします。
- ベストアンサー
- Microsoft ASP
- インデックスを張るべき項目について
20万件レコードのあるテーブルに、インデックスを張ると INSERTが遅くなるので、WHERE句で検索する項目のどれに インデックスを張るか悩んでいます。 インデックスはパターンが多い程、張った場合に 検索速度が向上すると理解しているのですが正しいでしょうか？ であれば、下記１．だけは貼ろうと思っているのですが・・ １．カラムに入るデータが殆どバラバラのVARCHAR(30) ２．カラムに入るデータは10万パターンのINT型 ３．カラムに入るデータは1万パターンのINT型 ４．カラムに入るデータはdatetime型 インデックスを張る事でINSERT速度が何％ぐらい下がるでしょうか？ よろしくお願いします。
- ベストアンサー
- MySQL
- textareaタグに入力された改行コードの扱い
PHPとMySQLを利用して簡単なWEBアプリを作っています。 <textarea>内に入力された文字列をMySQLにINSERTする際、使用しているブラウザによって改行コードの文字数が異なるため、MySQLにINSERTされる文字数が異なってしまいます。具体的にいうと、 --MySQL仕様-- <textarea>内のデータを、MySQLのとあるtableにINSERTする。 そのtableの<textarea>のデータを登録するcolumnにvarchar(1000)を指定している。 --PHP仕様-- <textarea>内のデータをPOSTより受け取り、其の生データを上記設定したcolumnにINSERTする。 （文字数チェックの際、\r\nを\nに変換し、改行コードを1文字として解釈させ、カウントしている。） そのデータを訪問者が閲覧できるようにpreg_replaceで改行コードを<br />に変換し表示している。 INSERTされたデータは再編集できるよう、編集ページを設けている。 ◆問題 ブラウザごとで<textarea>内の改行コードが異なるため、文字数カウントの際、\n、\r、\r\nによって文字数にズレが生じてしまい、その結果varchar(1000)で設定したcolumnでは使用したブラウザによってINSERT可能な文字数が異なってしまう。 ◆質問 ブラウザによって出力される改行コードが異なるため、MySQLのtableにINSERTされる文字数が異なってしまいます。改行コードを1文字と解釈し、ブラウザによる差異をなくす良い方法はありませんか？ --自身で考えた方法-- ・varchar(1000)を例えばvarchar(1200)などとし、PHP側のみで文字数の制限を掛ける。 ・一部のサイトでは\r\nを\nに変換し、INSERTするって方法が記載ありました。この方法を選択すると、再編集のページでブラウザを判別し、\nを\r\nに変換する手間があるので仕様変更に手間がかかりそう。 以上となります。独学の自分には「自身で考えた方法」しか思い浮かばず、MｙSQLに登録する際、ブラウザによる改行コードの文字数の差異の良い方法が浮かびませんでした。何か良い方法をご教授いただけませんでしょうか？宜しくお願いします。
- ベストアンサー
- MySQL
- PostgreSQLでVACUUMを実行したのですが・・
こんにちは、よろしくお願いします。 PostgreSQLでVACUUMを実行しましたところ なぜか逆にDBが重くなってしまいました。 VACUUM前は７秒程度の処理が、今は４０秒ぐらいかかります。 どういった原因が考えられますでしょうか・・？ RedHatLinux8.0+Apache1.3+PHP4.2.3+PostgreSQL7.2.2で 同じDB、同じプログラムのシステム環境を２つ作成しています（本番とテスト） データは同じではないのですが、件数はほとんど同じで 処理の速度も本番とテストでは変わらない状態でした。 この状態でテスト環境にVACUUMを行ったところ （vacuumdb -d test_db -z) なぜか処理が遅くなってしまいました・・。 試しに本番の方もVACUUMを行ってみたところ こちらは正常？にできたみたいで 処理時間が遅くなるということはありませんでした。 両方のDBに相違があるのではと思い DBダンプを見比べたのですが、違いはありませんでした。 あと試しに再びVACUUMをやってみましたが 状況は変わっていません・・。 アドバイスお願い致します。
- 締切済み
- その他(データベース)
- PostgreSQLのテーブルサイズ
LinuxでPostgreSQL 8.1.11を運用しています。24時間運用なので、VACUUM FULLはせずに毎日深夜にVACUUM ANALYZEを実行しています。 insert,update,deleteが頻繁に起こるシステムです。 VACUUM FULLを実行した場合、どの程度領域を回収できるのかを見積もろうとしています。 現在のデータサイズとOS上の使用容量の差分がわかれば、VACUUM FULLで回収できる領域が見積もれると考えているのですが、データサイズの確認方法がわかりません。 次のような計算でしかわからないでしょうか？ (1行のサイズ×行数)×全テーブル 上のような計算の場合でも、textのような場合は、サイズが行ごとに異なりそうな気がします。データサイズを確認する方法はありませんでしょうか？
- 締切済み
- PostgreSQL
- SQL SERVERの BULK INSERT
SQL SERVERの BULK INSERTについて。 TESTというテーブルにtest.csvをBULK INSERTによりデータをインポートしようとしているのですが変数を使わずに下記のように書くと正常に動くのですが、 BULK INSERT TEST FROM 'C:\data\test.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) 変数を使って下記のようにすると「不適切な構文があります。」とエラーとなってしまいます。 DECLARE @data varchar(100) SET @data = 'C:\data\test.csv' BULK INSERT TEST FROM @data WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) 解決策を教えてください。
- ベストアンサー
- SQL Server
- （レンタルサーバ）大量データで500エラー
初めて投稿させていただきます。 現在、勉強もかねてロリポップというレンタルサーバーで PHPのプログラムを作成しております。 プログラムの概要は、以下の通りです。 １）CSVファイルを[テーブルA（MｙSQL）]へinsert。 ２）[テーブルA（MｙSQL）]から対象レコードをselectし、 処理１を実行した結果を[テーブルB]へinsert。 ３）[テーブルA]と[テーブルB]と結合したテーブルから対象レコードをselectし、処理2を実行した結果を[テーブルC]へinsert。 上記プログラムを実行する際に、以下の事象が発生し、困っております。 ・CSVファイルの内容が300行だと正常に処理が終了する。 ・CSVファイルの内容が400行だと、「500 internal server error」が発生する。 実行環境は以下の通りです。 ・ロリポップサーバーのロリポプラン （ディスク容量：50GB） 「500 internal server error」について検索すると「パーミッションの設定」が関係するようなので 推奨値である「600」やデフォルトである「644」の両方で実行いたしましたが どちらも同じ結果でした。 また、ｔｒｙ～catchで各処理をかこっていますが、PHPのエラーは出ていません。 CSVファイルの行数を300から400に増やすと起こるので データ量が関係するのではないか、と考えております。 プログラムのエラー（不具合）か、否かを確認する方法はありますでしょうか？ また、データ量が問題の場合、どのような回避策がありますでしょうか？ CSVファイルを300行までしか読み込まないような制約を付けることも考えましたが プログラム的に可能なロジックがあれば、ご教示いただきたく思います。 ロリポップサーバのプランを上げることも考えましたが それでも、ファイルの読み込みには上限があるでのはないかと考えております。 何卒、よろしくお願いいたします。
- ベストアンサー
- PHP
- ｂｃｐインサートでのフォーマットファイルの書き方
bcpコマンドでインサートをしたいと考えています。 テーブルの形式は 1 datetime 日時 2 filed_1 varchar(10) 3 filed_2 varchar(10) 4 filed_3 varchar(10) です。 フォーマットファイルに 8.0 4 1 SQLDATETIME 0 8 "," 1 日時 "" 2 SQLCHAR 0 3 "," 2 filed_1 "" 3 SQLCHAR 0 3 "," 3 filed_2 "" 4 SQLCHAR 0 3 "\r\n" 4 filed_3 "" 書きました。 データは、 2009/04/01 0:00:00,11.0,11.0,11.0 2009/04/01 0:01:00,11.0,11.0,11.0 です。 SQLServerは2005のExpressです。 実行すると コピーを開始中です... SQLState = 37000, NativeError = 7339 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]リンク サーバー '(null)' の OLE DB プロバイダ 'STREAM' から、列 '[!BulkInsert].日時' に無効なデータが返さ れました。 BCP コピー in が失敗しました が返されます。 成功させるにはどこを直せばよいでしょうか。 よろしくお願いします。
- ベストアンサー
- SQL Server
お礼
ありがとうございます。 新規契約したばかりのサーバーなのでバキュームするまでもない、と思っていて試していなかったので 試しにかけてみました。 が、残念ながら数分かわる状況は変わらず、でした、 ありがとうございました