Semi OT: MySQL memory probs...

[alex at crackpot.org]

Quoting Randy Melder <randymelder at gmail.com>:

> [mysqld]
> log-bin=/usr/local/mysql_binlogs/auca-sql01m
> max_binlog_size=10240M
> server-id=1
> default-storage-engine=innodb
> innodb_flush_log_at_trx_commit=1
> sync_binlog=1
> key_buffer_size=2000M
> sort_buffer_size=200M
> innodb_buffer_pool_size=200M
> read_buffer_size=50M
> innodb_log_buffer_size=8M
>

Memory-related configuration options in Mysql break into 2 categories  
: per-thread and server-wide.  Your maximum memory consumption will be  
( per-thread-buffers * max-clients ) + server-wide-buffers.

These are per-thread:
thread_stack
net_buffer_length
read_buffer_size
join_buffer_size
tmp_table_size
myisam_sort_buffer_size
sort_buffer_size
max_allowed_packet
read_rnd_buffer_size

These are server-wide:
innodb buffer pool
key_buffer_size
query_cache_size
innodb_additional_memory_pool_size

It can be tricky to find optimum settings.  Not every per-thread  
buffer type is actually allocated by each thread.  Many are only used  
as-needed.  Also, there are several types of buffers which can be  
allocated by a thread multiple times if a single one isn't enough.   
Setting your maximum possible memory usage to be less that your  
physical RAM will probably mean you allow so few clients that you  
won't be able to get any work done.

You need to watch the values in 'SHOW GLOBAL STATUS' over time to  
determine how often each of your buffer types are actually used.  It  
can also be helpful to capture the output of 'show full processlist'  
when you're having memory troubles.  You might be able to tell if  
you're you having problems because you have lots of clients, or  
because a few threads are hogging all your resources, for instance.

To get some quick bang for your buck, try tuning your key_buffer_size  
and innodb_buffer_pool_size.  If you have mostly MyISAM tables, favor  
the key buffer.  If you have mostly InnoDB tables, favor the InnoDB  
buffer pool.  Since your default storage engine is InnoDB, that's  
likely what you're using mostly, so your 2GB key buffer may be a  
waste.  You can compute a hit rate and 'percent full' for both, which  
will give you an idea of how to adjust them.

BUT : Don't tune anything without first setting up some way of  
monitoring your performance.  You'll just be shooting in the dark and  
maybe make things worse.  Just capturing the output of 'show global  
status' every hour is a good start.

Resources :
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
http://www.oreilly.com/catalog/hpmysql/
http://www.amazon.com/Database-Design-Tuning-Developers-Library/dp/0672327651

The 'MySQL Administrator' GUI tool has a lot of graphing capabilities  
for watching the utilization of various buffers.  I don't use it much,  
but you can look at it to see what the formulas are for determining  
how well various buffers are utilized.  For example, if your key  
buffer has a 100% hit rate and it's only 20% full, you can chop its  
size quite a bit and not affect performance at all.

http://www.mysql.com/products/tools/administrator/

regards,
alex