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:mysqldata/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = C:mysqldata/ #innodb_log_arch_dir = C:mysqldata/ # 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> <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_read> </resources> 3.分离读写数据库，然后主从同步，是一个很好的选择。