Magento – the best database backend

For this amount of data in one collection you aren't really ever going to get this to be as fast as you probably want it to be - no matter what spec hardware you are running on. Not only are you pulling a vast amount of data from the database in one call, you are also then processing that data with PHP to display it in the grid which is always going to be slow.

With the grid taking 5-15 minutes to load surely sticking with default behaviour makes the most sense?

You are comparing dedicated hardware running on bare metal (presumably locally) to shared, highly contended hardware virtualised behind a hypervisor.

CPU dictates load time, of which your AWS lottery has served you something 30% slower than your dev. box. Its no surprise a cloud service would be slower, its widely regarded for its poor performance, its strength is merely size - you can spin up 600 slow servers at a whim. If you want good performance, drop "the cloud".

But it you are determined to stay with a cloud service, then start with the basics.

1. Begin with connectivity. Before trying to ascertain any application layer bottlenecks, understand your test conditions first.

   You should measure TCP latency between your development server and test client and live server and test client. I'd suggest tcpping for a L4 test.

   Or simpler still, just test from the respective machine. It rules out network and allows you to narrow the next bottleneck.

2. Move on to CPU and I/O. Your delays are going to be caused by either CPU contention or I/O contention. As its a VPS, this is mostly emulated, which will add considerable overhead, and because its shared, it is also contended.

   Test against a simple local static resource over HTTP, then repeat the same with a resource locally mounted in RAM. Then test the tmpfs and physical partition with something like dd. This will allow you to compare disk perfromance/throughput/seek with that of RAM - and you can measure if either have any outliers.

   Note that there's a very strong likelihood that your test results are going to differ each time you test, because its shared. You can't account for what other users on the same hypervisor might be doing, what context switching might be occuring or what peculiarities may exist in the VM emulation itself.

Testing needs to be performed across the stack to find your bottleneck. Don't even consider testing the application layer (ie. Magento) until you've got consistent even results for CPU, RAM disk and network I/O.