The process was basically me trying to understand why certain queries were taking as long as they did. PMM does a great job of showing exactly which queries they are, but you then need to do a bit of detective work.
I was using our test DB servers to try and understand the logic being used in the spatial query that was reported as slow. It was using a bounding box, based on geographic points stored in spatial indexes.
SHOW INDEXES FROM ;
Does is show me that I have 3 spatial indexes defined ( I did want to check that was the case) and that we have a sub_part setting of 32.
It was only by chance that running the same command against the test instance, which is a small t2.medium in AWS, did I eventually see the major performance difference.
I think the logic was that as both servers were showing a similar bad performance, then I may as well try an index rebuild, which brought an instant 100x speed up.
This is quite a small table, less than 3 GB, and took less than 2 mins to rebuild the index. That is likely to be much quicker on the main production server with SSD’s, but I tried one other thing.
The reasoning being that I hoped to be able to use pt-online-schema-change to copy the table and gain the performance increase that way.
I tried this on a second test instance, the command worked, and saved a little space, but no magic performance increase, which I think is to be expected. That only improved with an index rebuild also.
So my ultimate questions
Can you do an offline index rebuild - expect that the answer to that is no - use a secondary and promote it perhaps
How do I find out that an index needs a rebuild. What stats can I use to indicate that an index might need some maintenance. Waiting till it gets to the top of the slow queries list in PMM is perhaps not the best way to find out.