SQLite Forum

Out of Memory Errors - Possible Enhancement
Login

Out of Memory Errors - Possible Enhancement

(1.1) By Balaji Ramanathan (balaji) on 2021-02-23 23:24:25 edited from 1.0 [link] [source]

Hi,

This is not a bug report per se.  It may be a suggestion for improvement or enhancement, depending on how common this kind of occurrence may be.

The problem is as follows.  I have a database in which I have a bunch of measures.  I have written some views that summarize these measures (statistics such as count, min, max, percentile values, etc.).  There are 13 measures, and 111 statistics for each measure.

When I try to run a query that produces all these statistics in a single output (111 rows x 13 columns), I consistently run into "Out of Memory" errors on my 10-year old Windows 7 PC with 4 GB of RAM.  The query does run on my work laptop with Windows 10 and 16 GB of RAM, so there is no error in the query itself.

The query itself is somewhat heavy.  It uses 13 other views, and each of these views uses another view that uses window functions and aggregates heavily.  It takes about 15 to 20 seconds to run even on my powerful work laptop.

The work-around to this on my memory-challenged laptop is to create a separate temp table for each of the 13 measures (13 tables total) and then do a join on all 13 to create the final output I want (111 rows x 13 columns output).  This process of creating the 13 temp tables and then joining them takes about 30 seconds to a minute on my personal laptop.  Once again, the individual queries work and they are quite quick, it is just the process of running all the views and joining them in memory that causes the out of memory error.

Given that the query can produce the results using this work-around, I just thought I would post here to see if it would be possible for SQLite to live up to its Lite moniker with an enhancement:  when the system is running out of memory, write intermediate results to temp tables, and then try to use those temp tables internally to create the final output.  All this can be done transparently without the user even knowing that this is happening.  Now, this is coming from someone who has not looked at the SQLite source code even accidentally, so take that for what it is worth!

I have created a test database for you to play with if you are interested.  The link is at https://drive.google.com/file/d/1oVsOxP6-9JrzN2GiedvcuqmQnRV0-wa-/view?usp=sharing .

If your computer is as underpowered as mine is, you will find it impossible to run the view "TripSummary".  This is the view that is supposed to produce the 111 x 13 matrix as output.  On such a computer, you will find that you can go to the table "UsefulQueries" and run the two queries you find there one after the other (the first one creates the 13 temp tables, and the second one joins them to produce the final matrix output).

Please let me know if something is not clear or you have questions.  Please also feel free to let me know how to make my code more efficient and less memory-intensive in case I have done something unnecessary or stupid.

Thank you.

(2) By Stephan Beal (stephan) on 2021-02-23 23:21:35 in reply to 1.0 [link] [source]

On such a computer, you will find that you can go to the table "Useful Queries" and run the two queries you find there one after the other

sqlite> select * from "Useful Queries";
Error: no such table: Useful Queries

The actual name is apparently UsefulQueries (no space).

(3) By Balaji Ramanathan (balaji) on 2021-02-23 23:23:38 in reply to 2 [link] [source]

Argh! Sorry about that. Yes, I avoid putting spaces in my table, view and column names. But I misspelled it in my post.

(4.1) By Keith Medcalf (kmedcalf) on 2021-02-24 00:26:40 edited from 4.0 in reply to 3 [link] [source]

I get the following statistics on a relatively unconstrained system (Xeon 4 Ghz processor, x64, 32 GB RAM, 4 GB page cache) with the current tip:

