CODEWITHSUNDEEP
mysql
user873578
user873578

Reputation:

Does the amount of MySql users affect MySql performance much?

When a user registers on my site, they have their own table created in one of my databases. This table stores all of the posts that the user makes. What I would also like to do is also generate them their own MySql user - which ONLY has permission to read, write and delete from their table.

Creating that shouldn't be a problem - I've got Google for that.

What I'm wondering is, let's imagine that I clock myself 10,000,000 or more users at one point in the future, would having that many MySql users affect my database performance?

Upvotes: 1

Views: 1183

Answers (2)

ayckoster
ayckoster

Reputation: 6847

Yes it will drop your performance. Usually a server application uses a database connection pool with several connections (say app_user is connected 5 times). Every SQL request is handled by one of these connections. That way the overhead of creating a new connection, handling the query and dropping the connection is reduced to a minimum.

Now in your scenario every user would have his own table with its own user. That means if a user logs into your application he has to open his own connection, as he has to use his specific user account. Now instead of just 5 connections 10,000 connections have to be opened. That would not scale as each connection has its own thread and uses some ram space. Furthermore there are only about 64k ports available for your connections.

So your application would not scale for that many users.

Upvotes: 1

Layke
Layke

Reputation: 53166

For the sake of answering your question, a quick points... before I explain why you are doing it wrong...

The performance hit will come from having massive amount of tables. (The limit is massive so should you ever reach that high, I would for gods sake hope that you recruit someone who can slap your database silly and explain why you have mutilated it so much). Excuse the harshness :)

Okay, now onto how you should actually be doing it.

Multi-Tenancy

First, you need to learn about how to design a database that is designed for multi tenant application. This is exactly what you are creating by the sounds of it, but you are doing it COMPLETELY wrong. I cannot stress that enough.

Here are some resources which you should read immediately.

Then read this question: - How to design a multi tenant mysql database

After you have done that. You should learn about ACL (Access Control Lists).

If you explain what sort of data you are trying to model, I will be happy to update this post with a simple table schema to match what you might require.

Upvotes: 2

Related Questions