如何提高数据存入速度

[lorl2]

目前面临问题如下

一台服务器上的GPS数据接收程序，7X24小时不间断运行。
目前GPS源约有6000台左右，至少每台30秒回传一次数.一天的数据量约4个G。
为了提高查询速度，决定分库分表来存取。

每一天生成一个数据库（按日期命名），每个库里每一个GPS数据源为一个表。

我的程序通过socket不停的接收从网络过来的GPS定位数据，按协议解包后，利用线程池去解析内容，生成SQL语句，然后存入一个队列中。程序有一个专门的线程负责写入数据库，但是忙不过来，大约每秒插入150条就到了极限了。起先我认为是线程忙不过来，又改成N个线程同时入，后来发现性能还下降了1倍，估计150条是SQL Server服务器的极限了，队列读写同步反而令线程耗费开支，造成性能下降。

有没有高人出来指点一下.


目前测试环境如下：

CPU 赛扬双核 1.8G
内存 2G
系统 Windows server 2003
SQL SQL Server2005

[Alfred]

写给数据中间层，现在即时都游戏都这样搞。

[ycg_893]

using (SqlConnection cn = new SqlConnection(strDBName))
                            {
                                cn.Open();
                                if (cn.State == ConnectionState.Open)
                                {
                                    //SqlCommand cmd = m_SqlCommandPool.Pop();
                                    try
                                    {
                                        objSQLCommand.Connection = cn;
                                        objSQLCommand.CommandText = strSQL;
                                        objSQLCommand.CommandType = CommandType.Text;
                                        objSQLCommand.ExecuteNonQuery();
                                    }
                                    catch (Exception ex)
                                    {
                                        CDebug.Print(ex.ToString() + "\r\n" + sqlTask.Command);
                                    }
                                    finally
                                    {
                                        //释放对连接的引用
                                        objSQLCommand.Connection = null;
                                       // m_SqlCommandPool.Push(cmd);
                                    }
                                }
                            }

你这样性能肯定会下降,using 每次执行完都会关闭连接.对于你的这种需求应将SqlConnection 对象改成单实例,因为差不多实时都在写入,因此不需要每次都Close.
我也有一个和你类似的需求(与移动收发短信),虽然数据没有你的大,但改成单实例性差不多提高了10倍.当然也带一个问题就是只有程序退出才会释放这个连接.也就是这个连接是一直被占用.

[ycg_893]

如果每天都是一个库的话,在单实例创建时,判断传时的时间是否是新的一天,如果是则将上一天的连接关闭,创建新一天的连接实例.

[obuntu]

怎么觉得不像是数据库的问题，而是程序的问题呢。

[haitao]

[Quote=引用 23 楼 sz_haitao 的回复:]
1天1个表，最大就是2千万条记录，应该不算太大
而且往日的表，不会再插入的了，可以加够索引，支持查询就行了，不用担心影响插入效率了
只有当天表才需要插入的
[/Quote]

1天增加4G的数据，的确也挺麻烦。1天1个库倒是也省事
或者历史数据做成1天1个库，当天库还是 所有gps1天 1个表

等待楼主测试 1千个insert 和 1个insert插入1千行 的效率差别比较结果。。。。。。。。

[Q315054403]

这个需求看业务逻辑如何强制定义
有的需求是必须每条立即写入DB并返回结果给前端。。若批次1千条，若因为什么原因失败将如何？？

看来楼主不是玩DB的，对这个系统的瓶颈磁盘写入都没交待
若是业务强制要求每次一条的话，可分多个DB，将6000台GPS的数据分组写入对应的DB
即一个组对应一个DB，就没必要再分什么DB<库>了，但分区需要
按照每秒200条记录<不考虑未来规模持续扩大>，其实配置好磁盘与数据文件设计就足够了

提供有偿支持

[lorl2]

[Quote=引用 25 楼 sz_haitao 的回复:]
引用 23 楼 sz_haitao 的回复:
1天1个表，最大就是2千万条记录，应该不算太大
而且往日的表，不会再插入的了，可以加够索引，支持查询就行了，不用担心影响插入效率了
只有当天表才需要插入的


1天增加4G的数据，的确也挺麻烦。1天1个库倒是也省事
或者历史数据做成1天1个库，当天库还是 所有gps1天 1个表

等待楼主测试 1千个insert 和 1个insert……
[/Quote]


这个不用测了。前几年这个库设计就是所有的历史数据都在同一个库，按日期分表存一天一个表。在1000台以下的时候，速度还可以忍受。3000台以上，连我们程序员自己拿查询分析器查数据，都自己受不了，别说客户。

昨晚7点程序编译好后，放到营运中的服务器上跑。
并且我改了一些操作系统和SQLSERVER的设置。

1。在“设备管理器”的硬盘属性上的“策略”勾上那两个关于硬盘缓冲的开关。
2。在“我的电脑”属性里，将处理器计划和内存使用，都设置为优先后台服务和系统缓存。
3。在SQL的“企业管理器”勾上“提升SQL线程优先级”和使用“WINNT 纤程”

设置后重启服务器，程序从晚上7点多一直跑到晚上12点，平均插入插入速度达到130多，感觉比调整系统设置之前，性能上升明显，每秒高出20条左右。如果能再高出20条，应该可以应付目前的问题。还有微量的数据入不过来我可以丢掉一些，一天里面一台GPS三两小时丢一秒的数据，感觉不出来^_^！

