Intermittent issue in query execution time

[Yasith]

Hi All,

We are using a Neo4j Cluster in 4 core servers hosted in AWS EC2. The Neo4j version installed is "Neo4j Enterprise 4.3.6".

We currently have a query that takes an average time of execution of less than 5ms. But sometimes it gives an execution time up to 120 seconds intermittently in our production environment.  

Please let me know if anyone has any idea about this performance bottleneck or is there any common issue related to neo4j?

[busymo16]

Hi @Yasith,

Can you provide more information about the use case, query and the model. Without this information it is difficult to help solving this issue. What might be worth checking is also the network problems that might arise from AWS, in case it is possible? Still, this huge difference looks like a problem not related to Neo4j itself (even with the presence of locks, in case you are writing something to the graph). 

[bennu_neo]

As @busymo16 said, you may like sharing the query, query planner and your query.log for this 'buggy' statement.

[Yasith]

I forgot to mention that the query is mainly to retrieve a dataset and it also includes a small write query to update last seen date 

[busymo16]

Hi @Yasith,

I would still be good to have the type of query that you are trying to execute. The reason for that might be to check any speedup that might be possible on the query side. Other than that, check indexes, or the query.log. In query.log you can see the execution time of the query from Neo4j side and if is the case that Neo4j is causing this delay which I would not exclude but it is very less possible. Anyway, if you share the query, query.log, we can help you more, otherwise check also the EC2 instance for any network issues.

[Yasith]

Hi All,

Down below I have added the read query, write query, and the graph model

Read Query -:

