Как ограничить mysql по поглощению дисковой памяти?

#drupal #innodb #centos #mysql

Приветствую всех, кого не видел! Сразу к делу. Перевожу крупный портал в Amazon с
довольно мощной машинки:
Intel® Xeon® CPU E5-2620 0 @ 2.00GHz 
24 ядра 
32GB RAM 
1TB HDD

Особых настроек mysql/php/apache там конечно не было (ресурсов много, зачем напрягать
мозги). Упакованный дамп mysql весил примерно 250m, сам хост - порядка 1.5g. mysql/php/apache
уже поставлены, хост прописан и работает, но mysql постоянно жрет дисковую память,
независимо от нагрузки на сервер.
Кеширование отключено: boost не стоит, встроенное кеширование отключено.
Сейчас постоянно приходится расширять HDD, как только на нем остаются 5-7GB. На данный
момент HDD на 100g. Дальше расширяться не хочется, да и клиентам нужен бюджетный вариант.
Один нюанс: на старом хостинге HDD был заполнен на 254g и я не уверен, что эта цифра
до последнего момента не росла. Т.е., если я выделю 300g, вроде бы решу проблему, но
у меня задача уложиться по возможности в 30g :)
Кто-нибудь сталкивался, что посоветуете? Хотелось бы решить еще вопрос, как этот
кеш почистить, ведь все начиналось с 10g.
Если важно, то расширяю HDD по этому сценарию: How to Increase the size of a Linux
LVM by expanding the virtual machine disk

Сейчас зашел на сервер, и df уже показывает 54% вместо 94%:
# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       98G   50G   43G  54% /
tmpfs                  15G     0   15G   0% /dev/shm
/dev/xvda1            477M   68M  384M  15% /boot

при том, что сервер с последнего перезапуска ни кто не трогал. От du объемы не изменились.
После первого захода на портал по IP'у картина измнилась:
# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       98G   17G   76G  19% /
tmpfs                  15G     0   15G   0% /dev/shm
/dev/xvda1            477M   68M  384M  15% /boot

Что за выкрутасы?

