1) generally, eliminate the factors of high concurrency, or to find a cause which a few of your CPU is too high in the execution of SQL, show the processlist statement, find the heaviest load SQL statements, to optimize the SQL, such as appropriate to establish a field index;
2) open the slow query log, will the execution time is too long and takes up too much resources to analyze the explain SQL results in excessive CPU, majority is GroupBy, OrderBy scheduling problems as a result, then slowly is optimized to improve, such as optimizing the insert statement, optimize the group by statement, optimizing the order by statement, optimize join statements, etc.
How to optimize the database _ fast flexible security and stability of _ _ high availability
3) consider timing optimization of documents and index;
4) regular analysis table, use the optimize table;
5) to optimize the database objects;
6) consider whether lock problem;
7) adjust some MySQL Server parameters, such as key_buffer_size, your table_cache, innodb_buffer_pool_size, innodb_log_file_size, etc.
8) if the data volume is too big, can consider to use MySQL cluster and build a high availability environment,
9) may be due to memory latch (leak) in the database CPU high
10) in the case of multi-user high concurrency, any system would hold not to live, so, use the cache is necessary, use memcached or redis cache can be;
11) to see if both tmp_table_size size small, if allowed, appropriate increase a little.
12) if max_heap_table_size configuration is too small, increase a little;
13) the SQL mysql sleep problems setting up the connection timeout (wait_timeout)
14) use show the processlist check mysql connection number, and see whether more than mysql set the number of connections
CodePudding user response:
Thanks for sharing,
However, it is not essentially a knowledge, advice to mysql section,
CodePudding user response:
Agree with the upstairs,CodePudding user response: