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 entire project is the Php+nginx+mysql architecture, since 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 to do at the database level corresponding synchronization mutex control, MySQL lock mechanism I put in the MySQL database lock mechanism in this blog post. By looking at the official MySQL documentation, I came up with two solutions: First, write the SQL statement using lock TABLE or start TRANSACTION, and create a stored procedure directly in the database using create PROCEDURE. And then I tried both of these methods separately.

First, the use of lock mechanism

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

This isQuery 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 update of the database, another user read the nature is dirty data, which is why I did not release the lock on 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<cnt theninsert into Test (user,value) values (user,1); SET Success=1; Elseinsert into Test (user,value) values (user,0); SET success=0; END IF; SELECT success; End//delimiter;

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 (+); <span style= "White-space:pre" ></span>//fork 100 Threads threadtest[] ttarray=new threadtest[100];<span style= "White-space:pre" ></span>//wait for these threads to fork over, Also initiates 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 (); <span style= "White-space:pre" >< /span>//must write the await method waiting 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) {//TODO 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 name and time of the thread, the thread is randomly forked out, and the run is started at almost the same point, and the order of run is different from the order of the fork, which makes it more random and therefore 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

"T He Problem is and the Php_fcgi_children environment Variable is ignored under Windows , T Herefore php-cgidoes not spawn children , and when Php_fcgi_max_requests was reached The process terminates. so, php with Fast-cgi'll **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.

Think back to 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 of 10 sub-processes to handle 9000-port concurrent requests, so 100 requests are almost 10 times times the time of the single thread, and therefore a lot of fun ~ ~

In the process of data optimization, we also learned some tips for tuning:

• Nginx Configuration tuning:

Worker_processes 4; //Open 4 working processes with no more than the number of cores of 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 multi-Request
}

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 http://resinserver/

1.down indicates that the server is temporarily not participating in the load
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 default number of times to allow requests to fail is 1. Returns the error defined by the Proxy_next_upstream module when the maximum number of times is exceeded
4.fail_timeout:max_fails times 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: Open Process.max =

• 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.

PHP Nginx MySQL High concurrency tuning small test