4

Using Amazon RDS, we are running ETL scripts to migrate our data. However, every few hours there is a huge CPU spike.

Here are the ETL specs (per ETL):

50 records inserted / second
pool of 1000 connections

Here are the server's specs:

Amazon R3.8XL
244 GB RAM
32 vCPU
3TB SSD
10 GiB Network Performance
No Multi-AZ (yet)

Here are the main & modified PG parameter group settings:

checkpoint_completion_target = 0.9
checkpoint_segments          = 16
effective_cache_size         = {DBInstanceClassMemory/10923} (8kb)
maintenance_work_mem         = {DBInstanceClassMemory/16384} (kb)
max_connections              = {DBInstanceClassMemory/12582880}
max_locks_per_transaction    = 64
shared_buffers               = {DBInstanceClassMemory/32768} (8kb)
work_mem                     = {DBInstanceClassMemory/20480000} (kb)

In this case, DBInstanceClassMemory is approximately 244,000,000,000 bytes. The (8kb) means the value is taken as blocks of 8kb, so shared_buffers = 244000000000/32768*8000 = 60 gb. All of the changes made were based on this article, and I set the effective_cache_size to 75% because (as you will see below) the memory doesn't seem to be getting fully utilized.

Here is a screenshot of our database server's stats over a 6 hour time span: enter image description here

The graph on the top left shows the CPU spikes, and you can see the correlated drop in Write IOPS (the graph below it).

What can be the reason for these CPU spikes? They almost completely freeze queries by the ETL (taking upwards of 3 minutes for queries that would usually take less than a second).

5
  • Hmm. You're getting spikes every hour on the hour. You may have bad neighbors. If you have bad neighbors in Amazon there isn't much you can do other the try to move to a new host or hope they go away. To move to a new host you can usually just reboot your instance.
    – David Kerr
    Feb 5 '15 at 21:08
  • I've rebooted multiple times. They don't seem to be too random. They usually climb exponentially and sit at 100% for an hour or so.
    – Garrett
    Feb 6 '15 at 8:06
  • 60 MB shared_buffers? I have never seen a Postgres instance with this low value...
    – dezso
    Apr 21 '15 at 8:34
  • I'm calculating 60 GB - is my math off? 244000000000 / 32768 * 8000 = 59,570,312,500
    – Garrett
    Apr 23 '15 at 16:57
  • Shouldn't work_mem be set to {DBInstanceClassMemory/2048000} instead of your {DBInstanceClassMemory/20480000}?
    – Gerard
    Mar 10 '16 at 9:12
4

It was a combination of making too many transactions and too many functions. After combining statements into the same transactions and removing the functions, the CPU hasn't risen past an average of 5%.

0

i think you are facing spinlock contention. you can verify this using "perf" (in case s_lock shows up on top my guess is right).

in general try the following:

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag

we also got the impression here at cybertec that more recent versions of the linux kernel (later in the 3.x series) are less likely to show those symptoms.

1
  • 1
    I don't think RDS allows you to access the OS, only the database. This happens even when the data being imported is totally unrelated, or simply only one table is being imported.
    – Garrett
    Feb 5 '15 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.