另外，通过这次大数据量处理，我发现连接数据库的串，如果是连本机，用“.”号代替换"127.0.0.1”速度有明显变化，在SQL管理看到连接方式为LPC，用IP连接的话，是TCP/IP。


每秒130条的插入速度一直维持到昨晚12之前。过了12点后，速度直线下降到40-60条每秒了。不知道是什么原因！

于是昨晚12点多的时候，我远程上服务器关了启起这个程序，还是没有什么改变，一直在40-60左右。我以为是可以别的程序在访问数据库造成的干扰，就没有理会。等到差不多两点的时候，我又上去看了一次，还是这个速度。我又重开了一次这个程序。至今天早上8点的时候，估计也是这个速度，因为它崩了。看打印日志是由于内存错误，应该是入不过来，数据大量堆积造成的。


我的程序除了解析数据入库外，还有一个优先级较低的后台线程，它负责每天晚上过了23点后，为明天建立按日期命名的数据库，及在这个库里为每一个GPS建立一个表。这两个操作过程是写成存储过程的，调用的时候只需要传个日期参数入去就可以的了。库需要包含的表结构都在存储过程里定义。

昨晚时间跨天了之后，性能急剧下降的原因，我挠破脑袋也想不出原因来。有一点点怀疑是由于存储过程建的新库可能不对劲，但是，这几个月来的建库建库操作都是由这两个存储过程去做的，包括昨天晚上插入速度非常快的那个库和表。

[haitao]

使用“WINNT 纤程”
这个是有很多限制的，建议不要使用

所有的历史数据都在同一个库？
那那肯定不行啊，每天加4G，备份都恐怖啊
历史数据 应该 1天1个库，省事！——备份简单，丢弃过期的数据也简单
但是，当天库，为了插入效率高，应该 所有gps记录都存1个表

[haitao]

【连本机，用“.”号代替换"127.0.0.1”速度有明显变化，在SQL管理看到连接方式为LPC，用IP连接的话，是TCP/IP】
没有管道方式，快是不是因为使用了内部共享内存的方式？

【硬盘属性上的“策略”勾上那两个关于硬盘缓冲的开关】
如果掉电或突然死机，会比较危险


如果性能只够应付，是比较危险的，至少要宽裕1倍才能放心吧
是出租车管理公司？在什么地方？
半夜还要监控，的确比较辛苦了

[ccshigenvwa]

这服务器也扛得住哦,有点厉害

[guanjm]

LZ，你服务器上面配置？
是RAID10还是RAID5，RAID10的插入性能是RAID5的4倍左右
还有你是SQL2008还是SQL2005还是SQL2000？
内存配置多少？
SQL2008可以启用页压缩选项，然后再来测试。
当然磁盘阵列柜性能更佳。

[lorl2]

[Quote=引用 34 楼 guanjm 的回复:]
LZ，你服务器上面配置？
是RAID10还是RAID5，RAID10的插入性能是RAID5的4倍左右
还有你是SQL2008还是SQL2005还是SQL2000？
内存配置多少？
SQL2008可以启用页压缩选项，然后再来测试。
当然磁盘阵列柜性能更佳。
[/Quote]

服务器4G内存
至强处理器
2个SATA 2的硬盘，没有组阵列。其中这些实时数据的库单独放在一个硬盘上。
win2003标准版
SQL2000企业版


现在老板己经下单，再买一台DELL的服务器。8000块左右的。
内存8G
双SATA2的硬盘，准备组个RAID 0的阵列。

系统还是用回Win2003,准备上64位。SQL准备用2005 64位。
服务器上我们的程序，准备全部编译为64位的。


目前服务器上的程序.net写的，有vs03，vs05，vs08
不知道高版本的.net会不会性能也高一些？

到时候服务器回来再试试。

[lorl2]

今天改了一下程序，在环境不变的情况下，将插入速度提到每秒170以上了，满足了当前需求。但是新服务器下午也到了，老板很郁闷。

[orochi_gao]

[Quote=引用 35 楼 lorl2 的回复:]
现在老板己经下单，再买一台DELL的服务器。8000块左右的。
[/Quote]

这血出的,对这业务量。。。

[昵称被占用了]

[Quote=引用 35 楼 lorl2 的回复:]
引用 34 楼 guanjm 的回复:
LZ，你服务器上面配置？
是RAID10还是RAID5，RAID10的插入性能是RAID5的4倍左右
还有你是SQL2008还是SQL2005还是SQL2000？
内存配置多少？
SQL2008可以启用页压缩选项，然后再来测试。
当然磁盘阵列柜性能更佳。


服务器4G内存
至强处理器
2个SATA 2的硬盘，没有组阵列。其中这些实时数……
[/Quote]

1、采购中的硬件配置还是偏低。
2、如果服务器配置不能高，可考虑除了分库分表，还要分服务器，这样应用程序复杂度就更高了。
3、插入过程是可优化的，应该批量插入，一批500-1500数据。
4、应用程序中可使用队列暂存数据，实现异步化

[haitao]

每行一个insert，效率估计也改善不了多少
每500-1000行一个insert，才能本质的区别

[huangqing_80]

目前测试环境如下：

CPU 赛扬双核 1.8G
内存 2G
系统 Windows server 2003
SQL SQL Server2005

这样的硬件环境，还一天4个G的流量，乖乖，你怎么办到的啊

[zosky]

把数据库恢复模式改成简单，我就是这么搞的，写入效率成倍提高。

[zosky]

我这儿一秒钟一百笔不到，但服务器配置没你高。

[晓风斜阳]

你那CPU,你那内存，他们表示鸭梨很大呀