Cluster read/write issues

• Why are cluster writes failing?

• What causes a replica to lag?

• Why is the cluster size much greater than the amount of data stored?

• What should I do if I encounter any other application error?

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

1. If database storage is 95% full, the cluster will switch to read-only mode. Check the amount of free space in your storage and increase the storage size as required. To check the amount of free space:
   1. Go to the folder page and select Managed Service for MySQL.
   2. Click the cluster name and go to the Monitoring tab.
   3. Check the Disk usage chart.
2. Make sure that the host being written to is the master rather than a replica.

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

1. Check that the slave_rows_search_algorithms parameter is set to INDEX_SCAN,HASH_SCAN.
2. For large tables, we recommend using the pt-online-schema-change utility 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 parameters:

   slave_parallel_type=LOGICAL_CLOCK
   slave_parallel_workers=8
   

4. Run the SHOW SLAVE STATUS; command on the replica. If the value of Executed_Gtid_Set remains unchanged for a long time, make sure all the tables have indexes.
5. If data is continuously written to the DB, and the host has 8GB RAM or more, we recommend increasing innodb_log_file_size up to 1 or 2 GB (parameter updates apply on 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 happens because of the way MySQL® stores data and not because of Managed Service for MySQL® in Yandex Cloud. Factors affecting storage space usage:

• Fragmentation.
• Index fill factor.
• Rollback segment storage.
• Type packaging.

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 there is space available on the disk hosting your cluster:
   1. Go to the folder page and select Managed Service for MySQL.
   2. Click the cluster name and go to the Monitoring tab.
   3. Check the Disk usage chart.
2. Check host monitoring charts:
   1. Go to the folder page and select Managed Service for MySQL.
   2. Click the name of the cluster you need and select the Hosts tab.
   3. Go to the Monitoring page.
   4. Find the problem resource: its chart will be approaching or will have crossed the boundary.
   5. Select the other hosts from the drop-down list and check them as well.
3. Check the virtual machine or the server running the application connecting to your database for available resources.