[search piece of artifact]BT management program debug database speed optimizatio

DHT capture program source address: https://github.com/h31h31/H31DHTDEMO

Data processing program source address: HTTPS://github.com/h31h31/H31DHTMgr

Thank you for the support of friends, had found the VPS test, the foreign server: you can give me some advice...

The server at the same time in capturing and processing, so the access speed is slow some, especially the search speed by SQL like to query the slow, is through the improvement in word segmentation. .

---------------------------------------------------------------------------------------------------

When the database inside a lot of data in the table is about 3000000, the total file size is 8G size, the discovery of new increased data rate than the original slow a lot of problems, some performance and speed optimization problems must be considered.

Because the server is required to run the website, query speed and program insertion speed will lead to more people will become more and more slowly.

You can view the database table structure to store data number;

1 the data operation process is to query the database inside it, through the HASH value if no, is directly inserted, if any, count directly update the record.

2 because the record with ID and file list. Set ID for the primary key value, only for hashkey the constraint index design unique key, the table structure design as shown in Fig.:

3 no experience before, only increase the log output.

[2013-9-5 23:55:02]: The 2:2: thread[4]>>>>67F8DAC16B2ACB5CC79BDD02F7478457E99C5966 update to a database of 1010 successful 1TIME:0-78-0-140
[2013-9-5 23:55:02]: The 2:2: thread[1]>>>>57D55712F097DFDA3F3204C3E35B59461CCFE851 update to a database of 1011 successful 1TIME:140-109-0-219
[2013-9-5 23:55:03]: The 2:2: thread[3]>>>>720A898E7C76CD5AD0B7B379D3CD97329EC9BDFE update to a database of 4011 successful 4TIME:0-422-0-16
[2013-9-5 23:55:03]: The 2:2: thread[4]>>>>7B534EAFF508F861B8B1E5A5D79D9C11F1655B43 update to a database of 1011 successful 1TIME:0-78-16-31
[2013-9-5 23:55:03]: The 2:2: thread[5]>>>>9AAF76DE08F2ACA7DEDD11B139EE76798591D30F update to a database of 1011 successful 1TIME:0-485-0-31
[2013-9-5 23:55:03]: The 2:2: thread[1]>>>>6D8C1ACA280773A40958A4D3B4FF5DA447651C14 update to a database of 1011 successful 1TIME:140-485-0-15
[2013-9-5 23:55:03]: The 2:2: thread[2]>>>>6A511827CD07A9FB725AA9DF317DE180B342A4A4 update to a database of 1011 successful 1TIME:407-94-15-78
[2013-9-5 23:55:03]: The 2:2: thread[4]>>>>95A6DAF234532E10012169372448096544D58D68 update to a database of 1011 successful 1TIME:0-94-0-109
[2013-9-5 23:55:03]: The 2:2: thread[3]>>>>C0A3C675DE7848F19E1BACE2DFE729D825580290 update to a database of 3011 successful 3TIME:0-62-0-47
[2013-9-5 23:55:04]: The 2:2: thread[5]>>>>DC82DDB68F6F5F0CB310726CD9A3A382A8549802 update to a database of 2010 successful 2TIME:0-31-0-16
[2013-9-5 23:55:04]: The 2:2: thread[1]>>>>3CBB82952AA59A020388415B299AA79B46CCF7DF update to a database of 1011 successful 1TIME:140-62-0-32
[2013-9-5 23:55:04]: The 2:2: thread[4]>>>>BF59A4902E6424A84418239F7BE670CBBF84ED03 update to a database of 1010 successful 1TIME:0-47-0-110
[2013-9-5 23:55:04]: The 2:2: thread[1]>>>>3A804B13102E6C5B427F7E1F0F472A88F64A225C update to a database of 1011 successful 1TIME:140-78-0-16
[2013-9-5 23:55:04]: The 2:2: thread[5]>>>>EBE9FD044F8A07FF8E157B98BF8BE10F6977A570 update to a database of 1010 successful 1TIME:0-94-0-93
[2013-9-5 23:55:04]: The 2:2: thread[5]>>>>70CC8C9B5EDE1F5673B7A4B684219F39E75DB660 update to a database of 1011 successful 1TIME:0-31-0-32
[2013-9-5 23:55:05]: The 2:2: thread[2]>>>>CAEAF85ED0D3A9DEA7DA2EC75445565368F51E63 update to a database of 6011 successful 6TIME:407-328-0-47

