Spatial Index rebuilds

[mikes]

Hi,

Is there any way, other than doing a DROP INDEX followed by an ALER TABLE x ADD SPATIAL INDEX of carrying out SPATIAL INDEX maintanence ? I had hoped that using pt-online-schema-change might do the trick, but it seems not.

Also, is there any good method of showing the state of a spatial index. PMM revealed the underlying issue with a particular query, but is there any general test to show the state of an index.

The query in question’s performance improved by a couple of orders of magnitude, and needs to be done soon on our production setup.

Version | 5.7.22-22
Platform | Linux
Release | CentOS Linux release 7.7.1908 (Core)
Kernel | 3.10.0-1062.9.1.el7.x86_64

Thanks

[Michael_Coburn]

Hi @mikes - welcome back to the Percona Forums!

What specific maintenance are you looking to achieve?

Generally you can use the following statement to gather information about your indexes on a per table basis - which specific data are you looking for?

SHOW INDEXES FROM <TABLENAME>;

PMM doesn’t have information regarding index usage but this sounds like a good feature request. If you’re using Percona Server you’ll find the userstat variable, when enabled, reveals index usage information User Statistics

Can you show us the exact failure exhibited by pt-online-schema-change ?

[mikes]

Thanks Michael

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.

All that

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

1. Can you do an offline index rebuild - expect that the answer to that is no - use a secondary and promote it perhaps

2. 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.

Kind regards,

Mike

[Michael_Coburn]

Hi @mikes , thanks for your reply - In general you don’t need to schedule maintenance on indexes in general, nor on spatial indexes specifically. The best way to tell if your query is optimized is to use the EXPLAIN function, which will show you what the MySQL query optimizer would do when it executes the query. This ought to answer your question regarding

understand why certain queries were taking as long as they did

Regarding your questions:

1. An “offline” build could mean you pull the server from taking production queries, and run a no-op ALTER TABLE ... ENGINE=InnoDB. This has the benefit of rebuilding not just the indexes but the data rows as well, and will reduce the size on disk as the data will be sorted in Primary Key order. You can also do this while the instance is serving queries, however ALTER TABLE will block writes until it completes.
2. Generally examining the query performance over time is the best way to tell when you need to perform maintenance such as index rebuilds. You can do this via PMM Query Analytics and plot over the last 30 days - how has the avg performance changed? This is more pro-active than waiting until the query bubbles to the top of Query Analytics.

If you could share the full output of the SHOW INDEXES and SHOW CREATE TABLE, along with some of the queries you’re running we can potentially investigate this further.

[mikes]

Hi Michael,

That is the strange thing here, the ALTER TABLE …ENGINE=InnoDB was the first option I tried, it reduced the table in size, but didn’t increase the performance. That only changed when the index was dropped and rebuilt.

I’ll send you more details later on.

Thanks,

Mike