gerrit query performance issue: search status:merged branch:phone/jb_mr2 in web and never return

[Chunlin Zhang]

Gerrit version: 2.6.1

postgresql: 8.4

server OS: ubuntu 12.04

In gerrit web interface,when I input "status:merged branch:phone/jb_mr2" and press "search",it always showing "Working" and never return query result.

When I use "ssh mygerrit gerrit query status:merged branch:phone/jb_mr2",it can return 25 results after 67 seconds:

'''

type: stats

rowCount: 25

runTimeMilliseconds: 67492

'''

But when I use sql in postgresql( in my local laptop,performance much poorer compare with real server)

run 

'''

SELECT change_key, created_on, last_updated_on, sort_key, owner_account_id, 

       dest_project_name, dest_branch_name, open, status, current_patch_set_id, 

       subject, topic, row_version, change_id, last_sha1_merge_tested, 

       mergeable

  FROM changes WHERE status='M' AND dest_branch_name='refs/heads/phone/jb_mr2';

'''

it return immediately.

When run

'''

SELECT change_key, created_on, last_updated_on, sort_key, owner_account_id, 

       dest_project_name, dest_branch_name, open, status, current_patch_set_id, 

       subject, topic, row_version, change_id, last_sha1_merge_tested, 

       mergeable

  FROM changes WHERE status='M';

'''

It return 87000+ result after 36 seconds.

I am confuse with why "ssh mygerrit gerrit query status:merged branch:phone/jb_mr2" use so long time to finish?

When I open postgresql log to sql in local test environment ,and do a same query in browser, found that log many sql execution, I thought 1 sql is enough,is it?

How can I optimize this performance issue?

Thanks!

[Chunlin Zhang]

More information:

I found when query "branch:phone/jb_mr2" is much more slowly than query "branch:xmodel"( no "phone/" prefix), I think if there are many changes which branch are "phone/*" style,it will spend more time when compare string to judge if it is the branch to query,because it should compare at lease 6th character with 2 string with "phone/" prefix.

--
--
To unsubscribe, email repo-discuss...@googlegroups.com
More info at http://groups.google.com/group/repo-discuss?hl=en
 
---
You received this message because you are subscribed to a topic in the Google Groups "Repo and Gerrit Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/repo-discuss/Q13PGTPu0YA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to repo-discuss...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Shawn Pearce]

"status:merged branch:phone/jb_mr2" is not an indexed dimension that
Gerrit knows about. It doesn't write direct SQL queries to match the
query you input; instead it rewrites it to use a specific index it
knows about and works from there.

For your query its loading all merged changes and for each change
checks if the branch matches. This is obviously not as fast as the SQL
database can perform the same operation. Gerrit also has to check if
you have permission to read the change before it returns the result to
you.

2.8 with the Lucene based index will run the initial filtering much
faster on a query like this, but the permission checking is the same
as in 2.6 and may not be fast enough.

> You received this message because you are subscribed to the Google Groups

> "Repo and Gerrit Discussion" group.

> To unsubscribe from this group and stop receiving emails from it, send an

[Chunlin Zhang]

So I have to ask more questions.

When do you expect the 2.8 stable release?

What can I do before 2.8 stable release?

Can I make a postgresql index myself? If I can, what index should I make?

Thanks!

[Chunlin Zhang]

Can someone give me some advice? I am not so good at database?

Thanks!

[Dave Borowitz]

On Sun, Sep 8, 2013 at 6:20 PM, Chunlin Zhang <zhangc...@gmail.com> wrote:

So I have to ask more questions.

When do you expect the 2.8 stable release?

Just a guess...1-2 months.

 

What can I do before 2.8 stable release?

Well, you could build your own from master.

 

Can I make a postgresql index myself? If I can, what index should I make?

You can make an index but Gerrit will not use it if you don't teach the query rewriter about it. I'm not sure if we would accept a patch to add new SQL indexes given that we hope to get rid of them soon-ish, but you could patch it locally if it's a serious issue. If you really want to do this, let me know; there are a bunch of places you'd have to patch but it's fairly straightforward.

[Chunlin Zhang]

On Wed, Sep 11, 2013 at 10:46 PM, Dave Borowitz <dbor...@google.com> wrote:

On Sun, Sep 8, 2013 at 6:20 PM, Chunlin Zhang <zhangc...@gmail.com> wrote:

So I have to ask more questions.

When do you expect the 2.8 stable release?

Just a guess...1-2 months.

 

What can I do before 2.8 stable release?

Well, you could build your own from master.

 

Can I make a postgresql index myself? If I can, what index should I make?

You can make an index but Gerrit will not use it if you don't teach the query rewriter about it. I'm not sure if we would accept a patch to add new SQL indexes given that we hope to get rid of them soon-ish, but you could patch it locally if it's a serious issue. If you really want to do this, let me know; there are a bunch of places you'd have to patch but it's fairly straightforward.

Thanks for your information and analysis ,I decided to wait for the 2.8 release now. But hope it will release not so late.

[Oleksandr Presich]

Hello guys,

I have similar situation..could you please advise if anything can be changed to improve gerrit query performance for our instance.

Environment: Gerrit 2.5, Postgresql 9.1, tomcat 7, Ubuntu 12.04.

So, I have an account Test Developer.

1) I give him Read access to one project with 1 Change, then go to page: All -> Merged, Gerrit generates ~1000 queries to the Database, so the page loads ~20-30 seconds:

SELECT T.change_key,T.created_on,T.last_updated_on,T.sort_key,T.owner_account_id,T.dest_project_name,T.dest_branch_name,T.open,T.status,T.nbr_patch_sets,T.current_patch_set_id,T.subject,T.topic,T.last_sha1_merge_tested,T.mergeable,T.row_version,T.change_id FROM changes T WHERE T.open='N' AND T.status=$1 AND T.sort_key<$2 ORDER BY T.sort_key DESC","parameters: $1 = 'M', $2 = '0022f8bc00000007'

2) I remove any access for him, then go to page All -> Merged - Situation is the same, ~1000 queries, ~20 seconds to load the page

3) Then I give access to about 50 projects (with one from the first variant) - <10 queries, milliseconds to load a page.

As i can assume, that is a issue with gerrit logic, so, are there any ways to improve gerrit queries?

As workaround I found, that using Search like: status:merged project:MyProject is working quickly (because WHERE project='MyProject" statement adds to the query) BUT any OR statement in the search returns us to the sloooow queries.

[Shawn Pearce]

Yes. :-)

Upgrade to 2.8 and turn on the Lucene secondary index. 2.8 contains a
better search engine, and has made improvements to reduce the number
of database queries that are needed to filter changes for access
control reasons.