500k records for 2 tables (total 1M) on SQLite, heavy update scenario...... I really hope you'll manage that outside the normal web environment to avoid timeout issues that are likely to happen. I'll try to reproduce though.
On Tuesday, May 21, 2013 6:35:14 PM UTC+2, Simon Ashley wrote: > > Experiencing memory leaks when updating 500k records using either of the > following routines. > Quite reproducible, and running tests on both windows 7 and 2008 servers > (servers with 8 and 12G ram) > Initial speed tests suggest 100 recs per second, but routines fail to > complete after 3 hours. > Have tried with and without caching. > Using current trunk and stable versions. > > Currently exporting the entire db, deleting the database and reimporting > (have tried rebuilds through SQLite Manager). > If that doesn't work, looking at swapping databases, but before we do that > ... Any comments or ideas? > > Wondering if the initial selects are too big and need to be split? > (haven't tried caching to the disk yet) > If that is the issue, are there any recommended strategies? > > > def update_TABLE_A_1(): > import datetime > a = datetime.datetime.now() > q = db1(db1.TABLE_A.ITEM_ID == db1.TABLE_B.id).select(cache=(cache.ram > ,600),cacheable=True, limitby=(0,1000000)) #limitby merely to test speed > and ranges, tried with and without caching > for row in q: > db1.TABLE_A.update_or_insert(db1.TABLE_A.ITEM_ID == row.TABLE_B.id > , FIELD_A1 = row.TABLE_B.FIELD_B2, FIELD_A2 = row.TABLE_B.FIELD_B3,FIELD_A3 > = row.TABLE_B.FIELD_B4, FIELD_A4 = row.TABLE_B.FIELD_B5) > b = datetime.datetime.now() > b1 = b-a > return str(b1) > > > def update_TABLE_A_2(): > import datetime > a = datetime.datetime.now() > q = db1(db1.TABLE_A > 0).select(limitby=(0,1000000)) > for row in q: > q1 = db1(db1.TABLE_B == row.id).select(cache=(cache.ram,600), > cacheable=True, limitby=(0,1)) > row.update_record(FIELD_A = q1[0].FIELD_B1, FIELD_B = q1[0]. > FIELD_B2, FIELD_C = q1[0].FIELD_B3, FIELD_D = q1[0].FIELD_B4) > b = datetime.datetime.now() > b1 = b-a > b2 = b1.seconds/1000 > return (str(b1), str(b2)) > > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
