Cluster read/write issues
Written by
Updated at September 25, 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.
What causes a replica to lag?
- Check that the
slave_rows_search_algorithms
parameter is set toINDEX_SCAN,HASH_SCAN
. - For large tables, we recommend using the
pt-online-schema-change
utility from the Percona Toolkit rather thanALTER TABLE
statements 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 on the replica. If the value ofExecuted_Gtid_Set
remains unchanged for a long time, make sure 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
up 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.