This topic describes the LOADING_NCI state of in-memory column indexes (IMCIs) on column store nodes and its impacts on the read consistency of column-oriented data.
After a column store node is restarted, you may find that some IMCIs are in the LOADING_NCI state for a period of time by querying the INFORMATION_SCHEMA.IMCI_INDEXES table. The LOADING_NCI state indicates that the mappings between the IMCIs and row-oriented data are being rebuilt. The mappings ensure the real-time high-concurrency data update capability of hybrid transaction/analytical processing (HTAP) services. The duration of the LOADING_NCI state varies from several seconds to several hours based on the amount of column-oriented data and the cluster specifications.
Impact on transactional updates
The data in an IMCI in the LOADING_NCI state can still be updated in real time. However, the data updates are only cached in the IMCI and you cannot query the updated data in real time because the mapping between the IMCI and row-oriented data has not been established.
Impact on read consistency
The data in an IMCI in the LOADING_NCI state can still be read. IMCIs provide read services in different consistency levels based on your parameter settings, as described in the following table.
Index state | Parameter setting | Readability (read consistency) |
LOADING_NCI | imci_enable_read_during_load_nci=ON | Data can be read and the snapshots of data are read. If any IMCI is in the LOADING_NCI state, all IMCIs provide the snapshot read service. |
imci_enable_read_during_load_nci=OFF | For an IMCI whose data is added, deleted, or modified since the last snapshot, the data cannot be read. For an IMCI whose data is not added, deleted, or modified since the last snapshot, the data can be read. | |
COMMITTED | N/A | Updated data can be read in real time. |
Other states | N/A | Data cannot be read. |
Parameter settings
By default, if you set the
imci_enable_read_during_load_nciparameter to ON, IMCIs provide the snapshot read service before all IMCIs enter the COMMITTED state. The version of data that is read is the last complete snapshot before the column store node fails. In this case, you can use an IMCI to accelerate complex queries regardless of whether the IMCI is in the LOADING_NCI or COMMITTED state.NoteIf you set the
imci_enable_read_during_load_nciparameter to ON and one IMCI is in the LOADING_NCI state, all IMCIs provide the snapshot read service.If you set the
imci_enable_read_during_load_nciparameter OFF, IMCIs provide read services at a stricter consistency level.For an IMCI in the COMMITTED state, column-oriented data is used to accelerate complex queries and the latest column-oriented data can be read.
For an IMCI in the LOADING_NCI state, the real-time data cannot be read. Therefore, for an IMCI whose data is not added, deleted, or modified since the last snapshot, which is equivalent to the COMMITTED state, the data can be read. You can enable or disable the snapshot read service for IMCIs in the LOADING_NCI state on column store nodes based on your business requirements.
Parameter scopes and default values
If the version of a PolarDB for MySQL Enterprise Edition cluster is in the range from V8.0.1.1.29 to V8.0.1.1.36, the
imci_enable_read_during_load_nciparameter is globally effective. By default, the parameter is set to OFF. However, the default value of the parameter may be ON for some existing clusters. After you change the value of this parameter for a cluster, you must restart the cluster for the change to take effect.If the version of a PolarDB for MySQL Enterprise Edition cluster is V8.0.1.1.37 or later, the
imci_enable_read_during_load_nciparameter is effective at the session level. By default, the parameter is set to OFF.