Cluster read/write issues

• Why do cluster writes fail?

• What causes a replica to lag?

• Why is the cluster size a lot larger than the amount of data it stores?

• What should I do in case of an application error?

Why do cluster writes fail?Why do cluster writes fail?

1. If the database storage reaches 95% capacity, the cluster will switch to read-only mode. Check the amount of free space in your storage and increase the storage size, if required. To check the amount of free space:
   1. Navigate to the folder dashboard and select Managed Service for MySQL.
   2. Click the name of your cluster and open the Monitoring tab.
   3. Check the Disk usage chart.
2. Make sure all writes target the master host, not a replica.

What causes a replica to lag?What causes a replica to lag?

1. Check that slave_rows_search_algorithms is set to INDEX_SCAN,HASH_SCAN.
2. For large tables, we recommend using pt-online-schema-change from the Percona Toolkit rather than ALTER TABLE statements to avoid locking.
3. If the lag persists, enable parallel replication. To do this, configure the following settings:

   slave_parallel_type=LOGICAL_CLOCK
   slave_parallel_workers=8
   

4. Run the SHOW SLAVE STATUS; command on the replica. If the Executed_Gtid_Set value remains unchanged for a long time, make sure all the tables have indexes.
5. For continuous database writes on a host with 8 GB or more of RAM, we recommend increasing innodb_log_file_size up to 1 or 2 GB (changing this setting requires a server restart).

Why is the cluster size a lot larger than the amount of data it stores?Why is the cluster size a lot larger than the amount of data it stores?

This occurs because of MySQL® storage features, not due to Managed Service for MySQL® in Yandex Cloud. The following factors affect the amount of storage used:

• Fragmentation
• Index fill factor
• Rollback segment storage
• Data type packing

To find out the actual size of database tables, use the INNODB_SYS_TABLESPACES system table. For more information, see Finding MySQL table size on disk.

What should I do in case of an application error?What should I do in case of an application error?

1. Check whether the disk hosting your cluster has enough free space:
   1. Navigate to the folder dashboard and select Managed Service for MySQL.
   2. Click the name of your cluster and open the Monitoring tab.
   3. Check the Disk usage chart.
2. Check host monitoring charts:
   1. Navigate to the folder dashboard and select Managed Service for MySQL.
   2. Click the name of your cluster and open the Hosts tab.
   3. Go to the Monitoring page.
   4. Find the resource in question: its chart will be approaching or crossing the limit.
   5. Select the other hosts from the drop-down list and check them as well.
3. Check the VM or the server running the application connecting to your database for available resources.