Issue after removing unwanted hosts

[Davidus]

Hello folks,

my environment,
RHEL 5.3
MySQL 5.0.77
Zabbix 1.8.4

I keep history, actions, events, trends for 800 days

A couple of days ago I removed two hosts (they both contained around 2k of items in total).
Since, I noticed that MySQL using 10-15 % of CPU (its way over it used to).
Because of MySQL high CPU load, I guess, my frontend performance deteriorated. Now it takes around 5-8 seconds to load dashboard.php

Any suggestions what can be done in order to make MySQL work as normal as it used to?


Thank you for response in advance!

[Davidus]

for better understanding adding screenshots

[heaje]

Wow, that's an incredibly long time to keep data.

If you have the housekeeper enabled, it may account for why MySQL started using up more CPU when you deleted those hosts. You have history,trends,etc. for around 2000 items that now have to be deleted.

[Davidus]

Wow, that's an incredibly long time to keep data.

If you have the housekeeper enabled, it may account for why MySQL started using up more CPU when you deleted those hosts. You have history,trends,etc. for around 2000 items that now have to be deleted.

Thank you for response!

[Davidus]

Can I somehow check if zabbix done deleting those 2000 items?
Even though it’s already been 1 week since I pressed the delete button MySQL still uses lot of CPU

[Davidus]

In addition,
I see no active process in mysql


while

[BDiE8VNy]

If you delete large parts of a table it might be necessary to run OPTIMIZE TABLE. See MySQL documentation for details.

[heaje]

Just out of curiosity, have you tuned mysql at all? In the screenshot, you have an awful lot of ram not being used at all. Using the default mysql configuration parameters could easily cause issues like you're seeing.

[Davidus]

Just out of curiosity, have you tuned mysql at all? In the screenshot, you have an awful lot of ram not being used at all. Using the default mysql configuration parameters could easily cause issues like you're seeing.

Previously, I had no need of doing database tuning, because MySQL was working just fine; but definitely thinking of doing it now.

[Davidus]

If you delete large parts of a table it might be necessary to run OPTIMIZE TABLE. See MySQL documentation for details.

Would you recommend executing mysqlcheck --optimize --all-databases' ?

[Davidus]

Just out of curiosity, have you tuned mysql at all? In the screenshot, you have an awful lot of ram not being used at all. Using the default mysql configuration parameters could easily cause issues like you're seeing.

By the way, can you recommend any particular article on BD tuning? I got totally confused reading all this different methods and recommendations in google

Thank you in advance!

[BDiE8VNy]

Would you recommend executing mysqlcheck --optimize --all-databases' ?

Consult the MySQL documentation if this command would lock excessively what may lead to blocking queries or updates.

A good starting point to tune MySQL for beginners is the MySQL Performance Tuning Primer Script

[Davidus]

Consult the MySQL documentation if this command would lock excessively what may lead to blocking queries or updates.

A good starting point to tune MySQL for beginners is the MySQL Performance Tuning Primer Script

Thanks a lot! I’ll try it out

[Davidus]

Eventually, problem was solved without human intervention
CPU is back to normal, frontend is working fine.
Thank you guys for your support!

[heaje]

Just a suggestion, but you should really look at tuning your MySQL configuration even though your problem is gone. The defaults are anything but optimized for anything more than a very small Zabbix installation.