SQLite version 3.35.0 2021-02-22 22:34:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> .stats on
sqlite> .mode list
sqlite> .open test.db
sqlite> select * from tripsummary;
Summary Metric|Distance (KM)|Trip Time (HH:MM)|Scheduled Trip Time (HH:MM)|Departure Delay (HH:MM)|Arrival Delay (HH:MM)|Actual - Scheduled (HH:MM)|Cost (USD)|Out of Pocket (OoP) Cost (USD)|Speed in KMPH|Cost (c/KM)|Out
of Pocket (OoP) Cost (c/KM)|Cost ($/Hour)|Out Of Pocket (OoP) Cost ($/Hour)
Count|3515|3515|3515|3515|3515|3515|3515|3515|3515|3515|3515|3515|3515
Count (Non-zero)|3515|3515|976|1004|984|976|2234|1846|3515|2234|1846|2234|1846
Count (Distinct)|616|208|343|102|140|239|1022|777|1355|1359|1084|1389|1072
Total|2069029|6272:45|3512:50|400:13|90:04|-305:55|$72879.61|$24547.71|None|None|None|None|None
Average|588.63|1:47|0:60|0:07|0:02|-0:05|$20.73|$6.98|329.84|3.52|1.19|$11.62|$3.91
Average (Non-zero)|588.63|1:47|3:36|0:24|0:05|-0:19|$32.62|$13.30|329.84|5.61|2.96|$16.14|$6.67
Mode|43|0:40|0:42|0:05|0:03|-0:03|$4.04|$4.04|96.00|9.40|9.40|$6.06|$6.06
Minimum|1|0:05|0:10|-0:25|-2:45|-2:30|$0.04|$0.04|2.08|0.01|0.01|$0.08|$0.08
Maximum|14133|41:00|36:50|12:40|12:15|7:45|$1082.91|$484.43|1036.14|1198.00|1198.00|$505.08|$242.93
Above Average|656|871|301|288|264|409|402|322|676|986|996|472|585
Below Average|2859|2644|675|716|720|567|1832|1524|2839|1248|850|1762|1261
Percentile 1|5|0:10|0:42|0:00|-0:46|-1:09|$0.15|$0.15|17.81|0.27|0.27|$0.18|$0.18
Percentile 2|10|0:15|0:42|0:00|-0:40|-1:03|$0.25|$0.17|24.00|0.31|0.30|$0.23|$0.21
Percentile 3|14|0:15|0:42|0:00|-0:35|-0:60|$0.25|$0.25|30.00|0.36|0.33|$0.26|$0.25
Percentile 4|17|0:15|0:42|0:00|-0:35|-0:55|$0.27|$0.25|32.87|0.41|0.37|$0.30|$0.28
Percentile 5|20|0:15|0:42|0:00|-0:32|-0:51|$0.40|$0.30|36.00|0.47|0.42|$0.32|$0.30
Percentile 6|22|0:20|0:42|0:00|-0:30|-0:48|$0.40|$0.40|37.85|0.53|0.47|$0.37|$0.32
Percentile 7|24|0:20|0:42|0:00|-0:30|-0:47|$0.40|$0.40|40.00|0.57|0.50|$0.42|$0.36
Percentile 8|24|0:20|0:42|0:00|-0:27|-0:45|$0.45|$0.40|42.00|0.60|0.55|$0.46|$0.40
Percentile 9|26|0:20|0:42|0:00|-0:25|-0:44|$0.50|$0.40|44.00|0.65|0.58|$0.53|$0.44
Percentile 10|27|0:20|0:42|0:00|-0:25|-0:43|$0.61|$0.50|45.91|0.67|0.62|$0.62|$0.50
Percentile 11|29|0:25|0:42|0:00|-0:24|-0:42|$0.65|$0.55|47.65|0.73|0.66|$0.69|$0.54
Percentile 12|31|0:25|0:42|0:00|-0:22|-0:40|$0.65|$0.65|49.09|0.75|0.67|$0.77|$0.64
Percentile 13|32|0:25|0:43|0:00|-0:21|-0:40|$0.73|$0.65|51.60|0.80|0.71|$0.80|$0.69
Percentile 14|32|0:25|0:43|0:00|-0:20|-0:40|$0.75|$0.65|53.14|0.85|0.74|$0.96|$0.77
Percentile 15|34|0:25|0:43|0:00|-0:20|-0:39|$0.80|$0.75|56.00|0.90|0.78|$0.98|$0.80
Percentile 16|35|0:25|0:43|0:03|-0:20|-0:38|$0.88|$0.75|57.33|0.96|0.81|$1.11|$0.90
Percentile 17|37|0:25|0:43|0:05|-0:19|-0:38|$0.90|$0.80|57.33|1.01|0.86|$1.20|$0.96
Percentile 18|39|0:30|0:43|0:05|-0:18|-0:37|$0.95|$0.87|57.33|1.08|0.91|$1.27|$1.00
Percentile 19|39|0:30|0:43|0:05|-0:17|-0:36|$1.00|$0.93|60.00|1.14|0.95|$1.31|$1.13
Percentile 20|40|0:30|0:43|0:05|-0:16|-0:35|$1.05|$0.98|60.75|1.23|1.00|$1.43|$1.20
Percentile 21|40|0:30|0:43|0:05|-0:15|-0:35|$1.05|$1.03|63.43|1.25|1.07|$1.50|$1.30
Percentile 22|40|0:30|0:43|0:05|-0:15|-0:35|$1.15|$1.05|64.50|1.34|1.12|$1.60|$1.32
Percentile 23|42|0:30|0:43|0:05|-0:15|-0:34|$1.15|$1.05|64.50|1.44|1.15|$1.64|$1.44
Percentile 24|42|0:30|0:43|0:05|-0:14|-0:34|$1.20|$1.15|64.50|1.50|1.23|$1.80|$1.50
Percentile 25|43|0:30|0:43|0:05|-0:13|-0:33|$1.20|$1.15|64.50|1.55|1.27|$1.80|$1.58
Percentile 26|43|0:30|0:50|0:05|-0:11|-0:32|$1.20|$1.15|64.50|1.67|1.35|$1.95|$1.63
Percentile 27|43|0:35|0:55|0:05|-0:10|-0:31|$1.25|$1.20|66.86|1.74|1.44|$1.97|$1.80
Percentile 28|43|0:35|1:05|0:05|-0:10|-0:31|$1.31|$1.20|68.57|1.79|1.50|$2.06|$1.80
Percentile 29|43|0:35|1:08|0:05|-0:10|-0:30|$1.40|$1.20|69.60|1.88|1.54|$2.11|$1.80
Percentile 30|43|0:35|1:14|0:05|-0:10|-0:30|$1.50|$1.25|72.00|1.91|1.63|$2.17|$1.95
Percentile 31|43|0:35|1:18|0:05|-0:09|-0:30|$1.62|$1.31|73.20|2.01|1.72|$2.25|$1.97
Percentile 32|43|0:35|1:21|0:05|-0:08|-0:30|$1.70|$1.40|74.00|2.12|1.75|$2.30|$2.06
Percentile 33|43|0:35|1:25|0:05|-0:07|-0:29|$1.80|$1.50|76.50|2.21|1.82|$2.40|$2.10
Percentile 34|43|0:35|1:30|0:05|-0:07|-0:29|$1.95|$1.57|77.14|2.26|1.89|$2.45|$2.18
Percentile 35|45|0:40|1:32|0:07|-0:05|-0:28|$2.00|$1.70|79.50|2.31|1.91|$2.57|$2.25
Percentile 36|45|0:40|1:35|0:09|-0:05|-0:28|$2.20|$1.71|80.00|2.41|2.00|$2.70|$2.30
Percentile 37|47|0:40|1:39|0:10|-0:05|-0:27|$2.30|$1.82|81.00|2.62|2.08|$2.86|$2.36
Percentile 38|48|0:40|1:44|0:10|-0:05|-0:27|$2.38|$1.95|82.00|2.73|2.21|$2.89|$2.40
Percentile 39|50|0:40|1:45|0:10|-0:04|-0:26|$2.38|$2.00|84.00|2.81|2.25|$3.00|$2.50
Percentile 40|51|0:40|1:49|0:10|-0:03|-0:25|$2.50|$2.15|84.00|3.00|2.30|$3.04|$2.64
Percentile 41|52|0:40|1:50|0:10|-0:03|-0:25|$2.73|$2.25|85.71|3.03|2.41|$3.17|$2.71
Percentile 42|53|0:40|1:53|0:10|-0:03|-0:25|$3.13|$2.35|87.00|3.17|2.57|$3.25|$2.85
Percentile 43|54|0:40|1:55|0:10|-0:02|-0:25|$3.58|$2.38|88.50|3.28|2.69|$3.40|$2.91
Percentile 44|56|0:40|1:57|0:10|-0:01|-0:25|$4.04|$2.38|90.00|3.36|2.79|$3.48|$3.00
Percentile 45|56|0:45|2:00|0:10|0:00|-0:25|$4.04|$2.51|90.00|3.56|3.00|$3.57|$3.03
Percentile 46|58|0:45|2:00|0:10|0:00|-0:24|$4.04|$2.75|91.50|3.56|3.02|$3.69|$3.15
Percentile 47|59|0:45|2:04|0:10|0:00|-0:23|$4.04|$3.11|92.23|3.71|3.10|$3.87|$3.20
Percentile 48|60|0:45|2:06|0:11|0:00|-0:23|$4.04|$3.54|93.60|4.04|3.23|$4.03|$3.34
Percentile 49|61|0:45|2:11|0:13|0:00|-0:22|$4.04|$3.95|94.50|4.05|3.28|$4.18|$3.41
Percentile 50|61|0:45|2:15|0:15|0:02|-0:21|$4.04|$4.04|96.00|4.18|3.43|$4.33|$3.48
Percentile 51|64|0:50|2:18|0:15|0:02|-0:21|$4.04|$4.04|96.00|4.35|3.56|$4.44|$3.57
Percentile 52|64|0:50|2:22|0:15|0:02|-0:20|$4.04|$4.04|96.00|4.50|3.56|$4.70|$3.63
Percentile 53|66|0:50|2:25|0:15|0:02|-0:20|$4.04|$4.04|96.00|5.41|3.63|$4.85|$3.80
Percentile 54|68|0:50|2:30|0:15|0:02|-0:20|$4.04|$4.04|96.00|5.53|3.96|$5.01|$3.90
Percentile 55|71|0:55|2:31|0:15|0:02|-0:20|$4.31|$4.04|96.86|5.56|4.04|$5.30|$4.05
Percentile 56|72|0:55|2:33|0:15|0:02|-0:19|$4.60|$4.04|99.00|5.91|4.05|$5.39|$4.19
Percentile 57|75|1:00|2:36|0:15|0:02|-0:18|$4.91|$4.04|100.00|6.29|4.19|$5.39|$4.32
Percentile 58|80|1:00|2:40|0:15|0:02|-0:17|$5.32|$4.04|100.50|6.67|4.33|$5.39|$4.41
Percentile 59|84|1:00|2:43|0:15|0:03|-0:15|$5.75|$4.04|100.80|6.71|4.50|$5.69|$4.58
Percentile 60|88|1:05|2:45|0:15|0:03|-0:15|$6.24|$4.04|102.00|7.11|5.35|$6.06|$4.70
Percentile 61|96|1:05|2:48|0:15|0:03|-0:15|$6.66|$4.04|102.00|7.46|5.53|$6.06|$4.85
Percentile 62|105|1:05|2:51|0:16|0:03|-0:14|$7.00|$4.15|102.86|7.74|5.53|$6.06|$5.02
Percentile 63|111|1:10|2:59|0:18|0:03|-0:11|$7.52|$4.39|103.20|7.97|5.91|$6.06|$5.28
Percentile 64|117|1:10|3:10|0:20|0:03|-0:10|$8.13|$4.60|104.00|8.44|6.29|$6.06|$5.39
Percentile 65|124|1:15|3:20|0:20|0:03|-0:09|$8.44|$4.88|104.57|8.64|6.67|$6.12|$5.39
Percentile 66|130|1:15|3:26|0:20|0:03|-0:08|$9.02|$5.16|105.60|8.84|6.71|$6.53|$5.39
Percentile 67|135|1:20|3:30|0:20|0:03|-0:05|$9.81|$5.52|106.00|9.40|7.13|$6.92|$5.39
Percentile 68|142|1:25|3:40|0:20|0:03|-0:03|$10.73|$5.86|107.25|9.40|7.71|$7.23|$5.69
Percentile 69|153|1:25|3:42|0:20|0:04|-0:03|$11.18|$6.27|108.00|9.40|7.79|$8.00|$6.06
Percentile 70|166|1:30|3:48|0:20|0:05|-0:03|$11.73|$6.67|108.00|9.40|8.00|$8.60|$6.06
Percentile 71|182|1:30|3:52|0:22|0:05|-0:03|$12.38|$7.00|109.71|9.40|8.65|$9.11|$6.06
Percentile 72|196|1:35|3:56|0:25|0:05|-0:03|$13.27|$7.39|110.80|9.40|9.40|$9.83|$6.06
Percentile 73|216|1:40|4:00|0:25|0:07|-0:03|$14.31|$7.73|112.00|9.40|9.40|$10.54|$6.06
Percentile 74|225|1:45|4:01|0:25|0:07|-0:03|$15.40|$8.22|112.80|9.40|9.40|$11.37|$6.06
Percentile 75|250|1:45|4:05|0:25|0:08|-0:03|$16.64|$8.57|114.00|9.40|9.40|$12.80|$6.06
Percentile 76|278|1:50|4:10|0:26|0:08|-0:03|$18.42|$9.34|115.50|9.40|9.40|$13.79|$6.34
Percentile 77|311|2:00|4:11|0:30|0:08|-0:03|$19.61|$10.03|116.40|9.59|9.40|$14.57|$6.70
Percentile 78|348|2:00|4:15|0:30|0:10|-0:02|$21.53|$10.73|118.29|10.25|9.40|$15.31|$6.94
Percentile 79|409|2:05|4:18|0:30|0:10|-0:02|$24.22|$11.18|121.00|11.06|9.40|$16.84|$7.54
Percentile 80|475|2:15|4:22|0:30|0:10|-0:02|$27.42|$11.72|125.00|11.76|9.40|$18.37|$8.06
Percentile 81|545|2:20|4:25|0:30|0:11|-0:02|$30.95|$12.38|420.00|12.25|9.40|$20.34|$8.61
Percentile 82|663|2:25|4:30|0:35|0:12|0:00|$33.69|$13.24|553.85|13.16|9.40|$22.01|$9.07
Percentile 83|761|2:30|4:35|0:35|0:15|0:00|$37.64|$14.44|599.00|14.20|9.40|$23.65|$10.27
Percentile 84|948|2:40|4:42|0:35|0:17|0:02|$41.78|$15.40|622.50|15.22|9.40|$27.35|$10.94
Percentile 85|991|2:55|5:00|0:39|0:20|0:02|$47.08|$16.64|654.00|16.19|11.11|$30.28|$12.46
Percentile 86|1157|3:05|5:25|0:40|0:20|0:03|$51.94|$18.55|674.57|16.63|12.33|$34.90|$13.38
Percentile 87|1290|3:15|6:10|0:40|0:25|0:03|$62.90|$20.20|694.00|17.78|13.54|$39.99|$14.51
Percentile 88|1429|3:20|7:00|0:45|0:27|0:03|$78.65|$22.20|707.74|18.14|15.49|$44.97|$15.28
Percentile 89|1574|3:30|8:05|0:45|0:30|0:03|$91.60|$24.85|723.13|19.26|16.48|$50.00|$17.40
Percentile 90|1683|3:40|8:30|0:48|0:31|0:03|$106.04|$28.33|735.75|21.37|18.00|$54.45|$19.73
Percentile 91|1950|3:45|9:10|0:50|0:35|0:03|$129.76|$31.91|746.12|24.27|18.93|$60.25|$21.43
Percentile 92|2317|4:00|9:45|1:00|0:44|0:03|$144.70|$35.14|756.80|28.33|21.37|$67.69|$23.48
Percentile 93|2624|4:10|10:30|1:10|0:50|0:03|$159.86|$40.24|772.32|32.85|24.27|$74.09|$25.91
Percentile 94|2808|4:40|12:37|1:15|0:59|0:05|$176.20|$46.98|790.05|41.26|27.30|$81.18|$28.96
Percentile 95|2930|5:10|13:30|1:25|1:10|0:07|$191.63|$57.48|809.83|62.77|40.00|$95.52|$31.68
Percentile 96|2970|6:20|14:40|1:40|1:28|0:08|$212.30|$77.90|821.35|89.30|42.08|$104.86|$36.90
Percentile 97|3136|8:25|15:30|1:55|1:40|0:10|$245.60|$98.34|844.50|114.10|62.77|$119.93|$47.50
Percentile 98|6716|10:40|16:25|2:09|2:10|0:17|$306.10|$135.68|872.19|182.05|89.30|$158.37|$53.40
Percentile 99|9726|14:40|33:20|3:31|3:18|0:40|$404.61|$194.32|909.47|300.53|118.60|$240.54|$70.38
Percentile 100|14133|41:00|36:50|12:40|12:15|7:45|$1082.91|$484.43|1036.14|1198.00|1198.00|$505.08|$242.93
Memory Used:                         13825520 (max 887369520) bytes
Number of Outstanding Allocations:   72037 (max 186850)
Number of Pcache Overflow Bytes:     422664 (max 430119064) bytes
Largest Allocation:                  9699328 bytes
Largest Pcache Allocation:           4360 bytes
Lookaside Slots Used:                114 (max 123)
Successful lookaside attempts:       1154
Lookaside failures due to size:      213
Lookaside failures due to OOM:       476896
Pager Heap Usage:                    421872 bytes
Page cache hits:                     11974
Page cache misses:                   96
Page cache writes:                   0
Page cache spills:                   0
Schema Heap Usage:                   369560 bytes
Statement Heap/Lookaside Usage:      12628368 bytes
Fullscan Steps:                      622088
Sort Operations:                     502
Autoindex Inserts:                   1320
Virtual Machine Steps:               382592216
Reprepare operations:                0
Number of times run:                 1
Memory used by prepared stmt:        12628368
Run Time: real 9.011 user 8.578125 sys 0.234375
sqlite>

