Improve performance of sync command with many revisions

[Ryan J Ollos]

The patch was proposed in ​gmessage:trac-dev:2ityvgVZxsQ/KEA6IwY2CAAJ.

tracopt/versioncontrol/git/PyGIT.py

@@ -596 +596 @@
                           key=lambda (name, rev, head): (not head, name))
         return [(name, rev) for name, rev, head in branches]
 
+    def get_refs(self):
+        for refname, rev in self.rev_cache.refs_dict.iteritems():
+            if refname != 'HEAD':
+                yield refname, rev
+
     def get_commits(self):
         return self.rev_cache.rev_dict
 

tracopt/versioncontrol/git/git_fs.py

@@ -100 +100 @@
                 return count > 0
             return False
 
+        def needs_sync():
+            max_holders = 999
+            revs = sorted(set(rev for refname, rev in repos.git.get_refs()))
+            for idx in xrange(0, len(revs), max_holders):
+                revs_ = revs[idx:idx + max_holders]
+                holders = ','.join(('%s',) * len(revs_))
+                args = [self.id]
+                args.extend(revs_)
+                query = 'SELECT COUNT(*) FROM revision ' \
+                        'WHERE repos=%s AND rev IN (' + holders + ')'
+                for count, in self.env.db_query(query, args):
+                    if count < len(revs_):
+                        return True
+            return False
+
         def traverse(rev, seen):
             revs = []
             merge_revs = []
@@ -121 +136 @@
                     revs[idx:idx] = traverse(rev, seen)
             return revs
 
-        while True:
-            repos.sync()
-            repos_youngest = repos.youngest_rev or ''
+        def sync_revs():
             updated = False
             seen = set()
 
@@ -148 +161 @@
                     if feedback:
                         feedback(rev)
 
-            if updated:
-                continue  # sync again
+            return updated
 
+        while True:
+            repos.sync()
+            repos_youngest = repos.youngest_rev or ''
+            if needs_sync() and sync_revs():
+                continue  # sync again
             if meta_youngest != repos_youngest:
                 with self.env.db_transaction as db:
                     db(""" 

[Ryan J Ollos]

On 1.0-stable, timing execution of sync:

trac/versioncontrol/api.py

@@ -376 +376 @@
                 try:
                     repo = self.get_repository(reponame)
                     if repo:
+                        from timeit import default_timer as timer
+                        start = timer()
                         repo.sync()
+                        end = timer()
+                        print(end - start)
                     else:
                         self.log.warning("Unable to find repository '%s' for "
                                          "synchronization",

I did some rough profiling with teo repository configured for sync per request. This is just refreshing the page and no new revisions in repository.

rjollos.git:t12895_repos_sync_performance:

	cached_repository = False	cached_repository = True
persistent_cache = False	0.5s	0.5s
persistent_cache = True	0.04s	0.05s

1.0-stable:

	cached_repository = False	cached_repository = True
persistent_cache = False	0.5s	0.8s
persistent_cache = True	0.04s	0.4s

[Jun Omae]

My proposed changes has off-by-one error. See [a9fd49b97/jomae.git] (jomae.git@t12895).

[Jun Omae]

Max place-holders in sqlite3 is 999 on Windows, however it is 250000 on Ubuntu and Debian.

Python 2.7.10 (default, May 23 2015, 09:44:00) [MSC v.1500 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from trac.test import EnvironmentStub
>>> env = EnvironmentStub()
>>> def q(n):
...     query = 'SELECT * FROM system WHERE 0 IN ({0})'.format(','.join(('%s',) * n))
...     args = list(xrange(n))
...     return env.db_query(query, args)
...
>>> q(999)
[(u'database_version', u'29')]
>>> q(1000)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<stdin>", line 4, in q
  File "C:\venv\trac-1.0.15\lib\site-packages\trac\db\api.py", line 124, in execute
    return db.execute(query, params)
  File "C:\venv\trac-1.0.15\lib\site-packages\trac\db\util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "C:\venv\trac-1.0.15\lib\site-packages\trac\db\util.py", line 61, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\venv\trac-1.0.15\lib\site-packages\trac\db\sqlite_backend.py", line 82, in execute
    result = PyFormatCursor.execute(self, *args)
  File "C:\venv\trac-1.0.15\lib\site-packages\trac\db\sqlite_backend.py", line 60, in execute
    args or [])
  File "C:\venv\trac-1.0.15\lib\site-packages\trac\db\sqlite_backend.py", line 52, in _rollback_on_error
    return function(self, *args, **kwargs)
sqlite3.OperationalError: too many SQL variables
>>>

Python 2.5.6 (r256:88840, Oct 21 2014, 22:49:55)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from trac.test import EnvironmentStub
>>> env = EnvironmentStub()
>>> def q(n):
...     query = 'SELECT * FROM system WHERE 0 IN (%s)' % ','.join(('%s',) * n)
...     args = list(xrange(n))
...     return env.db_query(query, args)
...
>>> q(999)
[(u'database_version', u'29')]
>>> q(1000)
[(u'database_version', u'29')]
>>> q(250000)
[(u'database_version', u'29')]
>>> q(250001)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<stdin>", line 4, in q
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/api.py", line 124, in execute
    return db.execute(query, params)
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/util.py", line 61, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 82, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 60, in execute
    args or [])
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 52, in _rollback_on_error
    return function(self, *args, **kwargs)
sqlite3.OperationalError: too many SQL variables

[Jun Omae]

Committed in [16289] and merged in [16290-16291].