MySQL: эффективно заполнить таблицу внутри хранимой процедуры

Question

MySQL: эффективно заполнить таблицу внутри хранимой процедуры

Я тестирую производительность на MySQL Server и заполняю таблицу более чем 200 миллионами записей. Хранимая процедура очень медленно генерирует большую строку SQL. Любая помощь или комментарий действительно приветствуются.

Системная информация:

База данных: MySQL 5.6.10 База данных InnoDB (тест).
Процессор: AMD Phenom II 1090T X6, 3910 МГц каждое ядро.
Оперативная память: 16 ГБ DDR3 1600 МГц CL8.
HD: Windows 7 64bit SP1 в SSD, mySQL установлен в SSD, журналы записаны на механическом жестком диске.

Хранимая процедура создает SQL-запрос INSERT со всеми значениями, которые нужно вставить в таблицу.

DELIMITER $$
USE `test`$$

DROP PROCEDURE IF EXISTS `inputRowsNoRandom`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `inputRowsNoRandom`(IN NumRows BIGINT)
BEGIN
    /* BUILD INSERT SENTENCE WITH A LOS OF ROWS TO INSERT */
    DECLARE i BIGINT;
    DECLARE nMax BIGINT;
    DECLARE squery LONGTEXT;
    DECLARE svalues LONGTEXT;

    SET i = 1;
    SET nMax = NumRows + 1;
    SET squery = 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE) VALUES ';
    SET svalues = '("1", "a1", 100, 1, 500000, "2013-06-14 12:40:45"),';

    WHILE i < nMax DO
        SET squery = CONCAT(squery, svalues);
        SET i = i + 1;
    END WHILE;

    /*SELECT squery;*/
    SET squery = LEFT(squery, CHAR_LENGTH(squery) - 1);
    SET squery = CONCAT(squery, ";");
    SELECT squery;

    /* EXECUTE INSERT SENTENCE */
    /*START TRANSACTION;*/
    /*PREPARE stmt FROM squery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    */

    /*COMMIT;*/
END$$
DELIMITER ;

Результаты:

Конкатенация 20000 строк занимает около 45 секунд:

CALL test.inputRowsNoRandom (20000);

Объединение 100000 строк занимает около +5/12 минут. О_О:

CALL test.inputRowsNoRandom (100000);

_{Результат (упорядоченный по продолжительности) - заявленность (суммированная) в секундах ||} _{процент} _{освобождающие предметы 0.00005 50.00000} _{начиная с 0,00002 до 20,00000} _{выполняя 0,00001 10,00000} _{init 0.00001 10.00000} _{уборка 0.00001 10.00000} _{Итого 0,00010 100,00000} _{Изменение ПЕРЕМЕННЫХ СОСТОЯНИЯ в связи с выполнением запроса} _{описание значения переменной} _{Bytes_received 21 байт, отправленных с клиента на сервер} _{Bytes_sent 97 байт отправлено с сервера клиенту} _{Com_select 1 Количество операторов SELECT, которые были выполнены} _{Вопросы 1 Количество утверждений, выполненных сервером}

тесты:
Я уже тестировал разные конфигурации MySQL от 12 до 64 потоков, включал и выключал кеш, переносил логи на другой аппаратный диск...
Также проверено с использованием TEXT, INT..

Дополнительная информация:

Ссылки на производительность: общие и многоядерные, конфигурация, оптимизация ввода-вывода, Debiancores, лучшая конфигурация, конфигурация, оперативная память 48 ГБ.
Профилирование SQL-запроса: как профилировать запрос, проверить возможные узкие места в запросе

Вопросы:

Что-то не так в коде? Если я отправлю 100000 строк для построения окончательной строки SQL, результат SELECT squery; является пустой строкой. Что происходит? (ошибка должна быть, но я ее не вижу).
Могу ли я улучшить код любым способом, чтобы ускорить его?
Я прочитал, что некоторые операции в хранимых процедурах могут быть очень медленными, я должен сгенерировать файл на C/Java/PHP.. и отправить его в mysql?
mysql -u mysqluser -p имя_базы данных
MySQL, кажется, использует только одно ядро для одного SQL-запроса, будет ли nginx или другая система баз данных: многопоточные БД, Cassandra, Redis, MongoDB...) достигать лучшей производительности с помощью хранимых процедур и использовать более одного ЦП для одного запроса? (Так как мой единственный запрос использует только 20% от общего процессора с около 150 потоков).

