Greetings, I am indeed in a very strange situation, to me at least. We've got a good-sized database (about 60gb) over 4k tables. We average about 300 queries per second. We've recently noticed some strange behavior at around 430 connections. We get the "unable to create new thread (errno 11) if you are not out of available ram you may have encountered an OS bug, see the manual." error. When this happens, we usually have a gig or two of ram available. I haven't been able to gleam more details from the manual, so here I am.
Using the formula (key_buffer_size + (record_buffer + sort_buffer)*max_connections) from the docs, it appears that right when the connection count hits about 430, 2gb of ram is being used. After much tweaking of my.cnf, I can get just under 600 connections by reducing record and sort buffers. Conversely, if I set key_buffer to something large (ie. 1gb) I cap out at a couple hundred connections. Is their some kind of limit imposed on allocated memory by mysql? Or should I look elsewhere (Kernel, pthreads)? This question may sound somewhat absurd and if my hosting provider hadn't said "MySQL has a 2gb limit that is very painful to overcome" I wouldn't even ask. But I've been dealing with this for the last two weeks, at this point I'll settle for anything. :) Thank you for your time, Adam Erickson [EMAIL PROTECTED] System specs: 4x700 Xeon (1mb) 4gb Ram Kernel 2.4.9 (SMP, enterprise) MySQL-max v3.23.52 (MySQL AB binary release) my.cnf (hacked to bits at this point): ... [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=4096 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = thread_cache=12 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=12 set-variable = back_log=10 set-variable = thread_stack=64k set-variable = max_connect_errors=20 set-variable = record_rnd_buffer=2M log-bin server-id = 1 set-variable = max_connections=600 replicate-ignore-db=mysql set-variable = wait_timeout=600 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables set-variable = bdb_cache_size=384M set-variable = bdb_max_lock=100000 # Uncomment the following if you are using Innobase tables #innodb_data_file_path = ibdata1:2000M:autoextend:max:4000M;ibdata2:4000M #innodb_data_file_path = ibdata1:2000M;ibdata2:4000M:autoextend:max:6000M innodb_data_file_path = ibdata1:2000M;ibdata2:6000M;ibdata3:6000M;ibdata4:6000M;ibdata5:6000M;ibdata 6:10M:autoextend:max:6000M innodb_data_home_dir = /opt/mysql/data innodb_log_group_home_dir = /opt/mysql/data innodb_log_arch_dir = /opt/mysql/data set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=8M set-variable = innodb_additional_mem_pool_size=8M set-variable = innodb_file_io_threads=8 set-variable = innodb_lock_wait_timeout=50 [mysqldump] ... [safe_mysqld] open-files-limit=8192 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
