This is driving me nuts

classic Classic list List threaded Threaded
33 messages Options
12
Reply | Threaded
Open this post in threaded view
|

This is driving me nuts

curmudgeon
I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was working on code that stored RowIDs in a vector. I was sizing the vector beforehand and then timing how long it took to store the RowIDs returned by an sqlite query. By accident I sized the vector too big (by a factor of 10) and found the timings doubled (sometimes tripled) despite the fact it was still only stepping through the same query. We're talking going from 20 secs to 40-60 secs.

At first I thought 'memory problem' BUT as I'm using a ms surface pro 4 with 16 GB ram and 512 GB SSD and running a 64 bit release version I thought how can that be? I mean the mistakenly sized vector only used 8 GB?

I've managed to whittle the problem down to the following console application. The 'Big' table in the following code has just under 112 million records and contains 2 integer columns and 4 text columns (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique) index on the 2 integer columns. I did a vacuum on the database beforehand. Apart from background tasks the console app was the only one running.

#include <vcl.h>
#include <windows.h>
#pragma hdrstop
#pragma argsused
#include <tchar.h>
#include <stdio.h>
#include <conio.h>
#include <iostream>
#include <vector>
#include "sqlite.h"

std::vector<int64_t> v;
const int Size[]={112000000,1000000000}; // 112 million, 1 billion
int _tmain(int argc, _TCHAR* argv[])
{
        sqlite3 *DB;
        sqlite3_open("c:/SQLiteData/Formbook.db",&DB);
        sqlite3_stmt *stmt;
        sqlite3_prepare_v2(DB,"select RowID from Big order by RowID",-1,&stmt,NULL);

        for (int i=0; i<2; i++)
        {
                v.resize(Size[i]);

                // NB the exact same code is executed whether i is 0 or 1. The only thing that
                // changes is the size() of v and v isn't even used in the timed code below.

                clock_t Start=clock();

                while (sqlite3_step(stmt)==SQLITE_ROW) {}

                // Above just steps through stmt (111,724,900 steps to be exact).

                std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC << std::endl;

                sqlite3_reset(stmt);
        }
        sqlite3_finalize(stmt);
        sqlite3_close(DB);
        getch();
        return 0;
}

5 sets of timings (secs) were as follows

i==0 i==1
17.610    24.172
20.344    24.594
19.953    24.375
19.891    23.594
19.938    25.516

I can't understand why the second pass (exact same code executed) takes an average of 4.8 secs longer.

To add to the puzzle

I tried making v an int64_t*, replaced the resize with v=new int64_t[Size[i]] and added delete [] v to the end of the inner block. Pretty much the same thing as far as memory goes yet the average timings for i==0 and i==1 were almost identical at around 17.4.

I tried replacing the sqlite related code with non-sqlite code e.g. populating v up to v.begin+112000000 using rand(). Again the timing anomaly disappeared.

I swapped Size[0] with Size[1] so that the resize was going from large to small. The timings on each pass were then similar but were still around the 24.xxx mark associated with the i==1 pass.

I copied the Big table from its current db to a new db where it was the only table. I then redid the timings using the new db but there was no real change.

i==0 i==1
17.594    25.672
20.563    24.406
21.219    23.843
20.484    25.343
20.562    25.172

I lastly tried gradually lowering Size[1] from 1 billion to 200 million step 200 million. There was little difference to the results for Size[1]=800,000,000 but at Size[1]=600,000,000 the difference was down to under a second. At Size[1]=400,000,000 timings for i==0 and i==1 were pretty much the same.


While that last test suggests it must be a memory issue it begs the question why was there no difference in timings for i==0 & i==1 when an array was used rather than a vector? Surely the memory requirements are very similar?

The timing anomaly was still apparent when Size[1]=800 million so why, when you've got 16 GB ram, does a 6.4 GB vector cause any problems?

What's it got to do with sqlite, if anything? Why was sqlite_step slowed down?

Any suggestions appreciated.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Abroży Nieprzełoży
> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
Note the difference between size and capacity of the vector.
Reducing the size does not necessarily reduce the capacity,
so the vector may not free memory when reducing size.
You should call shrink_to_fit to free additional memory.
http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit


What's the value returned by sqlite3_memory_highwater after executing the query?
http://www.sqlite.org/c3ref/memory_highwater.html


2018-05-26 17:43 GMT+02:00, x :

> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
> working on code that stored RowIDs in a vector. I was sizing the vector
> beforehand and then timing how long it took to store the RowIDs returned by
> an sqlite query. By accident I sized the vector too big (by a factor of 10)
> and found the timings doubled (sometimes tripled) despite the fact it was
> still only stepping through the same query. We're talking going from 20 secs
> to 40-60 secs.
>
> At first I thought 'memory problem' BUT as I'm using a ms surface pro 4 with
> 16 GB ram and 512 GB SSD and running a 64 bit release version I thought how
> can that be? I mean the mistakenly sized vector only used 8 GB?
>
> I've managed to whittle the problem down to the following console
> application. The 'Big' table in the following code has just under 112
> million records and contains 2 integer columns and 4 text columns
> (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique)
> index on the 2 integer columns. I did a vacuum on the database beforehand.
> Apart from background tasks the console app was the only one running.
>
> #include <vcl.h>
> #include <windows.h>
> #pragma hdrstop
> #pragma argsused
> #include <tchar.h>
> #include <stdio.h>
> #include <conio.h>
> #include <iostream>
> #include <vector>
> #include "sqlite.h"
>
> std::vector<int64_t> v;
> const int Size[]={112000000,1000000000}; // 112 million, 1 billion
> int _tmain(int argc, _TCHAR* argv[])
> {
>         sqlite3 *DB;
>         sqlite3_open("c:/SQLiteData/Formbook.db",&DB);
>         sqlite3_stmt *stmt;
>         sqlite3_prepare_v2(DB,"select RowID from Big order by
> RowID",-1,&stmt,NULL);
>
>         for (int i=0; i<2; i++)
>         {
>                 v.resize(Size[i]);
>
>                 // NB the exact same code is executed whether i is 0 or 1.
> The only thing that
>                 // changes is the size() of v and v isn't even used in the
> timed code below.
>
>                 clock_t Start=clock();
>
>                 while (sqlite3_step(stmt)==SQLITE_ROW) {}
>
>                 // Above just steps through stmt (111,724,900 steps to be
> exact).
>
>                 std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC <<
> std::endl;
>
>                 sqlite3_reset(stmt);
>         }
>         sqlite3_finalize(stmt);
>         sqlite3_close(DB);
>         getch();
>         return 0;
> }
>
> 5 sets of timings (secs) were as follows
>
> i==0 i==1
> 17.610    24.172
> 20.344    24.594
> 19.953    24.375
> 19.891    23.594
> 19.938    25.516
>
> I can't understand why the second pass (exact same code executed) takes an
> average of 4.8 secs longer.
>
> To add to the puzzle
>
> I tried making v an int64_t*, replaced the resize with v=new
> int64_t[Size[i]] and added delete [] v to the end of the inner block. Pretty
> much the same thing as far as memory goes yet the average timings for i==0
> and i==1 were almost identical at around 17.4.
>
> I tried replacing the sqlite related code with non-sqlite code e.g.
> populating v up to v.begin+112000000 using rand(). Again the timing anomaly
> disappeared.
>
> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
>
> I copied the Big table from its current db to a new db where it was the only
> table. I then redid the timings using the new db but there was no real
> change.
>
> i==0 i==1
> 17.594    25.672
> 20.563    24.406
> 21.219    23.843
> 20.484    25.343
> 20.562    25.172
>
> I lastly tried gradually lowering Size[1] from 1 billion to 200 million step
> 200 million. There was little difference to the results for
> Size[1]=800,000,000 but at Size[1]=600,000,000 the difference was down to
> under a second. At Size[1]=400,000,000 timings for i==0 and i==1 were pretty
> much the same.
>
>
> While that last test suggests it must be a memory issue it begs the question
> why was there no difference in timings for i==0 & i==1 when an array was
> used rather than a vector? Surely the memory requirements are very similar?
>
> The timing anomaly was still apparent when Size[1]=800 million so why, when
> you've got 16 GB ram, does a 6.4 GB vector cause any problems?
>
> What's it got to do with sqlite, if anything? Why was sqlite_step slowed
> down?
>
> Any suggestions appreciated.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
Hi Abrozy.



