Shulou(Shulou.com)06/02 Report--

How to understand the communication between PHP and MySQL, in view of this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

In one of our WebGame production environments, when an inadvertent strace grabbed a packet, we found a large amount of data communicated between php and mysql. This situation is normal when the game server is just started, but if there are a large number of SELECT SQL queries after running for a period of time, it is absolutely problematic, and the database you are operating is not a configuration library, which means that our programmer's program has violated the rules. The specific results are as follows:

As shown in the figure above, php continuously receives response packet data with a descriptor 3 in the read process, and the descriptor 3 establishes a TCP communication link between php and mysql, which can also be confirmed by the SELECT statement on line 313. The original data was lost and I imitated one. So it is the SQL statement that configures the library)

What kind of program is this and what logic do you want to achieve? Why take so much data?

Following the sql statement of SELECT here, I locate the corresponding program segment:

/ * Code for business logic * / public function SItem ($roleId,$baseId) {/ /... / / # anyone who writes the following code will die. # $this- > dbrRole- > select ('*'); $this- > dbrRole- > from ('role_items') $this- > dbrRole- > where ('role_id',$roleId); $this- > dbrRole- > where (' baseId',$baseId); $result = $this- > dbrRole- > get ()-> row (); / / it seems normal here, and we all thought the framework would fetch only one for us. / /...}

From the point of view of the code, we seem to understand that the programmer wants to fetch a piece of data from the role_items table according to the corresponding role_id, so he calls the row method to get one. It looks like it's normal here, and we all thought the framework would take only one for us. But in fact, how does the framework handle it?

Let's take a look at the implementation of the corresponding row method of the framework. By the way, we are an older version of the CodeIgniter framework.

/ * in the framework, in DB drive, the code of row-related methods * * / public function row ($n = 0 array' type = 'array') {if (! is_numeric ($n)) {if (! Is_array ($this- > _ rowData) {$this- > _ rowData = $this- > rowArray (0);} if (isset ($this- > _ rowData [$n])) {return $this- > _ rowData [$n];} $n = 0;} return ($type = = 'object')? $this- > rowObject ($n): $this- > rowArray ($n) } / / continue to follow up on rowArray method public function rowArray ($n = 0) {$result = $this- > resultArray (); if (count ($result) = = 0) {return $result;} if ($n! = $this- > _ current & & isset ($result [$n])) {$this- > _ current = $n;} return $result [$this- > _ current] } / / continue to follow the resultArray method # this method is the key # public function resultArray () {if (count ($this- > resultArray) > 0) {return $this- > resultArray;} if (false = $this- > resulter | | 0 = = $this- > recordCount ()) {return array ();} $this- > _ dataSeek (0) While ($row = $this- > _ fetchAssoc ()) {$this- > resultArray [] = $row; / / # this array increases the memory size of the result of _ fetchAssoc () each time #} return $this- > resultArray } / / continue to follow up on the _ fetchAssoc method / * the code of the _ fetchAssoc method corresponding to driver * / protected function _ fetchAssoc () {return mysql_fetch_assoc ($this- > resulter);}

We can see that the resultArray method of the CodeIgniter framework uses the mysql_fetch_assoc function of mysql (our php calls mysql's api using the mysql function, which is somewhat circled, explained later) to traverse the data in the buffer. Copy all the data from all buffers to the $this- > resultArray attribute, and then determine whether the result of the key required in the row method exists, and then match the returned.

In other words, the framework layer does not just take one from the mysql server (subconscious mysql server) for our caller, but takes all the results and returns another one. (don't spray first, explain later) of course, what CI is doing is not wrong. But I think there is a better way to improve.

This problem, our group's dietoad (marriage seeking) found this problem, and gave a repair plan. Some students think that it is the programmer's fault that the programmer's SELECT statement does not add limit to limit the number of entries. I absolutely agree with this, and I think anyone who writes this kind of code has to die.

Business layer: add limit restrictions to the SQL statements for this business requirement

Framework layer: the framework automatically controls this requirement, finds this situation, and directly returns 1

For solution 1, I wrote a regular that after the matching select () method was called, and before the row () method was called, there was not all the code that used the limit () method in the middle. As a result, it was found that the amount was not small. Later, we decided to implement the two schemes at the same time to prevent the omission of the second.

Dietoad offers the following improvements:

/ * / improved: when _ rowData does not exist, start with the number of _ rowData and take less than $n records to avoid the memory consumption of * / public function row ($n = 0, $type = 'array') {if ($this- > _ rowData [$n]) {return $this- > _ rowData [$n] in the resultArray method above. } if (! Is_numeric ($n) {return $this- > rowObject ($n);} $ln=count ($this- > _ rowData); / / continue the last location while ($ln++_fetchAssoc ()) {$this- > _ rowData [] = $r } / / how many entries need to be read / / to prevent the recordset from being empty warning return isset ($this- > _ rowData [$n])? $this- > _ rowData [$n]: array ();}

At the end of April this year, I wrote another article about the design flaw in the CodeIgniter framework, which had a great impact on our game project. Later, I submitted it to github issues, but I didn't get a reply. I thought about it, although this small problem also exists in the official 2.1.3 version. However, I think that this will not be submitted, maybe our approach is also in line with their original intention of the design. However, we have improved on our project.

After this improvement, when we use the memory_get_usage () function of php to observe the results of the two row () methods, we find that memory usage has improved greatly (depending on the amount of data returned by SELECT).

It seems that it should be over here, and the problem is thus discovered and solved.

But what do I always feel missing? When I strace the package again, I find that there is still a lot of data communication, just like the screenshot at the beginning of the article. But what is the reason for this?

I casually wrote a memory footprint test code as follows:

$db = mysql_connect ('192.168.xx.xxx); $sql =' SELECT * from items'; mysql_select_db ('jv01',$db); echo' SELECT_DB:', convert (memory_get_usage ()), "\ n"; / / 619.26 kb $r = mysql_query ($sql,$db); echo 'QUERY_SQL:', convert (memory_get_usage ()), "\ n" / / 619.98 kb # what? After the query, the memory size has only increased by less than 1k? My table is tens of megabytes of data ah / / sleep (50); / / hold process, do not destroy, save to see the current process memory allocation 1$ arr = array (); while ($rs = mysql_fetch_assoc ($r)) {$arr [] = $rs;} echo 'FETCH_RS:', convert (memory_get_usage ()), "\ n"; / / 27.11mb # what? Didn't you just add 1k? How does the traversal result set here suddenly increase by dozens of M? Nima, what's going on? Unset ($arr); echo 'UNSET:', convert (memory_get_usage ()), "\ n"; / / 620.12 kb # $arr z occupies dozens of M mysql_free_result ($r); echo 'FREE_R:', convert (memory_get_usage ()), "\ n"; / / 620 kb # result set is only 0.12k? Isn't that bullshit? Is it... no, no, no. Is it possible that the data in the buffer zone cannot be counted by php? Isn't it possible to call the zend memory request function to apply for memory? / / sleep (50); / / hold the process, don't destroy it, but leave it to see the memory allocation of the current process 2 function convert ($size) {$unit=array ('baked memory memory / memory allocation / return @ round ($size/pow (1024, ($i=floor (log ($size,1024), 2). $unit [$]. } / * / the returned result is as follows: SELECT_DB: 619.26 kb QUERY_SQL: 619.98 kb FETCH_RS: 27.11 mb UNSET: 620.12 kb FREE_R: 620 kb * /

When I saw the result, I couldn't help but XX a tight, what? What's going on with your mother? After the query, the memory size has only increased by less than 1k? That watch of mine is dozens of megabytes of data? After traversing the result set, how to add dozens of M suddenly? Nima, what's going on? Where exactly is the large amount of data returned by strace stored? Is it an application by the php process?

Later, I executed the above program again, and then periodically used system tools such as free and / proc/PID/maps to check the memory usage of the system and confirmed that the memory footprint of the current process did exist. Then it is possible that the memory_get_usage () function does not get the memory footprint after mysql_query. Due to suspicion, the final school followed up the source code of the memory_get_usage () function, which was directly handed over to the zend_memory_usage function for processing.

/ / this is the code related to the memory_get_usage () function of php. See Zend_alloc.c line:2640 ZEND_API size_t zend_memory_usage (int real_usage TSRMLS_DC) {if (real_usage) {return AG (mm_heap)-> real_size;} else {size_t usage = AG (mm_heap)-> size; # if ZEND_MM_CACHE usage-= AG (mm_heap)-> cached # endif return usage;}} / / this is the code for the Zend memory allocation function / / Zend_alloc.c line:2418 ZEND_API void * _ emalloc (size_t size ZEND_FILE_LINE_DC ZEND_FILE_LINE_ORIG_DC) {TSRMLS_FETCH (); if (UNEXPECTED (! AG (mm_heap)-> use_zend_alloc)) {return AG (mm_heap)-> _ malloc (size) } return _ zend_mm_alloc_int (AG (mm_heap), size ZEND_FILE_LINE_RELAY_CC ZEND_FILE_LINE_ORIG_RELAY_CC);}

Php memory management (Chinese address: php-zend memory management Chinese version) this piece, for the end of learning, too complex, only a little understanding of the direct return of the mm_heap structure of the real_size/size value. (both articles are written by Brother Bird, and the address in Chinese, that is, Brother Bird's blog, has been unable to open recently.)

So where does the result set of mysql_query exist? How to apply for memory, is it not to call zend's _ emalloc memory allocation function? First of all, we have to clarify the problem of the mysql client class library, that is, which class library do we use? Libmysql or mysqlnd. By looking at the compilation parameters, I found that (my virtual machine) is libmysql. The compilation parameters are like this.

. / configure''--prefix=/services/php_5.3.19''--with-config-file-path=/services/php_5.3.19/etc''--with-pdo-mysql=/usr/bin/mysql_config''--with-mysql=/usr/bin/mysql_config''--with-mysqli=/usr/bin/mysql_config''--enable-bcmath''--enable-fpm / / production server is as follows: . / configure''--prefix=/services/php''--with-config-file-path=/services/php/etc''--with-pdo-mysql=mysqlnd''--with-mysql=mysqlnd''--with-mysqli=mysqlnd''--enable-bcmath''--enable-fpm

It's a little messy:

Mysql, mysqli, pdo-mysql, libmysql, mysqlnd a lot of nouns, a little messy, it doesn't matter, a picture to make you clear:

The relationship among mysql, mysqli, pdo-mysql, libmysql and mysqlnd

Like libmysql, mysqlnd is a driver class library that communicates directly with mysql server. Mysql, mysqli and pdo-mysql used by php programmers are API interfaces for programmers to call.

Continue:

The libmysql class library is the official class library provided by MYSQL. Each PHP compilation specifies parameters to determine which connection driver is used by mysql\ mysqli\ pdo-mysql. Also, you have to install the client of mysql (libmysql class library) first to ensure that there is libmysqlclient.so.

Finally, with the mentality of trying, he opened the source code of libmysql with a heavy heart, and finally found a code similar to libmysqlclient applying for memory near Safemalloc.c 's line:120.

/ / libmysql client library Safemalloc.c line:120 / * Allocate some memory. * / void * _ mymalloc (size_t size, const char * filename, uint lineno, myf MyFlags) {... / * Test for memory limit overrun. If compiled with DBUG, test for error injection. Described in my_sys.h. * / if ((size + sf_malloc_cur_memory > sf_malloc_mem_limit) IF_DBUG (| | my_malloc_error_inject)) {IF_DBUG (if (my_malloc_error_inject) errno= ENOMEM; my_malloc_error_inject= 0); irem= 0 } else {/ * Allocate the physical memory * / irem= (struct st_irem *) malloc (ALIGN_SIZE (sizeof (struct st_irem)) + sf_malloc_prehunc + size + / * size requested * / 4 + / * overrun mark * / sf_malloc_endhunc) / / the memory allocation function of the system malloc}.} / the following is the code driven by mysqlnd, which is specially put together in order to save another highlighted block of the code. / / Mysqlnd client library Mysqlnd_alloc.c line:77 / * {_ mysqlnd_emalloc * / void * _ mysqlnd_emalloc (size_t size MYSQLND_MEM_D) {. Ret = _ emalloc (REAL_SIZE (size) ZEND_FILE_LINE_CC ZEND_FILE_LINE_ORIG_RELAY_CC); / / call zend's memory allocation function _ emalloc. If (ret & & collect_memory_statistics) {* (size_t *) ret = size; MYSQLND_INC_GLOBAL_STATISTIC_W_VALUE2 (STAT_MEM_EMALLOC_COUNT, 1, STAT_MEM_EMALLOC_AMOUNT, size);} TRACE_ALLOC_RETURN (FAKE_PTR (ret));} / *}} * /

In other words, libmysql cannot record memory usage in the mm_heap structure without calling zend's internal allocation function _ emalloc, which is the reason why PHP's memory_get_usage () function cannot count. Well, although the last school is not very able to read the source code, but it seems to be in line with the phenomenon of the problem.

It seems that another problem comes to mind: if the result set saved by libmysql takes up the memory, then the memory_limit in php's configuration file will not be able to limit his memory usage? In other words, if we ideally allocate several php-fpm processes to start running according to the remaining memory of the system, if this happens, there will be insufficient memory, and the memory occupied by libmysql will not be counted. The result is obvious, and sure enough, it cannot be limited.

The relationship between libmysql and mysqlnd and memory_limit

Is mysqlnd all right? Does mysqlnd allocate memory using zend's _ emalloc function? Yes, that's right. Mysqlnd is our savior. In the code in Mysqlnd_alloc.c line:77, I clearly see it. When you SA compiles php, be sure to use mysqlnd as the class library driver for php connection mysql server.

The benefits of Mysqlnd are more than that.

Memory or memory:

At the end of learning weak English, I risked my life to flip through GFW and finally found these materials on the website of the "evil capitalist" country. Mysqlnd will save nearly 40% of its memory footprint than libmysql. As shown in the figure:

Mysqlnd saves 40% of memory footprint than libmysql.

Besides, the memory_limit parameter can control it.

Speed, speed:

A foreign friend gave a test result. The comparison API is mysql\ mysqli, and the comparison driver is libmysql\ mysqlnd.

The ext\ mysqli interface driven by mysqlnd is the fastest

The ext\ mysqli interface using the libmysql driver is 6% slower

The ext\ mysql interface using the libmysql driver is 3% slower

And the execution time of mysqli under two drivers is given:

Mysqli_select_varchar_buffered

And, oh... Mysqlnd also supports all kinds of debug debugging, all kinds of strace tracking. Also support... . Forget it, download your own mysqlnd compared to the advantages of libmysql to see. It took a long time for Maoxue to find this ppt.

Recommended:

1. Recommend another article on mysqlnd persistent links: PHP 5.3: Persistent Connections with ext/mysqli

2, does the programmer store the cache of your application in memcache based on DB data results, query conditions, and hash values? Do you want to try to do it automatically? The plug-in for mysqlnd can be tried: PHP: Client side caching for all MySQL extensions, which supports memcached,apc,sqlit.

Go back to the beginning:

It is said that when php calls mysql_query, mysql server returns the results of this query to the buffer of the server where php is located. When a program calls functions such as mysql_fetch_assoc/mysql_fetch_row / mysql_fetch_array/mysql_fetch_object, it always calls the php_mysql_fetch_hash function to buffer and read the data. What if I use the mysql_unbuffered_query () function? Let the result set not be returned directly after the query, but pull it back when the mysql_fetch_x function is called? This. Do you use mysql server's buffer to store this data? Do you think you are the only one on the client? Other clients also have to connect, especially php, if they use the mysql_unbuffered_query () function, they will put the result set into the buffer of mysql server. Isn't the memory footprint of mysql server increasing? You want DBA to hack you to death?

The manual also says that mysql_num_rows () and mysql_data_seek () cannot be used on the result set returned by mysql_unbuffered_query. I hardly ever use this function. Is this a non-mainstream function?

Some people say that our scheme 1 saves the memory footprint taken from the result set and traverses the values assigned to the new array, and does not reduce the transmission of network data. Yeah, you're right. You're absolutely right. In other words, our solution 2 can only slightly alleviate the negative effects of this problem, and if it is completely solved, it will have to be called correctly at the program level to retrieve the required data. In fact, if we use the mysqlnd driver, our changes basically have no advantage and will not save memory. In the case of mysqlnd, the reading of the result set simply references the data of the buffer. Libmysql, it has obvious effect.) I agree with the sentence that "people who write this kind of code have to die." SA that does not use mysqlnd as a php connection driver is a hooligan.

Conclusion:

Api recommends mysqli, driver recommends mysqlnd.

Consider the past you shall know the future?

After returning home, Maoxue swiped several rounds of "defend radish". Except for a few levels that need to be developed before unlocking, the shameful "Quanqing" + "golden radish", playing with it, suddenly remembered one thing, that is, Maxue wrote a blog php5.3.8 last year during the difficult process of compiling pdo_mysql. In the previous operation and maintenance parameters, mysqli used mysqlnd, while mysql used libmysql. Later, the pdo-mysql also used libmysql. .3 api, specify two connection drivers, is it because of the last mistake? Although the compilation parameters coincidentally solved the problem, the real reason was not understood at the beginning. Check it out next week... [updated at 23:31 on 2012-12-15]

Know shame and then be brave?

Today, just after writing this study note, when I went home to play the game, I remembered that Brother Bird had mentioned mysqlnd. I went back to see how Bird explained mysqlnd and whether I understood it correctly. I found that Brother Bird already had a link to Ulf Wendel blog, but Maoxue searched the Internet for a long time to find that article. At the same time, he found a large number of mysqlnd articles on his blog and secretly laughed, thinking that he had discovered a big gold mine. Only now did I find out that... . Hey, shame... [updated at 23:58 on 2012-12-15]

At the end of learning, there are a large number of blind spots for the knowledge points encountered in this learning experience, which will be slowly explored in the future. You are also welcome to give us your advice.

It seems that there are too many questions at the end of the school.

This is the answer to the question about how to understand the communication between PHP and MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.