MySQL接続プールとそれを並列化に使用する方法

私が最近出会った興味深いトピックをすべての読者と共有したいのですが、それが気に入りました。 このトピックの開発は私を喜ばせ、貯金箱に少しの経験を追加しました。 ほとんどの場合、またはそうではないかもしれませんが、データベース接続のプールに出くわしました。 この興味深いオプションを読んだ後、記事を書いてそれをあなたと共有したいと思いました。 おそらくこの記事は少し長くなると思いますが、誰かがこの投稿を読むことに興味を持ち、このトピックに興味があると思います。 誰かがこの記事の経験をプロジェクトで使用しているのかもしれませんが、いずれにしても、それを書いてあなたに伝えるのは面白いでしょう。



フリーランスに従事。 どういうわけか、私の友人が自動車学校の所有者と一緒に私を連れてきて、そこで彼らは私のチケットのコレクションを形成するプログラムを作成するように私に頼みました。 彼のSDAチケットブックのようなもの。 すべての質問はMySQL DBMSの3つのテーブルに保存されます。1つの質問のコレクション、これらの質問の別の主題、および質問に対する最後の回答です。 その結果、800問の質問（回答あり）があります。 フォーメーションの最初のバージョンは、内部に質問が順次形成される従来の方法でした。 マルチスレッドプログラムの実行のトピックに非常に興味があるので、完全に機能するメソッドを作成した後、すべてをより便利にすると同時に、サンプリング速度を追加することにしました。



まず、SDAチケットのバージョンを作成するために、問題のトピックを考慮する必要があります。 また、トピックは12個のみです。 最初の8つのトピックは40の質問で構成され、残りの120は質問です。各トピックにはチケットの特定の番号があり、最初の8つのトピックはチケットの1つの質問に対応します。 生成された質問のバージョンを保存するために、辞書辞書が使用されます。各辞書には、特定のトピックに関する質問のリストが保存されます。 さらに、質問の順序は常に異なっている必要があります。 0から40までの数字のシーケンスを繰り返しなしで生成するメソッドが必要です。そのため、チケットとそれに応じた質問を選択することになります。 これらすべてを念頭に置いて、すべての質問を生成するための次のアルゴリズムを取得します。

• 持っているすべてのトピックを取得します。
• トピックから、各トピックの質問番号のリストを作成します。
• 乱数を生成します;
• 質問のリストと乱数のシーケンスに基づいて、選択を行い、主題に関する質問のリストを作成します。
• 辞書に各リストを入力します。

最初の2つのステップの実装と操作はロジックに依存し、私の意見では、サンプリングの要求に応じて1回実行できます。 3番目のステップは、質問をどれだけ異ならせたいか、各トピックの前に新世代を行うか、すべてのトピックに対して1回行うかによって異なります。 私は一度だけ生成することを選択しましたが、私の意見ではこれで十分です。 4番目のステップは最も頻繁に機能し、この場所ではサンプリング時に最も時間がかかるはずです。 最後のステップは簡単で、スキップしてください。

DB接続

データベースへの接続を検討してください。 これを行うには、接続文字列ConnectionおよびDataReaderを格納する抽象クラスを使用します。

public abstract class SqlBase { protected static readonly String Connect; protected readonly MySqlConnection SqlConnection; protected MySqlDataReader SqlDataReader; static SqlBase() { Connect = String.Format("Database={0};Data Source={1};User ID={2};Password={3};CharSet=utf8", Settings.Default.Database, Settings.Default.DataSource, Settings.Default.UserId, Settings.Default.Password); } protected SqlBase() { try { this.SqlConnection = new MySqlConnection(Connect); this.SqlConnection.Open(); } catch (Exception ex) { throw new Exception(ex.Message, ex); } } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

抽象クラスが好きなのは、便利な作業ロジックを自分で作成して隠すことができ、派生物が他の詳細に完全に集中し、他の詳細を心配せずに、基本クラスに責任を割り当てることができるからです。 この場合、派生クラスのインスタンスを作成すると、データベースを操作するために必要な条件が自動的に取得され、クラス自体のロジックを実装して質問のサンプルを作成することができます。

