How to find out the memory cost of each mysql connection?

Solution 1:

Basic MySQL Memory Information

Assuming you're using mostly InnoDB tables, you can see how much memory is being used in MySQL:

SHOW ENGINE INNODB STATUS

It'll show you a lot of info but you're looking for something like: enter image description here

This database above has little to no activity on it at all.

Maximum memory per connection

If you want to know the maximum amount a connection can possibly use, it's this query:

SELECT ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size
+ 2*@@net_buffer_length
) / (1024 * 1024) AS MEMORY_PER_CON_MB;

The maximum number of connections can be found with:

select @@max_connections;

Average memory per query

If you want the average per query, that's a little tougher. You can take the information from the INNODB status and divide it by the number of connections. In general, I assume each connection, on average, will need about half of the available memory to it. That's been a pretty safe bet for a number of years now. YMMV.

Solution 2:

The amount of memory consumed by each connection is variable, depending on what queries you're running, the size of result sets, and probably the phase of the moon. You'll want to measure current memory usage, and (assuming a bunch here) extrapolate from the current average memory usage of a connection to work out how many connections you can get.

The problem with this method is that memory may not be your limiting factor -- query contention, disk IO, or whatever could kill you before you start swapping.