Optimize MySQL for large site replication

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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)

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