 public sealed class SqlPerceptionQuestions : SqlBase { public Dictionary<Int32, List<Question>> GetQuestion() { Generation(); GetTheme(); return Request(); } private Dictionary<Int32, List<Question>> Request() { var _collectionDictionary = new Dictionary<Int32, List<Question>>(); for(int ctr = 0; ctr < 12; ctr++) { using (var _questions = new SqlQuestions()) { if (ctr < 8) { _collectionDictionary[ctr] = _questions.GetQuestionSmall((Int16)ctr); } else { _collectionDictionary[ctr] = _questions.GetQuestionGreat((Int16)ctr); } } return _collectionDictionary; } private async void GetTheme() { //  } //       40  private List<Question> GetQuestionSmall(Int16 numTheme) { var _listQuestions = new List<Question>(); for (Int16 numCard = 0; numCard < 40; numCard++) { _listQuestions.Add(GetQuestion(numCard, numTheme)); } return _listQuestions; } //       120  private List<Question> GetQuestionGreat(Int16 numTheme) { var _listQuestions = new List<Question>(); for (Int16 numQuestion = 0; numQuestion < 3; numQuestion++) for (int numCard = 0; numCard < 40; numCard++) { _listQuestions.Add(GetQuestion(numQuestion, numTheme, numQuestion)); } return _listQuestions; } //          private Question GetQuestion(Int16 numCard, Int16 numTheme, Int16 numQuestion = 0) { //  } //    private List<String> GetResponse(Int32 questions_id) { //  } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは最もシンプルで最も同期したバージョンで、約2秒と200〜400ミリ秒で動作します。これにより、この間ずっとユーザーインターフェイスがブロックされます。 非常に最初の実装がかなりの時間（約6秒）働いたため、これは良い動作バージョンです。 改善後、約2秒しか出ませんでした。

非同期サンプリングバージョンの作成

すべてが正常であり、すべてがすでに機能していますが、本来あるべき状態ですか？ 結局のところ、コンソールアプリケーションではなく、同期メソッド（ブロッキング）があります。 正しく完全に機能するプログラムが必要です。これは0.5秒でもブロックされませんが、どのような負荷でも適切に機能します。 これを行うには、まず、GetQuestion（）メソッドを書き換えます。 TAP（Task-based Asynchronous Pattern）パターンに従って非同期にしましょう。 誰が気にかけているか、インターネットで読んでいるか、私が本当に好きなかなり良い本を持っています-このトピックが非常によく説明されているAlex DavisによるC＃5.0の非同期プログラミング、またはここを見てください 。 書き換えると、次のようになります。

