Quoting Randy Melder : > [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