figuring out high load cause from top and iotop
Solution 1:
Your particular problem is most certainly I/O-related due to how deep into your swap space mySQL has pushed you. Ideally you should be using zero swap the vast majority of the time since the instant the OS starts swapping memory to disk your performance will nosedive as processes wait for I/O. Think of it like Performance = 1 / N ^ swap where as the OS uses more swap space the performance generally gets exponentially worse. You appear to be 3 gigabytes deep into your swap space, which is just plain horrifying to me.
If, after you've adjusted your mySQL settings as you've mentioned, you're still averaging more than a few megabytes in swap you should consider: [best to worst idea, 2&3 are roughly tied]
- Optimizing your database schema and queries.
- Adding more RAM to the machine.
- Splitting the mySQL server onto another dedicated machine.
- Migrating your swap space to a high-speed device like an SSD. [this kills the SSD]
Solution 2:
What should i do if I want to prevent this high load.
FAster server, better programming?
seriously, you look at it from the wrong side. LIke "I am a taxi driver, I drive too many km per day, how do i cut that down". Your request side is fixed (not taking into consideration thigns like ddos attacks) and items must be served.
So it runs down into - get a beefier server, assign more ram for caching (beefier server) or hit some programmers with the "fix your bugs" stick to make them write better code.
In this case it could be a mySql issue, but then this is something that would go back to the developers of the site as "use indices, dudes" or "wrie better queries". Not that I have not seen that (sql server, portal, ZERO indices, server "died" with IO eating everything). But that is something the developers of the site(s) have to patch.