52

I want to set up a MySQL database for a social networking website for my college.

My app can have at most 10,000 users. What is the maximum number of concurrent MySQL connections possible?

Luke Peterson
  • 8,584
  • 8
  • 45
  • 46
user2075703
  • 567
  • 1
  • 4
  • 8
  • 6
    enough that you don't need to worry. – nathan hayfield Feb 15 '13 at 18:58
  • 1
    can 500-1000 concurrent connections be handled by it? – user2075703 Feb 15 '13 at 19:00
  • 1
    Also should i use mysql with hadoop or memcached to make it faster – user2075703 Feb 15 '13 at 19:00
  • 2
    MySql is unlikely to be your bottleneck. If it is, then you need to design better tables and indices. Don't prematurely optimize. – crush Feb 15 '13 at 19:03
  • 2
    The application that I worked had a connection pool of 30 connections. And it could handle 4000 CONCURRENT users pretty well. I guess it should be more than enough for you. – Slowcoder Feb 15 '13 at 19:07
  • yeah its the base...so whats options do i have for bottleneck? – user2075703 Feb 15 '13 at 19:08
  • If you are bottlenecking with so few concurrent connections, then you need to examine your database structure. Try to normalize your tables into at least `NF3`. Once you have a properly normalized data structure, if you are still struggling (I doubt it), then you need to work on maximizing your indices and keys on each table so that queries can execute faster. – crush Feb 15 '13 at 19:09
  • @Slowcoder: what were your hardware specifications?? like cpu,memory?? could you help on that..bcoz i need to buy a server for my database – user2075703 Feb 15 '13 at 19:10

3 Answers3

81

As per the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_user_connections

 maximum range: 4,294,967,295  (e.g. 2**32 - 1)

You'd probably run out of memory, file handles, and network sockets, on your server long before you got anywhere close to that limit.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • thanks..but should i use something else along with mysql like hadoop or memcached to increase the speed? – user2075703 Feb 15 '13 at 19:03
  • because may be mysql will handle concurrent connections .but surely it will slow down while handling so many connections – user2075703 Feb 15 '13 at 19:04
  • 17
    only if the basic mysql setup isn't good enough. start simple, add complexity later as required. the number of connections isn't an issue. it's what those connections are doing. 10,000 people logged into mysql is nothing. 10,000 users running big complex memory-hogging queries is the problem. And remember, 10,000 people might be on your site, but not all of them will be causing DB queries at the same time. you might only have (say) 50 queries running at a given time. – Marc B Feb 15 '13 at 19:04
  • my question is i have one register page for online exam test and i use `mysql_connect` function for database connection, will MYSQL be able to handle 500 request at a time ?? if not then what is the best way to do that. – Kishan Oza Sep 22 '17 at 10:13
  • what is the difference from what is described above with the short - live connections mentioned here https://dev.mysql.com/blog-archive/mysql-connection-handling-and-scaling/ – Dimitris Papageorgiou Aug 21 '23 at 04:48
45

You might have 10,000 users total, but that's not the same as concurrent users. In this context, concurrent scripts being run.

For example, if your visitor visits index.php, and it makes a database query to get some user details, that request might live for 250ms. You can limit how long those MySQL connections live even further by opening and closing them only when you are querying, instead of leaving it open for the duration of the script.

While it is hard to make any type of formula to predict how many connections would be open at a time, I'd venture the following:

You probably won't have more than 500 active users at any given time with a user base of 10,000 users. Of those 500 concurrent users, there will probably at most be 10-20 concurrent requests being made at a time.

That means, you are really only establishing about 10-20 concurrent requests.

As others mentioned, you have nothing to worry about in that department.

Chase
  • 9,289
  • 5
  • 51
  • 77
crush
  • 16,713
  • 9
  • 59
  • 100
  • Assuming a site of the scale of Facebook, and also assuming that there is no cache layer like Memcached, so that all queries have to hit the database, will MySQL be able to handle such load? – SexyBeast Jun 10 '16 at 18:56
  • @SexyBeast did you get any answer on this or made any understanding so far to share? – Zeeshan Hassan Memon Feb 24 '18 at 19:03
  • My limited understanding is that if you are looking for nearly real time response such as Google auto suggestion, or even moderately fast response, database cannot handle it, it will make it slow by orders of magnitude. – SexyBeast Feb 24 '18 at 19:37
  • @SexyBeast For a scenario like that, you'd want to do some type of caching local to the application. – crush Feb 27 '18 at 19:18
1

I can assure you that raw speed ultimately lies in the non-standard use of Indexes for blazing speed using large tables.

Robert
  • 11
  • 1