How to handle 5 million users? ASP.NET Identity

问题:

I am running a ASP.NET mvc5 app which currently has 5 million users. It is hosted in the Azure cloud. For the authentication I use the Asp.Net Identity for EntityFramework.

However, the more users I get, the slower the register function becomes. I tried scaling the database, but the results are still the same. It takes around 6-7 seconds for a new user to register.

Also I tried searching how I can improve the performance of the identity system, but I couldn't really find anything relevant.

I would really like to hear if somebody has some idea how can I improve the performance of it.

UPDATE: I have indexes on the fields that I am searching on, also, the database subscription that I have chosen in Azure is P3 SQL database with 200 DTUs.

I profiled the DB and I found a suspicious select query. I deleted some of the projections and replaced them with "...." so it doesn't get too long and you can see what is the query about.

SELECT 
    [UnionAll2].[Gender] AS [C1], 
    ....
    [UnionAll2].[UserName] AS [C27], 
    [UnionAll2].[C1] AS [C28], 
    [UnionAll2].[UserId] AS [C29], 
    [UnionAll2].[RoleId] AS [C30], 
    [UnionAll2].[UserId1] AS [C31], 
    [UnionAll2].[C2] AS [C32], 
    [UnionAll2].[C3] AS [C33], 
    [UnionAll2].[C4] AS [C34], 
    [UnionAll2].[C5] AS [C35], 
    [UnionAll2].[C6] AS [C36], 
    [UnionAll2].[C7] AS [C37], 
    [UnionAll2].[C8] AS [C38], 
    [UnionAll2].[C9] AS [C39]
    FROM  (SELECT 
        CASE WHEN ([Extent2].[UserId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        [Limit1].[Gender] AS [Gender], 
        ....
        [Limit1].[UserName] AS [UserName], 
        [Extent2].[UserId] AS [UserId], 
        [Extent2].[RoleId] AS [RoleId], 
        [Extent2].[UserId] AS [UserId1], 
        CAST(NULL AS int) AS [C2], 
        CAST(NULL AS varchar(1)) AS [C3], 
        CAST(NULL AS varchar(1)) AS [C4], 
        CAST(NULL AS varchar(1)) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8], 
        CAST(NULL AS varchar(1)) AS [C9]
        FROM   (SELECT TOP (1) 
            [Extent1].[Id] AS [Id], 
            ....
            [Extent1].[UserName] AS [UserName]
            FROM [dbo].[Users] AS [Extent1]
            WHERE ((UPPER([Extent1].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent1].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL)) ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[UserRoles] AS [Extent2] ON [Limit1].[Id] = [Extent2].[UserId]
    UNION ALL
        SELECT 
        2 AS [C1], 
        [Limit2].[Gender] AS [Gender], 
        ....
        [Limit2].[UserName] AS [UserName], 
        CAST(NULL AS varchar(1)) AS [C2], 
        CAST(NULL AS varchar(1)) AS [C3], 
        CAST(NULL AS varchar(1)) AS [C4], 
        [Extent4].[Id] AS [Id1], 
        [Extent4].[UserId] AS [UserId], 
        [Extent4].[ClaimType] AS [ClaimType], 
        [Extent4].[ClaimValue] AS [ClaimValue], 
        CAST(NULL AS varchar(1)) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8]
        FROM   (SELECT TOP (1) 
            [Extent3].[Id] AS [Id], 
            ....
            [Extent3].[UserName] AS [UserName]
            FROM [dbo].[Users] AS [Extent3]
            WHERE ((UPPER([Extent3].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent3].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL)) ) AS [Limit2]
        INNER JOIN [dbo].[UserClaims] AS [Extent4] ON [Limit2].[Id] = [Extent4].[UserId]
    UNION ALL
        SELECT 
        3 AS [C1], 
        [Limit3].[Gender] AS [Gender], 
        ....
        [Limit3].[UserName] AS [UserName], 
        CAST(NULL AS varchar(1)) AS [C2], 
        CAST(NULL AS varchar(1)) AS [C3], 
        CAST(NULL AS varchar(1)) AS [C4], 
        CAST(NULL AS int) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8], 
        [Extent6].[LoginProvider] AS [LoginProvider], 
        [Extent6].[ProviderKey] AS [ProviderKey], 
        [Extent6].[UserId] AS [UserId], 
        [Extent6].[UserId] AS [UserId1]
        FROM   (SELECT TOP (1) 
            [Extent5].[Id] AS [Id], 
            ....
            [Extent5].[UserName] AS [UserName]
            FROM [dbo].[Users] AS [Extent5]
            WHERE ((UPPER([Extent5].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent5].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL)) ) AS [Limit3]
        INNER JOIN [dbo].[UserLogins] AS [Extent6] ON [Limit3].[Id] = [Extent6].[UserId]) AS [UnionAll2]
    ORDER BY [UnionAll2].[Id] ASC, [UnionAll2].[C1] ASC

