[lug] mysqldump versus snapshot versus..

[steve at badcheese.com]

> based upon your experience, care to share any basic optimization
> techniques? my.cnf settings, etc? :-)

We set our "key_buffer" setting to 1/2 of available mem, which seems to do 
really well for large tables or large indexes.  No matter what kind of DB 
you use, you should 'know' your data and how it's used/queries/updated. 
If it's a small website, then you can just throw it all in a pot and stir 
it up, but if it's for other things, then you need to kind of tune 
accordingly.  Even Oracle can't read your mind with your usage and is able 
to be tuned in lots of different ways.

We had a situation once where we replaced the entire DB every 4 hours.  We 
had a small lag time after replacing the DB because none of the indexes 
had been cached, so we found a way to pre-cache the indexes (prime the DB 
as it were) before making the system live.  Mostly just for performance 
reasons.  We knew that the usage was unusual, but figured out a way to 
make it work.

Our basic my.cnf looks like:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
log-bin=mysql-bin
server-id=1
key_buffer = 1999M

A really tricked-out one looks like:

[mysqld]
[...]
skip-locking
key_buffer = 1999M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
# dual-quad-core = 8 CPUs
thread_concurrency = 16
ft_min_word_len = 3
server-id       = 3
log-bin=mysql-bin

- Steve

-- 
EMAIL: (h) steve at badcheese.com  WEB: http://badcheese.com/~steve