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