Help for very bad perf for MySQL

[Michael K. Smith - Adhost]

> -----Original Message-----
> From: owner-freebsd-questions at freebsd.org [mailto:owner-freebsd-
> questions at freebsd.org] On Behalf Of Josh Carroll
> Sent: Tuesday, November 27, 2007 8:12 AM
> To: Ted Mittelstaedt
> Cc: Albert.Shih at obspm.fr; freebsd-questions at freebsd.org
> Subject: Re: Help for very bad perf for MySQL
> 
> > Is Hyperthreading enabled  (by default it is not under
> > FreeBSD)  mysql is heavily dependent on threading, if it is not
> > built and linked into the freebsd threads package you will get
> > poor performance.  Some folks have installed the linux compat libs
> > and linked mysql into the linux threads package and reported good
> > results.
> 
> Actually, on 6.2, it's better to use libthr instead of libpthread.
> This can be done for MySQL only, but to test this without recompiling
> MySQL, he can:
> 
> % echo "libpthread.so.2 libthr.so.2" >> /etc/libmap.conf
> 
> Then restart the mysql server and test again. I noticed a huge
> increase in performance on 6.2 with libthr instead of libpthread. It
> wasn't a 10x improvement, though, so there is definitely something
> else going on with his setup.
> 
> Regards,
> Josh

Here are some things that helped us on a high-volume MySQL server.

-- /etc/sysctl.conf -- (these can be added dynamically from the command
line)
kern.threads.max_groups_per_proc=40000
kern.threads.max_threads_per_proc=40000
kern.maxfiles=65535
kern.maxfilesperproc=65535

-- /boot/loader.conf -- (You'll have to reboot for these to take effect)
kern.maxdsiz="1073741824" # 1GB
kern.dfldsiz="1073741824" # 1GB
kern.maxssiz="134217728" # 128MB

-- /etc/libmap.conf -- (as Josh said)
[mysqld]
libpthread.so.2 libthr.so.2
libpthread.so libthr.so

Regards,

Mike