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?
Add a comment
|
2 Answers
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
- max_connections
- max_connect_errors
- max_delayed_threads
- max_user_connections
and some query cache options
-
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).– OmryAug 30, 2009 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– quaieAug 30, 2009 at 16:34
How much memory is the database using? How much is installed? What OS are you running MySQL on?
-
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?– OmryAug 30, 2009 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– quaieAug 30, 2009 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.– mrdennyAug 31, 2009 at 2:40