Question

High load average on managed MySQL database

Posted November 24, 2020 486 views
MonitoringDigitalOcean Managed MySQL Database

My server instance is a 2GB, 1 vCPU.

I’m attempting to diagnose an issue with very high load average on my managed MySQL database. Every hour, my app dispatches a collection of jobs that perform an “upsert” of anywhere from 100s to 1000s of records.

These records are fetched from an external API in batches of 100, processed and then added to the database. Each “page” from the API, is generally processed in ~10 seconds. So for each job, one DB query every 10 seconds or so.

This process is identical every hour.

During the first 1-3 minutes, the load average spikes to as much as 10, then as the import process continues, the load average calms down to normal levels.

What is strange:

  1. Memory usage remains steady.
  2. CPU usage spikes consistently, but to very different levels.
  3. Load average often, but not always spikes.

See the monitoring screenshot for more detail: https://ibb.co/S5Qr50C

Notice how at 1pm, the load is high, but at 2 and 3pm, it is much smaller, despite the fact that the import process is identical. Meanwhile, CPU load is lower at 1pm, but higher at 2pm.

Also note that under connection status, the threads in use is well below the limit.

Since the DB is managed, I’m not really able to do much other than point out what I’m seeing.

Any ideas? Thanks so much!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
1 answer

One thing to do is check for slow query logs to see if there are any particular queries that are slow. While the time of day may be an issue depending on droplet type, if you are seeing an initial spike, which then goes down, despite the fact that you are doing the same work, the first thing I would look into slow queries and perhaps updating your indexes. That is usually the best place to start fixing any performance issues.