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

　　2.innodb

#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

　　3.mysql讀寫分離配置

<resources>

<db>

<table_prefix><![cdata[]]></table_prefix>

</db>

<default_setup>

<connection>

<host><![cdata[localhost]]></host>

<username><![cdata[root]]></username>

<password><![cdata[]]></password>

<dbname><![cdata[mg_wailian]]></dbname>

<initstatements><![cdata[set names utf8]]></initstatements>

<model><![cdata[mysql4]]></model>

<type><![cdata[pdo_mysql]]></type>

<pdotype><![cdata[]]></pdotype>

<active>1</active>

</connection>

</default_setup>

<default_read>

</default_read>

</resources>

　　3.分離讀寫資料庫，然後主從同步，是一個很好的選擇。

<b></b>

<b>最新内容請見作者的github頁：http://qaseven.github.io/</b>