Quoting Randy Melder <
randymelder@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
---------------------------------------------------
PLUG-discuss mailing list -
PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)