I don't think 887369520 bytes (847 MB) of RAM is very much at all ...

Using the 3.34.1 x32 from the sqlite.org website I get the following:

SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> .stats on
sqlite> .mode list
sqlite> .open test.db
sqlite> select * from tripsummary;
[ ... output elided ...]
Memory Used:                         10230728 (max 859270184) bytes
Number of Outstanding Allocations:   65479 (max 178385)
Number of Pcache Overflow Bytes:     323304 (max 346063992) bytes
Largest Allocation:                  4915200 bytes
Largest Pcache Allocation:           4256 bytes
Lookaside Slots Used:                111 (max 123)
Successful lookaside attempts:       1066
Lookaside failures due to size:      76
Lookaside failures due to OOM:       470883
Pager Heap Usage:                    407844 bytes
Page cache hits:                     11971
Page cache misses:                   95
Page cache writes:                   0
Page cache spills:                   0
Schema Heap Usage:                   267928 bytes
Statement Heap/Lookaside Usage:      9332264 bytes
Fullscan Steps:                      622088
Sort Operations:                     502
Autoindex Inserts:                   1320
Virtual Machine Steps:               374879725
Reprepare operations:                0
Number of times run:                 1
Memory used by prepared stmt:        9332264
Run Time: real 12.559 user 12.140625 sys 0.250000
sqlite>

