Umbraco Membership Provider for large ammounts of users
I am thinking about using the default UmbracoMemberShipProvider and ProfileProvider to power a new website. The number of users and user profiles might grow to 100.000 or more. I am wondering whether it is a good architectual choice to use the default provider,since all user and their profile data are stored in the same database table as the website content. The tables might grow extremly large, resulting in underperformance of back-end and front-end.
Should I go for the default Umbraco providers or should I create custom providers, e.g. having more control over how data is stored and retrieved so that I can optimize that for performance?
I'd follow Aaron's advice at http://www.aaron-powell.com/blog/july-2009/umbraco-member-profiles.aspx, personally. Especially with so many potential members (you really mean 'members' and not 'users', right? Members are website visitors and Users are those people who can log into umbraco's backend).
The solution that Aaron is sketching is actually about using the default UmbracoProfileProvider and how to access profile data in the front-end. Using this solutions for a hugh amount of members, will cause a couple of tables to explode (e.g. cmsPropertyData / umbracoNode / cmsMember) which will have a impact on performance. Especially since the website content is fetched from the same db tables.
Just curious if anyone on the forum already has experience with this. I will also do some stress testing by automatic scripting.
If you add your own custom DB indexes to those tables you'll see significate improvements in the performance of them. There's no real problems with large amounts of data (MS SQL Server scales to tens of millions of records), you just need to tweak your server, indexes, caching, etc.
You can drop in your own membership provider (say the SQL one), it should work without any problems (and if there were, you should raise them as bugs :))
Don't get hung up on potential user base. You've said might grow to 100,000.But if numbers turn out to be1,000 or so for the next year you may be better of concentrating your efforts elsewhere and just implementing a standard membership provider for now. It can be refactored at a later date or you may find it handles 100,000 no problem.
If, on the other hand, you are converting a site that has an existing user base of 100,00 that is a different story. SQL Server can handle it OK but you will need to test server performance, indexes etc as Slace mentioned.
Accessing web site content from the same tables shouldn't affect the result as this is cached as a XML file in IIS anyway so it is only member specific queries that will hit the DB.
@slace: any ideas where to put the indexes on in that case? Is there a good way to find out? (I remember something like SQL performance advisor?
@Paul Blair: Indeed, it is a conversion of an existing website, which is already on around 30.000 members. About your remark about the caching in an XML file: as far as I know, this is only done for presenting in the front-end. So for accessing the (member) data in the back-end, you still have hits on the database.
I just did a quick test by automtically creating 100.000 members using the Membership API. This, for now, results in database timeouts on "Tree fetching" (opening the different sections) in the Umbraco back-end (for all trees, including cotent & media). (In Umbraco, tree content is fetched using Javascript and then rendered on the client side).
I will experiment a little with putting indexes and put the results here.
I just did a test with 10.000 automatically created members. (without any profile information such as firstname, lastname etc.). So only memberinfo (username, password, email). This fills table 'cmsMember' as well as 'umbracoNode'. Since other content (pages, media) is also stored in the table 'umbracoNode', you would expect performance issues. And indeed, those arise. Trees (content & media) will not load anymore, basically because of SQL server timeouts (resulting from inefficient queries and lack of indexes).
Investigating further, all came down to one query in the Umbraco core. After analysing the query, I decided to create two indexes:
NodeId in table cmsContent ContentId in table cmsContentVersion
This result in normal performance of the Umbraco back-end again.
Of course, this is not an ideal solution, since it involves customization of the umbraco core database (making future upgrades harder). Still in doubt whether to use the out-of-the-box Umbraco provider or the ASP.NET default...
Now, I am testing the creation of additional profile fields (such as firstname, lastname).
What Peter said! We don't really have the data volume to test with when we're doing dev so sadly performance is something that really lacks. And also due to the design of Umbraco at the moment it's something that is very hard for us to fix :( (I know, I tried!)
I have a database with 76,000 users and I added this index to the database and it seemed to increase performance quite a bit. I haven't thouroughly tested it yet, but so far, so good! :)
CREATE INDEX Idx_cmsMember_LoginName ON cmsMember ("LoginName" ASC)
Umbraco Membership Provider for large ammounts of users
I am thinking about using the default UmbracoMemberShipProvider and ProfileProvider to power a new website. The number of users and user profiles might grow to 100.000 or more. I am wondering whether it is a good architectual choice to use the default provider,since all user and their profile data are stored in the same database table as the website content. The tables might grow extremly large, resulting in underperformance of back-end and front-end.
Should I go for the default Umbraco providers or should I create custom providers, e.g. having more control over how data is stored and retrieved so that I can optimize that for performance?
I'd follow Aaron's advice at http://www.aaron-powell.com/blog/july-2009/umbraco-member-profiles.aspx, personally. Especially with so many potential members (you really mean 'members' and not 'users', right? Members are website visitors and Users are those people who can log into umbraco's backend).
cheers,
doug.
Indeed, I do mean Members.
The solution that Aaron is sketching is actually about using the default UmbracoProfileProvider and how to access profile data in the front-end. Using this solutions for a hugh amount of members, will cause a couple of tables to explode (e.g. cmsPropertyData / umbracoNode / cmsMember) which will have a impact on performance. Especially since the website content is fetched from the same db tables.
Just curious if anyone on the forum already has experience with this. I will also do some stress testing by automatic scripting.
If you add your own custom DB indexes to those tables you'll see significate improvements in the performance of them. There's no real problems with large amounts of data (MS SQL Server scales to tens of millions of records), you just need to tweak your server, indexes, caching, etc.
You can drop in your own membership provider (say the SQL one), it should work without any problems (and if there were, you should raise them as bugs :))
Don't get hung up on potential user base. You've said might grow to 100,000.But if numbers turn out to be1,000 or so for the next year you may be better of concentrating your efforts elsewhere and just implementing a standard membership provider for now. It can be refactored at a later date or you may find it handles 100,000 no problem.
If, on the other hand, you are converting a site that has an existing user base of 100,00 that is a different story. SQL Server can handle it OK but you will need to test server performance, indexes etc as Slace mentioned.
(The above statements fit in well with the Agile methadology: http://en.wikipedia.org/wiki/Agile_software_development)
Accessing web site content from the same tables shouldn't affect the result as this is cached as a XML file in IIS anyway so it is only member specific queries that will hit the DB.
Thanks guys for the replies.
@slace: any ideas where to put the indexes on in that case? Is there a good way to find out? (I remember something like SQL performance advisor?
@Paul Blair: Indeed, it is a conversion of an existing website, which is already on around 30.000 members. About your remark about the caching in an XML file: as far as I know, this is only done for presenting in the front-end. So for accessing the (member) data in the back-end, you still have hits on the database.
I just did a quick test by automtically creating 100.000 members using the Membership API. This, for now, results in database timeouts on "Tree fetching" (opening the different sections) in the Umbraco back-end (for all trees, including cotent & media). (In Umbraco, tree content is fetched using Javascript and then rendered on the client side).
I will experiment a little with putting indexes and put the results here.
I'm far from a SQL server expert so I'm not sure where in addition you would want them other than the existings ones.
You could put some on the username field in cmsMember, as that's one of the primary selected fields.
I just did a test with 10.000 automatically created members. (without any profile information such as firstname, lastname etc.). So only memberinfo (username, password, email). This fills table 'cmsMember' as well as 'umbracoNode'. Since other content (pages, media) is also stored in the table 'umbracoNode', you would expect performance issues. And indeed, those arise. Trees (content & media) will not load anymore, basically because of SQL server timeouts (resulting from inefficient queries and lack of indexes).
Investigating further, all came down to one query in the Umbraco core. After analysing the query, I decided to create two indexes:
NodeId in table cmsContent
ContentId in table cmsContentVersion
This result in normal performance of the Umbraco back-end again.
Of course, this is not an ideal solution, since it involves customization of the umbraco core database (making future upgrades harder). Still in doubt whether to use the out-of-the-box Umbraco provider or the ASP.NET default...
Now, I am testing the creation of additional profile fields (such as firstname, lastname).
Gerben,
I think only adding a few indexes to the DB won't cause problems for future upgrades.
If you have indexes that really make a difference, post them on CodePlex so those indexes can be added in future releases!
Peter
What Peter said! We don't really have the data volume to test with when we're doing dev so sadly performance is something that really lacks. And also due to the design of Umbraco at the moment it's something that is very hard for us to fix :( (I know, I tried!)
Bump: Gerben would be very interested in what solution you ended up choosing and how well it went!
Bumping again, any solution to this?
I have a database with 76,000 users and I added this index to the database and it seemed to increase performance quite a bit. I haven't thouroughly tested it yet, but so far, so good! :)
CREATE INDEX Idx_cmsMember_LoginName ON cmsMember ("LoginName" ASC)is working on a reply...