Hi,
> [snip]
> I am having problems with my php+mysql driven site, running on a freebsd
> box. Mysql tends to lock up once-twice a day (or even more) and stop
> responding to queries.
Standard FreeBSD installation is not the happiest MySQL environment.
If your problem is really MySQL related, here are some optimizations that
might help you:
1. Instead of using the FreeBSD binary provided by mysql.com,
install MySQL with Linux Threads from ports.
This is supposedly "experimental", but all I can tell you - having
run MySQL-LT on multiple FreeBSD boxes for over a year -
is that MySQL under high load is much more stable with Linux
Threads then without them. So, cvsup your ports tree, then:
cd /usr/ports/mysql323-client
make WITH_LINUXTHREADS=YES install
cd /usr/ports/mysql323-server
make WITH_LINUXTHREADS=YES install
If you are not symlinking your databases, considering implementing
Jeremy's patch to make MySQL-LT even more stable:
http://jeremy.zawodny.com/blog/archives/000264.html#000264
(I don't know if this patch was merged into the mysql323-server port
Makefile).
2. Your current MySQL configuration's could (in theory)
use over 6 GBs of RAM. I am sure that you will find that
this most exceeds your machine's modest RAM resources.
Aside from changing your MySQL configuration, I suggest
that you add more memory 256 MB of RAM is not much
even for a desktop PC. Adding more memory is often the
single most effective optimization. Memory is cheap these days.
Until you do that, you'll have to decrease your buffers, query
cache size, max # of connections etc.
3. Read 'man tuning' and perform the optimizations that
apply to your individual situation... such as:
a) make sure that you have softupdates enabled (tunefs)
on the partition/disk that holds your mysql data dir.
b) add kernel optimizations, e.g.:
makeoptions COPTFLAGS="-O -pipe"
maxusers 512
options NMBCLUSTERS=65536
cpu I686_CPU
c) add sysctl optimizations to your /etc/rc.local file:
/sbin/sysctl -w kern.maxfiles=100000
/sbin/sysctl -w kern.maxfilesperproc=100000
/sbin/sysctl -w vfs.vmiodirenable=1
/sbin/sysctl -w kern.ipc.somaxconn=1024
/sbin/sysctl -w net.inet.tcp.always_keepalive=1
/sbin/sysctl -w kern.ipc.shmall=32768
/sbin/sysctl -w net.inet.tcp.inflight_enable=1
ulimit -n 100000
d) mount your mysql partition with noatime parameter
[these are obviously just examples, and you should
consult 'man tuning' and determine what optimizations
are appropriate in *your* situation]
> have_innodb YES
If you are not using innodb, make sure you disable it
to free some extra RAM.
> max_connections 1000
This is way too much and almost twice as much
as a LT-enabled MySQL can handle (without
patching LT). Decrease it to something like 200.
> read_buffer_size 2093056
This needs to be decreaed.
> query_cache_size 20971520
This is way too much for a 256 MB configuration.
> sort_buffer_size 4194296
Too much.
Hope this helps...
Good luck and let me know how it goes!
Simon Grabowski
GetResponse.com
---------------------------------------------------------------------
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
