Cluster read/write issues
Written by
Updated at March 27, 2024
-
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 are cluster writes failing?
- 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:
- Go to the folder page and select Managed Service for MySQL.
- Click the cluster name and go to the Monitoring tab.
- Check the Disk usage chart.
- Make sure that the host being written to is the master rather than a replica.
Why does replica lag?
- Check to see that
slave_rows_search_algorithms
is set toINDEX_SCAN,HASH_SCAN
. - Instead of
ALTER TABLE
statements, we recommend using thept-online-schema-change
utility from the Percona Toolkit on large tables to avoid locking. - If the lag persists, enable parallel replication. To do this, configure the following parameters:
slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8
- Run the
SHOW SLAVE STATUS;
command against the replica. If the value ofExecuted_Gtid_Set
remains unchanged for a long time, make sure that all the tables have indexes. - If data is continuously written to the DB, and the host has 8GB RAM or more, we recommend increasing
innodb_log_file_size
to 1 or 2 GB (parameter updates apply on server restart).
Why is the cluster size much bigger than the amount of data stored?
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 table size within a database, access the INNODB_SYS_TABLESPACES
system table. For more information, see Finding MySQL Table Size on Disk
What should I do if I encounter any other application error?
- Check whether there is space available on the disk hosting your cluster:
- Go to the folder page and select Managed Service for MySQL.
- Click the cluster name and go to the Monitoring tab.
- Check the Disk usage chart.
- Check host monitoring charts:
- Go to the folder page and select Managed Service for MySQL.
- Click the cluster name and select the Hosts tab.
- Go to the Monitoring page.
- Find the problem resource: its chart will be approaching or will have crossed the boundary.
- Select the other hosts from the drop-down list and check them as well.
- Check the virtual machine or the server running the application connecting to your database for available resources.