Random spikes could happen for any number of reasons. To narrow it down you can prob do something as simple as look at your Google Analytics to match the traffic to the spike, or install a monitoring agent to really look at how your resource usage is doing by running some benchmarks while you measure.
But if your caching is turned off, then indeed MySQL will start to take a huge hit since more page loads mean more queries being made to the database directly – more so if you have many sites.
While I’m tempted to go into separating your DB server for any sort of production use, given your current setup your performance would improve dramatically if you use Redis caching.
You mentioned not being able to update… I’m assuming this is adding Redis to the site? The
ee site update command is currently being developed for full EE4 inclusion which means ability to edit the
--cache option for existing sites isn’t available just yet .
In the meantime you can always create a new server, install EE4, recreate the sites with Redis enabled, and migrate the WordPress data and run some tests to compare.
I know it sounds like a lot of work but it actually isn’t since you’d be running the new test servers (with Redis) using direct DNS hostname mapping from your local machine, so as to not affect the production sites that have correct DNS publicly.
Benefit of this approach is that when you upload your WXR file with your pages they’ll actually be able to “Download and import file attachments” correctly to an empty media library. Also, no database search/replace required since the URL would be the same.