MySQL Master/Slave
As mentioned in Part 1 - Infrastructure & Hosting post, you need to take advantage of the MySQL master/slave support in Magento.
Gone are the days of running a single MySQL server for websites. MySQL's Master/slave replication is a great way leverage the power of multiple MySQL servers with very little effort.
Once you have setup your RDS (AWS database instances) as detailed in Part 1 - Infrastructure & Hosting, make sure your local.xml config file looks similar to the below (replace with your database connection details).
Gone are the days of running a single MySQL server for websites. MySQL's Master/slave replication is a great way leverage the power of multiple MySQL servers with very little effort.
Once you have setup your RDS (AWS database instances) as detailed in Part 1 - Infrastructure & Hosting, make sure your local.xml config file looks similar to the below (replace with your database connection details).
<resources> <db> <table_prefix><![CDATA[]]></table_prefix> </db> <default_setup> <connection> <host><![CDATA[RDS_HOST_MASTER:3306]]></host> <username><![CDATA[USER]]></username> <password><![CDATA[PASSWORD]]></password> <dbname><![CDATA[DATABASE]]></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> <use/> <host><![CDATA[RDS_HOST_REPLICA:3306]]></host> <username><![CDATA[USER]]></username> <password><![CDATA[PASSWORD]]></password> <dbname><![CDATA[DATABASE]]></dbname> <type><![CDATA[pdo_mysql]]></type> <model><![CDATA[mysql4]]></model> <pdoType><![CDATA[]]></pdoType> <initStatements>SET NAMES utf8</initStatements> <active>1</active> </connection> </default_read> </resources>
With this basic setup, Magento will push all 'READ' queries to your slave database, and all of the writes and critical reads to your master MySQL server.
Tune MySQL
If you have just released a Magento site and it's not performing don't loose hope, you most likely need to tune MySQL a little to perform better.
There is this great script you can run on your server which helps identify issues you may need to fix. You can find it here:
http://turnkeye.com/blog/magento-performance-optimize-mysql/
Here are some of the config changes 1 usually make to MySQL servers:
There is this great script you can run on your server which helps identify issues you may need to fix. You can find it here:
http://turnkeye.com/blog/magento-performance-optimize-mysql/
Here are some of the config changes 1 usually make to MySQL servers:
key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 max_connections = 120 query_cache_limit = 1M query_cache_size = 48M table_open_cache = 3000Part 4 - Magento Application Tuning
0 comments:
Post a Comment