Large tables and unique keys
The other day I had a task to add a new field to the table. It would seem, ALTER TABLE ... Not here it was.
I launched this query in the evening, but in the morning it was still executed (16 hours was not enough :)
I immediately suggested that the problem is most likely in the construction of a unique index and the huge size of the source table. As you know, with ALTER TABLE, the server creates a temporary file into which it copies the necessary data. SHOW PROCESSLIST confirmed this.
')
I will give a more detailed formulation of the problem.
It is given. There is a data table.
Type - MyISAM.
The table has a VARCHAR field 140 characters long and a unique key for it.
The table has about 150 million rows.
The amount of disk space is 15 GB.
You must add a new field to the table.
The search showed that the problem is known and that the usual way can last forever. The size of the index does not allow using the in-memory cache, so creating a new table line by line is very slow.
Petr Zaitsev’s blog suggested a non-standard “hacker” solution.
1. Create a new table (for example, test_new) with the desired structure, but without any indexes, even without a primary key; if there is an auto-increment field, then remove auto_increment from its definition.
2. Fill this table with data using INSERT INTO test_new ... SELECT .
3. Create another table with the same structure as test_new, but with all the necessary keys ; let's call it test_struct.
4. Go to the directory on the disk where our tables are stored and copy test_struct.frm over test_new.frm and test_struct.MYI over test_new.MYI.
5. Open the mysql client and execute FLUSH TABLES and REPAIR TABLE test_new queries.
In my case, REPAIR TABLE took 20 minutes.
Possible problem. I also encountered a shortage of space in the MySQL temporary directory. In my case it was / tmp with a capacity of 5 GB. The fact is that REPAIR TABLE creates a temporary file for the new index, which eventually weighed 7 GB, which, of course, did not fit into 5 GB. If there is not enough disk space, MySQL waits 1 minute, and then tries to find it again and so many times. We, of course, remain in ignorance, thinking that he is busy :)
You can set the MySQL temporary directory with the tmpdir parameter.
Where can I use this hack? When adding, deleting or changing a field in a very large table, where there is a unique index, is also very large (most likely on a string field). When creating a unique index in a similar table. When recreating such a table from a dump.
Addition. What's the secret? Normal creation of a unique index goes line by line, record by record. REPAIR TABLE creates an index at once, using sorting, which is significantly faster.
Good luck!
I launched this query in the evening, but in the morning it was still executed (16 hours was not enough :)
I immediately suggested that the problem is most likely in the construction of a unique index and the huge size of the source table. As you know, with ALTER TABLE, the server creates a temporary file into which it copies the necessary data. SHOW PROCESSLIST confirmed this.
')
I will give a more detailed formulation of the problem.
It is given. There is a data table.
Type - MyISAM.
The table has a VARCHAR field 140 characters long and a unique key for it.
The table has about 150 million rows.
The amount of disk space is 15 GB.
You must add a new field to the table.
The search showed that the problem is known and that the usual way can last forever. The size of the index does not allow using the in-memory cache, so creating a new table line by line is very slow.
Petr Zaitsev’s blog suggested a non-standard “hacker” solution.
1. Create a new table (for example, test_new) with the desired structure, but without any indexes, even without a primary key; if there is an auto-increment field, then remove auto_increment from its definition.
2. Fill this table with data using INSERT INTO test_new ... SELECT .
3. Create another table with the same structure as test_new, but with all the necessary keys ; let's call it test_struct.
4. Go to the directory on the disk where our tables are stored and copy test_struct.frm over test_new.frm and test_struct.MYI over test_new.MYI.
5. Open the mysql client and execute FLUSH TABLES and REPAIR TABLE test_new queries.
In my case, REPAIR TABLE took 20 minutes.
Possible problem. I also encountered a shortage of space in the MySQL temporary directory. In my case it was / tmp with a capacity of 5 GB. The fact is that REPAIR TABLE creates a temporary file for the new index, which eventually weighed 7 GB, which, of course, did not fit into 5 GB. If there is not enough disk space, MySQL waits 1 minute, and then tries to find it again and so many times. We, of course, remain in ignorance, thinking that he is busy :)
You can set the MySQL temporary directory with the tmpdir parameter.
Where can I use this hack? When adding, deleting or changing a field in a very large table, where there is a unique index, is also very large (most likely on a string field). When creating a unique index in a similar table. When recreating such a table from a dump.
Addition. What's the secret? Normal creation of a unique index goes line by line, record by record. REPAIR TABLE creates an index at once, using sorting, which is significantly faster.
Good luck!
Source: https://habr.com/ru/post/76149/
All Articles