What version of SQLite3 are you using? Note that setting Large Address Aware on the x86 executable makes no significant change even though that means that the process address space is expanded from 2GB to just under 4GB.

(5.1) By Balaji Ramanathan (balaji) on 2021-02-24 01:39:05 edited from 5.0 in reply to 4.1 [link] [source]

I am sorry, for some reason, I don't get the ability to edit my previous post. In the preview, it looked fine, but once I posted, all the line breaks went away for some reason.

Here is a better formatted post with the same information. Hope this goes through without getting chewed up. Enter ".help" for usage hints. sqlite> .timer on sqlite> .stats on sqlite> .mode list sqlite> select * from tripsummary; Memory Used: 23325376 (max 1317885864) bytes Number of Outstanding Allocations: 163442 (max 553284) Number of Pcache Overflow Bytes: 1995912 (max 357575560) bytes Largest Allocation: 9830400 bytes Largest Pcache Allocation: 4256 bytes Lookaside Slots Used: 120 (max 123) Successful lookaside attempts: 463 Lookaside failures due to size: 91357 Lookaside failures due to OOM: 5740027 Pager Heap Usage: 2075736 bytes Page cache hits: 4523979 Page cache misses: 153425 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1603936 bytes Statement Heap/Lookaside Usage: 19168912 bytes Fullscan Steps: 2171004 Sort Operations: 1882 Autoindex Inserts: 1396736 Virtual Machine Steps: 288595597 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 19168912 Run Time: real 52.945 user 28.267381 sys 6.583242 Error: out of memory sqlite>

