topic Optimize MySQL for large site replication in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/optimize-mysql-for-large-site-replication/m-p/285017#M238147 <HTML><HEAD></HEAD><BODY><SPAN>Hi All </SPAN><BR /><SPAN>My replication of large site were failing, after trouble shooting, I came to know that I have to optimize MySQL. I have to change(increase)DB connection timeout and transactional cache sizes for the MySQL&nbsp; </SPAN><BR /><SPAN>I am not expert in MySQL DB tuning,I would like to get guidance on the ideal parameters which I can give for this variables</SPAN><BR /><SPAN>at present connection_timeout is 10 as shown in the reuslt</SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />mysql&gt; show variables like "%timeout%";<BR />+—————————-+———-+<BR />| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value&nbsp;&nbsp;&nbsp; |<BR />+—————————-+———-+<BR />| connect_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| delayed_insert_timeout&nbsp;&nbsp;&nbsp;&nbsp; | 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| innodb_lock_wait_timeout&nbsp;&nbsp; | 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| innodb_rollback_on_timeout | OFF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| interactive_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 28800&nbsp;&nbsp;&nbsp; |<BR />| lock_wait_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 31536000 |<BR />| net_read_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| net_write_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 60&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| slave_net_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 3600&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| wait_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 28800&nbsp;&nbsp;&nbsp; |<BR />+—————————-+———-+<BR />10 rows in set (0.00 sec)<BR /><BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>And for the cache size the parameter is </SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />mysql&gt; show variables like "%cache%";<BR />+——————————+———————-+<BR />| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />+——————————+———————-+<BR />| binlog_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 32768&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| binlog_stmt_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 32768&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| have_query_cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | YES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| key_cache_age_threshold&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| key_cache_block_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 1024&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| key_cache_division_limit&nbsp;&nbsp;&nbsp;&nbsp; | 100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| max_binlog_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18446744073709547520 |<BR />| max_binlog_stmt_cache_size&nbsp;&nbsp; | 18446744073709547520 |<BR />| metadata_locks_cache_size&nbsp;&nbsp;&nbsp; | 1024&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| query_cache_limit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 1048576&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| query_cache_min_res_unit&nbsp;&nbsp;&nbsp;&nbsp; | 4096&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| query_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| query_cache_type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | ON&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| query_cache_wlock_invalidate | OFF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| stored_program_cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 256&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| table_definition_cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| table_open_cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| thread_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />+——————————+———————-+<BR />18 rows in set (0.00 sec)<BR /><BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>and for the transactional it is </SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />mysql&gt; show variables like "%trans%";<BR />+—————————————–+——-+<BR />| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value |<BR />+—————————————–+——-+<BR />| binlog_direct_non_transactional_updates | OFF&nbsp;&nbsp; |<BR />| slave_transaction_retries&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 10&nbsp;&nbsp;&nbsp; |<BR />| transaction_alloc_block_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 8192&nbsp; |<BR />| transaction_prealloc_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 4096&nbsp; |<BR />+—————————————–+——-+<BR />4 rows in set (0.00 sec)<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> </SPAN><BR /><BR /><SPAN>As said earlier, not an expert on DB, I am not sure what parameter values should I give. I request members to check MySQL paramaters and guide me for choosing the new value.</SPAN><BR /><BR /><BR /><SPAN>Thanks </SPAN><BR /></BODY></HTML> Thu, 30 May 2013 07:35:27 GMT bisana 2013-05-30T07:35:27Z Optimize MySQL for large site replication https://connect.hyland.com/t5/alfresco-archive/optimize-mysql-for-large-site-replication/m-p/285017#M238147 Hi All My replication of large site were failing, after trouble shooting, I came to know that I have to optimize MySQL. I have to change(increase)DB connection timeout and transactional cache sizes for the MySQL&nbsp; I am not expert in MySQL DB tuning,I would like to get guidance on the ideal parameters Thu, 30 May 2013 07:35:27 GMT https://connect.hyland.com/t5/alfresco-archive/optimize-mysql-for-large-site-replication/m-p/285017#M238147 bisana 2013-05-30T07:35:27Z Re: Optimize MySQL for large site replication https://connect.hyland.com/t5/alfresco-archive/optimize-mysql-for-large-site-replication/m-p/285018#M238148 <HTML><HEAD></HEAD><BODY><SPAN>Hi All </SPAN><BR /><SPAN>&nbsp; I want to update the status, I have change the parameters by adding&nbsp; </SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />binlog_cache_size = 600M<BR />query_cache_size = 100M<BR />thread_cache_size = 100M<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><SPAN>to the my.cnf </SPAN><BR /><SPAN>Now I have </SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />mysql&gt; show global variables like '%cache_size%';<BR />+—————————-+———————-+<BR />| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />+—————————-+———————-+<BR />| binlog_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 629145600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| binlog_stmt_cache_size&nbsp;&nbsp;&nbsp;&nbsp; | 32768&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| max_binlog_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18446744073709547520 |<BR />| max_binlog_stmt_cache_size | 18446744073709547520 |<BR />| metadata_locks_cache_size&nbsp; | 1024&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| query_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 104857600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />| thread_cache_size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 16384&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<BR />+—————————-+———————-+<BR />7 rows in set (0.01 sec)<BR /><BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /></BODY></HTML> Sun, 02 Jun 2013 10:04:00 GMT https://connect.hyland.com/t5/alfresco-archive/optimize-mysql-for-large-site-replication/m-p/285018#M238148 bisana 2013-06-02T10:04:00Z