Да, после ребута у du/df цифры те же. (
 --


Можно посмотреть, что там в кроне.

Сейчас ситуация кардинально поменялась: в du выдает уже папки с хоста, объем диска
то растет до 70% то падает до 20%.
 --
Я это сязываю пока с нехваткой оперативы. В top'е наблюдаю один процесс httpd, который
сожрал махом 30g вирт. памяти (о как!). По всей видимости, он ее пытается свопить на
диск, вот он и растет. Сейчас оптимизирую httpd.conf. Но при чем тут mysqld пока не
понял (я ему прописал в my.cnf забирать максимум 6g, что он и делает в top'е)
 --
Выходит, на своп для 32gRAM все равно придется обеспечить HDD, размером 64g как минимум
+ следить за аппетитами apache?    

Ответы

Ответ 1

Используй find, для поиска файлов, которые изменились или были созданы за искомое
время и сделай на основании этого вывод, кто жрёт место.
Пример поиска изменённых файлов в течении последних 60 секунд:
find /path -type f -mtime -60s

Для поиска созданных, используй ключ ctime

Транзакционная модель

#cpp #mysql #innodb

В рамках многопоточного приложения работа с БД осуществляется посредством использования
одной и той же учётной записи. Каждый поток имеет отдельное соединение с БД. Появилась
необходимость использовать транзакции.

В официальной документации мне не удалось обнаружить (буду признателен за ссылку),
где бы чётко оговаривалось, что транзакционная модель в InnoDb функционирует и на уровне
соединений, а не только на уровне отдельных учётных записей. Возможно, что это априори,
однако я некомпетентен в рассматриваемой области. Хотелось бы, по возможности, получить
точный ответ.

Второй вопрос вытекает из первого. Если транзакционная модель InnoDb всё же реализована
на уровне отдельных соединений, то существует ли также возможность создавать отдельные,
но параллельно выполняемые транзакции в рамках одного и того же соединения в одном
и том же потоке? Это, скажем, может понадобиться при асинхронном выполнении. К сожалению,
в документации я не увидел возможности указывать транзакциям какие-либо уникальные
имена или идентификаторы.

MySQL 5.7
    

Ответы

Ответ 1

Транзакционная модель функционирует, как ни странно, на уровне транзакций - группы
команд между begin и commit/rollback (одиночные запросы вне открытой явно транзакции
автоматически оборачиваются в транзакцию, без транзакции транзакционные хранилища не
работают). В рамках одного соединения может быть много транзакций. От одного пользователя
может быть много одновременных и независимых соединений, и, следовательно, конкурентных
транзакций тоже может быть много. В сущности, в понятии транзакции нет ни соединения,
ни пользователя. Есть только id транзакции, согласно которому вычисляется видимость
актуальных версий строк в MVCC и за которым закрепляются взятые блокировки.

В известных мне СУБД - mysql и postgresql - одно соединение одновременно держать
две разные транзакции открытыми не может, одно соединение - только одна открытая транзакция.
И не припоминаю возможности в рамках одного соединения выполнять одновременно несколько
команд. Библиотека может предоставлять неблокирующий вызов, но не дождавшись конца
ответа новые запросы отправлять не получится.


Ответ 2

В MySQL каждый тред держит соединение. Он не отслеживает, каким процессом это соединение
открыто, т.е. ему без разницы, один процесс открыл кучу соединений или это куча процессов.

MySQL выполняет запросы параллельно, в рамках тредной модели, но существуют блокировки:

на уровне MySQL в целом:


на метаданные
транзакционные


на уровне движка (InnoDb)


на таблицы, в случае индексации, оптимизации  
счетчик автоинкремента


на таблицу


в случае записи на строки
LOCK TABLE 
полный скан таблицы (индексация)


В случае транзакций:
После того как стартует транзакция - происходят какие-то изменения данных, которые
пишутся в журнал, далее мы эти изменения коммитим или отменяем. Как только мы выполнили
эти изменения, то они из журнала переходят в данные. На время выполнения транзакции
- данные используемой табл доступны для чтения (но это зависит от уровня изоляции транзакции). 

В рамках одного соединения может стартовать только одна транзакция. COMMIT закрепляет
транзакцию.

Советую книгу MySQL. «Оптимизация производительности», там очень хорошо расписано
про блокировки и транзакции.

Восстановление базы данных Innodb

#mysql #innodb

В результате 'печальных' событий был удалён каталог с установленной mysql и хранившейся
внутри БД. Уцелел лишь каталог из mysql/bin/data/mydbname, как я полагаю, с данными
внутри (судя по весу).

Собственно вопрос: как достать данные их этой папки? 
Там лежат файлы с названиями таблиц и расширениями .frm .ibd и db.opt
    

Ответы

Ответ 1

Попробуй 


  --innodb_force_recovery


Но надо действовать осторожно! Для начало создай резервную копию этих файлов. Подробное
руководство на хабре можно найти

Перенос InnoDB таблиц как файлов

#mysql #innodb

Известно, что MySQL таблицы MyISAM хранит в виде трёх файлов: tbl_name.frm - описание
структуры таблицы, tbl_name.myd (myData) - данные, хранящиеся в таблице, tbl_name.myi
(myIndex) - индексы. Если MySQL сервер остановить, то путём простого копирования этих
файлов можно перенести таблицу на другой сервер. Это иногда намного удобнее и быстрее,
чем сдампить таблицу на одном сервере и залить дамп на другом.

Вопрос - как проделать этот трюк для таблиц InnoDB? Таблицы там живут в файле (файлах)
ibdata*, перенести его можно только целиком?
    

Ответы

Ответ 1

Из коробки InnoDB хранит все таблицы в общем пуле ibdata и описанный в вопросе трюк
невозможен. Однако, если MySQL сервер настроен на использование file-per-table tablespaces,
для каждой таблицы будет создаваться отдельная пара файлов (вида tbl_name.frm - структура
и tbl_name.ibd - даныне и индексы). Помимо описанного трюка, это даёт другие преимущества,
вроде быстрого выполнения запросов TRUNCATE TABLE и возможности получить обратно дисковое
пространство, занятое таблицей, при её удалении (в случае использования ibdata* дисковое
пространство при удалении таблиц не освобождается). Настройка выглядит так (в my.cnf):

[mysqld]
innodb_file_per_table=1


При остановленном MySQL сервере файлы .frm и .ibd можно копировать и переносить,
как и в случае MyISAM, а если нужно скопировать таким образом таблицу, не останавливая
сервер, нужно прибегнуть к хитрости - сбросить на диск кеш и "выгрузить" таблицу (таблицы),
которые хотим скопировать:

FLUSH TABLES table_one, table_two FOR EXPORT;


Теперь файлы .frm и .ibd можно копировать на лету - разумеется, с момента FLUSH до
окончания копирования работать с данной таблицей нельзя (но можно с остальными).

"Подключение" перенесённой на другой сервер таблицы InnoDB выглядит так:


Создаём на новом месте в базе с таким же именем (это важно), как у базы, в которой
находилась переносящаяся таблица, таблицу с такой же структурой, как переносящаяся (пустую)
Выполняем

ALTER TABLE tbl_name DISCARD TABLESPACE;

Подкладываем скопированные файлы tbl_name.frm и tbl_name.ibd
Выполняем

ALTER TABLE tbl_name IMPORT TABLESPACE;



Вуаля, таблица перенесена!

Перед тем, как впервые воспользоваться этой инструкцией для операций с живыми данными,
потренируйтесь на чём-нибудь тестовом, что не жалко испортить. И всегда делайте бекапы!


Ответ 2

Ок, по базам: если нужен InnoDB - то:
 1. На источнике сменить тип на MyISAM
 2. Остановить сервер
 3. Скопировать
 4. На приемнике сделать тип InnoDB
 5. На исходнике вернуть InnoDB
 6. Запустить сервер на исходнике

В чем разница между InnoDB и MyISAM?

#mysql #innodb #myisam

В чем разница между движками MySQL - InnoDB и MyISAM? Каковы их слабые и сильные стороны?
    

Ответы

Ответ 1

MyISAM поддерживает сжатие таблиц в отличии от InnoDB.
MyISAM имеет встроенные полнотекстный поиск в отличии от InnoDB.
InnoDB поддерживает транзакции в отличии от MyISAM.
InnoDB поддерживает блокировки уровня строки (MyISAM - только уровня таблицы).
InnoDB поддерживает ограничения внешних ключей (MyISAM - нет).
InnoDB более надежна при больших объемах данных.
InnoDB в теории немного быстрее.


Ответ 2

До недавнего времени InnoDB не поддерживала полнотекстовые индексы.
InnoDB не хранит ни количество строк в таблице, ни следующее значение автоинкрементного
поля. Первое не так важно, поскольку мало кому бывает нужно считать все строки в таблице,
без какого бы то ни было фильтра.
А вот второе в теории чревато. Если после удаления последнего выданного id сервер
будет перегружен, то id будет выделен повторно. Впрочем, я не слышал о случавшихся
такого рода косяках на практике


Ответ 3

Есть один очевидный (но не сразу) минус MyISAM, вытекающий из особенностей блокировок.
Если в системе могут выполняться тяжелые SELECTы, то любой UPDATE на участвующие в
нем таблицы будет ждать окончания SELECTа и блочить все дальнейшие запросы. Если с
базой работают достаточно активно, то это не вариант.


Ответ 4

Если коротко, то в InnoDB есть поддержка транзакций, а у MyISAM нету.
И еще MyISAM в отличие от InnoDB поддерживает текстовую индексацию.
В основном используется MyISAM.

Гранулированый select c последующим update

#mysql #sql #innodb #транзакции

Есть ли способы выполнения селекта с последующим апдейтом на выбранном наборе таким
образом, чтобы между двумя запросами не выполнились запросы из других соединений, при
этом не блокируя всю таблицу целиком?

Задача банальна - выбрать N записей, после чего все их обновить(несколько полей)
так, чтобы между выборкой и обновлением те же самые записи не были выбраны параллельно
в других соединениях.

Такие блокировки, как select for update не подходят, т.к они блокируют записи с индексом
только если задано условие на "пространство" индексированного поля.

Есть ли какие-либо адекаватные методы решения этой задачи?

UPDATE:

Запросы выглядят следующим образом(все они - в триггерах):

START TRANSACTION ;

        DROP TABLE IF EXISTS TempTable;
        CREATE TEMPORARY TABLE TempTable AS(
            SELECT units.id AS id, units.authkey AS authkey
            FROM availablePublicUnitsView units
                LEFT JOIN media_info
                    ON units.id=media_info.unit_id AND media_info.media_id=mediaId
            WHERE media_info.media_id IS NULL
            LIMIT unitsCount
            #FOR UPDATE
        );

        UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash
        WHERE id IN (SELECT id from TempTable) AND reserved=false;

        SELECT * FROM TempTable;

COMMIT ;


В SQL`е я не эксперт, так что если что-то в корне делаю неверно, буду рад услышать,
что именно.
    

Ответы

Ответ 1

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

Сначала все необходимые записи обновляются с установкой "ключа" выборки в виде хэша
SHA-256(алгоритм не принципиален), затем все записи с установленным хэшом выбираются,
но уже после завершения транзакции. Работает решение довольно быстро и с минимальными
блокировками.

Важно также отметить, что данные из вью availablePublicUnitsView выбираются, предварительно
сортируясь в случайном порядке(ORDER BY RAND), сводя блокировки почти к нулю. 

CREATE PROCEDURE select_and_reserve_units(
    IN unitsCount INT,
    IN mediaId VARCHAR(40),
    IN reservationHash BINARY(64))
BEGIN

START TRANSACTION ;

    UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash
    WHERE id IN (

        SELECT units.id
        FROM availablePublicUnitsView units
            LEFT JOIN media_info
                ON units.id=media_info.unit_id AND media_info.media_id=mediaId
        WHERE media_info.media_id IS NULL    
        FOR UPDATE

    ) LIMIT unitsCount;

COMMIT ;

SELECT unit.id AS id, unit.authkey AS authkey FROM units
WHERE reservation_hash=reservationHash AND reserved=true;

END;//



  P.S видимо, пора переходить на PostgreSQL...


Ответ 2

Я так поняла, нужно запретить параллельное выполнение этих запросов над одними и
теми же записями?

Попробуйте изменить уровень изоляции. При этом используя SELECT FOR UPDATE.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ UNCOMMITTED - разрешает другим чтение данных, даже если транзакция не подтверждена.

Либо REPEATABLE READ - должна блокировать только изменяемые строки.

Транзакционная модель

В рамках многопоточного приложения работа с БД осуществляется посредством использования одной и той же учётной записи. Каждый поток имеет отдельное соединение с БД. Появилась необходимость использовать транзакции.
В официальной документации мне не удалось обнаружить (буду признателен за ссылку), где бы чётко оговаривалось, что транзакционная модель в InnoDb функционирует и на уровне соединений, а не только на уровне отдельных учётных записей. Возможно, что это априори, однако я некомпетентен в рассматриваемой области. Хотелось бы, по возможности, получить точный ответ.
Второй вопрос вытекает из первого. Если транзакционная модель InnoDb всё же реализована на уровне отдельных соединений, то существует ли также возможность создавать отдельные, но параллельно выполняемые транзакции в рамках одного и того же соединения в одном и том же потоке? Это, скажем, может понадобиться при асинхронном выполнении. К сожалению, в документации я не увидел возможности указывать транзакциям какие-либо уникальные имена или идентификаторы.
MySQL 5.7

Ответ

Транзакционная модель функционирует, как ни странно, на уровне транзакций - группы команд между begin и commit/rollback (одиночные запросы вне открытой явно транзакции автоматически оборачиваются в транзакцию, без транзакции транзакционные хранилища не работают). В рамках одного соединения может быть много транзакций. От одного пользователя может быть много одновременных и независимых соединений, и, следовательно, конкурентных транзакций тоже может быть много. В сущности, в понятии транзакции нет ни соединения, ни пользователя. Есть только id транзакции, согласно которому вычисляется видимость актуальных версий строк в MVCC и за которым закрепляются взятые блокировки.
В известных мне СУБД - mysql и postgresql - одно соединение одновременно держать две разные транзакции открытыми не может, одно соединение - только одна открытая транзакция. И не припоминаю возможности в рамках одного соединения выполнять одновременно несколько команд. Библиотека может предоставлять неблокирующий вызов, но не дождавшись конца ответа новые запросы отправлять не получится.

Перенос InnoDB таблиц как файлов

Известно, что MySQL таблицы MyISAM хранит в виде трёх файлов: tbl_name.frm - описание структуры таблицы, tbl_name.myd (myData) - данные, хранящиеся в таблице, tbl_name.myi (myIndex) - индексы. Если MySQL сервер остановить, то путём простого копирования этих файлов можно перенести таблицу на другой сервер. Это иногда намного удобнее и быстрее, чем сдампить таблицу на одном сервере и залить дамп на другом.
Вопрос - как проделать этот трюк для таблиц InnoDB? Таблицы там живут в файле (файлах) ibdata*, перенести его можно только целиком?

Ответ

Из коробки InnoDB хранит все таблицы в общем пуле ibdata и описанный в вопросе трюк невозможен. Однако, если MySQL сервер настроен на использование file-per-table tablespaces, для каждой таблицы будет создаваться отдельная пара файлов (вида tbl_name.frm - структура и tbl_name.ibd - даныне и индексы). Помимо описанного трюка, это даёт другие преимущества, вроде быстрого выполнения запросов TRUNCATE TABLE и возможности получить обратно дисковое пространство, занятое таблицей, при её удалении (в случае использования ibdata* дисковое пространство при удалении таблиц не освобождается). Настройка выглядит так (в my.cnf):
[mysqld] innodb_file_per_table=1
При остановленном MySQL сервере файлы .frm и .ibd можно копировать и переносить, как и в случае MyISAM, а если нужно скопировать таким образом таблицу, не останавливая сервер, нужно прибегнуть к хитрости - сбросить на диск кеш и "выгрузить" таблицу (таблицы), которые хотим скопировать:
FLUSH TABLES table_one, table_two FOR EXPORT;
Теперь файлы .frm и .ibd можно копировать на лету - разумеется, с момента FLUSH до окончания копирования работать с данной таблицей нельзя (но можно с остальными).
"Подключение" перенесённой на другой сервер таблицы InnoDB выглядит так:
Создаём на новом месте в базе с таким же именем (это важно), как у базы, в которой находилась переносящаяся таблица, таблицу с такой же структурой, как переносящаяся (пустую) Выполняем
ALTER TABLE tbl_name DISCARD TABLESPACE; Подкладываем скопированные файлы tbl_name.frm и tbl_name.ibd Выполняем
ALTER TABLE tbl_name IMPORT TABLESPACE;
Вуаля, таблица перенесена!
Перед тем, как впервые воспользоваться этой инструкцией для операций с живыми данными, потренируйтесь на чём-нибудь тестовом, что не жалко испортить. И всегда делайте бекапы!

В чем разница между InnoDB и MyISAM?

В чем разница между движками MySQL - InnoDB и MyISAM? Каковы их слабые и сильные стороны?

Ответ

MyISAM поддерживает сжатие таблиц в отличии от InnoDB. MyISAM имеет встроенные полнотекстный поиск в отличии от InnoDB. InnoDB поддерживает транзакции в отличии от MyISAM. InnoDB поддерживает блокировки уровня строки (MyISAM - только уровня таблицы). InnoDB поддерживает ограничения внешних ключей (MyISAM - нет). InnoDB более надежна при больших объемах данных. InnoDB в теории немного быстрее.

Гранулированый select c последующим update

Есть ли способы выполнения селекта с последующим апдейтом на выбранном наборе таким образом, чтобы между двумя запросами не выполнились запросы из других соединений, при этом не блокируя всю таблицу целиком?
Задача банальна - выбрать N записей, после чего все их обновить(несколько полей) так, чтобы между выборкой и обновлением те же самые записи не были выбраны параллельно в других соединениях.
Такие блокировки, как select for update не подходят, т.к они блокируют записи с индексом только если задано условие на "пространство" индексированного поля.
Есть ли какие-либо адекаватные методы решения этой задачи?
UPDATE:
Запросы выглядят следующим образом(все они - в триггерах):
START TRANSACTION ;
DROP TABLE IF EXISTS TempTable; CREATE TEMPORARY TABLE TempTable AS( SELECT units.id AS id, units.authkey AS authkey FROM availablePublicUnitsView units LEFT JOIN media_info ON units.id=media_info.unit_id AND media_info.media_id=mediaId WHERE media_info.media_id IS NULL LIMIT unitsCount #FOR UPDATE );
UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash WHERE id IN (SELECT id from TempTable) AND reserved=false;
SELECT * FROM TempTable;
COMMIT ;
В SQL`е я не эксперт, так что если что-то в корне делаю неверно, буду рад услышать, что именно.

Ответ

В итоге после проведения нескольких нагрузочных тестов оптимальным оказался вариант, не использующий временной таблицы(каждый раз пересоздается, занимая основное время), но использующий блокировку для обновления.
Сначала все необходимые записи обновляются с установкой "ключа" выборки в виде хэша SHA-256(алгоритм не принципиален), затем все записи с установленным хэшом выбираются, но уже после завершения транзакции. Работает решение довольно быстро и с минимальными блокировками.
Важно также отметить, что данные из вью availablePublicUnitsView выбираются, предварительно сортируясь в случайном порядке(ORDER BY RAND), сводя блокировки почти к нулю.
CREATE PROCEDURE select_and_reserve_units( IN unitsCount INT, IN mediaId VARCHAR(40), IN reservationHash BINARY(64)) BEGIN
START TRANSACTION ;
UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash WHERE id IN (
SELECT units.id FROM availablePublicUnitsView units LEFT JOIN media_info ON units.id=media_info.unit_id AND media_info.media_id=mediaId WHERE media_info.media_id IS NULL FOR UPDATE
) LIMIT unitsCount;
COMMIT ;
SELECT unit.id AS id, unit.authkey AS authkey FROM units WHERE reservation_hash=reservationHash AND reserved=true;
END;//
P.S видимо, пора переходить на PostgreSQL...