(6) By Larry Brasfield (larrybr) on 2021-02-24 01:14:22 in reply to 5.0 [source]

You likely want to look at how Fossil's Wiki markup works. In particular, the <code> (and </code>) tags which block line filling. For example, Line 1 and Line 2 need not be run together. Click on the 'Source' button to see the example in "source" form.

(7) By Balaji Ramanathan (balaji) on 2021-02-24 01:39:32 in reply to 6 [link] [source]

Thank you, Larry.

(8) By Warren Young (wyoung) on 2021-02-24 02:19:53 in reply to 6 [link] [source]

The forum uses Markdown formatting by default. You have to go out of your way when posting to switch to Wiki formatting,

The HTML <code> tag works the same in both systems, but for Markdown, fenced code blocks are simpler to type. For short blocks, whitespace indents are even simpler, and they look good even in plain text.

(12) By Stephan Beal (stephan) on 2021-02-24 05:07:25 in reply to 5.1 [link] [source]

I am sorry, for some reason, I don't get the ability to edit my previous post.

A post which is pending moderation cannot be edited, to avoid a race condition between the moderation and the edit. Once your post has passed moderation (or your account toggled to not need moderation) you should be able to edit it.

(9) By Keith Medcalf (kmedcalf) on 2021-02-24 02:54:42 in reply to 4.1 [link] [source]

