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

Question

I want to find out the memory cost of each mysql process. Right now, I have 800+ mysql connections but I have no idea when will the system start swapping. I use FreeBSD.

Answer 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:

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.

Answer 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.

Answer 3

use mysqltunner.pl to show you report

-------- Performance Metrics -----------------------------------------------
[--] Total buffers: 168.0M global + 2.8M per thread (10 max threads)
[OK] Maximum reached memory usage: 179.0M (18.04% of installed RAM)
[OK] Maximum possible memory usage: 195.5M (19.70% of installed RAM)

2.8M is my every thread memory costs including connection and 195.5M is total costs

Answer 4

You can determine the maximum memory cost per-connection to the server fairly easily from your configuration variables.

• The MySQL documentation and MySQL Performance Blog have some detailed information.
• Some utlities, such as MySQLTuner and mysql-memory-profile, can provide this information in a ready-to-use fashion.

Obtaining your actual memory usage per-connection is unfortunately a lot more difficult because of the way in which mysqld threads.

Answer 5

Another good tool that I use for understanding/configuring how mysql uses memory is mysqlreport.