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.