php concurrency puts pressure on MYSQL

  • 2020-05-27 04:40:34
  • OfStack

The PHP page has one page view and 200 concurrent views per second, causing insert to put a lot of pressure on mysql

In the environment of lnmp, other page contents have been cached without much load. It is this page view statistics function that brings a lot of pressure to mysql. Do you have any solutions?

Whatever you say, don't do it with MySQL, 200 concurrency per second isn't too hard for MySQL. And with Redis, Memcached, persistence aside, business code and operations deployment are not small.

Let me give you a few Suggestions for making your operations deployment and business code changes as small as possible.

You can do master separation, don't do high concurrent inserts on a library and do a lot of statistical calculations at the same time. After separation, the query is done from the slave library (or even imported into a specialized distributed system such as Hive), and the index can be removed from the master library to improve insertion performance. This way, the business code barely needs to be changed (just a database configuration file). MySQL operation and maintenance deployment can also choose a business trough to do online.

If you can live with a few changes to the business code (PHP), here are two Suggestions:
1. Sub-database, sub-table, the total amount of data in each table is small, so the performance of operation will be better, especially for the MyISAM table from the slave library. You may have a query before you insert, such as whether this IP is in the library or not, and whether it has been counted before.
2. Use the HandlerSocket plug-in to bypass SQL Parser and directly manipulate the stored files. If commercially possible, bulk insert (bulk insert) can also be used. MySQL InnoDB also launched InnoDB NoSQL Plugin, which is similar to HandlerSocket. memcached protocol, InnoDB Buffer, shares InnoDB Buffer, and no longer has to worry about how MySQL and Memcached maintain data 1 homogeneity.

Related articles: