SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 18.07.2013 13:56:13

Uglykoyote
Участник
Зарегистрирован: 18.07.2013
Сообщений: 3

40 миллионов записей в сутки, Мускул, логика = мрак. Что делать?

Доброго времени всем. Посоветуйте что делать. Есть файл логов на 40-42 миллиона записей в сутки. Изменить его формат или число данных выводимых в него я не могу, то есть исходные данные изменению с моей стороны не подлежат.
Первая проблема возникла с загрузкой в базу Мускула 5.6. Решилось путем переработки баш-скриптом файла в более менее удобоваримую форму (с отбрасыванием ненужных полей), загрузка 40 миллионов записей стала проходить в течении нескольких минут. Посмотрели размер базы, ужаснулись (образно), вес таблицы был около 5.5Гб. А нам надо хранить данных аж на 30 суток, что автоматически почти до 1.2миллиардов записей раздует таблицу. Решили пойти путем введения ключевых полей на повторяющиеся значения (IP, тип данных). Переработка баш скриптом с подменой ключевых полей показала что выигрыш будет хоть и не большой, но порядка 400-500Мб в сутки, за месяц набегает весьма неплохая экономия.
Добавили таблицы:
1.
CREATE TABLE IF NOT EXISTS IpTables(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ip_unique VARCHAR(15) NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 11818
AVG_ROW_LENGTH = 41
CHARACTER SET utf8
COLLATE utf8_general_ci;

2.
CREATE TABLE IF NOT EXISTS TypeName(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  type_name VARCHAR(500) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 263
AVG_ROW_LENGTH = 65
CHARACTER SET utf8
COLLATE utf8_general_ci;

и сама результирующая таблица:
CREATE TABLE IF NOT EXISTS Logs_Id(
  Ip_Id INT(6) DEFAULT NULL,
  `Date` DATETIME DEFAULT NULL,
  User TEXT DEFAULT NULL,
  Type_Id INT(4) DEFAULT NULL,
  Url TEXT DEFAULT NULL,
  size_blabla DECIMAL(8, 3) DEFAULT NULL,
  id INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 46048058
AVG_ROW_LENGTH = 88
CHARACTER SET utf8
COLLATE utf8_general_ci;

Начали писать курсор, который в первой версии просто перемещал данные с одной таблицы в другую (изменены типы полей, для более корректного выделения места под данные и тд), поставили заглушки на поля ключей, запустили. прошло примерно 40 минут, и всё норм. решили добавлять логику обрабатывающую проставление ключей вместо значений.. прошло полтора часа, и всё работает:

BEGIN
  #DECLARE temp_i INT DEFAULT 1;
  DECLARE ip_id_v int(6);
  DECLARE Type_Id_v int(4);
  DECLARE date_time_v datetime;
  DECLARE ip_v varchar(15);
  DECLARE data_size_v decimal(8,3) UNSIGNED;
  DECLARE URL_v text;
  DECLARE user_v text;
  DECLARE type_v tinytext;
  DECLARE done boolean DEFAULT 0;
  DECLARE temp_table_cur CURSOR FOR SELECT FROM_UNIXTIME(lt.date_stamp), lt.ip, ROUND(lt.data_size/1024/1024,3), SUBSTR(lt.URL,1), lt.user, lt.type FROM Logs_table lt;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  OPEN temp_table_cur;
  SET autocommit=0;
  WHILE done = 0 DO
  #SET temp_i = 1;
  #WHILE temp_i < 6 DO
    FETCH temp_table_cur INTO date_time_v, ip_v, data_size_v, URL_v, user_v, type_v;
    SELECT it.id INTO ip_id_v FROM IpTables it WHERE it.ip_unique = ip_v;
    SELECT tn.id INTO Type_Id_v FROM TypeName tn WHERE tn.type_name = type_v;
    INSERT INTO Logs_Id (Ip_Id, date, USER, Type_Id, Url, size_blabla) VALUES (
                         ip_id_v,
                         date_time_v,
                         user_v,
                         Type_Id_v,
                         URL_v,
                         data_size_v   
                          );
    #set temp_i= temp_i + 1;
  END WHILE;
  COMMIT;
  SET autocommit=1;
  CLOSE temp_table_cur;
END

Подскажите, может кто сталкивался уже, есть какие то варианты упростить работу?
По запросу "SHOW FULL PROCESSLIST" видно что работа данного курсора логична, берем запись из основной таблицы, и начинаем гонять по таблицам справочным.. (в одной 250 записей, в другой 11 тыс).

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

Неактивен

 

#2 18.07.2013 14:54:01

Uglykoyote
Участник
Зарегистрирован: 18.07.2013
Сообщений: 3

Re: 40 миллионов записей в сутки, Мускул, логика = мрак. Что делать?

и да, машина несет на борту 32Гб озу и проц Корку ай7 топовую. Выделен key_buffer_size в 9гб для мускула, по логике вещей памяти хватает с запасом, чтобы всю таблицу поместить в ОЗУ, и работать уже на уровне ЦПУ<>ОЗУ. Или мускул принципиально не умеет так делать?

ещё есть выделенный буфер max_allowed_packet=32m.

поможет ли включение в my.cnf ключа:

Кэширование результатов запросов. Обычно бывает достаточно 32 Мб (смотреть на статус Qcache_lowmem_prunes). Максимальный размер результата по умолчанию - 1 Мб, его можно регулировать.

set-variable = innodb_buffer_pool_size=780M

Отредактированно Uglykoyote (18.07.2013 14:55:59)

Неактивен

 

#3 19.07.2013 14:47:31

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: 40 миллионов записей в сутки, Мускул, логика = мрак. Что делать?

Пробовали хранить в MyISAM - иногда это эффективнее для хранения больших объемов. Курсоры обладают очень низкой производительностью по своей природе - MySQL их интерпретирует и выполняет каждую операцию как SQL-запрос.

Неактивен

 

#4 19.07.2013 15:05:24

Uglykoyote
Участник
Зарегистрирован: 18.07.2013
Сообщений: 3

Re: 40 миллионов записей в сутки, Мускул, логика = мрак. Что делать?

Да, к сожалению это увидели уже - ну что курсор это каждый шаг = запросу. пока ещё не пробовали, у нас пока попутно крутится ещё одна задача на сервере, надо подумать куда её "перенести". я ведь, правильно понимаю, что менять тип базы = переустановке мускула всего, или достаточно таблспейс создать с другим типом движка?

Неактивен

 

#5 19.07.2013 15:07:36

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: 40 миллионов записей в сутки, Мускул, логика = мрак. Что делать?

Можно сменить у одной таблицы:
ALTER TABLE `mytable` ENGINE=MyISAM;
Кроме того разрешается иметь таблицы разных типов. Следите за настройками и чтобы было место в tmpdir http://webew.ru/posts/2699.webew

Неактивен

 

#6 24.01.2014 09:48:15

MaximCarrera
Участник
Зарегистрирован: 08.08.2013
Сообщений: 12

Re: 40 миллионов записей в сутки, Мускул, логика = мрак. Что делать?

Uglykoyote написал:

Да, к сожалению это увидели уже - ну что курсор это каждый шаг = запросу. пока ещё не пробовали, у нас пока попутно крутится ещё одна задача на сервере, надо подумать куда её "перенести". я ведь, правильно понимаю, что менять тип базы = переустановке мускула всего, или достаточно таблспейс создать с другим типом движка?

А реплицировать на slave сервер и поэкспериментировать с движком - возможно, или никаких ресурсов не хватит? У нас админ в подобном случае поднял виртуалку в slave режиме, но там записей было намного меньше, конечно.

Неактивен

 

#7 11.02.2014 14:34:41

Neval
Гуру
Откуда: Киев
Зарегистрирован: 11.03.2008
Сообщений: 449

Re: 40 миллионов записей в сутки, Мускул, логика = мрак. Что делать?

rgbeast написал:

Пробовали хранить в MyISAM - иногда это эффективнее для хранения больших объемов.

А разве не наоборот? От больших объёмов MyISAM периодически валятся, а восстановление выполняется часами.

ЗЫ Некропостеры блин big_smile


Человек без чувства юмора - не серьёзный человек wink

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson