Hello guys. My name is Tiberio, i’m from Brazil. (Sorry about English mistakes)
I’m trying to solve a performance problem a few weeks and I’ve read many posts and did a lot of research. Already I went over all my application and it seems to be a problem related to operating system tuning.
The Percona runs very well, but at certain points of the day there is an increase in the amount of CPU usage for system processes (CPU Usage Image). After a restart MySQL everything returns to normal for a few hours (for the rest of the day sometimes).
Analyzing the status of INNODB “show engine innodb status” realized that many processes are waiting for Semaphores in “OS WAIT ARRAY INFO” (status image)
Can someone help me? My database is well structured and my application is not causing this performance problems.
Our solution uses (VMWARE virtualized guest O.S.):
Percona 5.5.44
CPU: 12 x Intel(R) Xeon(R) CPU E5-2687W 0 @ 3.10GHz (socket:1 core:12)
OS: CentOS release 6.6 (Final)
Kernel: 2.6.32-504.30.3.el6.x86_64
SSD disks (disk cache and controller cache)
64GiB RAM
We have already applied all the innodb tunning best practices:
innodb_flush_log_at_trx_commit=2
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_support_xa=0
innodb_open_files=9600
innodb_stats_on_metadata=0
innodb_buffer_pool_size=24G
innodb_buffer_pool_instances=8
innodb_log_file_size=1G
innodb_log_buffer_size=16M
innodb_io_capacity=400
innodb_adaptive_flushing_method=keep_average
key_buffer_size=9G
thread_cache_size=80
max_connections=800
open_files_limit=12348
log_bin=OFF
query_cache_type=0
query_cache_limit=0
query_cache_size=0
We have already applied leap second fix.
I have already read:
[url]https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/[/url]
[url]https://www.percona.com/blog/2011/09/02/understand-innodb-spin-waits-win-a-percona-live-ticket/[/url]