I inserted the line

        int64_t Mem=sqlite3_memory_highwater(0);

immediately after the while loop and added Mem to the cout. The values returned were

2234704 (I==0)

2234768 (I==1).



Tom



________________________________
From: sqlite-users <[hidden email]> on behalf of Abroży Nieprzełoży <[hidden email]>
Sent: Saturday, May 26, 2018 5:39:03 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
Note the difference between size and capacity of the vector.
Reducing the size does not necessarily reduce the capacity,
so the vector may not free memory when reducing size.
You should call shrink_to_fit to free additional memory.
http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit


What's the value returned by sqlite3_memory_highwater after executing the query?
http://www.sqlite.org/c3ref/memory_highwater.html


2018-05-26 17:43 GMT+02:00, x :

> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
> working on code that stored RowIDs in a vector. I was sizing the vector
> beforehand and then timing how long it took to store the RowIDs returned by
> an sqlite query. By accident I sized the vector too big (by a factor of 10)
> and found the timings doubled (sometimes tripled) despite the fact it was
> still only stepping through the same query. We're talking going from 20 secs
> to 40-60 secs.
>
> At first I thought 'memory problem' BUT as I'm using a ms surface pro 4 with
> 16 GB ram and 512 GB SSD and running a 64 bit release version I thought how
> can that be? I mean the mistakenly sized vector only used 8 GB?
>
> I've managed to whittle the problem down to the following console
> application. The 'Big' table in the following code has just under 112
> million records and contains 2 integer columns and 4 text columns
> (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique)
> index on the 2 integer columns. I did a vacuum on the database beforehand.
> Apart from background tasks the console app was the only one running.
>
> #include <vcl.h>
> #include <windows.h>
> #pragma hdrstop
> #pragma argsused
> #include <tchar.h>
> #include <stdio.h>
> #include <conio.h>
> #include <iostream>
> #include <vector>
> #include "sqlite.h"
>
> std::vector<int64_t> v;
> const int Size[]={112000000,1000000000}; // 112 million, 1 billion
> int _tmain(int argc, _TCHAR* argv[])
> {
>         sqlite3 *DB;
>         sqlite3_open("c:/SQLiteData/Formbook.db",&DB);
>         sqlite3_stmt *stmt;
>         sqlite3_prepare_v2(DB,"select RowID from Big order by
> RowID",-1,&stmt,NULL);
>
>         for (int i=0; i<2; i++)
>         {
>                 v.resize(Size[i]);
>
>                 // NB the exact same code is executed whether i is 0 or 1.
> The only thing that
>                 // changes is the size() of v and v isn't even used in the
> timed code below.
>
>                 clock_t Start=clock();
>
>                 while (sqlite3_step(stmt)==SQLITE_ROW) {}
>
>                 // Above just steps through stmt (111,724,900 steps to be
> exact).
>
>                 std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC <<
> std::endl;
>
>                 sqlite3_reset(stmt);
>         }
>         sqlite3_finalize(stmt);
>         sqlite3_close(DB);
>         getch();
>         return 0;
> }
>
> 5 sets of timings (secs) were as follows
>
> i==0 i==1
> 17.610    24.172
> 20.344    24.594
> 19.953    24.375
> 19.891    23.594
> 19.938    25.516
>
> I can't understand why the second pass (exact same code executed) takes an
> average of 4.8 secs longer.
>
> To add to the puzzle
>
> I tried making v an int64_t*, replaced the resize with v=new
> int64_t[Size[i]] and added delete [] v to the end of the inner block. Pretty
> much the same thing as far as memory goes yet the average timings for i==0
> and i==1 were almost identical at around 17.4.
>
> I tried replacing the sqlite related code with non-sqlite code e.g.
> populating v up to v.begin+112000000 using rand(). Again the timing anomaly
> disappeared.
>
> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
>
> I copied the Big table from its current db to a new db where it was the only
> table. I then redid the timings using the new db but there was no real
> change.
>
> i==0 i==1
> 17.594    25.672
> 20.563    24.406
> 21.219    23.843
> 20.484    25.343
> 20.562    25.172
>
> I lastly tried gradually lowering Size[1] from 1 billion to 200 million step
> 200 million. There was little difference to the results for
> Size[1]=800,000,000 but at Size[1]=600,000,000 the difference was down to
> under a second. At Size[1]=400,000,000 timings for i==0 and i==1 were pretty
> much the same.
>
>
> While that last test suggests it must be a memory issue it begs the question
> why was there no difference in timings for i==0 & i==1 when an array was
> used rather than a vector? Surely the memory requirements are very similar?
>
> The timing anomaly was still apparent when Size[1]=800 million so why, when
> you've got 16 GB ram, does a 6.4 GB vector cause any problems?
>
> What's it got to do with sqlite, if anything? Why was sqlite_step slowed
> down?
>
> Any suggestions appreciated.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
In reply to this post by Abroży Nieprzełoży
I also added v.shrink_to_fit() after the resize and tried again with Size[0] and Size[1] swapped. The time difference reappeared with the second pass 4+ secs faster than the first.



________________________________
From: sqlite-users <[hidden email]> on behalf of Abroży Nieprzełoży <[hidden email]>
Sent: Saturday, May 26, 2018 5:39:03 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
Note the difference between size and capacity of the vector.
Reducing the size does not necessarily reduce the capacity,
so the vector may not free memory when reducing size.
You should call shrink_to_fit to free additional memory.
http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit


What's the value returned by sqlite3_memory_highwater after executing the query?
http://www.sqlite.org/c3ref/memory_highwater.html


2018-05-26 17:43 GMT+02:00, x :

> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
> working on code that stored RowIDs in a vector. I was sizing the vector
> beforehand and then timing how long it took to store the RowIDs returned by
> an sqlite query. By accident I sized the vector too big (by a factor of 10)
> and found the timings doubled (sometimes tripled) despite the fact it was
> still only stepping through the same query. We're talking going from 20 secs
> to 40-60 secs.
>
> At first I thought 'memory problem' BUT as I'm using a ms surface pro 4 with
> 16 GB ram and 512 GB SSD and running a 64 bit release version I thought how
> can that be? I mean the mistakenly sized vector only used 8 GB?
>
> I've managed to whittle the problem down to the following console
> application. The 'Big' table in the following code has just under 112
> million records and contains 2 integer columns and 4 text columns
> (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique)
> index on the 2 integer columns. I did a vacuum on the database beforehand.
> Apart from background tasks the console app was the only one running.
>
> #include <vcl.h>
> #include <windows.h>
> #pragma hdrstop
> #pragma argsused
> #include <tchar.h>
> #include <stdio.h>
> #include <conio.h>
> #include <iostream>
> #include <vector>
> #include "sqlite.h"
>
> std::vector<int64_t> v;
> const int Size[]={112000000,1000000000}; // 112 million, 1 billion
> int _tmain(int argc, _TCHAR* argv[])
> {
>         sqlite3 *DB;
>         sqlite3_open("c:/SQLiteData/Formbook.db",&DB);
>         sqlite3_stmt *stmt;
>         sqlite3_prepare_v2(DB,"select RowID from Big order by
> RowID",-1,&stmt,NULL);
>
>         for (int i=0; i<2; i++)
>         {
>                 v.resize(Size[i]);
>
>                 // NB the exact same code is executed whether i is 0 or 1.
> The only thing that
>                 // changes is the size() of v and v isn't even used in the
> timed code below.
>
>                 clock_t Start=clock();
>
>                 while (sqlite3_step(stmt)==SQLITE_ROW) {}
>
>                 // Above just steps through stmt (111,724,900 steps to be
> exact).
>
>                 std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC <<
> std::endl;
>
>                 sqlite3_reset(stmt);
>         }
>         sqlite3_finalize(stmt);
>         sqlite3_close(DB);
>         getch();
>         return 0;
> }
>
> 5 sets of timings (secs) were as follows
>
> i==0 i==1
> 17.610    24.172
> 20.344    24.594
> 19.953    24.375
> 19.891    23.594
> 19.938    25.516
>
> I can't understand why the second pass (exact same code executed) takes an
> average of 4.8 secs longer.
>
> To add to the puzzle
>
> I tried making v an int64_t*, replaced the resize with v=new
> int64_t[Size[i]] and added delete [] v to the end of the inner block. Pretty
> much the same thing as far as memory goes yet the average timings for i==0
> and i==1 were almost identical at around 17.4.
>
> I tried replacing the sqlite related code with non-sqlite code e.g.
> populating v up to v.begin+112000000 using rand(). Again the timing anomaly
> disappeared.
>
> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
>
> I copied the Big table from its current db to a new db where it was the only
> table. I then redid the timings using the new db but there was no real
> change.
>
> i==0 i==1
> 17.594    25.672
> 20.563    24.406
> 21.219    23.843
> 20.484    25.343
> 20.562    25.172
>
> I lastly tried gradually lowering Size[1] from 1 billion to 200 million step
> 200 million. There was little difference to the results for
> Size[1]=800,000,000 but at Size[1]=600,000,000 the difference was down to
> under a second. At Size[1]=400,000,000 timings for i==0 and i==1 were pretty
> much the same.
>
>
> While that last test suggests it must be a memory issue it begs the question
> why was there no difference in timings for i==0 & i==1 when an array was
> used rather than a vector? Surely the memory requirements are very similar?
>
> The timing anomaly was still apparent when Size[1]=800 million so why, when
> you've got 16 GB ram, does a 6.4 GB vector cause any problems?
>
> What's it got to do with sqlite, if anything? Why was sqlite_step slowed
> down?
>
> Any suggestions appreciated.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Clemens Ladisch
In reply to this post by curmudgeon
x wrote:

> const int Size[]={112000000,1000000000}; // 112 million, 1 billion
>
>                 v.resize(Size[i]);
>
>                 // NB the exact same code is executed whether i is 0 or 1. The only thing that
>                 // changes is the size() of v and v isn't even used in the timed code below.
>
>                 clock_t Start=clock();
>
>                 while (sqlite3_step(stmt)==SQLITE_ROW) {}
>
>                 // Above just steps through stmt (111,724,900 steps to be exact).
>
>                 std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC << std::endl;
> i==0 i==1
> 19.938    25.516
>
> I can't understand why the second pass (exact same code executed) takes an average of 4.8 secs longer.

I'd guess that allocating the vector throws out cached data from the OS file cache,
so it has to be read again from disk.  To measure this, add a pause between the
loop iterations, and use some tool (e.g., Resource Monitor) to check actual disk
accesses.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Abroży Nieprzełoży
In reply to this post by curmudgeon
Collect some memory statistics before and after memory allocation and
before and after query execution.

----------------------------------------------------
#include <stdio.h>
#include <Windows.h>
#include <Psapi.h>

void memoryStats()
{
  PROCESS_MEMORY_COUNTERS_EX pmc;
  PERFORMANCE_INFORMATION pi;
  MEMORYSTATUSEX ms;
  ZeroMemory(&pmc, sizeof(pmc));
  ZeroMemory(&pi, sizeof(pi));
  ZeroMemory(&ms, sizeof(ms));
  pmc.cb = sizeof(pmc);
  pi.cb = sizeof(pi);
  ms.dwLength = sizeof(ms);
  GetProcessMemoryInfo(GetCurrentProcess(),
(PROCESS_MEMORY_COUNTERS*)&pmc, sizeof(pmc));
  GetPerformanceInfo(&pi, sizeof(pi));
  GlobalMemoryStatusEx(&ms);
  printf("PMC.PageFaultCount             = %20u\n", pmc.PageFaultCount);
  printf("PMC.PeakWorkingSetSize         = %20zu\n", pmc.PeakWorkingSetSize);
  printf("PMC.WorkingSetSize             = %20zu\n", pmc.WorkingSetSize);
  printf("PMC.QuotaPeakPagedPoolUsage    = %20zu\n",
pmc.QuotaPeakPagedPoolUsage);
  printf("PMC.QuotaPagedPoolUsage        = %20zu\n", pmc.QuotaPagedPoolUsage);
  printf("PMC.QuotaPeakNonPagedPoolUsage = %20zu\n",
pmc.QuotaPeakNonPagedPoolUsage);
  printf("PMC.QuotaNonPagedPoolUsage     = %20zu\n",
pmc.QuotaNonPagedPoolUsage);
  printf("PMC.PagefileUsage              = %20zu\n", pmc.PagefileUsage);
  printf("PMC.PeakPagefileUsage          = %20zu\n", pmc.PeakPagefileUsage);
  printf("PMC.PrivateUsage               = %20zu\n", pmc.PrivateUsage);
  printf("PI.CommitTotal                 = %20zu\n", pi.CommitTotal);
  printf("PI.CommitLimit                 = %20zu\n", pi.CommitLimit);
  printf("PI.CommitPeak                  = %20zu\n", pi.CommitPeak);
  printf("PI.PhysicalTotal               = %20zu\n", pi.PhysicalTotal);
  printf("PI.PhysicalAvailable           = %20zu\n", pi.PhysicalAvailable);
  printf("PI.SystemCache                 = %20zu\n", pi.SystemCache);
  printf("PI.KernelTotal                 = %20zu\n", pi.KernelTotal);
  printf("PI.KernelPaged                 = %20zu\n", pi.KernelPaged);
  printf("PI.KernelNonpaged              = %20zu\n", pi.KernelNonpaged);
  printf("PI.PageSize                    = %20zu\n", pi.PageSize);
  printf("PI.HandleCount                 = %20u\n", pi.HandleCount);
  printf("PI.ProcessCount                = %20u\n", pi.ProcessCount);
  printf("PI.ThreadCount                 = %20u\n", pi.ThreadCount);
  printf("MS.dwMemoryLoad                = %20u\n", ms.dwMemoryLoad);
  printf("MS.ullTotalPhys                = %20llu\n", ms.ullTotalPhys);
  printf("MS.ullAvailPhys                = %20llu\n", ms.ullAvailPhys);
  printf("MS.ullTotalPageFile            = %20llu\n", ms.ullTotalPageFile);
  printf("MS.ullAvailPageFile            = %20llu\n", ms.ullAvailPageFile);
  printf("MS.ullTotalVirtual             = %20llu\n", ms.ullTotalVirtual);
  printf("MS.ullAvailVirtual             = %20llu\n", ms.ullAvailVirtual);
  printf("MS.ullAvailExtendedVirtual     = %20llu\n",
ms.ullAvailExtendedVirtual);
}
----------------------------------------------------



2018-05-26 19:01 GMT+02:00, x <[hidden email]>:

