PHP: Memory savings with mysqlnd

mysqlnd may save memory. In the best cases, it may consume only 50% memory as that of libmysql esp. when the client application does not modify the data in the result set after executing a query. Keep in mind that the client must use ext/mysqli and treat the data returned by the query as read-only in order to fully realize mysqlnd's memory gains. If the client application modifies any of the data, mysqlnd behaves just like libmysql.

Let's have a quick look at the memory consumption in both the cases (mysqlnd and libmysql) with an example before delving into the internals. For easy comparison, the sample PHP script does not modify any of the arrays returned from the fetch method. The following example uses DTrace on Sun Solaris to monitor the calls to malloc() and prints the requested bytes of memory on the standard output.

Source code for the script: PHPmysqliClient.php. MySQL table structure and the sample data are shown in the other blog post: Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd.


bash# cat monitormalloc.d

#!/usr/sbin/dtrace -s

pid$1:libc:malloc:entry
{
 	printf("\\t\\tSize : %d Bytes", arg0);
	ustack();
	@malloc[probefunc] = quantize(arg0);
}

CASE 1: ext/mysqli with libmysql

In one terminal window:

bash# /opt/coolstack/php5/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU	ID			FUNCTION:NAME
  0  80920			malloc:entry		Size : 964 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23bb47
              mysqli.so`0xce11d292

  0  80920			malloc:entry		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23da60
              mysqli.so`0xce11dc72

  0  80920			malloc:entry		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21d991
              libmysqlclient.so.16.0.0`0xce21d9ce
              libmysqlclient.so.16.0.0`0xce23da72
              mysqli.so`0xce11dc72

  0  80920			malloc:entry		Size : 17 Bytes
		... elided stack traces for brevity ...
  0  80920			malloc:entry		Size : 152 Bytes
  0  80920			malloc:entry		Size : 16384 Bytes
  0  80920			malloc:entry		Size : 8199 Bytes
  0  80920			malloc:entry		Size : 7 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 18261 Bytes
  0  80920			malloc:entry		Size : 58 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 120 Bytes
  0  80920			malloc:entry		Size : 5 Bytes
  0  80920			malloc:entry		Size : 6 Bytes
  0  80920			malloc:entry		Size : 5 Bytes
  0  80920			malloc:entry		Size : 56 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
  0  80920			malloc:entry		Size : 92 Bytes
  0  80920			malloc:entry		Size : 56 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21a27b
              libmysqlclient.so.16.0.0`0xce23b8a4
              libmysqlclient.so.16.0.0`0xce23d4fa
              mysqli.so`0xce11fe56

  0  80920			malloc:entry		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x856fb98

\^C

  malloc		
           value  ------------- Distribution ------------- count
               2 |                                         0
               4 |@@@@@                                    4
               8 |                                         0
              16 |@@@@                                     3
              32 |@@@@                                     3
              64 |@@@                                      2
             128 |@                                        1
             256 |                                         0
             512 |@                                        1
            1024 |                                         0
            2048 |@@@@@@@@@@@@@@			   11
            4096 |@@@@                                     3
            8192 |@                                        1
           16384 |@@@                                      2
           32768 |                                         0
           65536 |                                         0
          131072 |                                         0
          262144 |@                                        1
          524288 |                                         0

CASE 2: ext/mysqli with mysqlnd

In one terminal window:

bash# /export/home/php53/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU	ID			FUNCTION:NAME
  0  80920			malloc:entry		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x82f702b
              php`0x82f80ab
              php`0x82f841f
              php`0x82f98c4
              php`0x82c7668
              php`0x83c30ae
              php`0x80c059c

\^C

  malloc		
           value  ------------- Distribution ------------- count
          131072 |                                         0
          262144 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1
          524288 |                                         0

