Properly rotate index if using only RT indexes (ALTER RENAME) #527

usatenko · 2021-04-09T10:40:06Z

I am having trouble finding a proper way to rotate data in RT index.
For the sake of speed, I am indexing data to RT index directly (sending concurrent queries and bulk inserts).
But, when I need to reindex the data from the scratch I can't just prepare a new table to create as "LIKE" the current table, fill it with the data, and replace the old table with the new one filled with new data.

What is the correct way to do this?

sanikolaev · 2021-04-09T10:51:16Z

You are probably looking for smth like ALTER TABLE RENAME (in mysql and postgresql)? Unfortunately it's not supported yet. If you elaborate more on why it's important in your case we can consider this a feature request.

barryhunter · 2021-04-09T11:10:56Z

I don't remember the exact details now, But pretty sure have had success using 'ATTACH' to move data from one RT index to the other.

Each 'shard' an RT index is effectively a 'plain' index. So just move each shard separately. (or can use OPTIMIZE to make just one shard, probably just after using FLUSH RAMCHUNK!)

I think had to create a 'fake' index for the shard.

ie if the 'source' RT index has a path like

 index reindex {
    path = /var/data/rtindex

then can create a new 'fake' RT index for each shard

 index rtindex_shard0 {
    type = plain
    path = /var/data/rtindex.0

Then this new fake index, is that is using the data form the single shard. Then can attach this

 ATTACH INDEX rtindex_shard0 TO RTINDEX newindex;

(only working from memory here, so probably some more details. Including faffing around with restarting searchd, as it doesnt like converting the RT shard to a plain index while running)

Never tried it, but perhaps could just rename all the index files, to 'rename' an index. Would have to be done while searchd is shutdown, and not forgetting to update the config file!

usatenko · 2021-04-19T01:05:01Z

ALTER TABLE RENAME is exactly what I am looking for.
@sanikolaev well, a use case here is the same as the standard way of getting RT index: plain index indexation then attach to RT index, however, the limitation of this method is the speed of getting plain index ready which requires continuous processing of records from e.g. CSV file, where each record is processed line by line.
When I am indexing the data I am creating RT index and push my records directly to it in the concurrent way 10 at a time in batch inserts, so, the indexation speed is much faster.
But, it is a bit inconvenient as there is no way to rotate my old RT index with the prepared new one right after it becomes filled and ready.
Hope it may convince you to implement this ALTER TABLE RENAME feature.
Not sure, but from my point of view, it is not something extraordinary as RT indexes anyway are separated in the manticore storage.

usatenko · 2021-04-19T01:06:31Z

@barryhunter as I remember I tried this attach option, however without FLUSHing, will try, many thanks.

usatenko · 2021-06-03T06:52:25Z

OK, I have tested an approach with attaching index.
providing in my case I do not have any preconfigured settings in the manticore config.
mysql> CREATE TABLE a (a text);
mysql> CREATE TABLE b (a text);
mysql> FLUSH RAMCHUNK a;
mysql> FLUSH RAMCHUNK b;
mysql> ATTACH INDEX a TO RTINDEX b;
ERROR 1064 (42000): 1st argument to ATTACH must be a plain index
So, I have no options to do it with the ATTACH command.

usatenko · 2021-06-09T09:17:27Z

can ALTER RENAME be implemented?

sanikolaev · 2021-06-09T09:21:28Z

Yes, it can. But it's not in our nearest plans. If it's mission critical for you we provide professional services including custom feature requests development - https://manticoresearch.com/services

usatenko · 2021-07-12T17:33:27Z

ok, we will will wait when you consider to add it

…plete content of the another RT index; fixed Github #527; fixed model at test 187; added cases to test 464

tomatolog · 2024-01-11T16:37:28Z

fixed at af5812c. Now ATTACH supports the RT index along with the plain index as the source.
After

ATTACH TABLE rt1 TO TABLE rt2

rt1 table becomes empty at the daemon and all data (disk chunks) got moved into rt2 table

sanikolaev · 2024-01-18T10:35:24Z

Demo of how it works:

mysql> drop table if exists t; drop table if exists t2; create table t; insert into t values(1); flush ramchunk t; insert into t values(2); flush ramchunk t; insert into t values(3); select * from t.@files limit 100; create table t2 like t; insert into t2 values(11); flush ramchunk t; insert into t2 values(12); select * from t; select * from t2; ATTACH TABLE t TO TABLE t2; select * from t.@files limit 100;
select * from t2.@files limit 100;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.02 sec)

--------------
drop table if exists t2
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t values(1)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
flush ramchunk t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t values(2)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
flush ramchunk t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
insert into t values(3)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from t.@files limit 100
--------------

+------+---------------------------------+---------------------------------+------+
| id   | file                            | normalized                      | size |
+------+---------------------------------+---------------------------------+------+
|    2 | /var/lib/manticore/t/t.settings | /var/lib/manticore/t/t.settings |    3 |
|    3 | /var/lib/manticore/t/t.0.sph    | /var/lib/manticore/t/t.0.sph    |  419 |
|    4 | /var/lib/manticore/t/t.0.spd    | /var/lib/manticore/t/t.0.spd    |    1 |
|    5 | /var/lib/manticore/t/t.0.spp    | /var/lib/manticore/t/t.0.spp    |    1 |
|    6 | /var/lib/manticore/t/t.0.spe    | /var/lib/manticore/t/t.0.spe    |    1 |
|    7 | /var/lib/manticore/t/t.0.spi    | /var/lib/manticore/t/t.0.spi    |    1 |
|    8 | /var/lib/manticore/t/t.0.spm    | /var/lib/manticore/t/t.0.spm    |    4 |
|    9 | /var/lib/manticore/t/t.0.sphi   | /var/lib/manticore/t/t.0.sphi   |   66 |
|   10 | /var/lib/manticore/t/t.0.spt    | /var/lib/manticore/t/t.0.spt    |   36 |
|   11 | /var/lib/manticore/t/t.0.spidx  | /var/lib/manticore/t/t.0.spidx  |  145 |
|   12 | /var/lib/manticore/t/t.0.spa    | /var/lib/manticore/t/t.0.spa    |   40 |
|   13 | /var/lib/manticore/t/t.1.sph    | /var/lib/manticore/t/t.1.sph    |  419 |
|   14 | /var/lib/manticore/t/t.1.spd    | /var/lib/manticore/t/t.1.spd    |    1 |
|   15 | /var/lib/manticore/t/t.1.spp    | /var/lib/manticore/t/t.1.spp    |    1 |
|   16 | /var/lib/manticore/t/t.1.spe    | /var/lib/manticore/t/t.1.spe    |    1 |
|   17 | /var/lib/manticore/t/t.1.spi    | /var/lib/manticore/t/t.1.spi    |    1 |
|   18 | /var/lib/manticore/t/t.1.spm    | /var/lib/manticore/t/t.1.spm    |    4 |
|   19 | /var/lib/manticore/t/t.1.sphi   | /var/lib/manticore/t/t.1.sphi   |   66 |
|   20 | /var/lib/manticore/t/t.1.spt    | /var/lib/manticore/t/t.1.spt    |   36 |
|   21 | /var/lib/manticore/t/t.1.spidx  | /var/lib/manticore/t/t.1.spidx  |  145 |
|   22 | /var/lib/manticore/t/t.1.spa    | /var/lib/manticore/t/t.1.spa    |   40 |
|    1 | /var/lib/manticore/t/t.meta     | /var/lib/manticore/t/t.meta     |  487 |
+------+---------------------------------+---------------------------------+------+
22 rows in set (0.00 sec)
--- 22 out of 22 results in 0ms ---

--------------
create table t2 like t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t2 values(11)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
flush ramchunk t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t2 values(12)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from t
--------------

+------+
| id   |
+------+
|    2 |
|    1 |
|    3 |
+------+
3 rows in set (0.00 sec)
--- 3 out of 3 results in 0ms ---

--------------
select * from t2
--------------

+------+
| id   |
+------+
|   12 |
|   11 |
+------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---

--------------
ATTACH TABLE t TO TABLE t2
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
select * from t.@files limit 100
--------------

+------+---------------------------------+---------------------------------+------+
| id   | file                            | normalized                      | size |
+------+---------------------------------+---------------------------------+------+
|    2 | /var/lib/manticore/t/t.settings | /var/lib/manticore/t/t.settings |    3 |
|    1 | /var/lib/manticore/t/t.meta     | /var/lib/manticore/t/t.meta     |  489 |
+------+---------------------------------+---------------------------------+------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---

--------------
select * from t2.@files limit 100
--------------

+------+-----------------------------------+-----------------------------------+------+
| id   | file                              | normalized                        | size |
+------+-----------------------------------+-----------------------------------+------+
|    2 | /var/lib/manticore/t2/t2.ram      | /var/lib/manticore/t2/t2.ram      |   12 |
|    3 | /var/lib/manticore/t2/t2.settings | /var/lib/manticore/t2/t2.settings |    3 |
|    4 | /var/lib/manticore/t2/t2.0.sph    | /var/lib/manticore/t2/t2.0.sph    |  419 |
|    5 | /var/lib/manticore/t2/t2.0.spd    | /var/lib/manticore/t2/t2.0.spd    |    1 |
|    6 | /var/lib/manticore/t2/t2.0.spp    | /var/lib/manticore/t2/t2.0.spp    |    1 |
|    7 | /var/lib/manticore/t2/t2.0.spe    | /var/lib/manticore/t2/t2.0.spe    |    1 |
|    8 | /var/lib/manticore/t2/t2.0.spi    | /var/lib/manticore/t2/t2.0.spi    |    1 |
|    9 | /var/lib/manticore/t2/t2.0.spm    | /var/lib/manticore/t2/t2.0.spm    |    4 |
|   10 | /var/lib/manticore/t2/t2.0.sphi   | /var/lib/manticore/t2/t2.0.sphi   |   82 |
|   11 | /var/lib/manticore/t2/t2.0.spt    | /var/lib/manticore/t2/t2.0.spt    |   41 |
|   12 | /var/lib/manticore/t2/t2.0.spidx  | /var/lib/manticore/t2/t2.0.spidx  |  149 |
|   13 | /var/lib/manticore/t2/t2.0.spa    | /var/lib/manticore/t2/t2.0.spa    |   48 |
|   14 | /var/lib/manticore/t2/t2.1.sph    | /var/lib/manticore/t2/t2.1.sph    |  419 |
|   15 | /var/lib/manticore/t2/t2.1.spd    | /var/lib/manticore/t2/t2.1.spd    |    1 |
|   16 | /var/lib/manticore/t2/t2.1.spp    | /var/lib/manticore/t2/t2.1.spp    |    1 |
|   17 | /var/lib/manticore/t2/t2.1.spe    | /var/lib/manticore/t2/t2.1.spe    |    1 |
|   18 | /var/lib/manticore/t2/t2.1.spi    | /var/lib/manticore/t2/t2.1.spi    |    1 |
|   19 | /var/lib/manticore/t2/t2.1.spm    | /var/lib/manticore/t2/t2.1.spm    |    4 |
|   20 | /var/lib/manticore/t2/t2.1.sphi   | /var/lib/manticore/t2/t2.1.sphi   |   66 |
|   21 | /var/lib/manticore/t2/t2.1.spt    | /var/lib/manticore/t2/t2.1.spt    |   36 |
|   22 | /var/lib/manticore/t2/t2.1.spidx  | /var/lib/manticore/t2/t2.1.spidx  |  145 |
|   23 | /var/lib/manticore/t2/t2.1.spa    | /var/lib/manticore/t2/t2.1.spa    |   40 |
|   24 | /var/lib/manticore/t2/t2.2.sph    | /var/lib/manticore/t2/t2.2.sph    |  419 |
|   25 | /var/lib/manticore/t2/t2.2.spd    | /var/lib/manticore/t2/t2.2.spd    |    1 |
|   26 | /var/lib/manticore/t2/t2.2.spp    | /var/lib/manticore/t2/t2.2.spp    |    1 |
|   27 | /var/lib/manticore/t2/t2.2.spe    | /var/lib/manticore/t2/t2.2.spe    |    1 |
|   28 | /var/lib/manticore/t2/t2.2.spi    | /var/lib/manticore/t2/t2.2.spi    |    1 |
|   29 | /var/lib/manticore/t2/t2.2.spm    | /var/lib/manticore/t2/t2.2.spm    |    4 |
|   30 | /var/lib/manticore/t2/t2.2.sphi   | /var/lib/manticore/t2/t2.2.sphi   |   66 |
|   31 | /var/lib/manticore/t2/t2.2.spt    | /var/lib/manticore/t2/t2.2.spt    |   36 |
|   32 | /var/lib/manticore/t2/t2.2.spidx  | /var/lib/manticore/t2/t2.2.spidx  |  145 |
|   33 | /var/lib/manticore/t2/t2.2.spa    | /var/lib/manticore/t2/t2.2.spa    |   40 |
|   34 | /var/lib/manticore/t2/t2.3.sph    | /var/lib/manticore/t2/t2.3.sph    |  419 |
|   35 | /var/lib/manticore/t2/t2.3.spd    | /var/lib/manticore/t2/t2.3.spd    |    1 |
|   36 | /var/lib/manticore/t2/t2.3.spp    | /var/lib/manticore/t2/t2.3.spp    |    1 |
|   37 | /var/lib/manticore/t2/t2.3.spe    | /var/lib/manticore/t2/t2.3.spe    |    1 |
|   38 | /var/lib/manticore/t2/t2.3.spi    | /var/lib/manticore/t2/t2.3.spi    |    1 |
|   39 | /var/lib/manticore/t2/t2.3.spm    | /var/lib/manticore/t2/t2.3.spm    |    4 |
|   40 | /var/lib/manticore/t2/t2.3.sphi   | /var/lib/manticore/t2/t2.3.sphi   |   66 |
|   41 | /var/lib/manticore/t2/t2.3.spt    | /var/lib/manticore/t2/t2.3.spt    |   36 |
|   42 | /var/lib/manticore/t2/t2.3.spidx  | /var/lib/manticore/t2/t2.3.spidx  |  145 |
|   43 | /var/lib/manticore/t2/t2.3.spa    | /var/lib/manticore/t2/t2.3.spa    |   40 |
|    1 | /var/lib/manticore/t2/t2.meta     | /var/lib/manticore/t2/t2.meta     |  491 |
+------+-----------------------------------+-----------------------------------+------+
43 rows in set (0.00 sec)
--- 43 out of 43 results in 0ms ---

--------------
select * from t2
--------------

+------+
| id   |
+------+
|    3 |
|    1 |
|   11 |
|    2 |
|   12 |
+------+
5 rows in set (0.00 sec)
--- 5 out of 5 results in 0ms ---

--------------
select * from t
--------------

Empty set (0.00 sec)
--- 0 out of 0 results in 0ms ---

As we can see, all data from table t was moved to table t2 and was merged with the existing data in t2 (in disk and ram chunks).

sanikolaev · 2024-01-18T10:37:09Z

I'm reopening this issue to:

implement ALTER TABLE ... RENAME based on the ATTACH command via Buddy.
update the docs.

tomatolog · 2024-01-18T10:45:13Z

I sure there is no need to issue flush ramchunk at any stage as it has done internally for source and destination tables, ie ramchunks will be empty for both tables after operation finished

djklim87 · 2024-05-02T12:29:28Z

Blocked by manticoresoftware/manticoresearch-buddy#273

djklim87 · 2024-05-09T11:47:21Z

waiting for review manticoresoftware/manticoresearch-buddy#260

donhardman · 2024-05-10T10:10:56Z

Looks good! Once you've finalized everything, we can move forward with merging.

djklim87 · 2024-05-10T11:21:50Z

Done in manticoresoftware/manticoresearch-buddy@0d69023

sanikolaev · 2024-05-10T15:56:58Z

@djklim87 pls create a PR with the doc update.

djklim87 · 2024-05-13T15:00:18Z

docs. Waiting for review Docs: Renaming tables #manticoresoftware/manticoresearch/527 #2169
refuse to rename distr and pq indexes. Waiting for review Feat/rename table manticoresearch-buddy#286

sanikolaev · 2024-05-16T04:37:02Z

docs. Waiting for review

Reviewed. Approved.

sanikolaev · 2024-05-16T04:38:17Z

@PavelShilin89 pls proceed with:

manual testing
adding CLT tests

sanikolaev · 2024-05-17T10:33:19Z

manual testing

As discussed, please test it more thoroughly, especially for edge cases.

sanikolaev · 2024-05-21T02:35:53Z

please test it more thoroughly, especially for edge cases.

I've tested it manually. Looks good to me.

PavelShilin89 · 2024-05-22T13:38:29Z

Done in #2189

sanikolaev added the waiting Waiting for the original poster (in most cases) or something else label Apr 12, 2021

githubmanticore removed the waiting Waiting for the original poster (in most cases) or something else label Apr 22, 2021

githubmanticore changed the title ~~Properly rotate index if using only RT indexes~~ Properly rotate index if using only RT indexes (ALTER RENAME) Apr 22, 2021

sanikolaev mentioned this issue Apr 4, 2022

Manticore index name rename #740

Closed

sanikolaev mentioned this issue Jul 28, 2023

Will manticoresearch support the rename (COLUMN) operation? #1288

Open

sanikolaev mentioned this issue Aug 16, 2023

Renaming RT index #1342

Closed

sanikolaev assigned tomatolog Nov 15, 2023

sanikolaev added est::TO_ESTIMATE est::size_M and removed est::TO_ESTIMATE labels Nov 15, 2023

tomatolog added a commit that referenced this issue Jan 11, 2024

added RT index as ATTACH index argument, now RT index could steal com…

af5812c

…plete content of the another RT index; fixed Github #527; fixed model at test 187; added cases to test 464

tomatolog closed this as completed Jan 11, 2024

sanikolaev added the rel::upcoming Upcoming release label Jan 12, 2024

sanikolaev reopened this Jan 18, 2024

sanikolaev assigned djklim87 and unassigned tomatolog Jan 18, 2024

sanikolaev added buddy_candidate est::size_S labels Jan 23, 2024

donhardman removed their assignment Apr 26, 2024

djklim87 assigned donhardman and unassigned djklim87 May 9, 2024

donhardman assigned djklim87 and unassigned donhardman May 10, 2024

djklim87 closed this as completed May 10, 2024

sanikolaev reopened this May 10, 2024

djklim87 mentioned this issue May 13, 2024

Docs: Renaming tables #manticoresoftware/manticoresearch/527 #2169

Merged

djklim87 assigned sanikolaev and donhardman and unassigned djklim87 May 13, 2024

donhardman removed their assignment May 14, 2024

sanikolaev assigned PavelShilin89 and unassigned sanikolaev May 16, 2024

PavelShilin89 linked a pull request May 16, 2024 that will close this issue

ALTER TABLE ....... RENAME - renaming RT tables #2189

Merged

PavelShilin89 closed this as completed in #2189 May 22, 2024

PavelShilin89 mentioned this issue May 23, 2024

Test/alter rename #2225

Merged

sanikolaev added rel::6.3.0 Released in 6.3.0 and removed rel::next labels May 29, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Properly rotate index if using only RT indexes (ALTER RENAME) #527

Properly rotate index if using only RT indexes (ALTER RENAME) #527

usatenko commented Apr 9, 2021 •

edited by githubmanticore

sanikolaev commented Apr 9, 2021

barryhunter commented Apr 9, 2021

usatenko commented Apr 19, 2021

usatenko commented Apr 19, 2021

usatenko commented Jun 3, 2021 •

edited

usatenko commented Jun 9, 2021

sanikolaev commented Jun 9, 2021

usatenko commented Jul 12, 2021

tomatolog commented Jan 11, 2024

sanikolaev commented Jan 18, 2024

sanikolaev commented Jan 18, 2024

tomatolog commented Jan 18, 2024

djklim87 commented May 2, 2024

djklim87 commented May 9, 2024

donhardman commented May 10, 2024

djklim87 commented May 10, 2024

sanikolaev commented May 10, 2024

djklim87 commented May 13, 2024 •

edited by sanikolaev

sanikolaev commented May 16, 2024

sanikolaev commented May 16, 2024

sanikolaev commented May 17, 2024

sanikolaev commented May 21, 2024

PavelShilin89 commented May 22, 2024

Properly rotate index if using only RT indexes (ALTER RENAME) #527

Properly rotate index if using only RT indexes (ALTER RENAME) #527

Comments

usatenko commented Apr 9, 2021 • edited by githubmanticore

sanikolaev commented Apr 9, 2021

barryhunter commented Apr 9, 2021

usatenko commented Apr 19, 2021

usatenko commented Apr 19, 2021

usatenko commented Jun 3, 2021 • edited

usatenko commented Jun 9, 2021

sanikolaev commented Jun 9, 2021

usatenko commented Jul 12, 2021

tomatolog commented Jan 11, 2024

sanikolaev commented Jan 18, 2024

sanikolaev commented Jan 18, 2024

tomatolog commented Jan 18, 2024

djklim87 commented May 2, 2024

djklim87 commented May 9, 2024

donhardman commented May 10, 2024

djklim87 commented May 10, 2024

sanikolaev commented May 10, 2024

djklim87 commented May 13, 2024 • edited by sanikolaev

sanikolaev commented May 16, 2024

sanikolaev commented May 16, 2024

sanikolaev commented May 17, 2024

sanikolaev commented May 21, 2024

PavelShilin89 commented May 22, 2024

usatenko commented Apr 9, 2021 •

edited by githubmanticore

usatenko commented Jun 3, 2021 •

edited

djklim87 commented May 13, 2024 •

edited by sanikolaev