Take the 2-minute tour ×
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.

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

3 Answers 3

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
    
Thanks for the info.Looking purely from a connection perspective, just want to know if my calculation is correct : that is RAM CAPACITY = per connection over head (26 GB) + 75% of RAM - for optimal performance. I understand that it depends on database patterns as well.But currently, I am looking at an estimated RAM required based on the calculation I did (Assuming my calculation is correct). –  srj Oct 25 '13 at 13:16
    
The +75% of ram is senseless. Really. Olap? OLTP - the 26gb are good, but the "+75% of RAM" is a useless number like that. –  TomTom Oct 25 '13 at 13:22
    
OLTP..It's actually for an ecom site. innodb_buffer_pool_size as 75% of RAM is a performance tuning recommendation I found in several sites and we were going with that.Please inform if this is wrong as a tuning recommendation.I understand that if we know the application, then we can set this parameter based on the size of the frequently accessed tables' size. But as a generic tuning recommendation 'innodb_buffer_pool_size should be 50 to 75% of RAM is wrong' ? Please let me know. –  srj Oct 25 '13 at 13:41
1  
It is a genearl number, but once you reach non-trivial database sizes, gneral numbers may be generally majorly OFF. If you have 100gb data but only 10gb are regularly used and 128gb memory - guess what, the generic 75% advice will not fit it. All this stuff is fine for home user level small databases - once you get large, you have to sit down and do your homework, i.e. look at how the buffers behave, where your sweet spot is. 75% ram is nice - but how much ram should that be to start with? –  TomTom Oct 25 '13 at 13:43
    
The fact is we are currently doing a capacity planning and thinking of whether to go for EC2 or RDS. So trying to find out for 5000 concurrent users, whether we should go for RDS and if yes, what RAM capacity to go for. –  srj Oct 25 '13 at 13:43

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
1  
VERY unlikely. Basic school math failure. THat is 1048576 kb memory. With 20.000 concurrent connections that is 52kb per user. How much network buffers you think you want to allocate here? Basically speaking: This is a basic school math failure, the numbers do not add up. Not for real work. –  TomTom Oct 25 '13 at 8:42
    
accept my sincere apologies ... i now understand the facts right. before i wrote what i have heard irrespective of the details presented here. –  Roshan Jha Oct 25 '13 at 8:47
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
    
Actually been there seen that in my time at MS SQL Server support. 128gb server, less than 20gb in use by sql server. Large database, but most data only archive - the hot "working set" was less than 8gb. Happens. One need to know the patterns. –  TomTom Oct 25 '13 at 9:31

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.