In the case of ext/mysqli with libmysql, there are more than 25 calls to malloc() accounting to a total size around 367KB, where as in the case of ext/mysqli with mysqlnd, there is only one call to malloc() with a size of 256KB. In other words, mysqlnd is consuming 30% less memory relative to libmysql to do similar database operations (in reality, it is incorrect to treat every byte allocated as the memory consumed unless there exists a corresponding memory mapping -- however for the sake of this discussion, let's just assume that all the allocated bytes are eventually consumed).

The memory savings in the above example are the result of mysqlnd's ability to hold the results only once in the memory. On the other hand, as libmysql is not a part of PHP, some of the results fetched by libmysql will be copied into memory twice. When libmysql fetches the data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into respective ext/mysqli data structures (often referred as zvals) before it is made available to the PHP clients to consume. So with ext/mysqli and libmysql, there might be two copies of the data in the main memory - one copy inside the libmysql buffers and the other inside zvals. With mysqlnd, there might be only one copy of the data in the memory. mysqlnd also uses buffers but links the zval structures directly to the read buffers, wherever possible. Therefore in majority of the instances, mysqlnd consumes less memory relative to libmysql. In the worst case, it may consume as much memory as that of libmysql. The total memory savings depend on the size of the buffered result set.

Shown below is the simplified behind-the-scenes actions of ext/mysqli with libmysql and ext/mysqli with mysqlnd when mysqli sends a query:

ext/mysqli with libmysql

  1. mysqli sends a query
  2. result set gets fetched into libmysql buffers
  3. mysqli allocates zvals, then new buffers
  4. mysqli copies data from libmysql to its own buffers
  5. mysqli calls mysql_free_result() and deallocates libmysql buffers

ext/mysqli with mysqlnd

  1. mysqli sends a query
  2. result set gets fetched row by row -- every row is a different buffer
  3. mysqlnd creates a result set of zvals pointing to the buffers
  4. mysqli calls mysqlnd_free_result() and deallocates the row buffers

In short, ext/mysqli with libmysql does:

  • one extra allocation for mysqli buffers
  • one extra data copy
  • one extra zval allocation (which can be saved with the zval cache)

when compared to ext/mysqli with mysqlnd.

Related Blog Posts:

  1. MySQL Native Driver for PHP, mysqlnd
  2. Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd

Acknowledgments
Andrey Hristov & Ulf Wendel, Sun-MySQL AB

Comments:

Its true that mysqlnd can save a bit of memory. Depending on your schema, the saving can be quite significant for buffered results. At its extreme, it can be as much as 40%, see also "PHP: mysqlnd saves 40% memory, finally (new tuning options)!", http://blog.ulf-wendel.de/?p=157 .

As a PHP user, as a PHP application developer you need to keep in mind that the saving is only there if you use ext/mysqli in a certain way. The trick used to achieve the memory savings can also be called "read-only variables" or "copy on write". Once you modify an array returned from any of the fetch methods, mysqlnd behaves exactly like libmysql: the saving is gone. Let's see what happens when you do the following with mysqlnd.

1) $row = mysqli_fetch_assoc($res);

On the C-level, inside mysqlnd:

1a) Create new variable $row
1b) Let $row point to internal network buffer holding the requested row data from MySQL

2) $row = mysqli_fetch_assoc($res);

On the C-level, inside mysqlnd:

2a) Can I overwrite $row contents during $row = mysqli_fetch_assoc()? Yes, nobody is referring to it.
2b) Change $row to point to internal network buffer holding the requested row data from MySQL. Do NOT copy any data from the network buffers into new memory areas to which the zval $row can point - libmysql would do the extra allocation and copy, but mysqlnd is clever enough NOT to do it.

3) $row = mysqli_fetch_assoc($res); $row['id'] = number_format($row['id'], 2, ",", ".");

On the C-level, inside mysqlnd:

3a) Can I overwrite $row contents during $row = mysqli_fetch_assoc($res)? Yes, nobody is referring to $row.
3b) Change $row to point to internal network buffer holding the requested row data from MySQL.
3c) Can I overwrite $row['id'] with the return value of number_format()? No, $row['id'] points to internal network buffers of mysqlnd which must not be modified. The value to which $row['id'] points must be copied into a new memory area before it may be modified through number_format(). Perform a zval-separation, perfom a copy-on-write. Zval-separations happen all over in your PHP script and are very fast operations.

As you can see, in real life, an application may be written in a way that it does not profit from the mysqlnd tricks at all. 3c) is the default using libmysql, though the order of instructions is different internally. 3c) is what can happen when you, as a PHP application developer, need to modify data returned from any of the fetch methods.

Therefore benchmarking and demonstrating the effect of the unique mysqlnd "read-only variables" is easy under lab conditions. In real life you will have to check your particular application.

If you are a C-hacker and want to dig deeper, don't forget about ZEND_USE_ALLOC, the Zend Memory Manager and its buffering and the configurable mysqlnd network buffers, check http://blog.ulf-wendel.de/?p=157 and Andrey's comments on it.

If you are an end-user, a PHP application developer, forget about the details. I have not been able to measure significant performance differences when experimenting with any of these fine tune screws. Just go for the defaults and enjoy mysqlnd.

Posted by Ulf Wendel on January 27, 2009 at 06:34 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today