Optimize MySQL for large site replication

[bisana]

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 
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
at present connection_timeout is 10 as shown in the reuslt

mysql> show variables like "%timeout%";
+—————————-+———-+
| Variable_name              | Value    |
+—————————-+———-+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+—————————-+———-+
10 rows in set (0.00 sec)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

And for the cache size the parameter is

mysql> show variables like "%cache%";
+——————————+———————-+
| Variable_name                | Value                |
+——————————+———————-+
| binlog_cache_size            | 32768                |
| binlog_stmt_cache_size       | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| max_binlog_stmt_cache_size   | 18446744073709547520 |
| metadata_locks_cache_size    | 1024                 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 0                    |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| stored_program_cache         | 256                  |
| table_definition_cache       | 400                  |
| table_open_cache             | 400                  |
| thread_cache_size            | 0                    |
+——————————+———————-+
18 rows in set (0.00 sec)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

and for the transactional it is

mysql> show variables like "%trans%";
+—————————————–+——-+
| Variable_name                           | Value |
+—————————————–+——-+
| binlog_direct_non_transactional_updates | OFF   |
| slave_transaction_retries               | 10    |
| transaction_alloc_block_size            | 8192  |
| transaction_prealloc_size               | 4096  |
+—————————————–+——-+
4 rows in set (0.00 sec)
‍‍‍‍‍‍‍‍‍‍‍‍

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.


Thanks

[bisana]

Hi All
  I want to update the status, I have change the parameters by adding 

binlog_cache_size = 600M
query_cache_size = 100M
thread_cache_size = 100M
‍‍‍‍‍

to the my.cnf
Now I have

mysql> show global variables like '%cache_size%';
+—————————-+———————-+
| Variable_name              | Value                |
+—————————-+———————-+
| binlog_cache_size          | 629145600            |
| binlog_stmt_cache_size     | 32768                |
| max_binlog_cache_size      | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size  | 1024                 |
| query_cache_size           | 104857600            |
| thread_cache_size          | 16384                |
+—————————-+———————-+
7 rows in set (0.01 sec)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