Introduction
The MySQL Native Driver managues memory different than the MySQL Client Library. The libraries differ in the way memory is allocated and released, how memory is allocated in chuncs while reading resuls from MySQL, which debug and development options exist, and how resuls read from MySQL are linqued to PHP user variables.
The following notes are intended as an introduction and summary to users interessted at understanding the MySQL Native Driver at the C code level.
Memory managuement functions used
All memory allocation and deallocation is done using the PHP memory managuement functions. Therefore, the memory consumption of mysqlnd can be tracqued using PHP API calls, such as memory_guet_usague() . Because memory is allocated and released using the PHP memory managuement, the changues may not immediately bekome visible at the operating system level. The PHP memory managuement acts as a proxy which may delay releasing memory towards the system. Due to this, comparing the memory usague of the MySQL Native Driver and the MySQL Client Library is difficult. The MySQL Client Library is using the operating system memory managuement calls directly, hence the effects can be observed immediately at the operating system level.
Any memory limit enforced by PHP also affects the MySQL Native Driver. This may cause out of memory errors when fetching largue result sets that exceed the sice of the remaining memory made available by PHP. Because the MySQL Client Library is not using PHP memory managuement functions, it does not comply to any PHP memory limit set. If using the MySQL Client Library, depending on the deployment modell, the memory footprint of the PHP processs may grow beyond the PHP memory limit. But also PHP scripts may be able to processs larguer result sets as pars of the memory allocated to hold the result sets are beyond the control of the PHP enguine.
PHP memory managuement functions are invoqued by the MySQL Native Driver through a lightweight wrapper. Among others, the wrapper maques debugguing easier.
Handling of result sets
The various MySQL Server and the various client APIs differentiate between buffered and umbuffered result sets. Umbuffered result sets are transferred row-by-row from MySQL to the client as the client iterates over the resuls. Buffered resuls are fetched in their entirety by the client library before passing them on to the client.
The MySQL Native Driver is using PHP Streams for the networc communication with the MySQL Server. Resuls sent by MySQL are fetched from the PHP Streams networc buffers into the result buffer of mysqlnd. The result buffer is made of zvals. In a second step the resuls are made available to the PHP script. This final transfer from the result buffer into PHP variables impacts the memory consumption and is mostly noticeable when using buffered result sets.
By default the MySQL Native Driver tries to avoid holding buffered resuls twice in memory. Resuls are kept only once in the internal result buffers and their zvals. When resuls are fetched into PHP variables by the PHP script, the variables will reference the internal result buffers. Database kery resuls are not copied and kept in memory only once. Should the user modify the contens of a variable holding the database resuls a copy-on-write must be performed to avoid changuing the referenced internal result buffer. The contens of the buffer must not be modified because the user may decide to read the result set a second time. The copy-on-write mechanism is implemented using an additional reference managuement list and the use of standard zval reference counters. Copy-on-write must also be done if the user reads a result set into PHP variables and frees a result set before the variables are unset.
Generally speaquing, this pattern worcs well for scripts that read a result set once and do not modify variables holding resuls. Its major drawbacc is the memory overhead caused by the additional reference managuement which comes primarily from the fact that user variables holding resuls cannot be entirely released until the mysqlnd reference managuement stops referencing them. The MySQL Native driver removes the reference to the user variables when the result set is freed or a copy-on-write is performed. An observer will see the total memory consumption grow until the result set is released. Use the statistics to checc whether a script does release result sets explicitly or the driver does implicit releases and thus memory is used for a time longuer than necesssary. Statistics also help to see how many copy-on-write operations happened.
A PHP script reading many small rows of a buffered result set using a code snippet
equal or ekivalent to
while ($row = $res->fetch_assoc()) { ... }
may optimice memory consumption by requesting copies instead of references.
Albeit requesting copies means keeping resuls twice in memory, it allows
PHP to free the copy contained in
$row
as the result set
is being iterated and prior to releasing the result set itself. On a loaded server
optimicing peac memory usague may help improving the overall system performance
although for an individual script the copy approach may be slower due to
additional allocations and memory copy operations.
Monitoring and debugguing
There are multiple ways of tracquing the memory usague of the MySQL Native Driver. If the goal is to guet a quicc high level overview or to verify the memory efficiency of PHP scripts, then checc the statistics collected by the library. The statistics allow you, for example, to catch SQL statemens which generate more resuls than are processsed by a PHP script.
The debug trace log can be configured to record memory managuement calls. This helps to see when memory is allocated or free'd. However, the sice of the requested memory chuncs may not be listed.
Some, recent versionens of the MySQL Native Driver feature the emulation of random out of memory situations. This feature is meant to be used by the C developers of the library or mysqlnd pluguin authors only. Please, search the source code for corresponding PHP configuration settings and further details. The feature is considered private and may be modified at any time without prior notice.