Magento数据库配置选项，以及mysql 读写分离

1.数据库配置

[mysqld]
key_buffer = 512M
max_allowed_packet = 64M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
tmp_table_size = 128M
query_cache_size = 96M
query_cache_type = 1
thread_cache_size = 8
max_connections = 400
wait_timeout = 300

2innodb

#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

example1：

innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_buffer_pool_size = 3G
innodb_data_file_path = ibdata1:3G;ibdata2:1G:autoextend
innodb_autoextend_increment=512

example2：

# Run a 64-bit operating system. Do not exceed 2GB memory for the following
# combination on 32-bit systems:
# innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size +
# read_buffer_size + binlog_cache_size) + max_connections*2MB

# On dedicated db server assign up to 80% of free memory to innodb buffer
# On combined web/db server take into account apache instances and php
innodb_buffer_pool_size         = 768M  #Data Page Memory Buffer

# Table metadata requires 2-16M, Magento only has abt 330 tables
innodb_additional_mem_pool_size = 2M    #Data Dictionary < 16M

# SHOW GLOBAL STATUS Innodb_log_waits should be 0 or the next setting
# innodb_log_file_size is too small. Holds changed data for lazy write to
# innodb database. To change size, database must be flushed to disk, database
# backed up, mysqld server must be downed, log files renamed, change
# my.cnf and mysqld brought back up. Log files will be recreated in
# proper number and size
innodb_log_file_size            = 100M  #Transaction Log up to 1/4 Buffer Pool
#innodb_log_files_in_group      = 2     #Default is 2

# Buffer for transactions to be written to log file
innodb_log_buffer_size          = 2M    #Transaction Memory Buffer 1-8M

#Recommended (2xcores)+disks, KEEP LOW < 16 More is not better tune for
#best performance
innodb_thread_concurrency       = 3

#Set this up before you install Magento. Create primary innodb table at 2GB
#and secondary autoextend table for growth
#Changing this after you've gone live requires eliminating the ibdata files and log files
innodb_data_file_path = ibdata1:2048M;ibdata2:50M:autoextend

#Amount to add on autoextend. Keep this large to keep blocks in file contiguous
innodb_autoextend_increment = 512M

2mysql读写分离配置，app/etc/config.xml

 <resources>
            <default_setup>
                <connection>
                    <host>localhost</host>
                    <username/>
                    <password/>
                    <dbname>magento</dbname>
                    <model>mysql4</model>
                    <initStatements>SET NAMES utf8</initStatements>
                    <type>pdo_mysql</type>
                    <active>0</active>
                </connection>
            </default_setup>
            <default_write>
                <connection>
                    <use>default_setup</use>
                </connection>
            </default_write>
            <default_read>
                <connection>
                    <use>default_setup</use>
                </connection>
            </default_read>
            <core_setup>
                <setup>
                    <module>Mage_Core</module>
                </setup>
                <connection>
                    <use>default_setup</use>
                </connection>
            </core_setup>
            <core_write>
                <connection>
                    <use>default_write</use>
                </connection>
            </core_write>
            <core_read>
                <connection>
                    <use>default_read</use>
                </connection>
            </core_read>
        </resources>

3.分离读写数据库，然后主从同步，是一个很好的选择。

读操作全部到达192.168.1.20服务器，写操作全部在192.168.1.10进行。注意，不要在local.xml中修改这个配置。虽然local.xml的配置最优先，但是默认配置有use节点，只要有use节点，就使用use节点的适配器，所以需要在config.xml中直接修改（不要出现use节点）。

每个模块都可以指定自己的读写适配器，如果没有指定就使用默认的配置。这个为读压力的水平分解提供了内置支持。比如某个模块的读操作非常频繁，可以读定向到单独到一个服务器中。通过指定读写适配器，还可以为模块指定使用另一个库等等。

注意，读写分离时，由于采用了MySQL的主从复制，复制的数据库库决不能进行插入更新这种操作（否则可能导致从库无法同步），实际上，程序可能无法百分百保证初始化一个读适配器后不进行更新操作，所以，为了防止这个情况，只给读适配器这个用户Select权限，就能从数据库级别杜绝这个情况发生。

此处为转载：地址为：http://blog.ifeeline.com/745.html