PHP Nginx MySQL High concurrency tuning small test

Project requirements to achieve a free ticket to the function, involving high concurrency problems, research for a few days, record down, welcome workers throw Bricks ~ ~

The whole project is Php+nginx+mysql architecture, because PHP is blocked single-threaded model, does not support multi-threading, so there is no Java so good synchronization mechanism, I think of the method is at the database level to do the corresponding synchronization mutex control, The MySQL lock mechanism I put in the MySQL database lock mechanism in this blog post. By looking at the MySQL official documentation, I came up with two solutions: first, write SQL statements using lock TABLE or start TRANSACTION, and create a stored procedure directly in the database using Create PROCEDURE, and then I'll try both of these methods.

First, the use of lock mechanism

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

This is the query on the day of the winning users (in order to signal the simplification of business logic), and then I use PHP to make a judgment: whether the winning user exceeds the day's limit, no more than the user to win, then update the database, if two users simultaneously read the total number of winning users, one of the update database , another user read the nature is dirty data, this is why I did not release the lock of the table just now, according to business logic, is to jump out of MySQL with the program to judge, and then update the database and then release the lock.

Update test (Name,value) VALUES (' Tomcat ', 1); COMMIT; UNLOCK TABLE;

The disadvantage of this method is that the use of two database connections, the middle of inserting PHP judgment, will inevitably cause a loss of performance, the benefit is that the database does not have to insert business logic, loose coupling.

Ii. use of stored procedures

DELIMITER//drop PROCEDURE IF EXISTS proc; CREATE PROCEDURE proc (in CNT int,in user VARCHAR (+)) Begindeclare num int;declare Success int;select COUNT (*) into Num fro M test where Value=1;if num
   
  
A little explanation of the code (familiar coworkers please pass): 1. Redefine the default MySQL delimiter semicolon to//to avoid MySQL executing only one of the words; 2. Create a stored procedure incoming parameter CNT (winning user limit), user (the person who robbed the ticket); 3. Define two temporary variables num (current number of winning users), success (whether or not to win); 4. Query the current number of winning users, not exceeding the user state 1 is inserted, whereas 0; 5. Returns the winning or not flag and restores the MySQL SQL delimiter.
  
  
  
This stored procedure is called in PHP: $db->query ("Call proc (+, ' hehe ')");
  
  
The disadvantage of this method is that the business logic is introduced in the database, the program modification is not easy, the advantage is that the database connection is used only once, the lock time of the table is greatly reduced, and the concurrency efficiency is very high.
  
  

  
  