> Hi Abrozy.
>
>
>
> I inserted the line
>
>         int64_t Mem=sqlite3_memory_highwater(0);
>
> immediately after the while loop and added Mem to the cout. The values
> returned were
>
> 2234704 (I==0)
>
> 2234768 (I==1).
>
>
>
> Tom
>
>
>
> ________________________________
> From: sqlite-users <[hidden email]> on behalf
> of Abroży Nieprzełoży <[hidden email]>
> Sent: Saturday, May 26, 2018 5:39:03 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] This is driving me nuts
>
>> I swapped Size[0] with Size[1] so that the resize was going from large to
>> small. The timings on each pass were then similar but were still around
>> the
>> 24.xxx mark associated with the i==1 pass.
> Note the difference between size and capacity of the vector.
> Reducing the size does not necessarily reduce the capacity,
> so the vector may not free memory when reducing size.
> You should call shrink_to_fit to free additional memory.
> http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit
>
>
> What's the value returned by sqlite3_memory_highwater after executing the
> query?
> http://www.sqlite.org/c3ref/memory_highwater.html
>
>
> 2018-05-26 17:43 GMT+02:00, x :
>> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
>> working on code that stored RowIDs in a vector. I was sizing the vector
>> beforehand and then timing how long it took to store the RowIDs returned
>> by
>> an sqlite query. By accident I sized the vector too big (by a factor of
>> 10)
>> and found the timings doubled (sometimes tripled) despite the fact it was
>> still only stepping through the same query. We're talking going from 20
>> secs
>> to 40-60 secs.
>>
>> At first I thought 'memory problem' BUT as I'm using a ms surface pro 4
>> with
>> 16 GB ram and 512 GB SSD and running a 64 bit release version I thought
>> how
>> can that be? I mean the mistakenly sized vector only used 8 GB?
>>
>> I've managed to whittle the problem down to the following console
>> application. The 'Big' table in the following code has just under 112
>> million records and contains 2 integer columns and 4 text columns
>> (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique)
>> index on the 2 integer columns. I did a vacuum on the database beforehand.
>> Apart from background tasks the console app was the only one running.
>>
>> #include <vcl.h>
>> #include <windows.h>
>> #pragma hdrstop
>> #pragma argsused
>> #include <tchar.h>
>> #include <stdio.h>
>> #include <conio.h>
>> #include <iostream>
>> #include <vector>
>> #include "sqlite.h"
>>
>> std::vector<int64_t> v;
>> const int Size[]={112000000,1000000000}; // 112 million, 1 billion
>> int _tmain(int argc, _TCHAR* argv[])
>> {
>>         sqlite3 *DB;
>>         sqlite3_open("c:/SQLiteData/Formbook.db",&DB);
>>         sqlite3_stmt *stmt;
>>         sqlite3_prepare_v2(DB,"select RowID from Big order by
>> RowID",-1,&stmt,NULL);
>>
>>         for (int i=0; i<2; i++)
>>         {
>>                 v.resize(Size[i]);
>>
>>                 // NB the exact same code is executed whether i is 0 or 1.
>> The only thing that
>>                 // changes is the size() of v and v isn't even used in the
>> timed code below.
>>
>>                 clock_t Start=clock();
>>
>>                 while (sqlite3_step(stmt)==SQLITE_ROW) {}
>>
>>                 // Above just steps through stmt (111,724,900 steps to be
>> exact).
>>
>>                 std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC <<
>> std::endl;
>>
>>                 sqlite3_reset(stmt);
>>         }
>>         sqlite3_finalize(stmt);
>>         sqlite3_close(DB);
>>         getch();
>>         return 0;
>> }
>>
>> 5 sets of timings (secs) were as follows
>>
>> i==0 i==1
>> 17.610    24.172
>> 20.344    24.594
>> 19.953    24.375
>> 19.891    23.594
>> 19.938    25.516
>>
>> I can't understand why the second pass (exact same code executed) takes an
>> average of 4.8 secs longer.
>>
>> To add to the puzzle
>>
>> I tried making v an int64_t*, replaced the resize with v=new
>> int64_t[Size[i]] and added delete [] v to the end of the inner block.
>> Pretty
>> much the same thing as far as memory goes yet the average timings for i==0
>> and i==1 were almost identical at around 17.4.
>>
>> I tried replacing the sqlite related code with non-sqlite code e.g.
>> populating v up to v.begin+112000000 using rand(). Again the timing
>> anomaly
>> disappeared.
>>
>> I swapped Size[0] with Size[1] so that the resize was going from large to
>> small. The timings on each pass were then similar but were still around
>> the
>> 24.xxx mark associated with the i==1 pass.
>>
>> I copied the Big table from its current db to a new db where it was the
>> only
>> table. I then redid the timings using the new db but there was no real
>> change.
>>
>> i==0 i==1
>> 17.594    25.672
>> 20.563    24.406
>> 21.219    23.843
>> 20.484    25.343
>> 20.562    25.172
>>
>> I lastly tried gradually lowering Size[1] from 1 billion to 200 million
>> step
>> 200 million. There was little difference to the results for
>> Size[1]=800,000,000 but at Size[1]=600,000,000 the difference was down to
>> under a second. At Size[1]=400,000,000 timings for i==0 and i==1 were
>> pretty
>> much the same.
>>
>>
>> While that last test suggests it must be a memory issue it begs the
>> question
>> why was there no difference in timings for i==0 & i==1 when an array was
>> used rather than a vector? Surely the memory requirements are very
>> similar?
>>
>> The timing anomaly was still apparent when Size[1]=800 million so why,
>> when
>> you've got 16 GB ram, does a 6.4 GB vector cause any problems?
>>
>> What's it got to do with sqlite, if anything? Why was sqlite_step slowed
>> down?
>>
>> Any suggestions appreciated.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Warren Young
In reply to this post by curmudgeon
On May 26, 2018, at 9:43 AM, x <[hidden email]> wrote:
>
> I was sizing the vector beforehand and then timing how long it took to store the RowIDs returned by an sqlite query.

You’re not actually modifying the vector, only allocating it and then not using it. A sufficiently smart optimizer might optimize that vector away.

> the mistakenly sized vector only used 8 GB?

How much memory is actually available to your program when it runs, and how does that change from run to run?

You say the machine has 16 GB of RAM, but Windows 10 will be using a huge chunk of that, and it won’t give all of what it isn’t immediately using to a single user program.

It is quite possible you’ve driven your computer into swapping with this test, if only to swap out other programs’ pages to disk so that Windows *can* give your single program half of the system RAM.

Never forget that your program isn’t the only one running on the machine.  Pull up process monitor and put it into the advanced view; you’ll find dozens of other programs trying to run at the same time, if only the OS and its core services.

> sqlite3_prepare_v2(DB,"select RowID from Big order by RowID",-1,&stmt,NULL);

How representative is this of the real code you intend to write?  If you are indeed going to be processing billions of rows in your real DB, you will typically either want to be processing the data as you read it in from the DB, not re-storing it in a std::vector unprocessed, or you will want to put WHERE clauses and such into the query to limit the amount of data you pull to only that which you actually require.

If you’re scanning the whole table on every program run, SQL — any dialect — isn’t buying you very much.  SQLite is just acting as a data file format for you in that case, offering little more above that than a flat data file.  SQLite is giving you a lot of power here; can you use more of it to get some of the benefit of the complexity you’ve bought?

>                v.resize(Size[i]);

As another reply said, this doesn’t necessarily do what you expect it will.  std::vector is allowed to do several clever things to avoid unnecessary reallocations, and you’re leaving the door open to them here.

If you want to ensure that the vector is completely reallocated every time through, declare it inside the loop to prevent std::vector from reusing previously allocated space.

> What's it got to do with sqlite, if anything? Why was sqlite_step slowed down?

Why do you believe that is the correct diagnosis?  Have you got a fine-grained measurement to prove it, such as from a profiler?

Or, have you repeated the measurement in two parts to separate the SQLite operations from the std::vector operations?

You’re on the path to Science here, but you’re missing a key tenet: test only one thing at a time.  This program of yours tests at least three different things — OS memory manager, std::vector memory allocator strategy, and SQLite — at the same time without providing a provision to isolate the confounds.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
Thanks for the input gents. I’m going to have to look into the memory stuff as it’s something I know little about and it’s near bedtime in Scotland.



Warren, the actual programme does use the vector (it’s used to store the RowIDs). The console app mark 1 also did this (results were the same) but I left it out when I noticed it made no difference to the timings to  add to the lunacy.



Your point about the swapping was something I thought about but can’t understand why this apparently doesn’t happen when I use an array created with new and why no slowdown occurs despite the fact it’s grabbing the same memory. Maybe it’s the array that’s being optimised away but, like I said, the results were the same when I was assigning the results of the sqlite3_steps to the vector.



