Impact of database indexes on server performance

During the voting activity from January 15, May 6-5, the activity brought unprecedented access traffic to the website. due to the imperfect anti-cheating measures of the voting system, a large number of users used the IP address to change votes, in addition, malicious users sometimes send a large number of CC attacks to the website from October 11 to October 15 due to the launching of voting activities. this activity has brought unprecedented access traffic to the website, due to the imperfect anti-cheating measures of the voting system, a large number of users use the IP address to change the ticket scanning software. In addition, malicious users sometimes send a large number of CC attacks to the website, the website's hardware service architecture and application design face huge challenges. The overall performance of the website's hardware service architecture is relatively good. The server adopts the front-end reverse proxy server + Nginx (FastCGI) backend servers + database servers, front-end reverse proxy servers cache static resources accessed by users (images, CSS styles, HTML files that are pseudo-static and static through backend servers ), when a user requests a dynamic program file or performs database operations, the frontend reverse proxy delivers the request service to the Nginx (FastCGI) backend server or database server. The reverse proxy server generates static HTML files for pseudo-static applications on the backend servers, which reduces a large number of database query operations and improves the overall performance of the website. However, during the development of the voting system, developers neglected the best design method of applications and data tables because they only focused on the implementation of their functions, resulting in an unnecessary 502 Bad Gateway error on the website.
After access tests on multiple sites, the front-end reverse proxy server is denied. Its 502 error occurs when Nginx and PHPFastCGI run in parallel. Nginx runs normally while PHPFastCGI is suspended and the process cannot be processed, you have to check which problem caused FastCGI to be unable to process the application; high concurrency, insufficient CPU, and influence of FastCGI Process count settings. In fact, all these problems are generated during PHPFastCGI processing, we need to leave all doubts about Nginx.
Go to the backend server and find that the CPU share ratio of a large number of php-cgi processes is very high based on the system monitoring command. after careful troubleshooting, there is no direct impact on the backend server, the database server finds that MySQL consumes a lot of CPU resources. after monitoring MySQL, it finds that a large number of users are waiting for data to be returned, and SQL statements have efficiency problems. a field is not indexed, this results in a large amount of data query waiting and write waiting, resulting in table locks, the server CPU resources are exhausted, and the database server cannot normally return data with the backend server, as a result, the backend server php-cgi cannot be processed normally. after this field is indexed, the backend server and database server load returns to normal, and the 502 error also disappears.