SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 21.12.2014 13:22:15

yar113
Участник
Зарегистрирован: 21.12.2014
Сообщений: 13

Кэширование записи

Необходимо средствами MySQL реализовать кэширования записи на диск на 1 час. Порывшись в документации, я не нашел каких либо встроенных средств (может плохо искал?), но у меня пришла идея использовать партицирование с перемещением одной из партиций на tmpfs.
Для проверки такой возможности я создал таблицу:

CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varbinary(255) NOT NULL,
  `partitionid` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`partitionid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`partitionid`)
(PARTITION partition0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION partition1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */
;

Директория tmp примонтирована как tmpfs, т.е. находится в оперативной памяти. В ней создался файл:

test1#P#partition1.ibd

Файл описания структуры таблицы test1.frm остался в основной директории таблиц на ssd.
Далее я перезагрузил сервер, что соответственно привело к удалению файла данных таблицы в ram test1#P#partition1.ibd. Теперь при SELECT запросе выводится ошибка 1146 - Table 'db.test1' doesn't exist. Файл test1.frm никуда не делся, т.е. таблица существует и по идее таблицу можно "восстановить" с потерей всех данных, что мне и нужно. Вопрос, как это можно сделать и можно ли вообще?

Отредактированно yar113 (21.12.2014 13:22:43)

Неактивен

 

#2 21.12.2014 18:23:49

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

Re: Кэширование записи

Такую вещь сделать документируемым способом нельзя. Потеря одного раздела таблицы Innodb приведет к необходимости восстанавливать всю таблицу (скорее всего в ручную, см., например, http://stackoverflow.com/questions/1223 … -ibd-files ). Если хотите кэшировать, храните в отдельной таблице, например, типа MEMORY, что потребует некоторой модификации логики приложения.

Неактивен

 

#3 21.12.2014 18:46:22

yar113
Участник
Зарегистрирован: 21.12.2014
Сообщений: 13

Re: Кэширование записи

Есть возможность изменить только серверную часть, которая осуществляет вставку и изменение данных. Клиентские приложения, выполняющие выборку, к сожалению, изменить нет возможности. У нас за пол года 4 ssd диска исчерпали свой ресурс перезаписи, может MySQL что-то предлагает для решения этой проблемы?

Неактивен

 

#4 21.12.2014 19:17:40

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

Re: Кэширование записи

Для SSD смотрите комментарий Петра Зайцева:
http://www.percona.com/blog/2013/10/03/ … workloads/

Главное увеличить innodb_buffer_pool_size и innodb_flush_log_at_trx_commit=0. Если не помогает, подумайте нужно ли хранить данную базу на SSD.

Неактивен

 

#5 21.12.2014 19:56:45

yar113
Участник
Зарегистрирован: 21.12.2014
Сообщений: 13

Re: Кэширование записи

rgbeast написал:

Если не помогает, подумайте нужно ли хранить данную базу на SSD.

Раньше всё хранилось на HDD. После добавления 10-ого диска дальнейшее расширение рейда перестало увеличивать производительность базы, поэтому пол года назад перешли на slc ssd. Всё бы ничего, но ssd очень дорогие.
Спасибо, попробую покопать в сторону postgre

Неактивен

 

#6 22.12.2014 10:40:34

yar113
Участник
Зарегистрирован: 21.12.2014
Сообщений: 13

Re: Кэширование записи

Попытка номер 2: Я создал таблицу:

CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varbinary(255) NOT NULL,
  `partitionid` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`partitionid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`partitionid`)
