[MCOL-4790] LDIF & cache_inserts - table lock stuck - unstable - crashes aria table on restart Created: 2021-07-01  Updated: 2021-10-05  Resolved: 2021-10-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: cache_insert
Affects Version/s: None
Fix Version/s: 6.1.1

Type: Bug Priority: Critical
Reporter: Allen Herrera Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Attachments: File DDL.sql     File loadDataInfile.bash     File test2.csv    
Issue Links:
PartOf
is part of MCOL-4769 Complete ColumnStore Insert Cache Part 1 Closed
Sprint: 2021-9, 2021-10, 2021-11, 2021-12

 Description   

When constantly loading data into CS with cache_inserts enabled via LDIF - The flush to CS freezes up, table locks and doesn't clear. Restarting Columnstore and MariaDB results in crashed aria tables. This is an unreliable process for clients needing to constantly load data.

Desired Outcome: Table doesn't excessively lock and crash requiring mariadb restart and aria_chk.

Steps to reproduce
-----------------------------------------------------------
deploy an aws t2.xlarge instance

# Update yum and install CS
 
yum update -y
 
yum install wget -y
 
wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
 
chmod +x mariadb_es_repo_setup
 
./mariadb_es_repo_setup --token="ca9d5fe2-ef44-438f-87ee-784ecc4fb9b0" --apply --mariadb-server-version="10.5" --mariadb-maxscale-version=2.5 
yum -y install MariaDB-server
 
systemctl start mariadb
 
yum install MariaDB-columnstore-engine.x86_64 -y
 
 
 
# add cache_inserts to config file
 
vi /etc/my.cnf.d/server.cnf
 
columnstore-cache-inserts=ON
 
 
 
# Restart mariadb & CS
 
systemctl restart mariadb
 
systemctl restart mariadb-columnstore
 
 
 
 
 
# Transfer the attached bash script & DDL & csv 
cd /tmp/
 
mariadb < DDL.sql
 
 
 
# On window 1
 
tail -f /var/log/messages
 
 
 
# On window 2
 
bash loadDataInfile.bash
 
 
 
# Notice after 137 iterations notice the flush to CS takes FreqRatioEvenTrack[root@ip-172-31-5-47 tmp]# mariadb test -e "show processlist;"
 
+-----+------+-----------+------+---------+------+------------+----------------------------------------------------------------------------------+----------+
 
| Id  | User | Host      | db   | Command | Time | State      | Info                                                                             | Progress |
 
+-----+------+-----------+------+---------+------+------------+----------------------------------------------------------------------------------+----------+
 
| 148 | root | localhost | test | Query   |  667 | table lock | LOAD DATA INFILE '/tmp/test2.csv' INTO TABLE CCB_CI_SER FIELDS TERMINATED BY ',' |    0.000 |
 
| 160 | root | localhost | NULL | Query   |    0 | starting   | show processlist                                                                 |    0.000 |
 
+-----+------+-----------+------+---------+------+------------+----------------------------------------------------------------------------------+----------+
 
 
 
# final message in /var/log/messages
 
Jul  1 20:39:57 ip-172-31-5-47 env: ExeMgr[28955]: 57.624387 |2147483796|0|0| D 16 CAL0041: Start SQL statement: select objectid from systable where schema='test' and tablename='CCB_CI_SER' --tableRID/; ||
 
Jul  1 20:39:57 ip-172-31-5-47 env: ExeMgr[28955]: 57.642600 |2147483796|0|0| D 16 CAL0042: End SQL statement



 Comments   
Comment by Gagan Goel (Inactive) [ 2021-09-28 ]

This is fixed by using columnstore_cache_use_import system variable. When this variable is set to ON, cache flush into ColumnStore is performed using cpimport. This variable is set to OFF by default. By default, we perform batch inserts for the cache flush.

Comment by Gagan Goel (Inactive) [ 2021-09-29 ]

For QA: Turn on the read-only system variable, columnstore_cache_inserts. This can only be set either in the .cnf file or on mysqld startup by passing --columnstore_cache_inserts as an argument.
Test the problem: Run the attached DDL.sql file, followed by the bash script (loadDataInfile.bash). You can change the loop counter in the bash script from 200 to, say, 500. You should notice the LDI statements hanging after a certain loop iteration. The loop should continue to hang forever.

Test the solution: With a fresh DB install, in the mariadb client session, execute this statement: set global columnstore_cache_use_import=ON;. Make sure columnstore_cache_inserts is also enabled. Now re-run the above bash script. You should notice the loop executes to completion. Ensure the number of records inserted into the table is the same as: number of records in the csv file X loop count.

To compare the performance of the LDI with the cache disabled, restart mariadb server but this time with columnstore_cache_inserts set to OFF. Compare the loop execution timings when the cache is disabled, with the scenario when the cache is enabled and columnstore_cache_use_import=ON.

Comment by Daniel Lee (Inactive) [ 2021-10-04 ]

I don't see PRs associated with this ticket. Is this ticket for 6.1.2?

Comment by Gagan Goel (Inactive) [ 2021-10-04 ]

dleeyh We did not create a dedicated PR for this issue. The variable columnstore_cache_use_import was added in 6.1.1 (it's just that we never assigned it for QA testing earlier).

Comment by Daniel Lee (Inactive) [ 2021-10-05 ]

Build verified: 6.1.1-1 (MariaDB Enterprise 10.6.4-1 download from company website)

Repeated LDI 700 times without any issues.

Generated at Thu Feb 08 02:53:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.