MySQL Max Connections – How to Calculate Max Connections

amazon-rdsmax-connectionsmysql-5.5performance

how to calculate this values to get the best results to resolve this error "too many connection error mysql" and make my API faster
in mysql

   wait_timeout = ??
   max_connections = ??
   max_user_connections = ??
   key_buffer = ??
   query_cache_size = ??
   query_cache_limit = ??
   tmp_table_size = ??
   table_cache = ??
   max_concurrent_transactions ?? 
   max_concurrent_queries ??

and in apache 2

   ServerLimit ??
   StartServers ??
   MaxClients ??
   MinSpareThreads ??
   MaxSpareThreads ??
   ThreadsPerChild ??

what values should i set to this variable to get the best performance

system specification

amazon RDS 100GB DB STORAGE db.t1.micro

1 amazon LBS having two node attached to it each with

   amazon EC2 m1.small 1.7 GB RAM and 8 GB MAGNETIC HARD DISK 
   DEBAIN 7.1 WHEZZY O.S

Best Answer

Before deciding on value for max_connections I suggest that you look at WHY it reached the max_connection condition. A bunch of sleeping/idle connection due to loose coding is very common cause. Check if you have proper value for wait_timeout and adjust if this is your symptom.

Besides that, I would check for INSERT/UPDATE query that holds table locks for too long, as well as slow queries that get piled up once in a while.

Related Solutions

MySQL Configuration – How to Calculate max_connections Variable

Going to post this as an answer, with the relevant information. The basic formulas are:

Available RAM = Global Buffers + (Thread Buffers x max_connections)

max_connections = (Available RAM - Global Buffers) / Thread Buffers

To get the list of buffers and their values:

SHOW VARIABLES LIKE '%buffer%';

Here's a list of the buffers and whether they're Global or Thread:

Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size

Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack

Postgresql – Too many TIME_WAIT connections in PostgreSQL

1500 to 2200 concurrent connections are only useful when you need them. Otherwise, bring the total number of open connections down and start using a connection pool like pgBouncer or PgPool-II. See Number of Connections (on the wiki). We do about 2500 transactions per second on 150 connections.

Don't forget, each connection uses RAM, RAM that could be used to get some real work done. PostgreSQL gets slower when you use too many connections without a connection pool. What "too many" is depends on your hardware and workload, but it's very unlikely that your system will perform better with 2000 than it will with 100; probably much worse.

Best Answer

Related Solutions

MySQL Configuration – How to Calculate max_connections Variable

Postgresql – Too many TIME_WAIT connections in PostgreSQL

Related Question