MySQL tuning

[Lukas Ertl]

Hi,

can someone recommend some values for tuning a MySQL installation running
on FreeBSD 4.9 on a Dual 2.4Ghz Xeon with 4GB RAM?

My my.cnf currently looks like this:

skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
set-variable    = max_connections=500
thread_concurrency = 8

Now I randomly get this error when connecting:

ERROR 1135: Can't create a new thread (errno 12). if you are not out of
available memory, you can consult the manual for a possible OS-dependent
bug.

And the MySQL error log says:

Out of memory;  Check if mysqld or some other process uses all
available memory. If not you may have to use 'ulimit' to allow mysqld to
use more memory or you can add more swap space

This is a plain MySQL 4.0.16 installation from ports, statically linked.

According to top, mysqld uses 515M RAM (148M RSS), and there's plenty of
RAM free (or rather, 'inactive').

Any ideas, hints, tips?

best regards,
le

-- 
Lukas Ertl                             eMail: l.ertl at univie.ac.at
UNIX Systemadministrator               Tel.:  (+43 1) 4277-14073
Vienna University Computer Center      Fax.:  (+43 1) 4277-9140
University of Vienna                   http://mailbox.univie.ac.at/~le/