(PARTITION partition0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION partition1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */
;

Далее сделал бекап файла

test1#P#partition1.ibd

с пустой таблицы.
После система отработала 10 минут, было произведено несколько тысяч INSERT'ов и UPDATE'ов в таблицу. Далее я перезагрузил сервер (без запуска MySQL), что привело к удалению файл партиции test1#P#partition1.ibd находящейся в tmpfs. Но у меня при создании таблицы был сделан его бекап, который я соответственно скопировал в директорию /tmp. Таким образом все файлы таблицы остались целы, но MySQL при старте пишет

InnoDB: cannot calculate statistics for table "test"."TEST" /* Partition "partition1" */ because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/ … oting.html

, что совершенно нелогично. Файл 100% цел, md5sum показывает одно и тоже значение. Это тоже тупиковый вариант, или я что-то мог сделать не так?

Неактивен

 

#7 22.12.2014 20:31:34

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

Re: Кэширование записи

В Innodb такая структура, что ссылки на внутреннее содержание содержатся в основном дереве ibdata. Поэтому, если файл заменить на пустой, возникает ошибка.

Неактивен

 

#8 23.12.2014 19:19:53

yar113
Участник
Зарегистрирован: 21.12.2014
Сообщений: 13

Re: Кэширование записи

Идея номер 3: использование view на две таблицы (innodb+memory) для скрытия от клиентов внутренней структуры.


CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varbinary(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

CREATE TABLE `test2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varbinary(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

Собственно вопросы:
1. Можно ли (и как) объединить эти две таблицы в одну через view, чтобы клиенты могли производить из них выборку как из одной таблицы?
2. Будут ли при выборке из view при этом падение производительности? Например перестанут ли использоваться индексы?
3. Можно ли сделать поле id уникальным в пространстве обеих таблиц, и можно ли обеспечить также AUTO_INCREMENT средствами самого MySQL?

Отредактированно yar113 (23.12.2014 19:22:21)

Неактивен

 

#9 23.12.2014 19:42:56

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

Re: Кэширование записи

1. CREATE VIEW
http://dev.mysql.com/doc/refman/5.5/en/create-view.html

2. да, с ограничениями
http://dev.mysql.com/doc/refman/5.0/en/ … tions.html

3. нет встроенными средствами MySQL

Неактивен

 

#10 26.12.2014 10:16:02

yar113
Участник
Зарегистрирован: 21.12.2014
Сообщений: 13

Re: Кэширование записи

Не понятно с индексами. Вот я создаю две таблицы:

CREATE TABLE `t1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `row_id` int(11) unsigned NOT NULL,
  `content` text NOT NULL,
  `pid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `main` (`row_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=39 DEFAULT CHARSET=latin1;
 

CREATE TABLE `t2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `row_id` int(11) unsigned NOT NULL,
  `content` text NOT NULL,
  `pid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `main2` (`row_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

И Вьюху на них:

CREATE VIEW test_view AS
          SELECT * FROM t1
          UNION ALL
          SELECT * FROM t2;

Которая через SHOW CREATE VIEW test_view выводится как:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `t1`.`id` AS `id`,`t1`.`row_id` AS `row_id`,`t1`.`content` AS `content`,`t1`.`pid` AS `pid` from `t1` union all select `t2`.`id` AS `id`,`t2`.`row_id` AS `row_id`,`t2`.`content` AS `content`,`t2`.`pid` AS `pid` from `t2`

Далее вставляю множество строк в обе таблицы и делаю выборку по ключу row_id. Из вьюхи получается так:

mysql> EXPLAIN SELECT * FROM `test_view` WHERE `row_id` = 9;
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key         | key_len | ref   | rows | Extra           |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------+
|  1 | PRIMARY      | <derived2> | ref  | <auto_key0>   | <auto_key0> | 4       | const |    7 | NULL            |
|  2 | DERIVED      | t1         | ALL  | NULL          | NULL        | NULL    | NULL  |   38 | NULL            |
|  3 | UNION        | t2         | ALL  | NULL          | NULL        | NULL    | NULL  |   34 | NULL            |
|NULL| UNION RESULT | <union2,3> | ALL  | NULL          | NULL        | NULL    | NULL  | NULL | Using temporary |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------+
4 rows in set (0,00 sec)

А прямая выборка из двух таблиц вот так:

mysql> EXPLAIN SELECT * FROM `t1` WHERE `row_id`=9  UNION SELECT * FROM `t2` WHERE `row_id`=9 ;
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key   | key_len | ref   | rows | Extra           |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------+
|  1 | PRIMARY      | t1         | ref  | main          | main  | 4       | const |    7 | NULL            |
|  2 | UNION        | t2         | ref  | main2         | main2 | 4       | const |    4 | NULL            |
|NULL| UNION RESULT | <union1,2> | ALL  | NULL          | NULL  | NULL    | NULL  | NULL | Using temporary |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------+
3 rows in set (0,00 sec)

Я правильно понимаю что при выборку из test_view индексы не используются?

Неактивен

 

#11 03.01.2015 15:37:15

yar113
Участник
Зарегистрирован: 21.12.2014
Сообщений: 13

Re: Кэширование записи

Нашел что алгоритм MERGE не может использоваться с UNION. Здесь http://dev.mysql.com/doc/refman/5.0/en/ … tions.html написано что при алгоритме temptable индексы могут использоваться при генерации временных таблиц. И по идее во вьюшке этому ничего не мешает?

Отредактированно yar113 (03.01.2015 17:05:45)

Неактивен

 

#12 04.01.2015 12:13:21

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

Re: Кэширование записи

MySQL не позволяет создавать индексы на VIEW явно. Остальное - на усмотрение алгоритмов, а как они работают доподлинно можно узнать только из исходников.

Неактивен

 

Board footer

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