list of lists with some properties

[joruxs]

Hi,

I am struggling find if it is possible to make a query to collect a list of lists with some of the properties of a node. To start from the beginning I have this

Tag: Player
Properties for tag:
name string
team int
goals int
contractStart int
contractEnd int

I want to query them and group them per team like this:

+-----+-----------+---------+
|team | team_size | members |
|     |           |         |
+-----+-----------+---------+

with the tricky part being that the members column should be a list of [, , , ] for each member.

Example output:

+-----+-----------+----------------------------------------------------------------------------------------+
|team | team_size | members                                                                                |
+-----+-----------+----------------------------------------------------------------------------------------+
|  1  |    2      | [["John Doe", 4, 1661983200, 1662069600], [["James Smith", 15, 1661983200,1662069600]] |
|  .  |    .      | .                                                                                      |
+-----+-----------+----------------------------------------------------------------------------------------+

My attempts so far involve:

MATCH (p:Player)
WITH p.Player.team as team,
count(*) as team_size,
collect(p.Player.name) as members
RETURN team, team_size, members;

(works but has only Player.name for each player in members column)

I tried to get all properties for p instead of just name by changing out

collect(p.Player.name) as members
to
collect(properties(p)) as members

but this query never finishes (maybe it runs out of memory?) and it would list team property redundantly which is not preferred. The machine I am testing on has 16g memory and ubuntu+nebula is all that is running on it.

How can this query be constructed so

1. it works?
2. it only outputs the preferred fields for each member?

[joruxs]

I found that you can create maps with map{key: value, ...} and the following query works:

MATCH (p:Player) \
WITH \
  p.Player.team as team, \
  collect(map{name: p.Player.phoneNumber, \
              goals: p.Player.connections, \
              contract_start: p.Player.contractStart, \
              contract_end: p.Player.contract}) as members, \
  size(collect(p.Player.name)) as team_size \
RETURN team, team_size, members LIMIT 3;

So in that regard we're pretty close to a good solution however(!) when removing the LIMIT to get the complete result set there is a problem with silently disappearing query. I removed the LIMIT and I expect to get ~86000 rows but:

• The result never arrives to client.
• The query disappears from SHOW QUERIES after a couple of minutes.
• CPU usage of all nebula processes return to near 0 on all machines.
• If running the query from nebula-console the query never finishes, I see this in graphd stderr log:
  HeaderServerChannel.cpp:356] Failed to send message: Attempting to send non-header frame that is too large
• If I run the query from a java program the session.execute() just never returns. no logs, no exceptions (waited 25 minutes).

Why does this happen? Why is there no exceptions, feedback or even logs on server side?

[wey-gu]

You could use [foo, bar] to represent the list:

MATCH (p:player) \
WITH \
  p.player.age as age, \
  collect([p.player.name, p.player.age]) as members, \
  size(collect(p.player.name)) as age_size \
RETURN age, age_size, members LIMIT 3;

(root@nebula) [basketballplayer]> MATCH (p:player) \
                               -> WITH \
                               ->   p.player.age as age, \
                               ->   collect([p.player.name, p.player.age]) as members, \
                               ->   size(collect(p.player.name)) as age_size \
                               -> RETURN age, age_size, members LIMIT 3;
+-----+----------+--------------------------------------------------------------------------------------------------+
| age | age_size | members                                                                                          |
+-----+----------+--------------------------------------------------------------------------------------------------+
| 41  | 1        | [["Manu Ginobili", 41]]                                                                          |
| 20  | 1        | [["Luka Doncic", 20]]                                                                            |
| 30  | 4        | [["DeAndre Jordan", 30], ["Russell Westbrook", 30], ["Kevin Durant", 30], ["Blake Griffin", 30]] |
+-----+----------+--------------------------------------------------------------------------------------------------+

About when the limit was removed, the result disappears, did you see any error about timeout? I suspect we need storage_client_timeout_ms to be enlarged in graphd-conf, (ref)

About Attempting to send non-header frame that is too large, I'll check with more guys and come back to you later.

[joruxs]

Cheers for being able to construct lists with the [] notation. It seems obvious in rear sight! I works well for me (if using LIMIT).

For not using limit and getting the whole result set I have the same problem, only now there is absolutely no logs apart from heartbeat notices in one of the machines's metad log file. I check ls -ltr in the logs folder on every server (i run a 3-node cluster) but the modify timestamp on files do not change after starting the query. So no feedback. I also tried raising the storage_client_timeout_ms to
--storage_client_timeout_ms=1200000
Before I had already raised it to 300000.
Ran query, waited 30 minutes.
No change. Client just keep waiting for result, no feedback in logs, query disappears from SHOW QUERIES after about 2 minutes of waiting (same time CPU usage goes down to ~0 on the machine that the client contacted).

[wey-gu]

@cangfengzhs do you have more ideas on this long query?

@joruxs regarding no logs, neither graphd, nor storaged came with logs, right?

[cangfengzhs]

Attempting to send non-header frame that is too large means data is too large( may be greater than 2G). Maybe you can try to give a smaller limit, such as 1000, to ensure successful execution, and then analyze where the execution is slow through the profile.

[joruxs]

Yes I confirm, there are no logs from storaged or graphd. From metad there is only these heartbeat notices on one of the servers (the leader?):
...
I20220915 08:55:27.799940 2059 HBProcessor.cpp:33] Receive heartbeat from "10.xx.xx.107":9779, role = STORAGE
I20220915 08:55:28.492235 2059 HBProcessor.cpp:33] Receive heartbeat from "10.xx.xx.104":9779, role = STORAGE
I20220915 08:55:29.065774 2059 HBProcessor.cpp:33] Receive heartbeat from "10.xx.xx.107":9669, role = GRAPH
I20220915 08:55:29.644594 2059 HBProcessor.cpp:33] Receive heartbeat from "10.xx.xx.108":9779, role = STORAGE
...

I think I have narrowed down what causes the query to not respond. It is that there was a huge amount of players in one team.
Nebula seems to fail returning the result set because the member list for one team is huge (several millions). Yes it is a bit unrealistic for a sports team to hold millions of players on their pay roll but still, could have been some other data where member count was higher.

Controlling max grp_size with a WHERE statement solves it and the query works.

But still it is a bit worrying that there was so no feedback about the problem.

[wey-gu]

Dear @joruxs, for another thing, could you confirm that the graphd doesn't OOM?

And if it's not the case(OOM), for any other reasons, the no-feedback behavior is wrong, we should fix this(then we could ask your help on creating an issue on this :-) )