Performance tuning case sharing: MySQL CPU is too high

Source: Internet
Author: User

Problem:
A system, MySQL database, after the data is large. MySQL CPU utilization is very high, a Test side access to the server when MySQL CPU utilization is 15%, 6 test end of the server when the MySQL CPU occupancy rate is 50%~60%.
PS 1: Each Test side of the thing is to insert records, but before inserting will first check whether there is already the same record, some words to update the original record, not directly inserted.

PS 2:cpu--pentium Dual E1240 @ 1.60GHZ
Memory--2g
Os--windows 2003
Tuning ideas:

Using replace into instead of SELECT, insert into will improve performance.

If it is InnoDB, it is recommended that configuration parameters be optimized.
The InnoDB engine has poor performance under the default parameter configuration.
Recommended InnoDB configuration (1G memory condition, main running MySQL server):
Innodb_buffer_pool_size = 600M
Innodb_additional_mem_pool_size = 64M
# Set: _log_file_size to% of buffer pool size
Innodb_log_file_size = 256M
#innodb_log_buffer_size = 8M
Innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
Innodb_file_per_table
where Innodb_flush_log_at_trx_commit and innodb_file_per_table have the greatest impact on I/O performance.
Note: My-large.cnf and other files are just parameters to optimize the MyISAM engine

Performance tuning case sharing: MySQL CPU is too high

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.