PHPNginxMySQL high concurrency Optimization Test-php Tutorial

PHPNginxMySQL high concurrency optimization test project requires a free coupon grabbing function, involving high concurrency issues, after studying for a few days, record it, welcome fellow workers to throw bricks ~~

The entire project is based on the PHP + Nginx + Mysql architecture.Blocking single-thread model,Multithreading is not supported, so it is not as easy to use as Java synchronization. the method I came up with is to implement synchronization mutex control at the database level, I have stored the Mysql database lock mechanism in this blog. By viewing the Mysql official documentation, I came up with two solutions: 1. use lock table or start transaction to write SQL statements; 2. use CREATE PROCEDURE to directly CREATE a stored PROCEDURE in the database, then I tried both methods.

I. use the lock mechanism

SET autocommit=0;LOCK TABLE test;select count(*) from test where value=1;COMMIT;

This is to query the winning user of the day (to simplify the business logic), and then I use PHP to make a judgment: whether the winning user has exceeded the quota of the day, and if not, the user won the prize, at this time, UPDATE the database. if two users read the total number of winning users simultaneously, one of them updates the database, and the other users read dirty data, that is why I did not release the lock of the table just now. according to the business logic, I decided to jump out of mysql and use the program to determine, and then update the database to release the lock.

update test(name,value) values('Tomcat',1);COMMIT;UNLOCK TABLE;

The disadvantage of this method is that the two database connections are used, and the PHP judgment is inserted in the middle, which will inevitably cause performance loss. the advantage is that the database does not have to insert business logic and is loosely coupled.

II. use stored procedures

DELIMITER // drop procedure if exists proc; create procedure proc (IN cnt INT, IN user VARCHAR (32) BEGINDECLARE num INT; DECLARE success INT; select count (*) INTO num from test where value = 1; IF num
   
  
Slightly explain the code (pass for familiar workers): 1. define the default separator semicolon of mysql as // to prevent mysql from executing only one sentence; 2. create the stored procedure and input the parameter cnt (winning user quota), user (the user who snatched the ticket); 3. define two temporary variables: num (currently the number of winners), success (whether to win); 4. query the number of currently winning users. if the number is not exceeded, the user status 1 is inserted; otherwise, 0 is inserted; 5. returns the winning or not mark to restore the SQL separator of mysql.
  
  
  
