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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com>
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
<abrozynieprzelozy314...@gmail.com>:
> 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 <tam118...@hotmail.com>:
>> 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 <sqlite-users-boun...@mailinglists.sqlite.org> on
>> behalf
>> of Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com>
>> 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 <tam118...@hotmail.com>:
>>> 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
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
