Блог

Мне нужно выполнить запрос в DATABASE2 (60G), который извлекает данные из DATABASE1 (80G). Некоторые таблицы содержат 400 миллионов строк в обеих базах данных.

 INSERT IGNORE INTO product_to_category (
SELECT DISTINCT p.product_id, pds.nodeid
 FROM product p 
JOIN DATABASE2.article_links al ON al.supplierid=p.manufacturer_id
 AND al.datasupplierarticlenumber=p.mpn
 JOIN DATABASE2.passanger_car_pds pds ON al.productid=pds.productid
)
  

Выполнение заняло более 6 дней!!! затем остановился, не вставляя ни одной строки в таблицу.

 [root@XXXX ~]# mysqladmin pr
 -------- ------------- ------------------- ------------- --------- -------- -------------- ------------------------------------------------------------------------------------------------------ 
| Id     | User        | Host              | db          | Command | Time   | State        | Info                                                                                                 |
 -------- ------------- ------------------- ------------- --------- -------- -------------- ------------------------------------------------------------------------------------------------------ 
| 939    | root        | localhost         | mws_autocms | Query   | 408622 | Sending data | INSERT IGNORE INTO product_to_category (
SELECT p.product_id, pds.nodeid
FROM product p
JOIN DATABASE2 |
| 107374 | root        | localhost         |             | Query   | 0      | starting     | show processlist                                                                                     |
 -------- ------------- ------------------- ------------- --------- -------- -------------- ------------------------------------------------------------------------------------------------------ 
  

если я запускаю запрос с ОГРАНИЧЕНИЕМ 100 в конце, он выполняет запрос и вставляет данные в таблицу.

Я настроил MySQL на:

 innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 1G
innodb_log_buffer_size          = 512M
query_cache_size                = 0
query_cache_type                = 0
innodb_buffer_pool_size         = 12G
innodb_buffer_pool_instances    = 8
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16
innodb_flush_log_at_trx_commit  = 2
innodb_large_prefix             = 1
innodb_file_per_table           = 1
innodb_file_format              = Barracuda
max_allowed_packet              = 1024M
lower_case_table_names          = 1
  

Без какого-либо успеха.

Любая помощь / совет по выполнению этого запроса, пожалуйста. Я боролся неделями.

Вот вывод команды EXPLAIN

  ---- ------------- --------------------- ------------ ------ -------------------------------------------------------- --------------------------- --------- ------------------------------------------------- --------- ---------- -------------------------- 
| id | select_type | table               | partitions | type | possible_keys                                          | key                       | key_len | ref                                             | rows    | filtered | Extra                    |
 ---- ------------- --------------------- ------------ ------ -------------------------------------------------------- --------------------------- --------- ------------------------------------------------- --------- ---------- -------------------------- 
|  1 | INSERT      | product_to_category | NULL       | ALL  | NULL                                                   | NULL                      | NULL    | NULL                                            |    NULL |     NULL | NULL                     |
|  1 | SIMPLE      | p                   | NULL       | ALL  | manufacturer_id                                        | NULL                      | NULL    | NULL                                            | 5357582 |   100.00 | Using temporary          |
|  1 | SIMPLE      | al                  | NULL       | ref  | PRIMARY,productid,supplierid,datasupplierarticlenumber | datasupplierarticlenumber | 100     | mws_autocms.p.mpn,mws_autocms.p.manufacturer_id |      56 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | pds                 | NULL       | ref  | productid                                              | productid                 | 4       | mws_tecdoc_2018_4_fr.al.productid               |    1322 |   100.00 | Using where; Using index |
 ---- ------------- --------------------- ------------ ------ -------------------------------------------------------- --------------------------- --------- ------------------------------------------------- --------- ---------- -------------------------- 
  

Это слишком широкий вопрос, чтобы отвечать здесь. Мой ответ здесь действительно комментарий, но он немного длинный.

«Я боролся неделями» — и это все, что вы можете показать за это?

Я настроил MySQL на:

Почему? Как? На каком оборудовании это работает?

Учитывая, что здесь есть несколько вариантов, которые требуют перезапуска, означает ли это, что у вас есть эксклюзивное использование экземпляра DB? Если да, то почему используется O_DIRECT?

Зачем объединение, когда вы используете только данные из одной таблицы?

Некоторые таблицы содержат 400 миллионов строк в обеих базах данных.

Вам нужно лучше понимать количество элементов или как сообщить об этом.

затем остановился, не вставляя ни одной строки в таблицу

Почему он остановился без вставки? Что вы сделали для расследования?

Всякий раз, когда я застревал в подобных вещах, я начинаю разбивать требование и вводить промежуточные шаги в свой план. Прочитав ваш вопрос, вам нужно:

1) Объедините данные из нескольких источников вместе, а затем 2) Вставьте этот результирующий набор в другую базу данных.

Таким образом, вы можете разбить его на несколько шагов, предоставляя базе данных меньше работы до истечения времени ожидания.

Создайте таблицу ТОЛЬКО из тех данных, которые вы хотите вставить (один запрос, что-то вроде следующего)

 CREATE TABLE dataToImport AS
SELECT DISTINCT p.product_id, pds.nodeid
 FROM product p 
JOIN DATABASE2.article_links al ON al.supplierid=p.manufacturer_id
 AND al.datasupplierarticlenumber=p.mpn
 JOIN DATABASE2.passanger_car_pds pds ON al.productid=pds.productid
  

Затем импортируйте эти данные:

 INSERT IGNORE INTO product_to_category SELECT product_id, nodeid FROM dataToImport
  

Это немного грубая операция, но это означает, что база данных выполняет меньше работы за один раз, так что вы можете обнаружить, что это решает вашу проблему.

Если это все еще не работает, вам нужно понять, насколько велик результирующий набор этого запроса SELECT, поэтому сначала запустите свой SELECT самостоятельно и посмотрите на результат.