Geek Answers Handbook

10 million updates per day for statistics. Which DB / Cache system should I use?

I am currently facing a performance issue on my website. The situation can be summarized as follows:

  • This is the .Net Mvc website using SQL Server 2012 Express. It was hosted on Windows Server 2012.
  • The main system contains about 10 million UPDATE queries per day (5 million in 2 different tables) and another 10 million for user statistics purposes (again, 2 * 5 million).
  • There are far fewer INSERT requests.
  • There are no performance issues on the MVC website, most requests are handled by a single method that returns simple content (rather than html).

Since statistics are not as important as the main system, and I see that SQL Server is struggling a lot, I thought it might be useful to move these statistics tables somewhere else.

The main question . What is the best way to handle statistics where updates prevail? The idea is also to save only one server.

I tried to see what can be done to improve the situation:

  • Having a separate SQL Server database for statistics on another hard drive? Maybe SQL Server can breathe better, but I'm not sure.
  • Using a NoSQL Database? I just have little experience with MongoDb (but not for millions of queries), and I am tempted to try RavenDB.
  • ? Redis , , Windows. AppFabric ?

.

:

TABLE [dbo].[UserStat](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[Hits] [int] NOT NULL,
[Points] [decimal](38, 6) NOT NULL,
[Date] [datetime] NOT NULL,
[LastHit] [datetime] NOT NULL,
[Ip] [varchar](256) NULL,
)

stat :

UPDATE [UserStat] SET Hits = Hits + 1, Points = Points + @Points, LastHit = @Last WHERE UserId = @Id AND [Ip] = @Ip AND [Date] = @Date

, :

INSERT INTO [UserStat] ([UserId],[Hits],[Points],[Date],[LastHit],[Ip]) VALUES (@UserId,@Hits,@Points,@Date,@LastHit,@Ip)

:

  • 1

  • 1

    INDEX [Select_UpdateUserStatNavigation] ON [dbo].[UserStat](
[UserId] ASC,[Date] ASC) INCLUDE ([Id],[Hits],[Points], [LastHit],[Ip])

- VPS. . .

SELECT, , :

SELECT Points, Hits, [Date] FROM [UserStat] WHERE UserId = @UId AND [Date] >= @date

SELECT Ip FROM [UserStat] WHERE UserId = @UId AND LastHit >= DATEADD(MINUTE,-15,getdate())

SELECT COUNT(Id) FROM [UserStat] WHERE [LastHit] >= DATEADD(MINUTE,-15,getdate())

SELECT, UPDATEs ^^.

+4
4

, ? , , . , ( )

INDEX [Select_UpdateUserStatNavigation] ON [dbo].[UserStat](
[UserId] ASC,[IP] ASC, [Date] ASC).

, . , . SELECT .

BY VPS, , ? IO, , IO . SQL? . - -.

, . - -.

, ? .

, procs MERGE, .

MERGE UserStat AS TargetTable
USING (SELECT @UserId UserID,@Hits Hits,@Points Points,@Date [Date],@LastHit LastHit,@Ip Ip) AS SourceData
ON SourceData.UserID = TargetTable.UserID 
    AND SourceData.IP = TargetTable.IP 
    AND SourceData.[Date] = TargetTable.[Date])
WHEN MATCHED THEN UPDATE SET Hits = Hits + 1, Points = Points + SourceData.Points, LastHit = SourceData.LastHit 
WHEN NOT MATCHED THEN INSERT (UserID,Hits,Points,[Date],LastHit,Ip)
                 VALUES(SourceData.UserID,SourceData.Hits,SourceData.Points,SourceData.[Date],SourceData.LastHit,SourceData.Ip)
+1

, NoSql . NoSql "-", , SQL ( ).

, SQL . , :

  • SQL . ( , , ), NoSql.
  • SQL . , , , 3-5 , NoSql - , , , .., NoSql .
  • SQL . , , , ( ), , .

:

  • . NoSql Set, . , .

  • . NoSql , ( , UserId) .

  • . NoSql , , . , . " " (yay, !).

  • . , SQL. NoSQL.

, , NoSql. Couchbase, ( - ). Elasticsearch , . NoSql, , . , , , DBA .

+2

RavenDB . . ACID . RavenDB MVC. , . RavenDB # MVC, . , . , NoSql CQRS, , .

0

, :

Express Edition Enterprise (Express vs others)

, ( )

, , (, 20 , 5 , 15 . , , . , )

Take a look at the available memory and processor. these two are the cornerstone for productivity.

and about your main problem, I need to know a little more about your statistics and its table, as well as about its use. do you mean sql server statistics, or do you mean something else?

-1
source

More articles:


All Articles