MySQL tuning

[David Miller]

On Wed, 26 Nov 2003, Lukas Ertl wrote:

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


4 GB of ram but only 384 set aside for the buffers?

Sort of an aside, but I did a lot of speed testing with mysql and freebsd
in the 4.7 timeframe.  Enabling SMP actuall slowed mysql related tasks
down by a dramatic factor - as much as 3-10X IIRC.  I have a nice dual
procesor SMP box with 2 GB of ram, and I run it in uniprocessor mode.

FreeBSD also has issues with tuning for more than 2 GB of ram.  I don't
recall that having more in would hurt anything, but one of the kernel
gurus could probably shed some light on it.  If it were my system, I'd be
inclined to pull 2 GB out and run in single CPU mode and see if the
problems continue.


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

How many connections are you trying to make?  How many do you have when
you start getting the messages?

--- David