The actual programme only does the resize once, setting the size to the largest one it’s likely to require.



If you look back at my original post you’ll see that on one test I did remove all the sqlite code and replace it with code assigning rand() numbers to the array. No difference in the timings of the two passes were noted in this case.



Will look in tomorrow. Thanks again.





________________________________
From: sqlite-users <[hidden email]> on behalf of Warren Young <[hidden email]>
Sent: Saturday, May 26, 2018 8:54:33 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

On May 26, 2018, at 9:43 AM, x <[hidden email]> wrote:
>
> I was sizing the vector beforehand and then timing how long it took to store the RowIDs returned by an sqlite query.

You’re not actually modifying the vector, only allocating it and then not using it. A sufficiently smart optimizer might optimize that vector away.

> the mistakenly sized vector only used 8 GB?

How much memory is actually available to your program when it runs, and how does that change from run to run?

You say the machine has 16 GB of RAM, but Windows 10 will be using a huge chunk of that, and it won’t give all of what it isn’t immediately using to a single user program.

It is quite possible you’ve driven your computer into swapping with this test, if only to swap out other programs’ pages to disk so that Windows *can* give your single program half of the system RAM.

Never forget that your program isn’t the only one running on the machine.  Pull up process monitor and put it into the advanced view; you’ll find dozens of other programs trying to run at the same time, if only the OS and its core services.

> sqlite3_prepare_v2(DB,"select RowID from Big order by RowID",-1,&stmt,NULL);

How representative is this of the real code you intend to write?  If you are indeed going to be processing billions of rows in your real DB, you will typically either want to be processing the data as you read it in from the DB, not re-storing it in a std::vector unprocessed, or you will want to put WHERE clauses and such into the query to limit the amount of data you pull to only that which you actually require.

If you’re scanning the whole table on every program run, SQL — any dialect — isn’t buying you very much.  SQLite is just acting as a data file format for you in that case, offering little more above that than a flat data file.  SQLite is giving you a lot of power here; can you use more of it to get some of the benefit of the complexity you’ve bought?

>                v.resize(Size[i]);

As another reply said, this doesn’t necessarily do what you expect it will.  std::vector is allowed to do several clever things to avoid unnecessary reallocations, and you’re leaving the door open to them here.

If you want to ensure that the vector is completely reallocated every time through, declare it inside the loop to prevent std::vector from reusing previously allocated space.

> What's it got to do with sqlite, if anything? Why was sqlite_step slowed down?

Why do you believe that is the correct diagnosis?  Have you got a fine-grained measurement to prove it, such as from a profiler?

Or, have you repeated the measurement in two parts to separate the SQLite operations from the std::vector operations?

You’re on the path to Science here, but you’re missing a key tenet: test only one thing at a time.  This program of yours tests at least three different things — OS memory manager, std::vector memory allocator strategy, and SQLite — at the same time without providing a provision to isolate the confounds.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
In reply to this post by Warren Young
I’ve changed the app to populate v with the query result and noted the free ram before and after each resize. I’m hoping that addresses some of the concerns re compiler optimisations even if it doesn’t supply any real answers? Results were similar to before.

#include <vcl.h>
#include <windows.h>
#pragma hdrstop
#pragma argsused
#include <tchar.h>
#include <stdio.h>
#include <conio.h>
#include <iostream>
#include <vector>
#include "sqlite.h"

std::vector<int64_t> v;
const int Size[]={112000000,1000000000}; // 112 million, 1 billion

uint64_t FreeMBs()
{
        MEMORYSTATUSEX status;
        status.dwLength = sizeof(status);
        GlobalMemoryStatusEx(&status);
        return status.ullAvailPhys / (1024 * 1024);
}

int _tmain(int argc, _TCHAR* argv[])
{
        sqlite3 *DB;
        sqlite3_open("c:/SQLiteData/MyTemp.db",&DB);
        sqlite3_stmt *stmt;
        sqlite3_prepare_v2(DB,"select RowID from Big order by RowID",-1,&stmt,NULL);

        std::cout << FreeMBs() << " MB" << std::endl;

        for (int i=0; i<2; i++)
        {
                v.resize(Size[i]);
                v.shrink_to_fit();
                int64_t Memi=FreeMBs();

                clock_t Start=clock();
                for (int i=0; i<Size[0] && sqlite3_step(stmt)==SQLITE_ROW; i++)
                       v[i]=sqlite3_column_int64(stmt,0);
                std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC << " secs - Mem" << i << " = "
                << Memi << " MB" << std::endl;
                sqlite3_reset(stmt);
        }
        sqlite3_finalize(stmt);
        sqlite3_close(DB);
        getch();
        return 0;
}

OUTPUT (5 runs)

12891 MB
21.234 secs - Mem0 = 12042 MB
26.828 secs - Mem1 = 5290 MB

12951 MB
22.344 secs - Mem0 = 12092 MB
27.063 secs - Mem1 = 5332 MB

12658 MB
22.938 secs - Mem0 = 11803 MB
27.047 secs - Mem1 = 5071 MB

12820 MB
22.734 secs - Mem0 = 11970 MB
26.688 secs - Mem1 = 5211 MB

12803 MB
22.469 secs - Mem0 = 11954 MB
26.609 secs - Mem1 = 5209 MB
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
This post was updated on .
In reply to this post by Warren Young
Strange. I repeated the test with v an int64 array (see below) however the FreeMB() doesn’t change. Both Memi’s return pretty much the value of FreeMB() before the loop ???????????????

        std::cout << FreeMBs() << " MB" << std::endl;

        for (int i=0; i<2; i++)
        {
                v=new int64_t[Size[i]];
                int64_t Memi=FreeMBs();

                clock_t Start=clock();
                for (int i=0; i<Size[0] && sqlite3_step(stmt)==SQLITE_ROW; i++)
                       v[i]=sqlite3_column_int64(stmt,0);
                std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC << " secs - Mem" << i << " = "
                << Memi << " MB" << std::endl;
                sqlite3_reset(stmt);
                delete [] v;
        }
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
It seems the array was being optimised away. I had to initialise every value
to get the OS to claim the RAM. Once I did that the timings for the array
were on a par with the vector with the second pass being slower than the
first.

While that clears up that part of the mystery I'm no closer to a solution.
Going back to the latest set of results why is the assignments in the second
pass taking so much longer when there's still 5+ GB of memory free?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Abroży Nieprzełoży
I think you can experiment with changing the the working set size
limits and see what the effect will be.
https://msdn.microsoft.com/en-us/library/cc441804

2018-05-27 17:09 GMT+02:00, curmudgeon <[hidden email]>:

> It seems the array was being optimised away. I had to initialise every value
> to get the OS to claim the RAM. Once I did that the timings for the array
> were on a par with the vector with the second pass being slower than the
> first.
>
> While that clears up that part of the mystery I'm no closer to a solution.
> Going back to the latest set of results why is the assignments in the second
> pass taking so much longer when there's still 5+ GB of memory free?
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
Starting to mess about with windows handles and pages I’ve never heard of is beyond my pain threshold Abrozy. Thanks anyway.



________________________________
From: sqlite-users <[hidden email]> on behalf of Abroży Nieprzełoży <[hidden email]>
Sent: Sunday, May 27, 2018 5:23:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

I think you can experiment with changing the the working set size
limits and see what the effect will be.
https://msdn.microsoft.com/en-us/library/cc441804

2018-05-27 17:09 GMT+02:00, curmudgeon <[hidden email]>:

> It seems the array was being optimised away. I had to initialise every value
> to get the OS to claim the RAM. Once I did that the timings for the array
> were on a par with the vector with the second pass being slower than the
> first.
>
> While that clears up that part of the mystery I'm no closer to a solution.
> Going back to the latest set of results why is the assignments in the second
> pass taking so much longer when there's still 5+ GB of memory free?
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Abroży Nieprzełoży
Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
you may use the function enableIncreaseWorkingSetPrivilege listed
below, it should return 0 on success - then you can use
SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
Working set sizes must be multiple of page size (typically 4096 bytes).
https://msdn.microsoft.com/en-us/library/ms686234

