连接池的最大连接数对并发数的影响

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/yenange/article/details/79699547

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp2
{
    public class ConnStr
    {
        public string Flag { get; set; }

        public string ConnString { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            string connStringNoPooling = @"data source=(local)\sqlserver2014;initial catalog=master;Integrated Security=True;pooling=false;";
            string connStringDefault = @"data source=(local)\sqlserver2014;initial catalog=master;Integrated Security=True;";
            string connStringPooling100 = @"data source=(local)\sqlserver2014;initial catalog=master;Integrated Security=True;pooling=true;connection lifetime=10;min pool size = 100;max pool size=100; ";
            string connStringPooling200 = @"data source=(local)\sqlserver2014;initial catalog=master;Integrated Security=True;pooling=true;connection lifetime=10;min pool size = 100;max pool size=200; ";
            string connStringPooling300 = @"data source=(local)\sqlserver2014;initial catalog=master;Integrated Security=True;pooling=true;connection lifetime=10;min pool size = 100;max pool size=300; ";
            string connStringPooling500 = @"data source=(local)\sqlserver2014;initial catalog=master;Integrated Security=True;pooling=true;connection lifetime=10;min pool size = 100;max pool size=500; ";
            string connStringPooling1000 = @"data source=(local)\sqlserver2014;initial catalog=master;Integrated Security=True;pooling=true;connection lifetime=10;min pool size = 100;max pool size=1000; ";

            List<ConnStr> list = new List<ConnStr>() {
                 new ConnStr(){ Flag="无连接池",ConnString=connStringNoPooling }
                ,new ConnStr(){ Flag="默认    ",ConnString=connStringDefault }
                ,new ConnStr(){ Flag="最大100 ",ConnString=connStringPooling100 }
                ,new ConnStr(){ Flag="最大200 ",ConnString=connStringPooling200 }
                ,new ConnStr(){ Flag="最大300 ",ConnString=connStringPooling300 }
                ,new ConnStr(){ Flag="最大500 ",ConnString=connStringPooling500 }
                ,new ConnStr(){ Flag="最大1000",ConnString=connStringPooling1000 }
            };

            foreach (ConnStr connObj in list)
            {
                Stopwatch sw = Stopwatch.StartNew();
                Parallel.For(0, 1000, item =>
                {
                    TestQuery(connObj.ConnString);
                });
                sw.Stop();
                Console.WriteLine("{0} => 消耗毫秒数:{1}", connObj.Flag, sw.ElapsedMilliseconds);
            }

            Console.Read();
        }

        private static void TestQuery(string connString)
        {
            string sql = "select top 1 * from dbo.spt_values;WAITFOR DELAY '00:00:00.500'";
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
            }
        }
    }
}


如此看来, 连接数增加对增加并发有一定的改善作用,但达到一定程度后不再上升。可能 256 ~ 512 比较合适。

可能跟CPU性能也有关系, 哪位有比较强悍的机器可以一试。


展开阅读全文

连接池超过最大连接数问题

09-11

[9/11/12 14:26:23:860 CST] 000000aa SystemOut O 1981-09-28rn[9/11/12 14:26:23:860 CST] 000000aa SystemOut O rn[9/11/12 14:26:23:860 CST] 000000aa SystemOut O aaaa=5按机构查询客户与操作员信息==select p.cifno,p.cifname,p.idnum,p.mangno,t.name from cr_personinfo p left JOIN cms_tlrctl t on p.mangno=t.tlrno where p.delstat='0' and p.stat <='2' and length(p.cifno)>=10 and p.canalno = '09903' and rownum<21 and p.cifname like '%董红霞%' union all select p.cifno,p.cifname,p.license,p.mangno,t.name from cr_corp_inf p left JOIN cms_tlrctl t on p.mangno=t.tlrno where p.delstat='0' and p.stat <='2' and length(p.cifno)>=10 and p.canalno = '09903' and rownum<21 and p.cifname like '%董红霞%'rn[[color=#FF0000]9/11/12 14:26:27:806 CST] 00000017 GenericDataSo I org.apache.struts.legacy.GenericDataSource getConnection getConnection()rn[9/11/12 14:26:27:818 CST] 00000017 GenericDataSo I org.apache.struts.legacy.GenericDataSource getConnection Check for timeout, activeCount=500, useCount=500rn[9/11/12 14:26:27:836 CST] 00000017 GenericDataSo I org.apache.struts.legacy.GenericDataSource getConnection Sleep until next testrn[9/11/12 14:26:28:854 CST] 00000017 GenericDataSo I org.apache.struts.legacy.GenericDataSource getConnection Check for timeout, activeCount=500, useCount=500rn[9/11/12 14:26:28:865 CST] 00000017 GenericDataSo I org.apache.struts.legacy.GenericDataSource getConnection Sleep until next testrn[9/11/12 14:26:29:885 CST] 00000017 GenericDataSo I org.apache.struts.legacy.GenericDataSource getConnection Check for timeout, activeCount=500, useCount=500rn[9/11/12 14:26:29:909 CST] 00000017 GenericDataSo I org.apache.struts.legacy.GenericDataSource getConnection Sleep until next test[/color]rnrn数据库为aix,was为webspherern今天出现两次由于超过最大连接池导致项目无法登陆,有一次还导致websphere宕掉。rn求解决方法?由于程序比较乱,而且是很多人都参与开发过?无法定位具体是由于什么原因导致的?rn我猜想有两种可能:1、是在访问了数据库没有释放链接所致,但功能模块太多,无法定位具体是那个模块?rn2、由于是中毒引起,因为该情况以前从来没有出现过,项目上线都半年了,今天才暴露这个问题。rn求高手帮忙解决一下?rn比如怎么定位错误?有什么命令可以定位具体是什么链接没有释放等等? 论坛

没有更多推荐了,返回首页