Call this stored procedure in php: $ db-> query ("call proc (100, 'hehes ')");
  
  
The disadvantage of this method is that the business logic is introduced in the database, and the program is not easy to modify. the advantage is that only one database connection is used, and the table lock time is greatly reduced, resulting in high concurrency efficiency.
  
  

 
  
  
3. PHP in windows
  
  
When I started simulating highly concurrent user access with great pleasure, the problem came...
  
  
Paste the multi-thread concurrent access program written in java first (php does not support multi-thread ..)
  
  
  
  
Import java. util. concurrent. cyclicBarrier; import com. test. run. threadTest; public class Test {public static void main (String [] args) {CyclicBarrier cb = new CyclicBarrier (100 ); // fork 100 threads ThreadTest [] ttarray = new ThreadTest [100]; // wait for these threads to fork and initiate an http request for (int I = 0; I <ttarray. length; I ++) {ttarray [I] = new ThreadTest (cb); ttarray [I]. start () ;}} import java. io. bufferedReader; import java. io. dataInputStream; import java. io. IOException; import java. io. inputStreamReader; import java.net. httpURLConnection; import java.net. malformedURLException; import java.net. URL; import java. util. concurrent. brokenBarrierException; import java. util. concurrent. cyclicBarrier; public class ThreadTest extends Thread {private CyclicBarrier cb; public ThreadTest (CyclicBarrier cb) {super (); this. cb = cb ;}@ Overridepublic void run () {String path =" http://127.16.0.57/concurrent/index.php?user= "+ Thread. currentThread (). getName (); try {URL url = new URL (path); HttpURLConnection huc = (HttpURLConnection) url. openConnection (); huc. setRequestMethod ("GET"); huc. setDoInput (true); huc. setDoOutput (true); huc. setUseCaches (false); huc. connect (); cb. await (); // you must write the await method to wait until other threads have been created, and then send the System in a unified manner. out. println (Thread. currentThread (). getName () + "\ t" + System. currentTimeMillis () + "\ tbegin"); // long l1 =; InputStreamReader Isr = new InputStreamReader (huc. getInputStream (), "UTF-8"); BufferedReader bf = new BufferedReader (isr); String str = bf. readLine (); while (str! = Null) {System. out. println (str); str = bf. readLine ();} long l2 = System. currentTimeMillis (); // System. out. println (L2-L1 + "" + Thread. currentThread (). getName (); // System. out. println (Thread. currentThread (). getName () + "\ t" + System. currentTimeMillis () + "end");} catch (MalformedURLException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace ();} catch (InterruptedException e) {// TODO Auto-generated catch blocke. printStackTrace ();} catch (BrokenBarrierException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}
  
  
  
  
  
  
I ran the program with confidence and found that the console gave me the result in one second and one second, that is, it took about one second to process one user server, this is an amazing task !! There is no way to quickly perform the test to check the cause, test plan and results:
  
  
1. test whether the requests produced and sent by all threads meet the requirements.
  
  
Result: by printing the thread name and time, it is found that the thread is randomly fork and run at almost the same time point. The run sequence is different from that of fork, which makes it more random, this is not a problem with multithreading.
  
  
2. access the database using the lock mechanism and stored procedure respectively, and compare the differences between the two.
  
  
Results: the lock mechanism of the first user took 1078 ms, the second 2146 ms, the third 3199 ms; the storage process was 1023 ms, 2084 ms; 3115 ms, this illustrates a problem: PHP seems to be handling these requests serially, even if these threads almost reach the server at the same time.
  
  
3. directly use PHP to insert a piece of data into mysql. it takes 1 s to insert data;
  
  
Result: the insertion time is about 1 s !!! The connection between PHP and mysql is also slow!
  
  
4. Use a linux server to test whether it is affected by the system.
  
  
Result: the data is inserted for about 30 ms, and 100 of the concurrency is about MS !!!
  
  

 
  
  
It took a long time to think of the fourth solution from the third solution. I did not expect it to be the cause of the system. google said this TM was a PHP bug.
  
  
"The problem is that the PHP_FCGI_CHILDREN environment variable is ignored under windows, therefore php-cgidoes not spawn children, and when PHP_FCGI_MAX_REQUESTS is reached the process terminates. so, php with fast-cgi will ** never ** work on Windows. from https://bugs.php.net/bug.php? Id = 49859
  
  
I just want to talk about WTF. windows doesn't seem to be suitable for servers. maybe the php creators don't want to use windows at all. In windows, php-cgi listens to Port 9000 by default, and only one process serves the user. even if nginx is highly concurrent, when forwarding data to php-cgi, it can only be executed serially. There was a very witty buddy who directly fork several php-cgi processes to process requests and worships:
  
  
  
  
Http {# window cannot dispatch child processes. you can only manually configure the upstream fastcgi_backend {server 127.0.0.1: 9000; server 127.0.0.1: 9001; server 127.0.0.1: 9002; server 127.0.0.1:; server 127.0.0.1: 9003;} server {listen 80; server_name q. qq; access_log. /.. /log/q.qq.access.txt; root d:/web/www; location ~ \. Php $ {fastcgi_pass fastcgi_backend ;}}
  
  
In the nginx configuration file, he uses upstream to create four processes to process requests, and then forwards php requests to something similar to the load balancer, you can improve the concurrent processing capability.
  
  
  
I think about how to start Php in Linux: run the command line to input spawn-fcgi-a 127.0.0.1-p 9000-C.10-U www-data-f/usr/bin/php-cgi, spawn generates 10 sub-processes to process concurrent requests on port 9000, therefore, the time of 100 requests is almost 10 times that of a single thread, so it is a lot of fun ~~
  
  
Some Tuning Tips have also been learned during data query optimization:
  
  
  
  
Nginx configuration optimization:
  
  
  
  
Worker_processes 4; // enable four worker processes. the number of worker processes is no more than the number of CPU cores. Nginx is a non-blocking IO & IO multiplexing model and is suitable for high concurrency.
 
Events {
Worker_connections 1024; // increase the maximum number of connections allowed by each worker process
Multi_accept on; // enables receiving multiple requests
}
 
  
  
For the nginx upstream mentioned above, you can use ip_hash to forward different IP requests to the corresponding server for load balancing,
  
  
# Define the Ip address and device status of the server load balancer device
  
  
Upstream resinserver {
Ip_hash;
Server 127.0.0.1: 8000 down;
Server 127.0.0.1: 8080 weight = 2;
Server 127.0.0.1: 6801;
Server 127.0.0.1: 6802 backup;
}
  
  
Add the balancer address proxy_pass http: // resinserver/to the server that needs to use server load balancer /;
  
  
 
The status of each device is set:
1. down indicates that the server before a ticket is not involved in the load
2. weight indicates the load weight. the default value is 1. The larger the weight, the larger the load weight.
3. max_fails: the default number of failed requests is 1. if the maximum number of failed requests is exceeded, an error defined by the proxy_next_upstream module is returned.
4. fail_timeout: the pause time after max_fails fails.
5. backup: requests the backup machine when all other non-backup machines are down or busy. Therefore, this machine is under the least pressure.
  
  
Nginx supports setting multiple groups of server load balancer instances for unused servers.
  
  
Client_body_in_file_only is set to On. you can use the client post data record in the file for debugging.
Client_body_temp_path: Set the Directory of the record file to a maximum of three levels.
Location matches the URL. you can perform redirection or perform new proxy load balancing.
  
  
 
 
  
  
  
  
PHP-fpm optimization: enable process. max = 128
  
  
  
  
  
  
For Mysql optimization, refer to the following two articles:
  
  
  
  
  
  
LAMP system performance optimization, Part 1: MySQL Server Optimization
MySQL monitoring and tuning
  
  
  
  
Plus: for the global variables that cannot be stored in PHP on the server, similar to the Java application variables, I used a shared memory method to temporarily solve this problem. I always feel bad about it, thank you for your comments ~~
  
  
  
  
// READ the variables in the shared memory, input the memory ID, access mode READ/WRITE, permission, block size
  
  
Function readMemory ($ systemid, $ mode, $ permissions, $ size) {$ shmid = shmop_open ($ systemid, $ mode, $ permissions, $ size ); $ size = shmop_size ($ shmid); $ res = shmop_read ($ shmid, 0, $ size); shmop_close ($ shmid ); // close shared memory is a must in case of dead lockreturn $ res;} // write the variable, function writeMemory ($ systemid, $ mode, $ permissions, $ size, $ content) {$ shmid = shmop_open ($ systemid, $ mode, $ permissions, $ size); shmop_write ($ shmid, $ content, 0); shmop_close ($ shmid );}
  
  
writeMemory(1024, 'c', 0755, 1024,$content);
  
  
readMemory(1024, 'a', 0755, 1024);
  
  
  
  
  
  
  
  

 
  
  
Share to promote social progress ~~
  
  

 
  
  

 
  
  
References:
  
  
Allocation method of nginx upstream;
  
  
Window + nginx + php-cgi thread/sub-process problems;
 
  
  
PHP kernel exploration;
  
  
Check whether nginx and php-fpm run in multi-process and multi-thread mode.