My EntityFramework User POCO class

    public class User : IdentityUser
    {
        [Index]
        public DateTime Created { get; set; }
        [Index(IsUnique = true), MaxLength(255)]
        public override string Email { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        [Index]
        public GenderType Gender { get; set; }
        [Index]
        public DateTime? Birthdate { get; set; }
        [Index, MaxLength(2)]
        public string Country { get; set; }
        [MaxLength(2)]
        public string Language { get; set; }
        [Index, MaxLength(256)]
        public string Referral { get; set; }
        public string ImageUrl { get; set; }
        [Index]
        public UserIdentityStatus IdentityConfirmed { get; set; }
        [Index]
        public DateTime? Deleted { get; set; }
        public ICollection<Reward> Ads { get; set; }
        public ICollection<Thought> Thoughts { get; set; }
        public ICollection<Achievement> Achievements { get; set; }
        public ICollection<Subscription> Subscriptions { get; set; }
        public DateTime? TutorialShown { get; set; }
        [Index]
        public DateTime? LastActivity { get; set; }
        [Index]
        public DateTime? LastBulkEmail { get; set; }
    }

回答1:

I think I have solution for you. I have made another tests and the second option - index on computed column works. Here are steps in sql code, you can probably do the same using EF annotations.

1. Create computed column on table users as upper(username):

   alter table users add upper_username as upper(username)

2. Create index on that column:

   create index ix2 on a_upload(upper_username)

Thats all. The EF select will still use UPPER, but MS SQL optimizer should be able to use this index as it has the same definition as the function in where clause.

Here are test results on my PC:

test sql: select field001 from a_upload where upper(field001)='10'

BEFORE (SCAN means that the engine has to read all records one by one)

AFTER CREATING THE INDEX on functional column (SEEK=engine will utilize index)

Dont get confuzed that even in the BEFORE scenario, sql engine is using index (ix1). It is only because I am selecting only "field001" and the optimizer knows, that it is contained not only in the table but in the index too. And index has less bytes than whole table. But it does not mean that the system utilized index, it has to compute upper() for every row on each select anyway.

回答2:

While I had to replace the userId guid with an int, I created a CustomUserStore. In this UserStore you can simply overwrite the FindByNameAsync:

public class CustomUserStore<TUser> : UserStore<TUser, CustomRole, int, CustomUserLogin, CustomUserRole, CustomUserClaim> where TUser : MyUser
{
    public CustomUserStore(MyDbContext context) : base(context) { }

    public override Task<TUser> FindByNameAsync(string userName)
    {
        return this.GetUserAggregateAsync(u => u.UserName.Equals(userName, StringComparison.InvariantCultureIgnoreCase));
    }
}

This results in a query without UPPER().

回答3:

Yes, this query is probably the problem. This is why I don't like SQL frameworks.

The problem is here:

WHERE 
((UPPER([Extent3].[UserName])) = (UPPER(@p__linq__0))) 

The right side is constant and the left side "upper(username)" is SQL engine trying to find somewhere. Index on simple "username" is useless, because it contains simple "username" and not upper(username) values. SQL engine has to calculate upper(username) for all 5 millions lines EVERYTIME you add new user and then find the corresponding value. 7 seconds...

You need to create such index:

create index ix_name on Users(upper(UserName))

Such index will be used automatically by sql engine. But I am afraid, that MS SQL will not allow you to do it without adding computed column (and it is useless for your purpose, because you can not force EF to use another column for username value).

The other option is to force Identity framework not to use UPPER function. But I don't know how.

By the way, I am really surprised to find this type of code in such important package - entity framework. This "searching functional value in index" is on of the basic mistakes when writing efficient sql code.

And one more comment: This everything should have been done by the framework, it is responsible for correct handling of "username" column. It is a bug, this advice is only workaround.

回答4:

Try to get the exact SQL command from some Azure audit tool and write it to the comment here. 7 second looks like problem in indexes (too long for access by index ant too short for transferring huge data to client).

INSERT command itself is always quick (if not slowed down by triggers) and does not get slower significantly with increasing number of rows. Identity framework is probably running some SELECT (e.g. to re-read new line...?). Having indexes on all affected columns is not enough. Select command can use only ONE index on each table, so you need to find only ONE index, but the proper one - the correct combination of columns, column order in index is also important. And all this depends on the SQL command, that is the Identity engine trying to execute. Try to find it, then I can help you.