Slow Update/Delete with large databases (1/4 second per delete)

[James Norman]

I'm experiencing slow updates with large databases at 30-70K documents.  It takes over 50 seconds to delete 200 documents on a database with 50K documents.  Is anyone else experiencing slow updates and deletes with databases this size?  Any tips on how to optimize it?  Here's some code to reproduce the issue, just create 50K documents and time deleting 200.  Thanks for any advice on how to speed this up.  This is with Couchbase Mobile for Android 1.03 and Samsung Galaxy Tab 4

Thanks -james

            try {

                Manager manager = new Manager( new AndroidContext(app), Manager.DEFAULT_OPTIONS);

                Database database = manager.getDatabase( "bigtest7" );

                //create 50k documents

                {

                    long startTime = System.currentTimeMillis();

                    database.beginTransaction();

                    for( int i=0; i<50000; i++ ) {

                        Document document = database.createDocument();

                        Map<String,Object> props = new HashMap<String,Object>();

                        for( int j=0; j<10; j++ ) {

                            props.put( "key" + j , "value" + j );

                        }

                        document.putProperties(props);

                    }

                    database.endTransaction(true);

                    long endTime = System.currentTimeMillis();

                    System.out.println("TIME: CREATE:" + ((endTime-startTime)/1000) );

                }

                //time it takes to delete 200 records

                {

                    List<String> toDelete = new ArrayList<String>();

                    Query query = database.createAllDocumentsQuery();

                    query.setLimit(200);

                    QueryEnumerator enum1 = query.run();

                    while( enum1.hasNext() ) {

                        toDelete.add( enum1.next().getDocumentId() );

                    }

                    long deleteStart = System.currentTimeMillis();

                    database.beginTransaction();

                    for( String toDeleteId:toDelete ) {

                        database.getDocument(toDeleteId).delete();

                    }

                    database.endTransaction(true);

                    long deleteEnd = System.currentTimeMillis();

                    System.out.println("TIME: DELETE:" + ((deleteEnd-deleteStart)/1000) );

                }

            }

            catch( Exception e ) {

                e.printStackTrace();

            }

[Jens Alfke]

On Oct 21, 2014, at 10:32 PM, James Norman <james....@gmail.com> wrote:

I'm experiencing slow updates with large databases at 30-70K documents.  It takes over 50 seconds to delete 200 documents on a database with 50K documents. 

I don't normally weigh in on Android issues because I work on iOS, but this sounds suspiciously like iOS issue #487 (fixed for 1.0.3) which is a problem with the SQLite schema and could thus affect Android as well; I see Traun's cloned it to the java-core  issue tracker as #297 (still open.)

The trigger for this issue is having SQLite 3.8 or higher. (It has a new query planner, which interacts badly with a poorly-designed index that's been in CBL for a long time, resulting in a super-inefficient index scan during document insertion.) That's why we didn't notice it until recently — the only Apple OS that has SQLite 3.8 yet is Mac OS X 10.10 (Yosemite).

I don't know an easy way to tell what version of SQLite is present on an Android device. Maybe it's in the OS release notes somewhere; otherwise you'd have to make a call to the SQLite C API. (What OS version does your device have?)

—Jens

[James Norman]

Android version 4.4.2 and SQLite 3.7.6.3, I ran the following sql on the device: SELECT sqlite_version() AS 'SQLite Version';

Thanks for any help on this.  I'll test with 1.03 this afternoon as well.

-james

[James Norman]

I tested this with CB Lite Android 1.0.3 and saw the same performance numbers, 1/4 second per update of document at 50K documents.  This seems awfully slow for a database, especially at only 50K records.  Does the IOS version have similar performance metrics?  Any ideas on how to address this would be appreciated.  

Thanks -james

[Jens Alfke]

On Oct 27, 2014, at 9:30 AM, James Norman <james....@gmail.com> wrote:

I tested this with CB Lite Android 1.0.3 and saw the same performance numbers, 1/4 second per update of document at 50K documents.  This seems awfully slow for a database, especially at only 50K records.  Does the IOS version have similar performance metrics?  Any ideas on how to address this would be appreciated.  

I don't have exact numbers handy, but the iOS version does on the order of 1,000 inserts/sec on an iPhone 5.

Just to ask the obvious: you do wrap the whole batch of updates/deletes in a transaction, right? Otherwise they will be slow, simply because each operation will be its own transaction, and SQLite transactions have a pretty high overhead.

—Jens

[James Norman]

Inserts are quite fast, even with large databases, the issue is the update/delete.  The code above will insert the 50K records in a minute, but updating a single document thereafter takes 1/4 second, even without any replication running.  We have a few use cases where we need to batch update up to 200 documents and it can take 1-2 minutes to do this.  All the calls are run under a transaction as well.  Thanks again for the response. -james

[Jens Alfke]

On Oct 27, 2014, at 10:03 AM, James Norman <james....@gmail.com> wrote:

Inserts are quite fast, even with large databases, the issue is the update/delete.  The code above will insert the 50K records in a minute, but updating a single document thereafter takes 1/4 second, even without any replication running. 

Very weird. Sounds like this is an Android-specific bug; you should file an issue on Github for Traun to look at. If you can collect CPU profile data while this is happening, that would be great to attach to it.

—Jens

[Traun Leyden]

Yeah, this sounds like it will need to be profiled.

Go ahead and file an issue on github.  I think step 1 is to make sure we have coverage for this in our performance test suite.