FreeBSD MySQL Performance Tunning suggestions???

[Rudy]

> MySQL:

> [mysqld]
> port  = 3306
> socket  = /tmp/mysql.sock
> skip-locking
> skip-networking
> skip-name-resolve
> server-id=1
> #2008-02-10
> max_connections=499
> interactive_timeout=100
> wait_timeout=100
> connect_timeout=10
> thread_cache_size=256
> connect_timeout=10
> myisam_sort_buffer_size=32M
> key_buffer=16M
> join_buffer=3M
> record_buffer=3M
> sort_buffer=5M
> table_cache=1024
> #-
> # increase until threads_created doesnt grow anymore
> thread_cache=512
> query_cache_type=1
> query_cache_limit=2M
> query_cache_size=64M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency=4
> set-variable=local-infile=0
> #ng below 5 lines
> init_connect='SET collation_connection = utf8_unicode_ci'
> init_connect='SET NAMES utf8'
> default-character-set=utf8
> character-set-server=utf8
> collation-server=utf8_unicode_ci
> #NG: new variables for fulltext search:
> ft_min_word_len=2
> ft_max_word_len=15
> 
> # Replication Master Server (default)
> # binary logging is required for replication
> log-bin=mysql-bin
> # required unique id between 1 and 2^32 - 1
> # defaults to 1 if master-host is not set
> # but will not function as a master if omitted
> server-id = 1

I don't see innodb tunings... if you have innodb tables, you should use about 4GB of RAM for that.
DOn't forget, Edit your /boot/loader.conf to allow your MySQL to grow up to about 8GB (for 
starters).  People forget this about as often as they forget to put INDEXES on their tables.

Speaking of that!  Turn on your slow_query_log... set it to 5 seconds and monitor the 
slow_query_log file.

Are you going to replicate?  If not, disable log-bin (you have skip-networking, so I assume you are 
not setting up a master/slave).  Only reason to do log-bin is to fill your disk up with files you 
will never yse.

Up query_cache_limit=8M (depends, may not matter for your site).  (That is the max limit per query 
to cache...).  Keep an eye on your cache fill rate and up those values if you need to.

Keep an eye on your table_cache... table_cache=1024 may be fine, but if you have 4000 tables, that 
may need to be higher.

Set your key_buffer higher (depending on your site) --- several 100GB.  Connections can *probably* 
be lower... the only reason to have a real hig number for connections is to get your machine to 
swap.  :p

Oh, check out the my-huge.cnf file that comes with MySQL... I think it is in 
/usr/local/share/mysql/ or somewhere.... good tips there.


Good luck.

Rudy