1
  1. 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?

  2. how do I calculate maximum memory usage per open mysql connection?

2 Answers 2

3

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

2
  • 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, 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
    – quaie
    Aug 30, 2009 at 16:34
0

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

3
  • 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, 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
    – quaie
    Aug 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.
    – mrdenny
    Aug 31, 2009 at 2:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.