Issue with COPY FROM a CSV with ~2M records - CrateDB

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

逃跑的棒棒糖 · oracle系列教材（一）- 安装数据库· 2 月前 ·

博学的黄瓜 · 首届长三角书法篆刻大展拟入展(推优)名单公示 ...· 2 月前 ·

近视的针织衫 · 关于规范网络转载版权秩序的通知-其他行政性规 ...· 4 月前 ·

近视的桔子 · 阿里云CDN ...· 6 月前 ·

不敢表白的豆浆 · 女子流产男友要分手 ...· 8 月前 ·

Hello there, I’m currently evaluating CrateDB.

I have a CSV file with ~2M records.

First, I used the try.crate.io script to try it out. I successfully imported the file above using the COPY FROM command.

Then, I went ahead and used the provided docker-compose file to create a cluster with 3 nodes:

CrateDB documentation

Then, I entered the Admin UI and attempted the same COPY FROM command.

However, I’m never able to fully import it. At some random point, it fails with:

JobKilledException[Job killed. Participating node=cratedb02 disconnected.]

JobKilledException[Job killed. Participating node=cratedb03 disconnected.]

When I SELECT COUNT(*) it has inserted some lines there, but not all of them. The number of lines inserted is random: 20k, 50k, 200k, 500k, etc.

I tried to increase the CRATE_HEAP_SIZE from 2g to 8g but there’s no difference.

Am I doing something wrong?

Here are the node logs: Master Node: crate02 · GitHub

Thanks

I’m making some tests using the Crate Python client.

I’m testing inserting 100k records.

