Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Going by this : When should I think about upgrading our RDS MySQL instance based on memory usage? for max_connections to be set at 5000 concurrent users,how much should be the RAM capacity be ?

If I add per connection over head for 5000 users, as per what I set the values as shown below, I get :

[ read_buffer_size (0.125) MB + sort_buffer_size (2 MB) + join_buffer_size (2 MB) + read_rnd_buffer_size (1 MB) + thread_stack (0.25 MB) + binlog_cache_size (0.03125 MB) ] * 5000

= ~ 26 GB.

And as per my understanding, innodb_buffer_pool_size we normally give as 75% of RAM - for optimal performance.

So my question is, per connection over head (26 GB) + 75% of RAM = RAM, then RAM capacity should be ~ 104 GB ??

Can any one pls help me out on this.

share|improve this question

Your problem is the innodb buffer size - that is a lot more dependant on database patterns than users. If your database is only 20gb there is no sense in a larger buffer.

And the sensible size there can not be determined from user count only.

So, given you need something for the OS and MySql as a server, then 26gb for the connections - and man, I don't do MySql, but in SQL Server world 5000 connections would be crazy and be for like 100.000 users with connection pooling at the same time.

Assuming that is not just crazy bad programming - I would go with 32 to 64gb memory and the best xeon chips I can get my hands on.... RAM is not your problem here, processing 5000 concurrent SQL statements is. As a start - 64gb memory are cheap in such a server. If the database is larger, then 128gb to 256gb memory would be good. But your problem is not memory, if you really have 5000 concurrent users doing things and not just keeping passive users connected, which is bad programming.

share|improve this answer

well i don't know very much about the memory usage but i have heard somewhere that 1GB RAM is capable of handling 20000 concurrent connections on a 2.5Ghz dual-core processor, i don't remember on what Operating system it is. so according to this 256MB of RAM is sufficient to handle 5000 concurrent users.

PS: I really don't remember the actual stats..

share|improve this answer
2  
If that is hearsay, they likely meant 20.000 users as "on the website". That is not 20.000 concurrent requests.... and even concurrent requests can be a lot less database connections with proper connection pooling. You may look at just 50 or 100 connections with 20.000 users on a website. – TomTom Oct 25 '13 at 9:33

I'm adding this post written by Rolando on sizing the buffer pool as a complement to TomTom's answer.

What's important here and why this answer is just an add on to TomTom's is the concept of sizing the buffer pool to cache just what's needed and not insist on a huge buffer pool because of assumptions about what those needs might be.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.