-------------------------------------------------------------
int enableIncreaseWorkingSetPrivilege()
{
        int rc;
        DWORD err;
        HANDLE hToken;
        TOKEN_PRIVILEGES privilege;
        memset(&privilege, 0, sizeof(privilege));
        privilege.PrivilegeCount = 1;
        privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
        rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
&privilege.Privileges[0].Luid);
        if (!rc)
                return -1;
        rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, &hToken);
        if (!rc)
                return -2;
        rc = AdjustTokenPrivileges(hToken, 0, &privilege, sizeof(privilege),
NULL, NULL);
        err = GetLastError();
        CloseHandle(hToken);
        if (!rc || err)
                return -3;
        return 0;
}
-------------------------------------------------------------




You can also compile SQLite with -DSQLITE_ZERO_MALLOC=1 and
-DSQLITE_ENABLE_MEMSYS5=1 and configure it to use buffer of locked
memory.
Locked memory won't get swapped so SQLite won't encounter page faults.

Function configSqliteMemory listed below allocs few megabytes of
memory, locks it and configures SQLite. It also increases working set
size because maximum quantity of memory that process can lock is equal
to minimum working set size (locked memory is included in the working
set).

-------------------------------------------------------------
int configSqliteMemory()
{
        int rc;
        void* memPtr;
        SIZE_T memSize = 64 * 1024 * 1024;
        SIZE_T wsMinSize, wsMaxSize;
        rc = GetProcessWorkingSetSize(GetCurrentProcess(), &wsMinSize, &wsMaxSize);
        if (!rc)
                return -1;
        wsMinSize += memSize;
        wsMaxSize += memSize;
        rc = SetProcessWorkingSetSize(GetCurrentProcess(), wsMinSize, wsMaxSize);
        if (!rc)
                return -2;
        memPtr = VirtualAlloc(NULL, memSize, MEM_RESERVE | MEM_COMMIT, PAGE_READWRITE);
        if (!memPtr)
                return -3;
        rc = VirtualLock(memPtr, memSize);
        if (!rc)
        {
                VirtualFree(memPtr, 0, MEM_RELEASE);
                return -4;
        }
        rc = sqlite3_config(SQLITE_CONFIG_HEAP, memPtr, (int)memSize, 64);
        if (rc != SQLITE_OK)
        {
                VirtualFree(memPtr, 0, MEM_RELEASE);
                return -5;
        }
        return 0;
}
-------------------------------------------------------------




2018-05-27 20:03 GMT+02:00, x <[hidden email]>:

> Starting to mess about with windows handles and pages I’ve never heard of is
> beyond my pain threshold Abrozy. Thanks anyway.
>
>
>
> ________________________________
> From: sqlite-users <[hidden email]> on behalf
> of Abroży Nieprzełoży <[hidden email]>
> Sent: Sunday, May 27, 2018 5:23:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] This is driving me nuts
>
> I think you can experiment with changing the the working set size
> limits and see what the effect will be.
> https://msdn.microsoft.com/en-us/library/cc441804
>
> 2018-05-27 17:09 GMT+02:00, curmudgeon <[hidden email]>:
>> It seems the array was being optimised away. I had to initialise every
>> value
>> to get the OS to claim the RAM. Once I did that the timings for the array
>> were on a par with the vector with the second pass being slower than the
>> first.
>>
>> While that clears up that part of the mystery I'm no closer to a solution.
>> Going back to the latest set of results why is the assignments in the
>> second
>> pass taking so much longer when there's still 5+ GB of memory free?
>>
>>
>>
>> --
>> Sent from: http://sqlite.1065341.n5.nabble.com/
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Abroży Nieprzełoży
Alternatively you can alloc physical memory using AWE mechanism.
AWE requires SeLockMemoryPrivilege, so you may have to run the
application on an administator account (or other account with
sufficient rigths).
When using AWE there is no need to increase working set size because
AWE pages are not included in the working set.

-----------------------------------------------------------
int enableLockMemoryPrivilege()
{
        int rc;
        DWORD err;
        HANDLE hToken;
        TOKEN_PRIVILEGES privilege;
        memset(&privilege, 0, sizeof(privilege));
        privilege.PrivilegeCount = 1;
        privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
        rc = LookupPrivilegeValue(NULL, SE_LOCK_MEMORY_NAME,
&privilege.Privileges[0].Luid);
        if (!rc)
                return -1;
        rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, &hToken);
        if (!rc)
                return -2;
        rc = AdjustTokenPrivileges(hToken, 0, &privilege, sizeof(privilege),
NULL, NULL);
        err = GetLastError();
        CloseHandle(hToken);
        if (!rc || err)
                return -3;
        return 0;
}

struct PhysMem
{
        void* pMem;
        SIZE_T sMem;
        ULONG_PTR nPages;
        ULONG_PTR* aPages;
};

void unmapAndFreePhysicalPages(struct PhysMem* mem)
{
        if(mem->pMem)
                VirtualFree(mem->pMem, 0, MEM_RELEASE);
        if (mem->aPages)
        {
                FreeUserPhysicalPages(GetCurrentProcess(), &mem->nPages, mem->aPages);
                free(mem->aPages);
        }
        memset(mem, 0, sizeof(*mem));
}

int allocAndMapPhysicalPages(struct PhysMem* mem, SIZE_T size)
{
        int rc;
        SYSTEM_INFO sysInfo;
        memset(mem, 0, sizeof(*mem));
        GetNativeSystemInfo(&sysInfo);
        mem->nPages = size / sysInfo.dwPageSize;
        if (size % sysInfo.dwPageSize > 0)
                mem->nPages++;
        size = mem->nPages * sysInfo.dwPageSize;
        mem->aPages = (ULONG_PTR*)calloc(mem->nPages, sizeof(ULONG_PTR));
        if (!mem->aPages)
                return -1;
        rc = AllocateUserPhysicalPages(GetCurrentProcess(), &mem->nPages, mem->aPages);
        if (!rc)
        {
                free(mem->aPages);
                memset(mem, 0, sizeof(*mem));
                return -2;
        }
        mem->sMem = size = mem->nPages * sysInfo.dwPageSize;
        mem->pMem = VirtualAlloc(NULL, mem->sMem, MEM_RESERVE | MEM_PHYSICAL,
PAGE_READWRITE);
        if (!mem->pMem)
        {
                unmapAndFreePhysicalPages(mem);
                return -3;
        }
        rc = MapUserPhysicalPages(mem->pMem, mem->nPages, mem->aPages);
        if (!rc)
        {
                unmapAndFreePhysicalPages(mem);
                return -4;
        }
        return 0;
}

int configSqlitePhysMemory()
{
        int rc;
        struct PhysMem mem;
        rc = allocAndMapPhysicalPages(&mem, 64 * 1024 * 1024);
        if (rc < 0)
                return -1;
        rc = sqlite3_config(SQLITE_CONFIG_HEAP, mem.pMem, (int)mem.sMem, 64);
        if (rc != SQLITE_OK)
        {
                unmapAndFreePhysicalPages(&mem);
                return -2;
        }
        return 0;
}

int enableLockMemPrivilegeAndConfigSqliteMemory()
{
        int rc;
        rc = enableLockMemoryPrivilege();
        if (rc < 0)
                return -1;
        rc = configSqlitePhysMemory();
        if (rc < 0)
                return -2;
        return 0;
}
-----------------------------------------------------------



2018-05-28 0:23 GMT+02:00, Abroży Nieprzełoży
<[hidden email]>:

> Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
> you may use the function enableIncreaseWorkingSetPrivilege listed
> below, it should return 0 on success - then you can use
> SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
> Working set sizes must be multiple of page size (typically 4096 bytes).
> https://msdn.microsoft.com/en-us/library/ms686234
>
> -------------------------------------------------------------
> int enableIncreaseWorkingSetPrivilege()
> {
> int rc;
> DWORD err;
> HANDLE hToken;
> TOKEN_PRIVILEGES privilege;
> memset(&privilege, 0, sizeof(privilege));
> privilege.PrivilegeCount = 1;
> privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
> rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
> &privilege.Privileges[0].Luid);
> if (!rc)
> return -1;
> rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES,
> &hToken);
> if (!rc)
> return -2;
> rc = AdjustTokenPrivileges(hToken, 0, &privilege, sizeof(privilege),
> NULL, NULL);
> err = GetLastError();
> CloseHandle(hToken);
> if (!rc || err)
> return -3;
> return 0;
> }
> -------------------------------------------------------------
>
>
>
>
> You can also compile SQLite with -DSQLITE_ZERO_MALLOC=1 and
> -DSQLITE_ENABLE_MEMSYS5=1 and configure it to use buffer of locked
> memory.
> Locked memory won't get swapped so SQLite won't encounter page faults.
>
> Function configSqliteMemory listed below allocs few megabytes of
> memory, locks it and configures SQLite. It also increases working set
> size because maximum quantity of memory that process can lock is equal
> to minimum working set size (locked memory is included in the working
> set).
>
> -------------------------------------------------------------
> int configSqliteMemory()
> {
> int rc;
> void* memPtr;
> SIZE_T memSize = 64 * 1024 * 1024;
> SIZE_T wsMinSize, wsMaxSize;
> rc = GetProcessWorkingSetSize(GetCurrentProcess(), &wsMinSize,
> &wsMaxSize);
> if (!rc)
> return -1;
> wsMinSize += memSize;
> wsMaxSize += memSize;
> rc = SetProcessWorkingSetSize(GetCurrentProcess(), wsMinSize, wsMaxSize);
> if (!rc)
> return -2;
> memPtr = VirtualAlloc(NULL, memSize, MEM_RESERVE | MEM_COMMIT,
> PAGE_READWRITE);
> if (!memPtr)
> return -3;
> rc = VirtualLock(memPtr, memSize);
> if (!rc)
> {
> VirtualFree(memPtr, 0, MEM_RELEASE);
> return -4;
> }
> rc = sqlite3_config(SQLITE_CONFIG_HEAP, memPtr, (int)memSize, 64);
> if (rc != SQLITE_OK)
> {
> VirtualFree(memPtr, 0, MEM_RELEASE);
> return -5;
> }
> return 0;
> }
> -------------------------------------------------------------
>
>
>
>
> 2018-05-27 20:03 GMT+02:00, x <[hidden email]>:
>> Starting to mess about with windows handles and pages I’ve never heard of
>> is
>> beyond my pain threshold Abrozy. Thanks anyway.
>>
>>
>>
>> ________________________________
>> From: sqlite-users <[hidden email]> on
>> behalf
>> of Abroży Nieprzełoży <[hidden email]>
>> Sent: Sunday, May 27, 2018 5:23:12 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] This is driving me nuts
>>
>> I think you can experiment with changing the the working set size
>> limits and see what the effect will be.
>> https://msdn.microsoft.com/en-us/library/cc441804
>>
>> 2018-05-27 17:09 GMT+02:00, curmudgeon <[hidden email]>:
>>> It seems the array was being optimised away. I had to initialise every
>>> value
>>> to get the OS to claim the RAM. Once I did that the timings for the
>>> array
>>> were on a par with the vector with the second pass being slower than the
>>> first.
>>>
>>> While that clears up that part of the mystery I'm no closer to a
>>> solution.
>>> Going back to the latest set of results why is the assignments in the
>>> second
>>> pass taking so much longer when there's still 5+ GB of memory free?
>>>
>>>
>>>
>>> --
>>> Sent from: http://sqlite.1065341.n5.nabble.com/
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
Many thanks for your efforts Abrozy. I will attempt to understand it later but the artificially created ‘Big’ table is something of a worst case scenario for me so I’m not sure it would be the best use of my time. If THEY can’t get memory management right who am I to think I will.



I have to say this has severely dented my confidence though. The example console app is the thin edge of a much bigger wedge. The original problem (part of a relatively small c++ sqlite wrapper test app) is more complex because the storing of RowIDs was taking place in a separate thread. Increasing the size of the vector in that (112 million to 400 million) increases the running time of the thread from 22 secs to around 44. That’s the only change required to bring about that increase. How can a 3+ GB vector cause such mayhem on a pc with 16 GB RAM?





________________________________
From: sqlite-users <[hidden email]> on behalf of Abroży Nieprzełoży <[hidden email]>
Sent: Monday, May 28, 2018 1:26:13 AM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

Alternatively you can alloc physical memory using AWE mechanism.
AWE requires SeLockMemoryPrivilege, so you may have to run the
application on an administator account (or other account with
sufficient rigths).
When using AWE there is no need to increase working set size because
AWE pages are not included in the working set.

-----------------------------------------------------------
int enableLockMemoryPrivilege()
{
        int rc;
        DWORD err;
        HANDLE hToken;
        TOKEN_PRIVILEGES privilege;
        memset(&privilege, 0, sizeof(privilege));
        privilege.PrivilegeCount = 1;
        privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
        rc = LookupPrivilegeValue(NULL, SE_LOCK_MEMORY_NAME,
&privilege.Privileges[0].Luid);
        if (!rc)
                return -1;
        rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, &hToken);
        if (!rc)
                return -2;
        rc = AdjustTokenPrivileges(hToken, 0, &privilege, sizeof(privilege),
NULL, NULL);
        err = GetLastError();
        CloseHandle(hToken);
        if (!rc || err)
                return -3;
        return 0;
}

struct PhysMem
{
        void* pMem;
        SIZE_T sMem;
        ULONG_PTR nPages;
        ULONG_PTR* aPages;
};

void unmapAndFreePhysicalPages(struct PhysMem* mem)
{
        if(mem->pMem)
                VirtualFree(mem->pMem, 0, MEM_RELEASE);
        if (mem->aPages)
        {
                FreeUserPhysicalPages(GetCurrentProcess(), &mem->nPages, mem->aPages);
                free(mem->aPages);
        }
        memset(mem, 0, sizeof(*mem));
}

int allocAndMapPhysicalPages(struct PhysMem* mem, SIZE_T size)
{
        int rc;
        SYSTEM_INFO sysInfo;
        memset(mem, 0, sizeof(*mem));
        GetNativeSystemInfo(&sysInfo);
        mem->nPages = size / sysInfo.dwPageSize;
        if (size % sysInfo.dwPageSize > 0)
                mem->nPages++;
        size = mem->nPages * sysInfo.dwPageSize;
        mem->aPages = (ULONG_PTR*)calloc(mem->nPages, sizeof(ULONG_PTR));
        if (!mem->aPages)
                return -1;
        rc = AllocateUserPhysicalPages(GetCurrentProcess(), &mem->nPages, mem->aPages);
        if (!rc)
        {
                free(mem->aPages);
                memset(mem, 0, sizeof(*mem));
                return -2;
        }
        mem->sMem = size = mem->nPages * sysInfo.dwPageSize;
        mem->pMem = VirtualAlloc(NULL, mem->sMem, MEM_RESERVE | MEM_PHYSICAL,
PAGE_READWRITE);
        if (!mem->pMem)
        {
                unmapAndFreePhysicalPages(mem);
                return -3;
        }
        rc = MapUserPhysicalPages(mem->pMem, mem->nPages, mem->aPages);
        if (!rc)
        {
                unmapAndFreePhysicalPages(mem);
                return -4;
        }
        return 0;
}

int configSqlitePhysMemory()
{
        int rc;
        struct PhysMem mem;
        rc = allocAndMapPhysicalPages(&mem, 64 * 1024 * 1024);
        if (rc < 0)
                return -1;
        rc = sqlite3_config(SQLITE_CONFIG_HEAP, mem.pMem, (int)mem.sMem, 64);
        if (rc != SQLITE_OK)
        {
                unmapAndFreePhysicalPages(&mem);
                return -2;
        }
        return 0;
}