TIME:[reads the text time has nothing to do with this and have to query the HASH whether there have updated record count and have written to the IP information into the IP table.

The average query hashkey at a rate of about 100ms, or time-consuming process. And the SQLSERVER CPU occupation often at around 30%, to the need to consider the problem.

The program query:

string tempstr1 = string.Format("select top 1 ID,Detail from H31_DHT_TYPE_{0}_{1} where hashKey='{2}'", hashtype, isHanzhi, hashname);

The query whether HASH storage time is relatively large, how to optimize.?


Search database optimization experience from the Internet:

Database optimization
1To avoid disk I/O bottleneck., 
2To reduce the CPU utilization.
3Reduce resource competition. 
Angle: 
The basic table design, extended design and database table object placement
Method: 
1A database design.
2Indexes
3Avoid long transaction.
4The storage process.
5.sql optimization. 

Whether the 1 began to analysis is to jump update queries in tables, thus affecting the query speed?

2 database index are set correctly? If the main index to the hashkey whether it? Because before considering self enhancement of ID on the main index will be a lot easier, after all hashkey is 40 bytes, the index will be slower.

Hold try attitude, set the primary key of the hashkey,


Speed has been increased quickly after setting the query server program, statistical information is as follows:

[2013-9-6 0:50:43]: The 2:2: thread[3]>>>>1EE9C3804EF32A5A6EA9ACA18948B426C44BC9CA update to a database of 1011 successful 1TIME:0-15-0-16
[2013-9-6 0:50:43]: The 2:2: thread[2]>>>>46E527F7CE92F958688F40CD30F4D068ECCF0BDC update to a database of 1011 successful 1TIME:171-0-0-15
[2013-9-6 0:50:43]: The 2:2: thread[1]>>>>F865CBCEA3D97F4D8706D8EEC5D1D2C3E5B93CDF update to a database of 4010 successful 4TIME:0-47-0-31
[2013-9-6 0:50:43]: The 2:2: thread[3]>>>>46F08974CB1861B5A18BDA403F12DF3BF72BD990 update to a database of 1011 successful 1TIME:0-0-0-16
[2013-9-6 0:50:43]: The 2:2: thread[5]>>>>2E0CB6E4A3CAF3DF42FE063DAA4C0B2DED0E393A update to a database of 3011 successful 3TIME:0-31-16-31
[2013-9-6 0:50:43]: The 2:2: thread[4]>>>>2889CAB940CD696A645868FACC3893774A1C6252 update to a database of 3010 successful 3TIME:15-47-0-31
[2013-9-6 0:50:43]: The 2:2: thread[1]>>>>9DC1DE71F39C237B264EE8FDE618642F20F0610C update to a database of 1011 successful 1TIME:0-0-0-32
[2013-9-6 0:50:43]: The 2:2: thread[2]>>>>23710902E6EB72D8099ECF39CEFD6B17DF942AFC update to a database of 1011 successful 1TIME:171-15-0-32
[2013-9-6 0:50:43]: The 2:2: thread[3]>>>>4E8D7DA699BD577C2AA06B71D173049657067DB7 update to a database of 1011 successful 1TIME:0-0-0-16
[2013-9-6 0:50:44]: The 2:2: thread[1]>>>>FD410EE7433B4796D611D2BF7F942029DFEEC0A9 update to a database of 1011 successful 1TIME:0-0-0-31
[2013-9-6 0:50:44]: The 2:2: thread[5]>>>>B62136A52A711275BE375592B377DC8F28AB35A5 update to a database of 2011 successful 2TIME:0-62-16-16
[2013-9-6 0:50:44]: The 2:2: thread[2]>>>>8DF4D76D34A8A352118F6D34F8017AF16EF30A80 update to a database of 2011 successful 2TIME:171-32-31-62
[2013-9-6 0:50:44]: The 2:2: thread[4]>>>>353B7E166C071433DDDFB82928625D1D8E762204 update to a database of 1011 successful 1TIME:15-0-0-15
[2013-9-6 0:50:44]: The 2:2: thread[3]>>>>511C7C32A4B0CA0F77853944FB9BD03DAC389579 update to a database of 1011 successful 1TIME:0-0-0-94
[2013-9-6 0:50:44]: The 2:2: thread[1]>>>>562E5E0CDADD9ED787765C83DD6EA1508986E97C update to a database of 1011 successful 1TIME:0-0-0-31
[2013-9-6 0:50:44]: The 2:2: thread[2]>>>>3748B7734A277347F8206898B827BDE6DB4FD131 update to a database of 3011 successful 3TIME:171-31-0-31
[2013-9-6 0:50:44]: The 2:2: thread[4]>>>>CFDB99C97C4EB6AF3B698DC3690FA9F8E35DD4B1 update to a database of 1011 successful 1TIME:15-0-0-94
[2013-9-6 0:50:44]: The 2:2: thread[3]>>>>7D4CF91647D6268580CC7D9A525786B5F3F2D22E update to a database of 1011 successful 1TIME:0-0-0-78

Query hashkey whether there are speed is only about 10ms. At this time the SQLSERVER process consumes CPU rate at around 17%, basically drop by half, but the query speed by 10 times.

The first database optimization is successful, but the insertion of IP information list of time also occupy relatively large, need to consider how to optimization problems. We recorded it.

Summary:

1 the initial database design is fully considered the problem size database each table, so the table segmentation problem does not currently exist, need to consider other factors.

Most of the 2 Web indexing, is very important to establish the index, the best query field set as the primary key value, because to a large number of queries involving Ji;

3 sometimes not all program problem, a lot of, need to consider problems such as database, server memory, CPU will need to be considered.

4 if the field compared to many fields such as optimization? Need more time and other information? I hope everyone under the guidance of this form if there is room for optimization, thanks

Table structure:

CREATE TABLE [dbo].[H31_DHT_LOG_201307](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [hashID] [int] NOT NULL,
    [hashKey] [nvarchar](40) COLLATE Chinese_PRC_CI_AS NULL,
    [recvTime] [datetime] NULL,
    [RecvIP] [nvarchar](16) COLLATE Chinese_PRC_CI_AS NULL,
    [recvPort] [int] NULL,
    [keyType] [int] NULL,
    [recvTimes] [int] NULL,
    [areaID] [int] NULL,
    [areaID2] [int] NULL,
 CONSTRAINT [PK_H31_DHT_LOG_201307] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Queries to this day IP information a record update counts:

StringBuilder strSql = new StringBuilder();
DateTime starttime = Convert.ToDateTime(thehash.updateTime.ToString("yyyy-MM-dd"));
DateTime endtime = starttime.AddDays(1);
int daytable = Convert.ToInt32((thehash.updateTime.Day - 1) / 5) + 1;

string tempstr1 = string.Format("select top 1 ID from H31_DHT_LOG_{0}_{1:D2} where hashKey='{2}' and RecvIP='{3}' and recvTime>='{4}' order by id desc", thehash.updateTime.ToString("yyyyMM"), daytable, thehash.hashKey, thehash.recvIp, starttime.ToString("yyyy-MM-dd"));
object countid = dbsql.ExecuteScalar(CommandType.Text, tempstr1.ToString(), null);
if (countid != null && dbsql.GetInt(countid) > 0)
{
    string tempstr = string.Format("update H31_DHT_LOG_{0}_{1:D2}  set recvTimes=recvTimes+1 where id={2}", thehash.updateTime.ToString("yyyyMM"), daytable,dbsql.GetInt(countid));
    strSql.Append(tempstr);
    return dbsql.ExecuteNonQuery(CommandType.Text, strSql.ToString(), null);
}

We hope a lot of guidance and recommendation for help.

Posted by Enoch at November 22, 2013 - 12:09 AM