const cypher =
`
MATCH
(user:SystemUser{_id:$userId})-[r1:OWN]->(nf:NewsFeed)<-[r2:LINK]-(post:Post:Active)<-[r3:VALUE]-(lw:Leafwire)
MATCH
(user2:SystemUser:Active)-[:CREATE]->(post)
WHERE
toString(user2.blockedStatus) <> "1"
AND NOT
(user)-[:BLOCK]->(user2) AND
toFloat(duration.inseconds(datetime(r2.lastSent), datetime()).minutes) > toFloat($lastDisplayMinutes)
WITH
user,
user2,
r1,
nf,
r2,
post,
r3,
lw
ORDER BY
datetime(post.postCreatedDate) DESC, post._id DESC
LIMIT $limit
OPTIONAL MATCH
(post)<-[r9:LIKE|HAHA|ANGRY|HEART|SAD{status:1}]-(user4:SystemUser)
OPTIONAL MATCH
(post)-[r7:CONTAIN{status:1}]->(pc1:PostComment)
OPTIONAL MATCH
(pc1)<-[r8:CREATE]-(user3:SystemUser:Active)
WHERE
toString(user3.blockedStatus) <> "1" AND
NOT (user)-[:BLOCK]->(user3)
OPTIONAL MATCH
(pc1)<-[r10:LIKE|HAHA|ANGRY|HEART|SAD{status:1}]-(user5:SystemUser)
OPTIONAL MATCH
(post)-[r13:CONSIST_OF]->(psm:PostMedia)
OPTIONAL MATCH
(post)-[r14:CONSIST_OF]->(psv:PostVideo)
OPTIONAL MATCH
(user)-[:CONNECT]-(userFriend:SystemUser)-[:CONNECT]-(user2)
WHERE
user._id <> userFriend._id
WITH
{
_id:r10._id,
USER_ID:user5._id,
CREATED_DATE:r10.createdDate,
USER_STATUS: toString(user5.status),
EMOTION_TYPE:CASE type(r10)
WHEN "LIKE" THEN 1
WHEN "HEART" THEN 2
WHEN "HAHA" THEN 3
WHEN "SAD" THEN 4
WHEN "ANGRY" THEN 5
END
} as commentReactions,
post,
psm,
psv,
user3 as postCommentCreatedUser,
pc1 as postComment,
r9 as postReactionsRelationship,
user4 as postReactedUsers,
user2 as postCreatedUser,
post.score as newsFeedScore,
count(userFriend) as postCreatorMutualConnectionCount
WITH
{
USER_ID:postCommentCreatedUser._id,
POST_COMMENT:postComment.description,
POST_COMMENT_CREATED_DATE:toString(postComment.postCommentCreatedDate),
POST_COMMENT_LIKED: NULL,
_id:postComment._id,
FIRST_NAME:postCommentCreatedUser.firstName,
LAST_NAME:postCommentCreatedUser.lastName,
MEMBER_TYPE:toString(postCommentCreatedUser.memberType),
PROFILE_PHOTO:postCommentCreatedUser.photo,
JOB_TITLE:postCommentCreatedUser.jobTitle,
SYSTEM_USER_STATUS:toString(postCommentCreatedUser.status),
COMPANY_ID:postCommentCreatedUser.companyId,
COMPANY_NAME:postCommentCreatedUser.companyName,
IS_EXPERT_CONTRIBUTOR:postCommentCreatedUser.isExpertContributor,
RICH_TEXT:toString(postComment.richText),
IS_COMMENT_UPDATED:postComment.isCommentUpdated,
IS_PRO_MEMBER:postCommentCreatedUser.isProMember,
POST_COMMENT_LIKE : COLLECT(DISTINCT commentReactions),
HEADLINE:postCommentCreatedUser.headline
} as postCommentDetails,
post,
postReactionsRelationship,
postReactedUsers,
postCreatedUser,
psm,
psv,
newsFeedScore,
postCreatorMutualConnectionCount
ORDER BY
datetime(postCommentDetails.POST_COMMENT_CREATED_DATE) ASC
WITH
CASE
WHEN
postCommentDetails.USER_ID IS NOT NULL
THEN
postCommentDetails END AS postCommentDetails,
{
_id:postReactionsRelationship._id,
TYPE:CASE type(postReactionsRelationship)
WHEN "LIKE" THEN 1
WHEN "HEART" THEN 2
WHEN "HAHA" THEN 3
WHEN "SAD" THEN 4
WHEN "ANGRY" THEN 5
END,
USER_ID:postReactedUsers._id,
CREATED_DATE:toString(postReactionsRelationship.createdDate),
MAIN_ELEMENT_ID:null,
MODULE:toString("1"),
ELEMENT_ID:post._id
} as postReactions,
{
_id: postCreatedUser._id,
FIRST_NAME:postCreatedUser.firstName,
LAST_NAME:postCreatedUser.lastName,
MEMBER_TYPE:toFloat(postCreatedUser.memberType),
CITY:postCreatedUser.city,
STATE:postCreatedUser.state,
COUNTRY:postCreatedUser.country,
COMPANY_ID:postCreatedUser.companyId,
COMPANY_NAME:postCreatedUser.companyName,
JOB_TITLE:postCreatedUser.jobTitle,
PROFILE_PHOTO:postCreatedUser.photo,
IS_EXPERT_CONTRIBUTOR:postCreatedUser.isExpertContributor,
HEADLINE:postCreatedUser.headline,
IS_PRO_MEMBER:postCreatedUser.isProMember,
BLOCKED_STATUS:toFloat(postCreatedUser.blockedStatus)
}as postCreatorDetails,
{
FILE_NAME : psm.fileName,
TYPE : toString(psm.type)
} as postMedia,
{
FILE_NAME: psv.fileName,
TYPE: toString(psv.type),
STATUS: toFloat(psv.status)
} as postVideo,
post,
newsFeedScore,
postCreatorMutualConnectionCount
WITH
{
_id:post._id,
POST_CREATOR:post.postCreator,
POST_DESCRIPTION:post.postDescription,
POST_MEDIA:postMedia,
POST_VIDEO:postVideo,
POST_TYPE:toString(post.postType),
POST_STATUS: toString(post.postStatus),
POST_CREATED_DATE:toString(post.postCreatedDate),
POST_UPDATED_DATE:toString(post.postUpdatedDate),
POST_COMMENT:collect(DISTINCT postCommentDetails)[..10],
POST_CREATOR_DETAILS:postCreatorDetails,
POST_LIKE:collect(DISTINCT postReactions),
POST_LIKE_COUNT:toString(size(collect(DISTINCT postReactions))),
POST_DISLIKE:NULL,
POST_COMMENT_COUNT:toString(size(collect(DISTINCT postCommentDetails))),
POST_COVER:NULL,
RICH_TEXT:toString(post.richText),
GROUP_IDS:[],
IS_ACTIVE_GROUP:FALSE,
IS_UPDATED:post.isUpdated,
NEWSFEED_SCORE:toString(newsFeedScore),
MUTUAL_FRIENDS_COUNT:toString(postCreatorMutualConnectionCount)
}as postDetails
RETURN
DISTINCT postDetails
ORDER BY
datetime(postDetails.POST_CREATED_DATE) DESC
`

