Seems constant to me. I'd show you, but Windows has no protection for the OS when system memory is exhausted so the whole computer go kaboom.
However, memory size growth was constant, and insert time was pretty constant (which includes the overhead of generating random values etc). Go boom when all RAM is full at 31 GB (about 140,000,000 records). Running it again and getting it to stop at 120,000,000 inserts showed as follows: >testinsert.py sys.version_info(major=3, minor=6, micro=3, releaselevel='final', serial=0) Row(journal_mode='off') Row(journal_mode='off') Row(synchronous=0) insert into data values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); 1000000 9.751389 1000000 9.751389 2000000 19.518461 1000000 9.767072 3000000 29.595865 1000000 10.077404 4000000 40.991316 1000000 11.395450 5000000 52.543077 1000000 11.551762 6000000 64.178037 1000000 11.634959 7000000 75.347824 1000000 11.169787 8000000 86.437358 1000000 11.089534 9000000 97.772551 1000000 11.335193 10000000 109.154860 1000000 11.382308 11000000 120.132103 1000000 10.977243 12000000 131.221607 1000000 11.089504 13000000 142.555831 1000000 11.334224 14000000 153.595233 1000000 11.039402 15000000 164.749915 1000000 11.154682 16000000 175.282969 1000000 10.533054 17000000 186.272268 1000000 10.989299 18000000 197.274567 1000000 11.002299 19000000 208.608760 1000000 11.334193 20000000 219.676239 1000000 11.067479 21000000 230.995386 1000000 11.319148 22000000 242.520074 1000000 11.524688 23000000 254.029814 1000000 11.509740 24000000 265.071223 1000000 11.041409 25000000 276.094553 1000000 11.023329 26000000 286.886328 1000000 10.791775 27000000 298.175561 1000000 11.289233 28000000 308.891103 1000000 10.715541 29000000 319.565535 1000000 10.674432 30000000 330.200866 1000000 10.635331 31000000 340.765001 1000000 10.564136 32000000 351.553740 1000000 10.788738 33000000 362.221151 1000000 10.667412 34000000 373.168309 1000000 10.947158 35000000 383.733089 1000000 10.564780 36000000 394.125019 1000000 10.391931 37000000 403.954467 1000000 9.829448 38000000 414.158771 1000000 10.204304 39000000 424.097607 1000000 9.938836 40000000 434.161623 1000000 10.064016 41000000 444.116177 1000000 9.954554 42000000 454.414414 1000000 10.298238 43000000 464.478468 1000000 10.064053 44000000 474.776956 1000000 10.298488 45000000 485.001610 1000000 10.224653 46000000 495.143748 1000000 10.142138 47000000 505.129763 1000000 9.986015 48000000 515.271824 1000000 10.142061 49000000 525.554411 1000000 10.282587 50000000 536.149616 1000000 10.595204 51000000 546.135350 1000000 9.985735 52000000 556.402481 1000000 10.267131 53000000 566.919720 1000000 10.517239 54000000 578.046368 1000000 11.126648 55000000 588.990274 1000000 10.943906 56000000 599.987536 1000000 10.997262 57000000 610.828424 1000000 10.840888 58000000 621.324319 1000000 10.495896 59000000 632.009688 1000000 10.685369 60000000 642.563769 1000000 10.554081 61000000 653.049639 1000000 10.485870 62000000 664.206841 1000000 11.157203 63000000 674.797024 1000000 10.590183 64000000 685.523797 1000000 10.726773 65000000 696.855562 1000000 11.331765 66000000 708.156918 1000000 11.301356 67000000 718.994999 1000000 10.838081 68000000 729.723788 1000000 10.728789 69000000 740.643086 1000000 10.919299 70000000 751.140253 1000000 10.497167 71000000 761.664491 1000000 10.524239 72000000 772.482858 1000000 10.818367 73000000 783.014121 1000000 10.531263 74000000 794.438486 1000000 11.424365 75000000 805.444951 1000000 11.006465 76000000 816.032293 1000000 10.587342 77000000 826.511347 1000000 10.479054 78000000 837.076599 1000000 10.565252 79000000 847.528613 1000000 10.452014 80000000 857.831194 1000000 10.302581 81000000 868.286178 1000000 10.454984 82000000 878.765237 1000000 10.479059 83000000 889.114724 1000000 10.349487 84000000 900.053886 1000000 10.939162 85000000 909.930211 1000000 9.876325 86000000 919.790808 1000000 9.860597 87000000 929.651311 1000000 9.860503 88000000 939.527506 1000000 9.876195 89000000 949.356789 1000000 9.829282 90000000 959.186170 1000000 9.829381 91000000 969.031142 1000000 9.844972 92000000 978.891677 1000000 9.860535 93000000 988.752464 1000000 9.860787 94000000 998.597371 1000000 9.844907 95000000 1008.457877 1000000 9.860506 96000000 1018.318437 1000000 9.860560 97000000 1028.163434 1000000 9.844996 98000000 1038.024322 1000000 9.860888 99000000 1047.854005 1000000 9.829683 100000000 1057.761420 1000000 9.907415 101000000 1067.637771 1000000 9.876351 102000000 1078.089400 1000000 10.451629 103000000 1088.756892 1000000 10.667492 104000000 1099.582771 1000000 10.825879 105000000 1110.237257 1000000 10.654486 106000000 1121.181479 1000000 10.944222 107000000 1132.092627 1000000 10.911147 108000000 1142.934579 1000000 10.841952 109000000 1153.866774 1000000 10.932196 110000000 1164.808998 1000000 10.942224 111000000 1175.667999 1000000 10.859001 112000000 1186.503937 1000000 10.835938 113000000 1197.440143 1000000 10.936207 114000000 1208.192860 1000000 10.752717 115000000 1218.891432 1000000 10.698572 116000000 1229.737401 1000000 10.845968 117000000 1240.846065 1000000 11.108665 118000000 1251.648885 1000000 10.802819 119000000 1262.131912 1000000 10.483027 120000000 1273.303061 1000000 11.171149 Source code: #! python3 from __future__ import print_function import apsw import apswrow import random import sys import time sql_create = """ create table data ( id int, path int, month int, val1 double, val2 double, val3 double, val4 double, val5 double, val6 double, val7 double, val8 double, val9 double, val10 double, val11 double, val12 double, val13 double, val14 double, val15 double, val16 double, val17 double, val18 double, val19 double, val20 double ); """ print(sys.version_info) db = apsw.Connection(':memory:') db.cursor().execute(sql_create) for row in db.cursor().execute('pragma journal_mode=off; pragma synchronous=off;'): print(row) for row in db.cursor().execute('pragma journal_mode; pragma synchronous;'): print(row) stmt = 'insert into data values (' + ','.join('?'*23) + ');' print(stmt) st = time.time() lt = time.time() rows = 1000000 # db.cursor().execute('begin immediate;') id = 0 while id < 120000000: id += 1 data = [id, id, id] for i in range(20): data.append(random.random()) db.cursor().execute(stmt, data) if id % rows == 0: # db.cursor().execute('commit;') ct = time.time() print('%10d %12.6f %10d %12.6f' % (id, ct-st, rows, ct-lt)) lt = ct # db.cursor().execute('begin immediate;') # db.cursor().execute('commit;') db.close() --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2 >Sent: Thursday, 21 December, 2017 12:06 >To: SQLite mailing list >Subject: [sqlite] generic advice for insert performance in-memory >database optimization > >I've got an in-memory database with a single table that I need to >fill with ~500 million rows. There are no indexes and the table >definitions is > >create table data(id int, path int, month int, val1 double, val2 >double, val3 double... val20 double) > >I'm running on linux with the OS page size configured to 4096 and >~380 GB of ram (much more than required for the table so I think I'm >not swapping) and haven't altered the sqlite page size. I am using >sqlite version 3.13.0 and these pragmas immediately after database >creation. > >pragma temp_store = MEMORY >pragma journal_mode = off > >With these settings I'm seeing nonlinear (in a bad way) times for the >insert. Is that expected? I've fiddled about with various >performance-related settings like described on > >https://blog.devart.com/increasing-sqlite-performance.html > >with varying results but haven't managed to arrive at fairly-linear >insert behavior. It's a single-threaded insert on a prepared query >with bound arguments in a tight loop. > >Is linear-ish insert time a reasonable goal for an in-memory >database? > >--------------------------------------------------------------------- >- >This message, and any attachments, is for the intended recipient(s) >only, may contain information that is privileged, confidential and/or >proprietary and subject to important terms and conditions available >at http://www.bankofamerica.com/emaildisclaimer. If you are not the >intended recipient, please delete this message. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users