Third, the wonderful Windows environment of PHP
  
  
When I started to simulate high-concurrency user access with joy, the problem came ...
  
  
First, the Java write multi-threaded concurrent Access Program (PHP does not support multithreading: ）
  
  
  
  
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];When these threads are finished, the HTTP request is also initiated 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 ();The await method must be written to wait for other threads to be created, and then send System.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) {//TOD O auto-generated catch Blocke.printstacktrace ();}}}
  
  
  
  
  
  
Confidently run the program, the results found that the console 1 seconds 1 seconds to give me the results, that is, 1 user server needs about 1 seconds of processing time, this is a Tuo xiang!! There is no way to do the test to check the cause, test plan and results:
  
  
1. Test the generation and issuance of thread threads separately to meet the requirements.
  
  
Result: By printing the thread name and time, the thread is randomly forked out and run at almost the same point, the order of run is not the same as the order of the fork, it is more random, so it is not a multi-threading problem.
  
  
2. Access the database using the lock mechanism and the stored procedure, respectively, to compare the differences.
  
  
Result: The first user of the lock mechanism is time consuming 1078 MS, the second 2146ms, and the third 3199ms; stored procedure 1023ms, 2084ms; 3115ms; This is a problem: PHP seems to be processing these requests serially, even if the threads arrive at the server almost at the same time.
  
  
3. Use PHP directly to insert a piece of data into MySQL, whether or not to insert it requires 1s;
  
  
Results: Inserting a data time true TM is about 1s!!! This PHP and MySQL connection is also slow!
  
  
4. Test with Linux server, whether it is System impact
  
  
Results: Inserting data around 30ms, 100 concurrency around 300ms to fix!!!
  
  

  
  
From the third plan to think of the fourth one spent a long time, never thought it was the system, Google said this TM is PHP bug
  
  
"The problem is that the PHP_FCGI_CHILDREN environment variable are 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 would **never** work on Windows. " From https://bugs.php.net/bug.php?id=49859
  
  
I just want to say WTF, Windows seems really unsuitable for servers, and perhaps the creators of PHP don't want to use windows at all. Under Windows, PHP-CGI is the default listening on port 9000, only one process in service to the user, even if nginx how high concurrency, when forwarded to PHP-CGI can only be executed serially. There was a very witty buddy who directly fork several php-cgi processes to handle the request, worshipping:
  
  
  
  
HTTP {#window cannot derive child processes, only manually php_fcgi_children upstream Fastcgi_backend {server 127.0.0.1:9000;server that do not work in window 127.0.0.1:9001;server 127.0.0.1:9002;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;}}
  
  
He used upstream to create 4 processes in the Nginx configuration file to process the request, and then forward the PHP request to something similar to the load balancer, so that it can improve the concurrency of processing power.
  
  
  
Recall the way I started PHP under Linux: command line input spawn-fcgi-a 127.0.0.1-p 9000-c-u www-data-f/usr/bin/php-cgi, spawn out 10 sub-processes to place 9000 ports of concurrent requests, so 100 requests are almost 10 times times the time of a single thread, so happy ~ ~
  
  
In the process of data optimization, we also learned some tips for tuning:
  
  
  
  
Nginx Configuration tuning:
  
  
  
  
Worker_processes 4;//opens 4 working processes with no more than the number of cores in the CPU. Nginx non-blocking IO & IO multiplexing model for high concurrency

Events {
Worker_connections 1024;//Increase the number of connections per worker process that can accept requests
Multi_accept on;//Open Accept multiple requests
}
  
  
About the Nginx upstream mentioned above can be Ip_hash, the different IP requests forwarded to the appropriate server to do load balancing,
  
  
#定义负载均衡设备的Ip及设备状态
  
  
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 Equalizer address Proxy_pass http://resinserver/in the server that needs to use load balancing;
  
  

The status of each device is set to:
1.down indicates that the server is temporarily not participating in the load
The 2.weight represents the load weight, which defaults to 1. The larger the weight, the greater the weight of the load.
3.max_fails: The number of times that a request failed is allowed defaults to 1. Returns the error defined by the Proxy_next_upstream module when the maximum number of times is exceeded
4.fail_timeout:max_fails the time of the pause after the failure.
5.backup: When all other non-backup machines are down or busy, request the backup machine. So the pressure on this machine is the lightest.
  
  
Nginx supports multiple sets of load balancing at the same time, which is used for unused servers.
  
  
Client_body_in_file_only set to On can speak the client post data logged to the file to do debug
Client_body_temp_path setting a directory of record files can be set up to 3 levels of directories
The location matches the URL. Can redirect or perform new proxy load balancing
  
  

  
  
  
  
PHP-FPM tuning: Turn on process.max = 128
  
  
  
  
  
  
You can refer to these two articles for MySQL tuning:
  
  
  
  
  
  
LAMP System Performance Tuning, part 3rd: MySQL Server Tuning
On the monitoring and tuning of MySQL
  
  
  
  
Plus: for PHP can not store global variables in the server, similar to java application variable, I used a method of shared memory to temporarily solve the problem, the total feeling where not good, welcome coworkers to teach ~ ~
  
  
  
  
Read variables in shared memory, enter memory ID, access mode read/write, permissions, 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 variable, function writememory ($systemid, $mode, $permissions, $size, $content) {$shmid = Shmop_open ($systemid, $mode, $ permissions, $size); Shmop_write ($shmid, $content, 0); Shmop_close ($shmid);}
  
  
WriteMemory (1024x768, ' C ', 0755, 1024x768, $content);
  
  
ReadMemory (1024x768, ' A ', 0755, 1024);
  
  
  
  
  
  
  
  

  
  
Sharing promotes social progress ~ ~
  
  

  
  

  
  
Reference documents:
  
  
The allocation method of Nginx upstream;
  
  
window+nginx+php-cgi php-cgi Thread/sub-process issues;
  
  
PHP kernel exploration;
  
  
Discuss whether Nginx and PHP-FPM are running in multi-process multithreading mode.
  
  

  
