872074 - Interesting performance difference between AWS instances

Status: RESOLVED FIXED

(Cloud Services :: Operations: Miscellaneous, task)

Description

[Gene Wood [:gene]]

Today while re-establishing slave replication for all 6 production persona DBs in AWS I found an interesting difference between them.

When loading in the DB export (after having stopped the slave) on each of the 6 DBs, 4 of them loaded the 200MB export in a few minutes. 2 of them have been working on loading the exports for about 45 minutes now.

The two odd instances are in us-west-2 :
* i-04667b36
* i-78607d4a

The one normal instance at us-west-2 is i-e24d50d0

All 3 instances in us-east-1 loaded the export quickly

I've compared them and see few differences :
* The instances are all EBS Optimized enabled
* The database volumes are all configured for io1 (500) aka 500 IOPS committed

The us-west-2 instances can be reached by :

ssh -A root@50.112.185.194
ssh ip_goes_here

The ips of the instances are :
10.148.35.29 # one of the slow instances
10.148.37.14 # one of the slow instances
10.148.33.176 # one of the quick instances

Can someone look into this during the week to see what's causing this?

Comment 1

[Gene Wood [:gene]]

Looked into this with Sheeri a bit and found that each insert was taking about 100 seconds. there are about 1 million records so I aborted (cause I don't have 3.1 years to wait for it to finish). One more piece of data, I've done an import just like this about a month ago on all these machines and they completed just fine

Comment 2

[Sheeri Cabral [:sheeri]]

We compared MySQL configurations and they were the same other than hostname-specific stuff and the server ids. I hadn't realized these all went fast before; that kind of makes me want to think that these are shared servers and 2 of the 3 are heavily loaded, but only Amazon can confirm that really :(

Comment 3

[Brandon Johnson [:cyborgshadow]]

My ssh key is denied on the jumphost (see below) Additionally, this sounds like it's something at the host level, not the MySQL level. What comes to mind immediately is provisioned IOPS. Who has AWS login capability to view the details for these servers?

bash-3.2$ ssh -A root@50.112.185.194
Permission denied (publickey,gssapi-keyex,gssapi-with-mic).

Comment 4

[Brandon Johnson [:cyborgshadow]]

As a side note: Amazon has no vision of the systems other than "up or down". With local storage, you're given physical drive space as a partition from a shared drive. With EBS, you're given shared space until you purchase Provisioned IOPS (the only way you can guarantee your IOPS with AWS).

Comment 5

[Gene Wood [:gene]]

Ok, so to find the easiest path I built 2 new systems and they work fine. Here's the graph of the relative write operations with the 2 new ones and the 2 slow ones

http://i.imgur.com/638dxfR.png

No idea what caused it, hope it doesn't return.