ОБНОВИТЬ:

Эффективный способ заполнения таблицы, проверьте ответ peterm ниже.
Выполнение хранимых процедур, современных СУБД или встроенных запросов.

4

mysql database multithreading stored-procedures concat

Источник

user921456 16 июн '13 в 18:54

1 ответ

Решение

У меня была такая же задача, как и у вас. Ответ выше объясняет все гладко. Я хочу рассказать о своем решении. В моей задаче есть запрос. Сначала я генерирую случайные данные, затем помещаю в очередь для запроса, а затем удаляю оттуда и записываю в db. Вначале на запись 242 M строк данных ушло 70 часов. Потом поменял двигатель. MyISAM намного быстрее, чем InnoDB. После этого писать стало 30 часов. Все еще слишком... Так что мне нужно сменить smt...

Вместо того, чтобы делать "вставить..... ЗНАЧЕНИЯ (1,2,3)",

Я сделал "вставить.... ЗНАЧЕНИЯ (1,2,3), (4,5,6), (7,8,9)"

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

РЕДАКТИРОВАТЬ: Я написал это, потому что у меня было так много проблем, а потом я понял это, теперь я хочу поделиться этим. лол

0

Источник

user10806451 28 авг '20 в 15:35

Другие вопросы по тегам mysql database multithreading stored-procedures concat

user1920232 17 июн '13 в 02:44 2013-06-17 02:44 · Accepted Answer · 2013-06-17 02:44

Не используйте циклы особенно в этом масштабе в RDBMS.

Попробуйте быстро заполнить таблицу с 1м строк с помощью запроса

INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
SELECT 1, 'a1', 100, 1, 500000, '2013-06-14 12:40:45'
  FROM
(
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + 1 N
from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) f
) t

Это заняло у меня на коробке (MacBook Pro 16 ГБ оперативной памяти, 2,6 ГГц Intel Core i7) ~8 секунд, чтобы завершить

Запрос в порядке, затронуто 1000000 строк (7,63 с)
Записи: 1000000 Дубликаты: 0 Предупреждения: 0

UPDATE1 Теперь версия хранимой процедуры, которая использует подготовленный оператор

DELIMITER $$
CREATE PROCEDURE `inputRowsNoRandom`(IN NumRows INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    PREPARE stmt 
       FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
             VALUES(?, ?, ?, ?, ?, ?)';
    SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45';

    WHILE i < NumRows DO
        EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6;
        SET i = i + 1;
    END WHILE;

    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Завершено через ~3 мин:

mysql> CALL inputRowsNoRandom (1000000);
Запрос в порядке, затронуто 0 строк (2 мин 51,57 с)

Почувствуй разницу 8 сек против 3 мин

ОБНОВЛЕНИЕ2 Чтобы ускорить процесс, мы можем явно использовать транзакции и фиксировать вставки в пакетах. Так что здесь идет улучшенная версия SP.

DELIMITER $$
CREATE PROCEDURE inputRowsNoRandom1(IN NumRows BIGINT, IN BatchSize INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    PREPARE stmt 
       FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
             VALUES(?, ?, ?, ?, ?, ?)';
    SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45';

    START TRANSACTION;
    WHILE i < NumRows DO
        EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6;
        SET i = i + 1;
        IF i % BatchSize = 0 THEN 
            COMMIT;
            START TRANSACTION;
        END IF;
    END WHILE;
    COMMIT;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Результаты с различными размерами партии:

mysql> CALL inputRowsNoRandom1 (1000000,1000); Запрос в порядке, затронуто 0 строк (27,25 с)

mysql> CALL inputRowsNoRandom1(1000000,10000); Запрос в порядке, затронуто 0 строк (26,76 с)

mysql> CALL inputRowsNoRandom1(1000000,100000); Запрос в порядке, затронуто 0 строк (26,43 с)

Вы видите разницу сами. Все же> в 3 раза хуже, чем кросс-соединение.