Hey list
We keep adding devices to Observium, and it seems we're getting close to some tipping point for MySQL performance.
Looking at output from "top" we frequently see high iowait:
[cid:image001.png@01D89790.EF319B00]
Looking at iotop, it seems that MySQL is the aggressor here:
[cid:image002.png@01D89790.EF319B00]
We figured syslog was the culprit at first, and tried disabling that for a while to see if stuff died down. It did not.
Been playing a little with mytop, and even though we have a QPS around 780, it's not possible for me to nail down what queries are hogging the IO on the server. I googled around and did some changes to the InnoDB, and though I saw improvements, they were not as massive as I had hoped. This is what I currently have changed from defaults:
[mysqld] # This is a little too much for the installed 8G in the machine and will be adjusted at a later time. Just trying stuff out now. innodb_buffer_pool_size=6G
# We have fast SSD drives on vSAN innodb_io_capacity=1000
# Don't flush file write so often, be more relaxed. innodb-flush-method=O_DIRECT_NO_FSYNC
So I'm kind of stumped here. It's not killing Observium totally just yet, but we have experienced some outages this week and will probably see more as we keep adding devices.
Any recommendations will be much appreciated.
Lars ________________________________ Disclaimer: This e-mail, including any attachments, is for the intended recipient only. If you have received this e-mail by mistake please notify the sender immediately by return e-mail and delete this e-mail and any attachments, without opening the attachments, from your system. Access, disclosure, copying, distribution or reliance on any part of this e-mail by anyone else is prohibited. This e-mail is confidential and may be legally privileged. Chr. Hansen does not represent and/or warrant that the information sent and/or received by or with this e-mail is correct and does not accept any liability for damages related thereto. https://www.chr-hansen.com/en/legal-notice
How big is the install? What is the hardware?
I usually recommend MySQL/Web/rrdcached is placed on a system with higher individual core speed for just this reason, mysql scales better with faster cores than more cores.
Perhaps it would be useful if we tracked MySQL time per-module.
I suspect a lot of the load might be updating very heavy indexes, but I'm not sure how to profile that.
Adam.
From: observium observium-bounces@observium.org On Behalf Of Lars Joergensen via observium Sent: 14 July 2022 14:27 To: Observium observium@observium.org Cc: Lars Joergensen DKLARJ@chr-hansen.com Subject: [Observium] MySQL IO Wait
Hey list
We keep adding devices to Observium, and it seems we're getting close to some tipping point for MySQL performance.
Looking at output from "top" we frequently see high iowait:
Looking at iotop, it seems that MySQL is the aggressor here:
We figured syslog was the culprit at first, and tried disabling that for a while to see if stuff died down. It did not.
Been playing a little with mytop, and even though we have a QPS around 780, it's not possible for me to nail down what queries are hogging the IO on the server. I googled around and did some changes to the InnoDB, and though I saw improvements, they were not as massive as I had hoped. This is what I currently have changed from defaults:
[mysqld]
# This is a little too much for the installed 8G in the machine and will be adjusted at a later time. Just trying stuff out now.
innodb_buffer_pool_size=6G
# We have fast SSD drives on vSAN
innodb_io_capacity=1000
# Don't flush file write so often, be more relaxed.
innodb-flush-method=O_DIRECT_NO_FSYNC
So I'm kind of stumped here. It's not killing Observium totally just yet, but we have experienced some outages this week and will probably see more as we keep adding devices.
Any recommendations will be much appreciated.
Lars
_____
Disclaimer: This e-mail, including any attachments, is for the intended recipient only. If you have received this e-mail by mistake please notify the sender immediately by return e-mail and delete this e-mail and any attachments, without opening the attachments, from your system. Access, disclosure, copying, distribution or reliance on any part of this e-mail by anyone else is prohibited. This e-mail is confidential and may be legally privileged. Chr. Hansen does not represent and/or warrant that the information sent and/or received by or with this e-mail is correct and does not accept any liability for damages related thereto. https://www.chr-hansen.com/en/legal-notice
Are you perhaps collecting syslog in to Observium? I suspected those massive database writes are not generated by Observium itself unless this is a very big setup.
/Markus
Den tors 14 juli 2022 15:44Adam Armstrong via observium < observium@observium.org> skrev:
How big is the install? What is the hardware?
I usually recommend MySQL/Web/rrdcached is placed on a system with higher individual core speed for just this reason, mysql scales better with faster cores than more cores.
Perhaps it would be useful if we tracked MySQL time per-module.
I suspect a lot of the load might be updating very heavy indexes, but I’m not sure how to profile that.
Adam.
*From:* observium observium-bounces@observium.org *On Behalf Of *Lars Joergensen via observium *Sent:* 14 July 2022 14:27 *To:* Observium observium@observium.org *Cc:* Lars Joergensen DKLARJ@chr-hansen.com *Subject:* [Observium] MySQL IO Wait
Hey list
We keep adding devices to Observium, and it seems we’re getting close to some tipping point for MySQL performance.
Looking at output from “top” we frequently see high iowait:
Looking at iotop, it seems that MySQL is the aggressor here:
We figured syslog was the culprit at first, and tried disabling that for a while to see if stuff died down. It did not.
Been playing a little with mytop, and even though we have a QPS around 780, it’s not possible for me to nail down what queries are hogging the IO on the server. I googled around and did some changes to the InnoDB, and though I saw improvements, they were not as massive as I had hoped. This is what I currently have changed from defaults:
[mysqld]
# This is a little too much for the installed 8G in the machine and will be adjusted at a later time. Just trying stuff out now.
innodb_buffer_pool_size=6G
# We have fast SSD drives on vSAN
innodb_io_capacity=1000
# Don't flush file write so often, be more relaxed.
innodb-flush-method=O_DIRECT_NO_FSYNC
So I’m kind of stumped here. It’s not killing Observium totally just yet, but we have experienced some outages this week and will probably see more as we keep adding devices.
Any recommendations will be much appreciated.
Lars
Disclaimer: This e-mail, including any attachments, is for the intended recipient only. If you have received this e-mail by mistake please notify the sender immediately by return e-mail and delete this e-mail and any attachments, without opening the attachments, from your system. Access, disclosure, copying, distribution or reliance on any part of this e-mail by anyone else is prohibited. This e-mail is confidential and may be legally privileged. Chr. Hansen does not represent and/or warrant that the information sent and/or received by or with this e-mail is correct and does not accept any liability for damages related thereto. https://www.chr-hansen.com/en/legal-notice _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
Hi Markus
It was indeed syslog. There was a massive amount of writes, and the table was also corrupted.
Somebody had set a couple of devices to do debug logging. Never a good idea. Looking into ways for syslog to ignore the higher levels since we don't need debugging output in the database anyway.
Lars
From: observium observium-bounces@observium.org On Behalf Of Markus Klock via observium Sent: 14. juli 2022 18:08 To: Observium observium@observium.org Cc: Markus Klock markus@best-practice.se Subject: Re: [Observium] MySQL IO Wait
Are you perhaps collecting syslog in to Observium? I suspected those massive database writes are not generated by Observium itself unless this is a very big setup.
/Markus
Den tors 14 juli 2022 15:44Adam Armstrong via observium <observium@observium.orgmailto:observium@observium.org> skrev: How big is the install? What is the hardware?
I usually recommend MySQL/Web/rrdcached is placed on a system with higher individual core speed for just this reason, mysql scales better with faster cores than more cores.
Perhaps it would be useful if we tracked MySQL time per-module.
I suspect a lot of the load might be updating very heavy indexes, but I'm not sure how to profile that.
Adam.
From: observium <observium-bounces@observium.orgmailto:observium-bounces@observium.org> On Behalf Of Lars Joergensen via observium Sent: 14 July 2022 14:27 To: Observium <observium@observium.orgmailto:observium@observium.org> Cc: Lars Joergensen <DKLARJ@chr-hansen.commailto:DKLARJ@chr-hansen.com> Subject: [Observium] MySQL IO Wait
Hey list
We keep adding devices to Observium, and it seems we're getting close to some tipping point for MySQL performance.
Looking at output from "top" we frequently see high iowait:
[cid:image001.png@01D89790.21BABA80]
Looking at iotop, it seems that MySQL is the aggressor here:
[cid:image002.png@01D89790.21BABA80]
We figured syslog was the culprit at first, and tried disabling that for a while to see if stuff died down. It did not.
Been playing a little with mytop, and even though we have a QPS around 780, it's not possible for me to nail down what queries are hogging the IO on the server. I googled around and did some changes to the InnoDB, and though I saw improvements, they were not as massive as I had hoped. This is what I currently have changed from defaults:
[mysqld] # This is a little too much for the installed 8G in the machine and will be adjusted at a later time. Just trying stuff out now. innodb_buffer_pool_size=6G
# We have fast SSD drives on vSAN innodb_io_capacity=1000
# Don't flush file write so often, be more relaxed. innodb-flush-method=O_DIRECT_NO_FSYNC
So I'm kind of stumped here. It's not killing Observium totally just yet, but we have experienced some outages this week and will probably see more as we keep adding devices.
Any recommendations will be much appreciated.
Lars ________________________________ Disclaimer: This e-mail, including any attachments, is for the intended recipient only. If you have received this e-mail by mistake please notify the sender immediately by return e-mail and delete this e-mail and any attachments, without opening the attachments, from your system. Access, disclosure, copying, distribution or reliance on any part of this e-mail by anyone else is prohibited. This e-mail is confidential and may be legally privileged. Chr. Hansen does not represent and/or warrant that the information sent and/or received by or with this e-mail is correct and does not accept any liability for damages related thereto. https://www.chr-hansen.com/en/legal-noticehttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.chr-hansen.com%2Fen%2Flegal-notice&data=05%7C01%7Cdklarj%40chr-hansen.com%7C2588f22a725f465e447508da65b32434%7C89d9c49978c045d4a33fee275c523232%7C0%7C0%7C637934117335223120%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=F%2FJRTOeHT8iYnWOvRmJEpKrGODTXUCKwTXShfTcYakA%3D&reserved=0 _______________________________________________ observium mailing list observium@observium.orgmailto:observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observiumhttps://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fpostman.memetic.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fobservium&data=05%7C01%7Cdklarj%40chr-hansen.com%7C2588f22a725f465e447508da65b32434%7C89d9c49978c045d4a33fee275c523232%7C0%7C0%7C637934117335223120%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=h%2FguHi1%2BVqoHwo%2BXI6cRHrh115YEUPbA%2BbJiDQP5bDY%3D&reserved=0 ________________________________ Disclaimer: This e-mail, including any attachments, is for the intended recipient only. If you have received this e-mail by mistake please notify the sender immediately by return e-mail and delete this e-mail and any attachments, without opening the attachments, from your system. Access, disclosure, copying, distribution or reliance on any part of this e-mail by anyone else is prohibited. This e-mail is confidential and may be legally privileged. Chr. Hansen does not represent and/or warrant that the information sent and/or received by or with this e-mail is correct and does not accept any liability for damages related thereto. https://www.chr-hansen.com/en/legal-notice
That's a pretty small I/O load especially for SSD's. When doing an iostat on the lun what are you showing for iops? Normally when I see large i/o wait it's the back-end subsystem. Since you're indicating a san here do you have/can you get any data from your san/storage admin on your luns?
Not knowing your installation size, we also had flash based san storage here but I moved our observium deployment to local ssd's (raid 10) which I clipped to 50% of capacity for better garbage collection instead due to over saturation of the san's i/o channels for that tier of storage. This was for ~600 devices ~200,000+ ports. It just wasn't cost effective to use the san for the iops needs.
On 7/14/22 08:27, Lars Joergensen via observium wrote:
Hey list
We keep adding devices to Observium, and it seems we’re getting close to some tipping point for MySQL performance.
Looking at output from “top” we frequently see high iowait:
Looking at iotop, it seems that MySQL is the aggressor here:
We figured syslog was the culprit at first, and tried disabling that for a while to see if stuff died down. It did not.
Been playing a little with mytop, and even though we have a QPS around 780, it’s not possible for me to nail down what queries are hogging the IO on the server. I googled around and did some changes to the InnoDB, and though I saw improvements, they were not as massive as I had hoped. This is what I currently have changed from defaults:
[mysqld]
# This is a little too much for the installed 8G in the machine and will be adjusted at a later time. Just trying stuff out now.
innodb_buffer_pool_size=6G
# We have fast SSD drives on vSAN
innodb_io_capacity=1000
# Don't flush file write so often, be more relaxed.
innodb-flush-method=O_DIRECT_NO_FSYNC
So I’m kind of stumped here. It’s not killing Observium totally just yet, but we have experienced some outages this week and will probably see more as we keep adding devices.
Any recommendations will be much appreciated.
Lars
Disclaimer: This e-mail, including any attachments, is for the intended recipient only. If you have received this e-mail by mistake please notify the sender immediately by return e-mail and delete this e-mail and any attachments, without opening the attachments, from your system. Access, disclosure, copying, distribution or reliance on any part of this e-mail by anyone else is prohibited. This e-mail is confidential and may be legally privileged. Chr. Hansen does not represent and/or warrant that the information sent and/or received by or with this e-mail is correct and does not accept any liability for damages related thereto. https://www.chr-hansen.com/en/legal-notice
observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
participants (4)
-
Adam Armstrong -
Lars Joergensen -
Markus Klock -
Steve Costaras