MySQL : limit the number of clients served concurrently

Question

I am experiencing a problem where the server runs out of memory in specific scenarios. one solution I am thinking about is to limit the number of clients served concurrently, but have the extra clients wait for a free slot instead of having them get a "too many connections error". is this possible?
how do I calculate maximum memory usage per open mysql connection?

petre · Answer 1 · 2009-08-30 09:29:04Z

up vote 3 down vote

take a look in my.cnf (should be somewhere in /etc or /etc/mysql, depending on your distro); there you have several options for fine tuning, like

and some query cache options

answered Aug 30 '09 at 9:29

petre
78728

	this does not really answer my question. limiting max_connections results in an error if the number of connections is reached (instead of the desired 'wait for a free slot' behavior). – Omry Aug 30 '09 at 14:53
	I don't think you can setup a waiting queue; all connections coming after the "slots" have been filled up are discarded – petre Aug 30 '09 at 16:34

feedback

mrdenny · Answer 2 · 2009-08-30 10:02:54Z

up vote 0 down vote

How much memory is the database using? How much is installed? What OS are you running MySQL on?

answered Aug 30 '09 at 10:02

mrdenny♦
20.7k11745

	Debian lenny, and the machine has 4GB of RAM and is also doing other things. lets assume mysql cannot handle the peak usage (with the required max_allowed_packet - there are big inserts). is there a way to achieve what I specified by tweaking mysql? – Omry Aug 30 '09 at 14:56
	key_buffer = 512M max_allowed_packet = 64M thread_stack = 128K thread_cache_size = 32K max_connections = 350 table_cache = 256 thread_concurrency = 8 sort_buffer_size = 64M wait_timeout = 20 query_cache_limit = 64M query_cache_size = 512M query_alloc_block_size = 4M query_prealloc_size = 128M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M ########## try those or modify the left values to your needs – petre Aug 30 '09 at 22:12
	If changing those settings doesn't correct the issue, you may need to increase the amount of RAM in the server. 4 Gigs for a busy server isn't a whole lot. – mrdenny♦ Aug 31 '09 at 2:40

feedback

2 Answers