int enableLockMemPrivilegeAndConfigSqliteMemory()
{
        int rc;
        rc = enableLockMemoryPrivilege();
        if (rc < 0)
                return -1;
        rc = configSqlitePhysMemory();
        if (rc < 0)
                return -2;
        return 0;
}
-----------------------------------------------------------



2018-05-28 0:23 GMT+02:00, Abroży Nieprzełoży
<[hidden email]>:

> Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
> you may use the function enableIncreaseWorkingSetPrivilege listed
> below, it should return 0 on success - then you can use
> SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
> Working set sizes must be multiple of page size (typically 4096 bytes).
> https://msdn.microsoft.com/en-us/library/ms686234
>
> -------------------------------------------------------------
> int enableIncreaseWorkingSetPrivilege()
> {
>        int rc;
>        DWORD err;
>        HANDLE hToken;
>        TOKEN_PRIVILEGES privilege;
>        memset(&privilege, 0, sizeof(privilege));
>        privilege.PrivilegeCount = 1;
>        privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
>        rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
> &privilege.Privileges[0].Luid);
>        if (!rc)
>                return -1;
>        rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES,
> &hToken);
>        if (!rc)
>                return -2;
>        rc = AdjustTokenPrivileges(hToken, 0, &privilege, sizeof(privilege),
> NULL, NULL);
>        err = GetLastError();
>        CloseHandle(hToken);
>        if (!rc || err)
>                return -3;
>        return 0;
> }
> -------------------------------------------------------------
>
>
>
>
> You can also compile SQLite with -DSQLITE_ZERO_MALLOC=1 and
> -DSQLITE_ENABLE_MEMSYS5=1 and configure it to use buffer of locked
> memory.
> Locked memory won't get swapped so SQLite won't encounter page faults.
>
> Function configSqliteMemory listed below allocs few megabytes of
> memory, locks it and configures SQLite. It also increases working set
> size because maximum quantity of memory that process can lock is equal
> to minimum working set size (locked memory is included in the working
> set).
>
> -------------------------------------------------------------
> int configSqliteMemory()
> {
>        int rc;
>        void* memPtr;
>        SIZE_T memSize = 64 * 1024 * 1024;
>        SIZE_T wsMinSize, wsMaxSize;
>        rc = GetProcessWorkingSetSize(GetCurrentProcess(), &wsMinSize,
> &wsMaxSize);
>        if (!rc)
>                return -1;
>        wsMinSize += memSize;
>        wsMaxSize += memSize;
>        rc = SetProcessWorkingSetSize(GetCurrentProcess(), wsMinSize, wsMaxSize);
>        if (!rc)
>                return -2;
>        memPtr = VirtualAlloc(NULL, memSize, MEM_RESERVE | MEM_COMMIT,
> PAGE_READWRITE);
>        if (!memPtr)
>                return -3;
>        rc = VirtualLock(memPtr, memSize);
>        if (!rc)
>        {
>                VirtualFree(memPtr, 0, MEM_RELEASE);
>                return -4;
>        }
>        rc = sqlite3_config(SQLITE_CONFIG_HEAP, memPtr, (int)memSize, 64);
>        if (rc != SQLITE_OK)
>        {
>                VirtualFree(memPtr, 0, MEM_RELEASE);
>                return -5;
>        }
>        return 0;
> }
> -------------------------------------------------------------
>
>
>
>
> 2018-05-27 20:03 GMT+02:00, x <[hidden email]>:
>> Starting to mess about with windows handles and pages I’ve never heard of
>> is
>> beyond my pain threshold Abrozy. Thanks anyway.
>>
>>
>>
>> ________________________________
>> From: sqlite-users <[hidden email]> on
>> behalf
>> of Abroży Nieprzełoży <[hidden email]>
>> Sent: Sunday, May 27, 2018 5:23:12 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] This is driving me nuts
>>
>> I think you can experiment with changing the the working set size
>> limits and see what the effect will be.
>> https://msdn.microsoft.com/en-us/library/cc441804
>>
>> 2018-05-27 17:09 GMT+02:00, curmudgeon <[hidden email]>:
>>> It seems the array was being optimised away. I had to initialise every
>>> value
>>> to get the OS to claim the RAM. Once I did that the timings for the
>>> array
>>> were on a par with the vector with the second pass being slower than the
>>> first.
>>>
>>> While that clears up that part of the mystery I'm no closer to a
>>> solution.
>>> Going back to the latest set of results why is the assignments in the
>>> second
>>> pass taking so much longer when there's still 5+ GB of memory free?
>>>
>>>
>>>
>>> --
>>> Sent from: http://sqlite.1065341.n5.nabble.com/
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

curmudgeon
In reply to this post by Abroży Nieprzełoży
I’ve just discovered the thread in the original app decreases the available memory by around 4 GB. Are they really that expensive? It has very little data of its own and just calls a function declared in the main thread.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Rowan Worth-2
On 28 May 2018 at 17:29, x <[hidden email]> wrote:

> I’ve just discovered the thread in the original app decreases the
> available memory by around 4 GB. Are they really that expensive?


A thread itself is not expensive in terms of memory.


> It has very little data of its own


Either this statement is wrong, or you've misattributed the 4 GB of memory.
-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Jim Callahan
> why, when you've got 16 GB ram, does a 6.4 GB vector cause any problems?

Architecturally, 4 GB is like the sound barrier -- you may get turbulence
or a sonic boom.

Because 4 GB was the limit for 32 bit word size any residual 32 bit code or
32 bit assumptions anywhere in the stack (or CPU) could create issues.
The operating system attempts to present a unified linear address space,
but under the hood all sorts of kludges may exist.

Windows AWE exists because of 4 GB.
https://en.wikipedia.org/wiki/Address_Windowing_Extensions

Thus, from an architectural standpoint, I would try to avoid allocating 4
GB of RAM  to one object under ANY operating system, but if I had to do it
I would try Linux because that is what the supercomputers use
(supercomputers have big memory in addition to fast speed and tens of
thousands of cpus).
https://www.zdnet.com/article/linux-totally-dominates-supercomputers/

Twenty-five years ago developers were more concerned about Y2K, than beyond
4 GB.
To the extent anyone in the Windows NT world thought about 4 GB of RAM, it
was allocating memory
between the operating system and applications WITHIN 4 GB (ie. do we split
4 GB of RAM between opsys:apps 2:2 or 1:3).

Jim Callahan
Orlando, FL




On Mon, May 28, 2018 at 5:35 AM, Rowan Worth <[hidden email]> wrote:

> On 28 May 2018 at 17:29, x <[hidden email]> wrote:
>
> > I’ve just discovered the thread in the original app decreases the
> > available memory by around 4 GB. Are they really that expensive?
>
>
> A thread itself is not expensive in terms of memory.
>
>
> > It has very little data of its own
>
>
> Either this statement is wrong, or you've misattributed the 4 GB of memory.
> -Rowan
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: This is driving me nuts

Warren Young
In reply to this post by Rowan Worth-2
On May 28, 2018, at 3:35 AM, Rowan Worth <[hidden email]> wrote:
>
> On 28 May 2018 at 17:29, x <[hidden email]> wrote:
>
>> I’ve just discovered the thread in the original app decreases the
>> available memory by around 4 GB. Are they really that expensive?
>
> A thread itself is not expensive in terms of memory.

If you keep to ~1 thread per CPU core, then yes, threads are cheap.  

However, some people advocate strategies like 1 thread per TCP connection in a networked server, so that if each thread takes 2 MiB for a stack and assorted other small bits of RAM for thread-local storage and such, you’ve limited yourself to under 2000 TCP connections on a 32-bit OS because you run out of addressable VM beyond that with a typical 2/2 GiB OS/userland split.

This is why you see so many languages and application frameworks moving to post-thread models, generically under the “green threads” umbrella: Erlang’s processes, Go’s goroutines, F#’s async mechanism, etc.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12