 public async Task<Dictionary<Int32, List<Question>>> GetQuestionAsync() { return await Task.Factory.StartNew(() => { Generation(); GetTheme(); return Request(); }, TaskCreationOptions.LongRunning); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このメソッドで最も興味深いものを考えてみましょう：Task.Factory.StartNew（）。 バージョン.NET 4.5以降では、Task.Run（）のバージョンを使用できます。これは、作成時のパラメーターが少ない単純な宣言によって以前のものとは異なります。 本質的に、Task.Run（）はTask.Factory.StartNew（）よりも便利なシェルであり、非同期タスクの単純な作成に非常に適していますが、同時に制御の柔軟性がやや劣ります。 どのストリームが計算を実行するか、またはどのように計画されるかをより正確に制御する必要がある場合は、Task.Factory.StartNew（）を使用します。 興味があれば、 こちらをチェック この場合、TaskCreationOptions.LongRunningなどのパラメーターも指定したため、このオプションを使用しました。このパラメーターは、このタスクを長時間としてマークし、このワークアイテムが長期間実行され、他のワークアイテムをブロックする可能性があることを意味します。 このフラグは、過剰なサブスクリプションが予想されるTaskSchedulerの情報も提供します。これにより、使用可能なハードウェアスレッドの数よりも多くのスレッドを作成できます。 このパラメーターを使用すると、グローバルおよびローカルキューを含むThreadPoolを完全に回避できます。 「タスクスケジューラ」の詳細をご覧ください。



したがって、データ収集生成の非同期実行は既に取得されており、メインストリームはブロックされません。

サンプルの並列化

その後、並列化に進みます。これにより、データサンプリングの速度が向上します。 ただし、このためには、メインクラスを少しやり直し、基本クラスを変更し、ループの並列動作を確保するために別のクラスを追加して、異なるスレッドのデータ競合、クリティカルセクション、変数の問題を回避する必要があります。

最初に、 SqlPerceptionQuestionsクラスに新しいネストされたクラスSqlQuestionsを作成します。 質問と回答を取得するメソッドを転送し、SqlBaseから派生させます。外部クラスでは、それらを取得するためにメソッドを残し、一度だけ呼び出して、一連の数字を形成します。



SqlQuestionsクラスコード ：

  internal sealed class SqlQuestions : SqlBase { internal List<Question> GetQuestionSmall(Int16 numTheme) { var _listQuestions = new List<Question>(); for (Int16 numCard = 0; numCard < 40; numCard++) { _listQuestions.Add(GetQuestion(numCard, numTheme)); } return _listQuestions; } internal List<Question> GetQuestionGreat(Int16 numTheme) { var _listQuestions = new List<Question>(); for (Int16 numQuestion = 0; numQuestion < 3; numQuestion++) for (int numCard = 0; numCard < 40; numCard++) { _listQuestions.Add(GetQuestion(numQuestion, numTheme, numQuestion)); } return _listQuestions; } private Question GetQuestion(Int16 numCard, Int16 numTheme, Int16 numQuestion = 0) { //  } private List<String> GetResponse(Int32 questions_id) { //  } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ループを並列化するには、Parallel.For（）を使用します。 これは、データのロードを複数のストリームに整理するための非常に便利な方法です。 ただし、これには、1つのConnectionで1つのDataReaderを処理できるため、少なくとも複数のデータベース接続を作成する必要があるという事実も伴います。 .Request（）メソッドを書き換えます：

 private Dictionary<Int32, List<Question>> Request() { var _collectionDictionary = new Dictionary<Int32, List<Question>>(); var _po = new ParallelOptions { MaxDegreeOfParallelism = Environment.ProcessorCount }; Parallel.For(0, 12, _po, ctr => { using (var _questions = new SqlQuestions()) { if (ctr < 8) { _collectionDictionary[ctr] = _questions.GetQuestionSmall((Int16)ctr); } else { _collectionDictionary[ctr] = _questions.GetQuestionGreat((Int16)ctr); } } }); return _collectionDictionary; }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、接続を開いた後、それを閉じる必要があります。 さらに、これはすべてサイクルで行われます。 これをすべて実装するために、個別の派生クラスSqlQuestionsを作成することにしました。 接続を閉じるには、.Dispose（）を呼び出します。この中で、閉じるときに行う必要があることを書き留めます。 これを行うには、最初に基本クラスで.Dispose（）メソッドを宣言します。

  public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected abstract void Dispose(Boolean disposing);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、派生クラスで異なる方法で実装します。 なぜそう まず、データベース接続を作成して閉じた場合はどうなりますか？ 接続を開いてから閉じると、しばらくの間（約3分間）MySQL接続プールに配置されます。新しい接続を開くと、接続はプールから取得されます。つまり、再オープンのための時間とリソースは使用されません。 新しいメソッドを起動して、データベース接続を開くのにかかる時間を確認しましょう。このため、接続が開かれているコードの基本クラスStopwatchに挿入し、出力にあるものを確認します。 コード：

 protected SqlBase() { Stopwatch st = new Stopwatch(); st.Start(); try { this.SqlConnection = new MySqlConnection(Connect); this.SqlConnection.Open(); } catch (MySqlException ex) { throw new Exception(ex.Message, ex); } st.Stop(); Debug.WriteLine("    : " + st.Elapsed.Seconds.ToString() + "  " + st.Elapsed.Milliseconds.ToString() + " "); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初の接続は最も長く、 SqlPerceptionQuestionsクラスの接続を開きます。これは、メソッドの全期間にわたって開かれます。 後続の接続は、 SqlQuestionsクラスをインスタンス化するときにループ内で開かれた接続です。 私のコンピューターのプロセッサーの数を考慮に入れると、最大4サイクルで4つの接続が開かれます。 合計で5つの接続が最初に開かれ、サイクルではそれらが開いたり閉じたりします。 したがって、最初の5つの接続を開くには時間が必要です。その後、サイクル内で古い接続が閉じられ、新しい接続が開かれると、接続はプール内にあり、必要になるたびに単純に発行されるため、時間とリソースはかかりません。 このため、クラスのクリーンアップはわずかに異なる方法で実装されます。 SqlPerceptionQuestionsクラスでは、メソッドは次のようになります。

 protected override void Dispose(bool disposing) { if (!this.disposed) { if (SqlDataReader != null) { SqlDataReader.Close(); SqlDataReader.Dispose(); } SqlConnection.Close(); SqlConnection.Dispose(); MySqlConnection.ClearAllPools(); } disposed = true; }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SqlQuestionsクラスでも、MySqlConnection.ClearAllPools（）;の行を除きます。 結局のところ、このままにしておくと、次のような状況になります。





ご覧のように、スレッドプールを絶えずクリーニングすることにより、接続が常に開かれ、その後の結果が生じます。

MySql接続プール

この瞬間をより詳しく考えてみましょう。 Connector / Net MySqlは、接続プーリングをサポートして、使用率の高いアプリケーションデータベースのパフォーマンスとスケーラビリティを向上させます。 この機能はデフォルトで有効になっています。 接続文字列オプションを使用して、無効にするか、特性を変更できます。 接続文字列は、接続プールに関するオプションをサポートしています。

オプション名	価値による デフォルト	詳細
キャッシュサーバーのプロパティ、CacheServerProperties	オフ	プールからの接続が返されるたびに、いくつかのシステム変数（SHOW VARIABLES）のパラメーターを更新するかどうかを決定します。 このオプションを有効にすると、接続プールコンテキストへの接続が高速化されます。 アプリケーションには、他の接続によって行われた構成の変更は通知されません。 このオプションは、バージョンConnector / Net 6.3以降に追加されました。
接続ライフタイム、ConnectionLifeTime	0	接続がプールに返されると、その作成時間が現在の時間と比較され、ConnectionLifeTime値を超えると、この接続は破棄されます。 これは、運用サーバーとオンラインサーバーの間で負荷を分散するクラスター構成で役立ちます。 値がゼロ（0）の場合、プール内の接続は可能な限り最大の時間待機します。
接続リセット、ConnectionReset	偽	trueの場合、プールから取得されたときの接続状態がリセットされます。 デフォルト値を使用すると、サーバーが接続を取得するための追加の処理サイクルを回避できますが、接続ステータスはリセットされません。
最大プールサイズ、最大プールサイズ、MaximumPoolsize、maxpoolsize	100	プール内に存在できる接続の最大数。
最小プールサイズ、最小プールサイズ、MinimumPoolSize、minpoolsize	0	プールに入れられる化合物の最小数。
プーリング	本当	trueの場合、MySqlConnectionオブジェクトはプールから取得され、必要に応じて作成され、対応するプールに追加されます。 定義されている値は、true、false、yes、およびnoです。

これらのパラメーターを使用して、必要に応じてプールを管理できます。 例：

• 接続リセットは接続コンテキストをリセットし、デフォルトで使用されていない場合、プールから接続を受信する時間が少し長くなります。
• 最小プールサイズを使用すると、接続数が最小プールサイズの値以下の場合、必要に応じて、無期限に存在するプール内の接続数を設定できます。
• （このプログラムの例のように）スレッドのプールを絶えずクリアすると、プーリングを無効にしても同じ結果が得られます

デフォルトでは、MySQL Connector / Net 6.2以降、3分ごとに実行され、3分以上スタンバイ（使用されていない）であるプールから接続を削除するバックグラウンドジョブがあります。 プールをクリアすると、クライアント側とサーバー側の両方でリソースが解放されます。 これは、クライアント側では各接続がソケットを使用し、サーバー側では各接続がソケットとストリームを使用するためです。

並列呼び出しスタック

興味を引くために、たとえば.GetQuestion（）メソッドにブレークポイントを置いて、並列呼び出しスタックを見ると、次のようになります。





スクリーンショットからわかるように、中断されているスレッドの1つにあり、コールスタックから、このメソッドが質問の小さなコレクションをロードするメソッドから呼び出されたと判断します（40）。 その左側にはさらに3つのスレッドがあり、そのうち2つはコレクションに質問を追加するための行でこの時点で停止します。このスレッドは、小さな質問のコレクションも処理します。 そして最後のスレッド4は処理に従事しており、この時点で質問に対する回答を受け取ります。大規模なコレクション（120）からの質問に対してのみです。 これらの4つのスレッドはすべて並列ループで作成され、残りのループ反復スレッドとほぼ同時に動作します。 これらのスレッドは、プログラム内のスレッドの合計数に含まれ、そのうち8つは残りの4つがプログラムの他のタスクを解決します。

最後の仕上げ-例外処理

そして最後に、プログラムが機能するためには、例外処理が必要です。 突然、フィールドまたは他のパラメータがデータベースから変更されたか、プログラム自体で予期しないエラーが発生しました。 GetQuestionAsync（）メソッドを書き換えます：

 public async Task<Dictionary<Int32, List<Question>>> GetQuestionAsync() { return await Task.Factory.StartNew(() => { try { Generation(); GetTheme(); return Request(); } catch (AggregateException ex) { throw new AggregateException(ex.Message, ex.InnerExceptions); } catch (Exception ex) { throw new AggregateException(ex.Message, ex.InnerException); } }, TaskCreationOptions.LongRunning); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

AggregateException例外処理は、例外が発生した場合、Parallel.Forループがこのタイプのエラーを発生させるという事実に関連しているため、処理して呼び出し元に渡す必要があります。 並列ループがこの種のエラーを生成するのは論理的です。 この瞬間をより詳細に考えてみましょう。このため、.GetQuestion（）のSqlクエリを変更し、データベーステーブルに存在しないパラメーターの1つを故意に誤って示しました。 取得するもの：





さらに、デバッグを続行すると、合計でこの例外が4回発生しますが、これは非常に論理的です。 1つの理由に関連しているにもかかわらず、4つすべてを処理するには、何らかの方法でそれらを配置する必要があります。AggregateExceptionが適しています。



例外処理は、.GetTheme（）メソッドで例外が発生した場合、例外が1つあり、それもキャッチする必要があるという事実に関連しています。



呼び出しコードは次のとおりです。

 private async void Button_Click(object sender, RoutedEventArgs e) { Stopwatch st = new Stopwatch(); st.Start(); try { SqlQuest = new SqlPerceptionQuestions(); collectionQuest = await SqlQuest.GetQuestionAsync(); } catch (AggregateException ex) { ex.ShowError(); } catch(Exception ex) { ex.ShowError(); } finally { if (SqlQuest != null) SqlQuest.Dispose(); } st.Stop(); Debug.WriteLine("  : " + st.Elapsed.Seconds.ToString() + "  " + st.Elapsed.Milliseconds.ToString() + " "); Debugger.Break(); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最後に...

一般に、並列バージョンのサイクルを書いたときに、データベース接続の頻繁なオープンとクローズがどれほど大きな影響を与えるかについて考えました。 フォーラムにアクセスして賢い人に尋ねると、接続プールについて学びました。 それから彼は彼自身に尋ねました、それを作成する必要があるか、それは暗黙的に作成されますか？ 私は少し実験してMySQLのドキュメントを読みました。その結果、このことは非常に成功しているという結論に達しました、接続プールです！