That is 437 MB more RAM than is being used by 3.34.1 x86. I presume that you are using the same version (whatever that is, I asked but you haven't said) of SQLite3 on both Windows 7 and Windows 10?

I suppose it could also be due to differences in the heap memory manager in the subsystem runtime (MSVCRT.DLL) between the two OSes as well.

(10) By Balaji Ramanathan (balaji) on 2021-02-24 04:23:18 in reply to 9 [link] [source]

Yes, same version of SQLite on both systems. I think the difference in RAM usage is because I ran my query on a database with additional tables. I removed those tables to create the test database and only retained the tables necessary to be able to run the required queries.

(11) By RandomCoder on 2021-02-24 04:38:21 in reply to 9 [link] [source]

I suspect you're right to call out the version of SQLite.

I was curious, so I dusted off a Windows 7 machine I have (with 4gb of RAM). The query works. However, if I use SQLite 3.30 the following happens on both a machine running Windows 10 with plenty of RAM, and the machine with only 4GB:

SQLite version 3.30.0 2019-10-04 15:03:17
....
sqlite> select * from tripsummary;
Memory Used:                         24935480 (max 1600678816) bytes
....
Run Time: real 17.453 user 15.843750 sys 1.593750
Error: out of memory
sqlite>

SQLite 3.31 still uses a lot more memory than the latest version of SQLite, but just barely manages to work. I suspect it would fail in some scenarios since it's probably getting close to the 2gb user space limit of Windows on x86.

(14) By Keith Medcalf (kmedcalf) on 2021-02-24 05:25:39 in reply to 11 [link] [source]

Indeed. This is for my base SQLite3 3.30.0 x64 with the same configuration as the prior x64 stats. Note how much more memory it takes and how much slower it is.

SQLite version 3.30.0 2019-10-04 17:51:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode list
sqlite> .stats on
sqlite> .timer on
sqlite> .open test.db
sqlite> select * from tripsummary;
[ ... output elided ...]
Memory Used:                         39516528 (max 3174130272) bytes
Number of Outstanding Allocations:   200668 (max 598267)
Number of Pcache Overflow Bytes:     422664 (max 1663026784) bytes
Largest Allocation:                  19398656 bytes
Largest Pcache Allocation:           4360 bytes
Lookaside Slots Used:                100 (max 100)
Successful lookaside attempts:       450
Lookaside failures due to size:      5394
Lookaside failures due to OOM:       1168975
Pager Heap Usage:                    421864 bytes
Page cache hits:                     11974
Page cache misses:                   96
Page cache writes:                   0
Page cache spills:                   0
Schema Heap Usage:                   432616 bytes
Statement Heap/Lookaside Usage:      37869344 bytes
Fullscan Steps:                      622088
Sort Operations:                     502
Autoindex Inserts:                   1320
Virtual Machine Steps:               1127164121
Reprepare operations:                0
Number of times run:                 1
Memory used by prepared stmt:        37869344
Run Time: real 26.297 user 25.156250 sys 1.015625
sqlite>

So that is 3027 MB which would run in x64 or x86 with Large Address Aware (on x64) or x86 with Large Address Aware and /3GB enabled, but would not run in a standard windows x32 2GB process.

(15) By Balaji Ramanathan (balaji) on 2021-02-24 18:47:25 in reply to 14 [link] [source]

Where can I get a 64-bit version of the sqlite3 executable? It looks like there is only a 32-bit executable available for download from the sqlite website. I am not sure that will solve my problem but it may be worth a shot.

(16) By Larry Brasfield (larrybr) on 2021-02-24 19:04:06 in reply to 15 [link] [source]

See section 20 in the CLI doc, where you will see how to build the shell. The makefiles mentioned there are included in the source amalgamations whose link text resembles 'sqlite-autoconf-#######.tar.gz'. To get a 64-bit executable with GNU's gcc, you need a '-m64' option. To get a 64-bit executable with Microsoft's CL.exe, you need to have run the batch file (provided with the compiler installation) which selects that target and associated system libraries.

(13) By Balaji Ramanathan (balaji) on 2021-02-24 05:25:21 in reply to 9 [link] [source]

Sorry, the sqlite version was on top of the output from my computer, but I edited it out by accident trying to fix the formatting.

I am using the latest version of SQLite - version 3.34.1 released on 1/20/2021 on both the computers.