13

I'm importing data into Core Data and find that the save operation is slow. Using the iOS simulator, I watch the sqlite-wal file grow and grow until its over 7GB in size.

I'm importing approx 5000 records with about 10 fields. This isn't a lot of data.

Each object I'm inserting has a to-one relation to various other objects (6 relations total). All of those records combined equal less than 20 fields. There are no images or any binary data or anything that I can see that would justify why the resulting size of the WAL file is so huge.

I read the sqlite docs describing the wal file and I don't see how this can happen. The source data isn't more than 50 MB.

My app is multi-threaded. I create a managed object context in the background thread that performs the import (creates and saves the core data objects).

Without writing the code out here, has anyone encountered this? Anyone have a thought on what I should be checking. The code isn't super simple and all the parts would take time to input here so lets start with general ideas.

I'll credit anyone who gets me going in the right direction.

Extra info:

  • I've disabled the undo manager for the context as I don't need that (I think it's nil by default on iOS but I explicitly set it to nil).
  • I only call save after the entire loop is complete and all managed objects are in ram (ram goes up to 100 MB btw).
  • The loop and creation of the core data objects takes only 5 seconds or so. The save takes almost 3 minutes as it writes the the awl file.
Eric Risler
  • 319
  • 2
  • 9
  • Will you provide any code? – Shmidt Nov 26 '13 at 21:40
  • I will if need be. But I'm really looking for ideas first. There is a lot of code invoked as I'm using wrapper classes and multiple threads. The behaviour seems to be related to the sqlite sub-system. I'm sure it's something I'm doing. If this hasn't been encountered by anyone, then I'll post code and maybe we can see whats causing this. – Eric Risler Nov 26 '13 at 21:44
  • Have you tried turning on core data SQL debugging to see what SQL is actually being executed? – Jesse Rusak Nov 26 '13 at 21:47
  • 1
    What happens if you don't use WAL mode ? – Duncan Groenewald Nov 27 '13 at 01:33
  • SQL Debugging(at level 5) shows that there are no odd sql queries happening. I limited the test to 1000 records. The awl file grew to 877mb. The console in Xcode output 86,231 lines of text (total of 10.3mb in size). – Eric Risler Nov 27 '13 at 14:20
  • @DuncanGroenewald - Changing the journal_mode back to "DELETE" (previous sqlite version's default) makes a huge difference in terms of file i/o. The database journal never grows past 1MB. Final DB size is less than 100MB. I'll credit you the answer. Perhaps CoreData and awl mode has some quirks to work out. – Eric Risler Nov 27 '13 at 14:52
  • @DuncanGroenewald - how do I credit your comment as the answer? – Eric Risler Nov 27 '13 at 14:58
  • @EricRisler I'll post it as an answer. – Duncan Groenewald Nov 27 '13 at 20:27
  • I experienced the exact same thing after deletion about 150.000 entries out of the db. – d.ennis Jan 10 '14 at 20:33

2 Answers2

9

It seems my comment to try using the old rollback(DELETE) journal mode rather than WAL journal mode fixed the problem. NOTE that there seem to be a range of problems when using WAL journal mode including the following:

  • this problem
  • problems with database migrations when using the migratePersistentStore API
  • problems with lightweight migrations

Perhaps we should start a Core Data WAL problems page and get a comprehensive list and ask Apple to fix the bugs.

Note that the default mode under OS X 10.9 and iOS 7 now uses WAL mode. To change this back add the following option 

@{ NSSQLitePragmaOptions : @{ @"journal_mode" : @"DELETE" } }
mafu
  • 31,798
  • 42
  • 154
  • 247
Duncan Groenewald
  • 8,496
  • 6
  • 41
  • 76
1

All changed pages of a transaction get appended to the -wal file. If you are importing multiple records, you should, if possible, use a single transaction for the entire import.

SQLite cannot do a full WAL checkpoint while some other connection is reading the database (which might just be some statement that you forgot to close).

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
  • How do you "forget to close" a core data transaction? – Mundi Nov 26 '13 at 22:05
  • I do this. I've actually disabled the undo manager for the context as I don't need that (I think it's nil by default on iOS but I explicitly set it to nil). I only call save after the entire loop is complete and all managed objects are in ram (ram goes up to 100 MB btw). The loop and creation of the core data objects takes only 5 seconds or so. The save takes almost 3 minutes as it writes the the awl file. (added this to the main question) – Eric Risler Nov 26 '13 at 22:10
  • Good question Mundi. I do understand that in the sqlite world, if you have "Readers" open on the database (say in other threads I was reading the db) then the writes in the thread that is writing to the db file will only append to the WAL file. This makes sense of course - what doesn't is that the WAL file grows past 7GB. I'm reviewing sql debug output. Keep you posted. – Eric Risler Nov 26 '13 at 22:13
  • From the SQLite web site: "However, if the last connection does not shutdown cleanly, the WAL file will remain in the filesystem and will be automatically cleaned up the next time the database is opened." So even in a sqlite setting "forgot to close the file" this is nonsense. – Mundi Nov 26 '13 at 22:14
  • @Mundi Possible scenario: a single application starts a transaction but doesn't close it: all changes are posted to WAL, but because transaction isn't closed, ie, neither commited neither rolledback, WAL file just keeps growing and growing. – LS_ᴅᴇᴠ Nov 27 '13 at 09:09
  • @LS_dev Not in Core Data. – Mundi Nov 27 '13 at 09:13