Analysis on the relationship between php-fpm and mysql

Today, we will not talk about the old syntax. We can look for an extension to analyze the communication principle between the underlying layer of php and mysql.

First, let's take a look at the working principle of php-fpm. php-fpm is a php-cgi process manager, which is actually a connection pool. The working principle of working with nginx is as follows.

Let's start with the simplest static method and observe how it works.

Vim php-fpm.ini.

[Www]
Pm = static
Pm. max_children = 5
Pm. max_requests = 2
What are the meanings of the above three sentences:

1. static indicates that the php-fpm process is generated statically.

2. pm. max_children = 5 indicates that when php-fpm is started, five php-fpm sub-processes are started to wait for nginx requests to be processed.

3. pm. max_requests = 2 indicates that each php-fpm sub-process processes two requests and destroys them. Of course, each time the parent process sees a destroyed sub-process, a new sub-process is generated.

Let's simply verify this statement:

First, restart php-fpm to reset it.

Next, write a simple statement to output the current process ID.

Echo "current php-fpm process ID:". posix_getpid ();
Constantly refresh the browser to observe output changes

Current php-fpm process ID: 24548

Current php-fpm process ID: 24549

Current php-fpm process ID: 24550

Current php-fpm process ID: 24547

Current php-fpm process ID: 24551

Current php-fpm process ID: 24548

Current php-fpm process ID: 24549

Current php-fpm process ID: 24550

Current php-fpm process ID: 24547

Current php-fpm process ID: 24551

Current php-fpm process ID: 24563

Current php-fpm process ID: 24564

Current php-fpm process ID: 24565

Current php-fpm process ID: 24566

Current php-fpm process ID: 24567

Current php-fpm process ID: 24563

Current php-fpm process ID: 24564

Current php-fpm process ID: 24565

Current php-fpm process ID: 24566

Current php-fpm process ID: 24567

Current php-fpm process ID: 24568

Current php-fpm process ID: 24569

Current php-fpm process ID: 24570

Current php-fpm process ID: 24571

Current php-fpm process ID: 24572

Current php-fpm process ID: 24568

Current php-fpm process ID: 24569

Current php-fpm process ID: 24570

Current php-fpm process ID: 24571

Current php-fpm process ID: 24572

It can be seen that the first batch of IDS is not executed in order, and the process whose process id is 24547 is processed in the fourth place, and then starts from below, all IDs are executed sequentially and each generation of IDS is incremental. Is there a way to catch up with the mysql auto-incrementing primary key?

It should be noted that, whether it is static or the following Dynamic Configuration method, as long as max_requests is not set, the process will not be destroyed, that is to say, when an endless loop or memory overflow occurs in a process that causes the process to become dead, the process will be less than one.

Well, after understanding the static processing method, we can also easily understand the disadvantages of this method. Of course, it is impossible for our servers to launch five processes and each process can process two requests at ordinary times, let's do a simple addition, subtraction, multiplication, division, to see how many php-fpm should be enabled on a server

First, let's see how much memory a simple echo needs:

$ Size = memory_get_usage ();
$ Unit = array ('B', 'KB', 'mb', 'GB', 'TB', 'petab ');
$ Memory = @ round ($ size/pow (1024, ($ I = floor (log ($ size, 1024), 2 ). ''. $ unit [$ I];
Echo "memory used by the current php-cgi process:". $ memory;
Observe the browser and we can get the following data:

Current memory used by the php-cgi process: 227.17 kb

That is to say, a simple php that does nothing has occupied more than 200 kB of memory. Of course, this is not much.

However, the process will slow down when there is more cpu to switch the process, so this number still needs to be tested using AB and other testing tools to test how much should be opened.

We will start from 200 and continue to increase. When the setup is increased to 800, the efficiency is the same as 400, so we do not need to enable 800 so many processes to waste memory.

The problem arises. What if more than 400 requests are sent at the same time? Some people say they will wait in queue. Will they really wait in queue? The answer is obviously that php-fpm is not capable of queuing, because the php-fpm sub-processes that process requests are used up, so the waiting will only be in nginx waiting, generally, an nginx does not just forward requests to php-fpm, but does it need to process static files? If these php requests cause too many nginx requests to be waiting, the access to static files will naturally become stuck. At this time, we need to configure the dynamic processing method below.

[Www]
Pm. max_children = 10
Pm. start_servers = 5
Pm. min_spare_servers = 2
Pm. max_spare_servers = 8
; Pm. max_requests = 2
What are the meanings of the above five sentences:

1. dynamic indicates that the php-fpm process is generated dynamically in static mode.

2. pm. max_children = 10 simultaneously active processes: 10

3. pm. start_servers = 5 indicates that five php-fpm sub-processes are started when the main php-fpm process starts.

4. pm. min_spare_servers = 2 indicates the minimum number of slave processes.

5. pm. max_spare_servers = 8 indicates the maximum number of slave processes.

6. pm. max_requests = 2. I won't talk about it anymore.

Current php-fpm process ID: 2270

Current php-fpm process ID: 2271

Current php-fpm process ID: 2272

Current php-fpm process ID: 2273

Current php-fpm process ID: 2274

Current php-fpm process ID: 2270

Current php-fpm process ID: 2271

Current php-fpm process ID: 2272

Current php-fpm process ID: 2273

Current php-fpm process ID: 2274

Current php-fpm process ID: 2270

Current php-fpm process ID: 2271

Current php-fpm process ID: 2272

Current php-fpm process ID: 2273

Current php-fpm process ID: 2274

Why didn't a new process be regenerated here? Because pm. max_requests = 2 has been commented out, this is actually mentioned once.

We can also see the id of these processes from ps.

Ps aux | grep php

Root 2269 0.0 0.1 134560 4616? Ss php-fpm: master process (/etc/php/php-fpm.ini)
Www-data 2270 0.2 0.2 136736 9188? S php-fpm: pool www
Www-data 2271 0.2 0.2 136740 9192? S php-fpm: pool www
Www-data 2272 0.2 0.2 134684 7284? S php-fpm: pool www
Www-data 2273 0.2 0.2 136732 9120? S php-fpm: pool www
Www-data 2274 0.1 0.2 134684 7244? S php-fpm: pool www
From the above we can see that a php-fpm master process with id 2269 manages five php-fpm sub-processes with id 2270, 2271, 2272, 2273, and 2274.

It should be noted that when the number of concurrency exceeds the processing capability of start_servers, the slave process will start. When the number of concurrency is small, the slave process will also be destroyed, so no matter what time, ps processes are the five above

Let's take a look at the results of php-fpm + mysql.

Mysql> show processlist;
+ ---- + ------------------ + ----------- + ------ + --------- + ------ + ---------------- + --------------------------- +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------------------ + ----------- + ------ + --------- + ------ + ---------------- + --------------------------- +
| 9 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+ ---- + ------------------ + ----------- + ------ + --------- + ------ + ---------------- + --------------------------- +
Next we will look at short connections:

$ Conn = new mysqli ("192.168.0.170", "redol", "redol", "test_db ");
And then constantly access the above php file.

+ ---- + --------- + ----------- + ------ + --------- + ------ + ---------------- + -------------------- +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + --------- + ----------- + ------ + --------- + ------ + ---------------- + -------------------- +
| 9 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+ ---- + --------- + ----------- + ------ + --------- + ------ + ---------------- + -------------------- +
This is also the magic of php. You don't need to close it. After each request is complete, he will close the connection with mysql, this does also reduce the troubles of many new users.

Warning: mysqli (): (HY000/1040): Too connector connections in...

Don't think the language should be like this. That's the mistake. Let's check out golang.

Next let's take a look at persistent connections.

$ Conn = new mysqli ("p: 192.168.0.170", "redol", "redol", "test_db ");

You are not mistaken. The persistent connection of mysqli is different from that of mysql. It is added p: in front of the host, without the use of mysqli_pconnet. It is estimated that many of them are confused when mysqli is used at the beginning?

First visit:

+ ---- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
| 9 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 10 | redol | bbs.demo.kkk5.com: 16650 | redol | Sleep | 34 | NULL |
+ ---- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
Refresh the webpage

+ ----- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
| Id | User | Host | db | Command | Time | State | Info |
+ ----- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
| 9 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 10 | redol | bbs.demo.kkk5.com: 16650 | redol | Sleep | 4 | NULL |
| 727 | redol | bbs.demo.kkk5.com: 16657 | redol | Sleep | 1 | NULL |
+ ----- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
Refresh the page, and I will not send the results. Anyway, you know that no matter how you refresh the page, it will be as follows.

+ ----- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
| Id | User | Host | db | Command | Time | State | Info |
+ ----- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
| 9 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 10 | redol | bbs.demo.kkk5.com: 16650 | redol | Sleep | 4 | NULL |
| 727 | redol | bbs.demo.kkk5.com: 16657 | redol | Sleep | 1 | NULL |
| 728 | redol | bbs.demo.kkk5.com: 16659 | redol | Sleep | 16 | NULL |
| 729 | redol | bbs.demo.kkk5.com: 16661 | redol | Sleep | 12 | NULL |
| 730 | redol | bbs.demo.kkk5.com: 16663 | redol | Sleep | 8 | NULL |
+ ----- + ------- + ------------------------- + ------- + --------- + ------ + ------- + ------------------ +
That is to say, persistent connections will occupy mysql connections all the time. The problem arises. If I didn't restart php-fpm and only restarted mysql, what will happen? The answer is that the following error will be reported during the first connection.

Warning: mysqli (): MySQL server has gone away in

Therefore, before using a persistent connection query, you must first determine whether there is a connection. If there is no connection, close the connection. Be sure to close the connection before connecting it. Otherwise, the connection will fail.

Next, let's try the Too connector connections error.

First adjust the maximum number of connections of mysql

Vim/etc/mysql/my. cnf
Max_connections = 3
You didn't see it wrong. I only gave him three connections, and the above php-fpm was five, so I don't need to know the results, as scheduled.

Warning: mysqli (): (HY000/1040): Too connector connections in...

So for mysql online, pay attention to the max_connections number. I can only tell you that the default value is 100. If you think 100 is not enough, change it by yourself.

We can see from the above that short connections will be automatically closed without close, if pm is set. max_requests = 2. If every php-fpm processes two requests, it will be destroyed. Will it be close if it is destroyed? I will not take a screenshot. I will tell you the answer, yes, so no matter what, the number of connections in mysql is increased slowly, the decrease is due to the destruction of the php-fpm sub-process.

Okay, the paper is over. I have understood the principles of php-fpm for mysql.