Edit: Please note that the comment section is now irrelevant because my original answer is gone.
Your question:
How can the Private Bytes of a process be significantly less than its effect on the system commit charge?
This can be answered with a direct quote from Mark Russinovich:
There are two types of process virtual memory that do count toward the commit limit: private and pagefile-backed.
The private bytes attributed to the process can be (and often is) less than that processes' effect on the system commit charge because the process can also be allocating pagefile-backed virtual memory.
Pagefile-backed virtual memory is difficult to attribute to a specific process because it is sharable between processes. There is no process-specific performance counter that can tell you how much pagefile-backed virtual memory any process has allocated or is referencing, yet, it does still count against the commit limit.
This article is the authoritative article on the subject, and in that article, he specifically demonstrates a case where a process has allocated tons of pagefile-backed VM, and yet the private bytes of the process remains very low.
He also shows you how to use handle.exe
to detect the allocation size of handles to section objects. That is how you can detect what process is having such a large effect on the commit charge.
You mention that you have already looked at sqlservr.exe
with handle.exe
and that it does not have handles open to a significant amount of section objects that would account for the commit charge that is released when you kill sqlservr.exe
.
Coincidentally, there are also memory allocations in kernel space that are charged against the system commit limit, such as paged and nonpaged pools, and driver locked memory, including things like virtual machine balloon drivers, etc. I don't believe this is relevant to this case but I didn't want to leave it unsaid.
SQL Server is a massive, complex product consisting of many different processes that work together on the system to provide all the SQL Server services. In fact SQL Server has its very own internal memory manager that can make it look atypical from the perspective of tools designed to measure Windows virtual memory allocations.
sqlservr.exe
does not act alone. There's also
msmdsrv.exe
(Analysis Services)
sqlwriter.exe
(SQL VSS Writer)
sqlagent.exe
(SQL Agent)
fdlauncher.exe
(Full-Text Filter Daemon Launcher)
fdhost.exe
(Full-Text host)
ReportingServicesService.exe
SQLBrowser.exe
When I kill sqlservr.exe
, sqlagent.exe
also dies automatically. This means the system commit charge will fall by the amount contributed to it by both processes. The other SQL-related processes may also be releasing pagefile-backed sections when sqlservr.exe
is killed, even though the processes themselves remain running. All of these would cause the current commit charge of the system to fall when sqlservr.exe
is killed, even though they were never part of the private bytes of sqlservr.exe
.
You need to be aware of what the InnoDB Buffer Pool breakdown is from the status variables
Please run the following query
select var,concat(numunit,' ',unit) size from
(
select var,format(num/power(1024,ex),2) numunit,SUBSTR(units,ex*2+1,2) unit
from
(
select var,num,FLOOR(LOG(IF(num=0,1,num))/LOG(1024)) ex
from
(
select variable_name var,variable_value*pagesize num
from information_schema.global_status AAA,
(
select variable_value pagesize
from information_schema.global_status
where variable_name='innodb_page_size'
) BBB
where AAA.variable_name like 'innodb_buffer_pool_pages%'
) AA
) A,(select 'B KBMBGBTB' units) B
) M;
This will gives a status of the Buffer Pool's current usage and how much data has been flushed from it.
Example
mysql> select var,concat(numunit,' ',unit) size from
-> (
-> select var,format(num/power(1024,ex),2) numunit,SUBSTR(units,ex*2+1,2) unit
-> from
-> (
-> select var,num,FLOOR(LOG(IF(num=0,1,num))/LOG(1024)) ex
-> from
-> (
-> select variable_name var,variable_value*pagesize num
-> from information_schema.global_status AAA,
-> (
-> select variable_value pagesize
-> from information_schema.global_status
-> where variable_name='innodb_page_size'
-> ) BBB
-> where AAA.variable_name like 'innodb_buffer_pool_pages%'
-> ) AA
-> ) A,(select 'B KBMBGBTB' units) B
-> ) M;
+----------------------------------+-----------+
| var | size |
+----------------------------------+-----------+
| INNODB_BUFFER_POOL_PAGES_DATA | 9.17 GB |
| INNODB_BUFFER_POOL_PAGES_DIRTY | 0.00 B |
| INNODB_BUFFER_POOL_PAGES_FLUSHED | 179.59 GB |
| INNODB_BUFFER_POOL_PAGES_FREE | 32.00 KB |
| INNODB_BUFFER_POOL_PAGES_MISC | 177.05 MB |
| INNODB_BUFFER_POOL_PAGES_TOTAL | 9.34 GB |
+----------------------------------+-----------+
6 rows in set (0.00 sec)
mysql>
I have the following
- 9.34G Buffer Pool (9566 MB)
- 9.17G of Data
- 177 .05 MB for the adaptive hash index and administrative stuff
- 32 KB free
- No dirty pages
Why is INNODB_BUFFER_POOL_PAGES_FLUSHED
so sky high at 179.59 GB
?
That is the amount of flushing the buffer pool has had to commit to the InnoDB Architecture.
Here is a diagram
In my case,
mysql> SELECT variable_value INTO @Uptime FROM information_schema.global_status WHERE variable_name='Uptime'; SELECT NOW() "Right Now",MySQLStartupTime "MySQL Started",TimeDisplay "MySQL Has Been Running For" FROM (SELECT NOW() - INTERVAL @Uptime SECOND MySQLStartupTime) M,(SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours '))),IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),' ',' ')) TimeDisplay FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM (SELECT dy,hr,FLOOR((sec_aaa - dy*86400 - hr*3600)/60) mn,sec_aaa sec_aaaa FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa FROM (SELECT @Uptime sec_a) A) AA) AAA) AAAA) B) N;
Query OK, 1 row affected (0.02 sec)
+---------------------+---------------------+-------------------------------+
| Right Now | MySQL Started | MySQL Has Been Running For |
+---------------------+---------------------+-------------------------------+
| 2014-08-08 17:13:42 | 2014-06-26 16:38:56 | 43 days 34 minutes 46 seconds |
+---------------------+---------------------+-------------------------------+
1 row in set (0.00 sec)
MySQL has been up for 43 days. It has flushed 179GB out of the Buffer Pool.
The Buffer Pool has to flush dirty pages to three places
- the Log Buffer
- the Insert Buffer
- the .ibd file for the Table
I have a rather busy server and it's a VM.
In your case, 18,699,968 K of flushes from a 2,069,572 K Buffer Pool is nothing to worry about.
Best Answer
Do a google for "russinovich pushing limits" and you'll find a series of articles penned by the author of the sysinternals suite of tools for Windows on various aspects of memory usage by Windows.
I'm not sure there really are a lot of significant drawbacks to running 32 bit applications on a 64 bit OS. The biggest bonuses to running 64 on 64 are memory addressing and...that's a big one.
Even the latest version of OS X defaults to 32 bit (you hit 6 and 4 at bootup to start the 64 bit kernel, but you won't get all the kernel extensions to work properly as I recall). OS X has been a "mixed" OS for quite awhile.
The only drawbacks I've heard that impact people with 64 bit OS's involves drivers (some 32 bit drivers not working properly) and older machines not liking a 64 bit OS if the processor doesn't support it, and there's still a lot of older machines out there.