I tested with a batch size of 5k, then 8k, then 10k. This is when it failed.

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/urllib3/connectionpool.py", line 699, in urlopen
    httplib_response = self._make_request(
  File "/usr/local/lib/python3.9/site-packages/urllib3/connectionpool.py", line 445, in _make_request
    six.raise_from(e, None)
  File "<string>", line 3, in raise_from
  File "/usr/local/lib/python3.9/site-packages/urllib3/connectionpool.py", line 440, in _make_request
    httplib_response = conn.getresponse()
  File "/usr/local/Cellar/[email protected]/3.9.9/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 1377, in getresponse
    response.begin()
  File "/usr/local/Cellar/[email protected]/3.9.9/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 320, in begin
    version, status, reason = self._read_status()
  File "/usr/local/Cellar/[email protected]/3.9.9/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 289, in _read_status
    raise RemoteDisconnected("Remote end closed connection without"
http.client.RemoteDisconnected: Remote end closed connection without response
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/crate/client/http.py", line 473, in _request
    response = self.server_pool[next_server].request(
  File "/usr/local/lib/python3.9/site-packages/crate/client/http.py", line 154, in request
    return self.pool.urlopen(
  File "/usr/local/lib/python3.9/site-packages/urllib3/connectionpool.py", line 755, in urlopen
    retries = retries.increment(
  File "/usr/local/lib/python3.9/site-packages/urllib3/util/retry.py", line 532, in increment
    raise six.reraise(type(error), error, _stacktrace)
  File "/usr/local/lib/python3.9/site-packages/urllib3/packages/six.py", line 769, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.9/site-packages/urllib3/connectionpool.py", line 699, in urlopen
    httplib_response = self._make_request(
  File "/usr/local/lib/python3.9/site-packages/urllib3/connectionpool.py", line 445, in _make_request
    six.raise_from(e, None)
  File "<string>", line 3, in raise_from
  File "/usr/local/lib/python3.9/site-packages/urllib3/connectionpool.py", line 440, in _make_request
    httplib_response = conn.getresponse()
  File "/usr/local/Cellar/[email protected]/3.9.9/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 1377, in getresponse
    response.begin()
  File "/usr/local/Cellar/[email protected]/3.9.9/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 320, in begin
    version, status, reason = self._read_status()
  File "/usr/local/Cellar/[email protected]/3.9.9/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py", line 289, in _read_status
    raise RemoteDisconnected("Remote end closed connection without"
urllib3.exceptions.ProtocolError: ('Connection aborted.', RemoteDisconnected('Remote end closed connection without response'))
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/Users/rafa/repos/crate/test.py", line 21, in <module>
    cratecur.executemany("""
  File "/usr/local/lib/python3.9/site-packages/crate/client/cursor.py", line 67, in executemany
    self.execute(sql, bulk_parameters=seq_of_parameters)
  File "/usr/local/lib/python3.9/site-packages/crate/client/cursor.py", line 53, in execute
    self._result = self.connection.client.sql(sql, parameters,
  File "/usr/local/lib/python3.9/site-packages/crate/client/http.py", line 396, in sql
    content = self._json_request('POST', self.path, data=data)
  File "/usr/local/lib/python3.9/site-packages/crate/client/http.py", line 522, in _json_request
    response = self._request(method, path, data=data)
  File "/usr/local/lib/python3.9/site-packages/crate/client/http.py", line 513, in _request
    self._drop_server(next_server, ex_message)
  File "/usr/local/lib/python3.9/site-packages/crate/client/http.py", line 581, in _drop_server
    raise ConnectionError(
crate.client.exceptions.ConnectionError: No more Servers available, exception from last server: ('Connection aborted.', RemoteDisconnected('Remote end closed connection without response'))
              The script is very simple, I’m getting data from a MySQL db and inserting into Crate:
from crate import client
import mysql.connector
mysqlconn = mysql.connector.connect(host="127.0.0.1", port=3306, user="root", password="admin", database="dev_db")
mysqlcur = mysqlconn.cursor()
crateconn = client.connect(['http://localhost:4201/'], username='crate', password='')
cratecur = crateconn.cursor()
mysqlcur.execute("""
  SELECT application_id, follow_up_number, follow_up_date, follow_up_date_string, follow_up_result, resulted_by, title_name
  FROM untitled_name
  LIMIT 100000""")
batch_size = 10000
while True:
  rows = mysqlcur.fetchmany(batch_size)
  if len(rows) == 0:
    break
  cratecur.executemany("""
    INSERT INTO followups (application_id, follow_up_number, follow_up_date, follow_up_date_string, follow_up_result, resulted_by, title_name)
    VALUES (?, ?, ?, ?, ?, ?, ?)""",
    rows)
mysqlconn.close()
crateconn.close()
              Hi @rafbgarcia,
the NullPointerException happening on crate02 is strange. I believe it leads to the JobKilledException.
What version of CrateDB are you using?
Can you give some specs (CPU, RAM, disk) for the system you use to evalute CrateDB?

Maybe this could be the issue as running MySQL, three CrateDB nodes, ingest script is potentially overloading your test-setup. This could potentially also explain why your script worked fine for single node CrateDB cluster but does fail when you use the 3-node-cluster.
  Model Name:	MacBook Pro
  Model Identifier:	MacBookPro16,1
  Processor Name:	6-Core Intel Core i7
  Processor Speed:	2.6 GHz
  Number of Processors:	1
  Total Number of Cores:	6
  L2 Cache (per Core):	256 KB
  L3 Cache:	12 MB
  Hyper-Threading Technology:	Enabled
  Memory:	16 GB
Disk: 319GB available Flash Storage
Note that it also fails when I attempt to COPY FROM a CSV file.
Let me know if you need anything else.
Thanks!
              @jayeff here it is: crate01.md · GitHub
The last file contains the logs for the Python script execution.
Removed server http://localhost:4201 from active pool
Let me know if you need something else. I’m up to Zoom if you think it’d be helpful.
No logs, it just crashed.

The logs below are after its reinitialization (seem to be only start up logs)
Yes, looks like start up logs only.
Maybe you can inspect logs with docker logs after the node crashed (check docker ps --all to find container ids for your crashed containers).
Regarding the java.lang.NullPointerException: Cannot invoke "String.length()" because "str" is null": A fix for this is merged and will be included in next batch release. Testing build for 4.8.1 will likely go out in a couple days.
If you want to continue before that with your testing you could use the latest nightly Docker build.
Can you please re-run your COPY FROM import and insert tests with this fix included and let us know if you still see any exceptions. If you do please share them with us including logs.
  COPY_FROM_crate02.md
[2022-05-23T14:45:15,233][INFO ][o.e.c.c.Coordinator      ] [cratedb02] master node [{cratedb01}{onjmxXu0Q5mGvEZqBYve8A}{PCxqmcR3SCGdHGFt5WR8Ww}{172.21.0.4}{172.21.0.4:4300}{http_address=172.21.0.4:4200}] failed, restarting discovery
org.elasticsearch.transport.NodeDisconnectedException: [cratedb01][172.21.0.4:4300][disconnected] disconnected
[2022-05-23T14:45:15,321][INFO ][o.e.c.s.ClusterApplierService] [cratedb02] master node changed {previous [{cratedb01}{onjmxXu0Q5mGvEZqBYve8A}{PCxqmcR3SCGdHGFt5WR8Ww}{172.21.0.4}{172.21.0.4:4300}{http_address=172.21.0.4:4200}], current []}, term: 72, version: 1292, reason: becoming candidate: onLeaderFailure
[2022-05-23T14:45:15,329][WARN ][o.e.c.NodeConnectionsService] [cratedb02] failed to connect to {cratedb01}{onjmxXu0Q5mGvEZqBYve8A}{PCxqmcR3SCGdHGFt5WR8Ww}{172.21.0.4}{172.21.0.4:4300}{http_address=172.21.0.4:4200} (tried [1] times)
org.elasticsearch.transport.ConnectTransportException: [cratedb01][172.21.0.4:4300] connect_exception
	at org.elasticsearch.transport.TcpTransport$ChannelsConnectedListener.onFailure(TcpTransport.java:961) ~[crate-server.jar:?]
	at org.elasticsearch.action.ActionListener.accept(ActionListener.java:56) ~[crate-server.jar:?]
	at org.elasticsearch.action.ActionListener.accept(ActionListener.java:39) ~[crate-server.jar:?]
	at java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:863) ~[?:?]
    This file has been truncated. show original
    There are more than three files. show original
PS: My posts that include external links to gist.github.com were automatically hidden, I believe. I’m getting this message when I attempt to post a gist as a link:
Sorry you cannot post a link to that host.
 rafbgarcia:
PS: My posts that include external links to gist.github.com were automatically hidden, I believe. I’m getting this message when I attempt to post a gist as a link:
Sorry, this is some automatic flagging from discourse when new users post multiple links. I restored your posts.
 rafbgarcia:
@jayeff same problem.
Too bad 
Can you remove…
      restart_policy:
        condition: on-failure
from your docker-compose file (on all three CrateDB nodes)?
With the automatic restart old logs are gone.
I believe by removing it this should allow you to inspect the logs after the node crashes with docker logs. Can you test this please?
              @jayeff I updated my docker settings to allow for 12 max CPU instead of 6 and now I can successfully COPY FROM until the end.
Is this expected?
 jayeff:
I believe by removing it this should allow you to inspect the logs after the node crashes with docker logs . Can you test this please?
              @jayeff I changed CPUs back to 6, removed the restart_policy, ran the COPY FROM, same error happened.
Inspected the dead container. It doesn’t produce any logs about the crash.
It crashes with Code 137, though:
Exit Code 137: Indicates failure as container received SIGKILL (Manual intervention or 'oom-killer' [OUT-OF-MEMORY])
 rafbgarcia:
@jayeff I updated my docker settings to allow for 12 max CPU instead of 6 and now I can successfully COPY FROM until the end.
@jayeff sorry, I had also bumped the memory from 8g to 10g in the Docker settings for that test.
I just tested again. Changing only the memory from 8g to 10g is enough to make it work.
I’m not very familiar with Docker either. My apologies for all of this trouble and thanks for your help!
 rafbgarcia:
I’m not very familiar with Docker either. My apologies for all of this trouble and thanks for your help!
You are welcome and good that you could solve this