Hi everyone,
Description:
When we installed and started up pmm-agent (2.12, on Centos6 server and MySQL 5.5), we had a performance decrease on our applications. Exemple, a dedicated thread on our mySQL instance take time 50ms but with pmm activated, it take time 800ms ? It was a big problem for us, so we stopped pmm-agent and so, no more performance problem.
Nota : slow_query AND performance_schema are on.
Version:
pmm2 : 2.12
pmm2-server : 2.37
MySQL client : 5.5.62
Server OS client Centos 6
Server OS pmm : Centos 7
Can you help us ?
Thanks a lot.
Greg
Hello @greg_Atos,
Can you check if innodb_stats_on_metadata is ON or OFF? If ON, this is most likely the source of the performance problem as PMM collects much metatdata every second. When ON, this causes index statistics to be recalculated each time. If it is ON, you are perfectly safe to set this OFF as OFF became the default in 5.6+
Hello @matthewb ,
Yes, innodb_stats_on_metadata is ON. We can’t right now set as OFF because it’s a production server but we will try it in two days.
Do you know if we can see the impact of this option ? Before AND after ? On Percona graph ? or other tool ?
Thanks a lot for your help,
Greg
Yes, as you can see that blog is from 2013!! innodb_stats_on_metadata is very well known in the MySQL community for being a performance killer. You should turn this off as soon as you can. You should try SET GLOBAL innodb_stats_on_metadata=OFF I don’t recall if it is dynamic, or requires restart.
The impact you will see will actually be a performance GAIN! Your queries should execute faster, and less CPU will be used.
And I have to say that MySQL 5.5 has been completely dead for almost 7 years. 5.6 is also dead and 5.7 will be dead at the end of October. You should make every effort to upgrade ASAP.
@matthewb , yes I know but I’ worked in a very secure client context and I can’t upgrade right now.
When you set global variables, it’s dynamics 
Thanks again !!!