Write Query -: 

let cypher = `
MATCH
(user:SystemUser{_id:$userId})-[r1:OWN]->(nf:NewsFeed)<-[r2:LINK]-(post:Post:Active)
WHERE
post._id IN $postIdArray
SET
r2.lastSent = datetime($updatedTime)
`

Graph Model -: 

 

Here is the link to download the query log file 

[Yasith]

Hi All,

Down below I have added the read query, write query, graph model, and the link to download query log file

Read Query -: 

const cypher =
`
MATCH
(user:SystemUser{_id:$userId})-[r1:OWN]->(nf:NewsFeed)<-[r2:LINK]-(post:Post:Active)<-[r3:VALUE]-(lw:Leafwire)
MATCH
(user2:SystemUser:Active)-[:CREATE]->(post)
WHERE
toString(user2.blockedStatus) <> "1"
AND NOT
(user)-[:BLOCK]->(user2) AND
toFloat(duration.inseconds(datetime(r2.lastSent), datetime()).minutes) > toFloat($lastDisplayMinutes)
WITH
user,
user2,
r1,
nf,
r2,
post,
r3,
lw
ORDER BY
datetime(post.postCreatedDate) DESC, post._id DESC
LIMIT $limit
OPTIONAL MATCH
(post)<-[r9:LIKE|HAHA|ANGRY|HEART|SAD{status:1}]-(user4:SystemUser)
OPTIONAL MATCH
(post)-[r7:CONTAIN{status:1}]->(pc1:PostComment)
OPTIONAL MATCH
(pc1)<-[r8:CREATE]-(user3:SystemUser:Active)
WHERE
toString(user3.blockedStatus) <> "1" AND
NOT (user)-[:BLOCK]->(user3)
OPTIONAL MATCH
(pc1)<-[r10:LIKE|HAHA|ANGRY|HEART|SAD{status:1}]-(user5:SystemUser)
OPTIONAL MATCH
(post)-[r13:CONSIST_OF]->(psm:PostMedia)
OPTIONAL MATCH
(post)-[r14:CONSIST_OF]->(psv:PostVideo)
OPTIONAL MATCH
(user)-[:CONNECT]-(userFriend:SystemUser)-[:CONNECT]-(user2)
WHERE
user._id <> userFriend._id
WITH
{
_id:r10._id,
USER_ID:user5._id,
CREATED_DATE:r10.createdDate,
USER_STATUS: toString(user5.status),
EMOTION_TYPE:CASE type(r10)
WHEN "LIKE" THEN 1
WHEN "HEART" THEN 2
WHEN "HAHA" THEN 3
WHEN "SAD" THEN 4
WHEN "ANGRY" THEN 5
END
} as commentReactions,
post,
psm,
psv,
user3 as postCommentCreatedUser,
pc1 as postComment,
r9 as postReactionsRelationship,
user4 as postReactedUsers,
user2 as postCreatedUser,
post.score as newsFeedScore,
count(userFriend) as postCreatorMutualConnectionCount
WITH
{
USER_ID:postCommentCreatedUser._id,
POST_COMMENT:postComment.description,
POST_COMMENT_CREATED_DATE:toString(postComment.postCommentCreatedDate),
POST_COMMENT_LIKED: NULL,
_id:postComment._id,
FIRST_NAME:postCommentCreatedUser.firstName,
LAST_NAME:postCommentCreatedUser.lastName,
MEMBER_TYPE:toString(postCommentCreatedUser.memberType),
PROFILE_PHOTO:postCommentCreatedUser.photo,
JOB_TITLE:postCommentCreatedUser.jobTitle,
SYSTEM_USER_STATUS:toString(postCommentCreatedUser.status),
COMPANY_ID:postCommentCreatedUser.companyId,
COMPANY_NAME:postCommentCreatedUser.companyName,
IS_EXPERT_CONTRIBUTOR:postCommentCreatedUser.isExpertContributor,
RICH_TEXT:toString(postComment.richText),
IS_COMMENT_UPDATED:postComment.isCommentUpdated,
IS_PRO_MEMBER:postCommentCreatedUser.isProMember,
POST_COMMENT_LIKE : COLLECT(DISTINCT commentReactions),
HEADLINE:postCommentCreatedUser.headline
} as postCommentDetails,
post,
postReactionsRelationship,
postReactedUsers,
postCreatedUser,
psm,
psv,
newsFeedScore,
postCreatorMutualConnectionCount
ORDER BY
datetime(postCommentDetails.POST_COMMENT_CREATED_DATE) ASC
WITH
CASE
WHEN
postCommentDetails.USER_ID IS NOT NULL
THEN
postCommentDetails END AS postCommentDetails,
{
_id:postReactionsRelationship._id,
TYPE:CASE type(postReactionsRelationship)
WHEN "LIKE" THEN 1
WHEN "HEART" THEN 2
WHEN "HAHA" THEN 3
WHEN "SAD" THEN 4
WHEN "ANGRY" THEN 5
END,
USER_ID:postReactedUsers._id,
CREATED_DATE:toString(postReactionsRelationship.createdDate),
MAIN_ELEMENT_ID:null,
MODULE:toString("1"),
ELEMENT_ID:post._id
} as postReactions,
{
_id: postCreatedUser._id,
FIRST_NAME:postCreatedUser.firstName,
LAST_NAME:postCreatedUser.lastName,
MEMBER_TYPE:toFloat(postCreatedUser.memberType),
CITY:postCreatedUser.city,
STATE:postCreatedUser.state,
COUNTRY:postCreatedUser.country,
COMPANY_ID:postCreatedUser.companyId,
COMPANY_NAME:postCreatedUser.companyName,
JOB_TITLE:postCreatedUser.jobTitle,
PROFILE_PHOTO:postCreatedUser.photo,
IS_EXPERT_CONTRIBUTOR:postCreatedUser.isExpertContributor,
HEADLINE:postCreatedUser.headline,
IS_PRO_MEMBER:postCreatedUser.isProMember,
BLOCKED_STATUS:toFloat(postCreatedUser.blockedStatus)
}as postCreatorDetails,
{
FILE_NAME : psm.fileName,
TYPE : toString(psm.type)
} as postMedia,
{
FILE_NAME: psv.fileName,
TYPE: toString(psv.type),
STATUS: toFloat(psv.status)
} as postVideo,
post,
newsFeedScore,
postCreatorMutualConnectionCount
WITH
{
_id:post._id,
POST_CREATOR:post.postCreator,
POST_DESCRIPTION:post.postDescription,
POST_MEDIA:postMedia,
POST_VIDEO:postVideo,
POST_TYPE:toString(post.postType),
POST_STATUS: toString(post.postStatus),
POST_CREATED_DATE:toString(post.postCreatedDate),
POST_UPDATED_DATE:toString(post.postUpdatedDate),
POST_COMMENT:collect(DISTINCT postCommentDetails)[..10],
POST_CREATOR_DETAILS:postCreatorDetails,
POST_LIKE:collect(DISTINCT postReactions),
POST_LIKE_COUNT:toString(size(collect(DISTINCT postReactions))),
POST_DISLIKE:NULL,
POST_COMMENT_COUNT:toString(size(collect(DISTINCT postCommentDetails))),
POST_COVER:NULL,
RICH_TEXT:toString(post.richText),
GROUP_IDS:[],
IS_ACTIVE_GROUP:FALSE,
IS_UPDATED:post.isUpdated,
NEWSFEED_SCORE:toString(newsFeedScore),
MUTUAL_FRIENDS_COUNT:toString(postCreatorMutualConnectionCount)
}as postDetails
RETURN
DISTINCT postDetails
ORDER BY
datetime(postDetails.POST_CREATED_DATE) DESC
`

Write Query -: 

let cypher = `
MATCH
(user:SystemUser{_id:$userId})-[r1:OWN]->(nf:NewsFeed)<-[r2:LINK]-(post:Post:Active)
WHERE
post._id IN $postIdArray
SET
r2.lastSent = datetime($updatedTime)
`

Graph Model -: 

Here is the link to download the query log file