Can I suggest to make myisam the default storage engine for creating a new database scheme ?
I recently redeployed my emoncms from scratch and didn't bother to check the database scheme. I just imported my old feeds in the newly created feeds after some thinkering with the fields (went from a very old emoncms to the latest).
I have a rather busy mysql, so I didn't bother at first, but after a week of not settling down, I started searching and bumped into the myisam rpi blog post.
My server load was very high and my mysql did about 60-70 disk iops/s. After changing the storage engine to myisam, the disk iops/s dropped to 5-8 disk iops/s and the load of my server went from 50% (dual socket vm) to 5%.
Re: mysql myisam
Yes good idea, I will add that in.
ps: Its good to see that you replicated the same results, quite a marked difference
Re: mysql myisam
This can be a bit surprising.
I've got no knowledge about this, but I had a look, and AFAIU, myisam is the old engine. It's weird that going back makes things better, unless we're in a corner case where the new engine is inefficient.
Before changing the default, it would be nice to understand the reasons for this performance gap.
Re: mysql myisam
Hi Trystan and ziporah.
I also converted the database to myisam (with 32 feeds, and short 30 sec updates). The average load realy dropped down:
and my diskIO is also dropped down a 50%.
The Spikes are less high.. oh and I'm not running it on an Rpie, just an overkill server with a 1.6 dual celeron.
Tristan, I also uploaded a simple PHP script to alter all the emoncms tables. It may be usefully to integrate this at emoncms github?