Прискорення mysql MyISAM, Data Base Index in Memory

Наша сьогоднішня мета збільшити швидкодію бази даних mysql MyISAM штатними засобами на операційній системі Linux (або подібної) без фінансових вкладень.

Назвемо нашу систему Data Base Index in Memory (DBIM).

В даному мануалі розпишемо всі кроки від створення таблиці. Отже, почнемо.

Кожна таблиця складається з 3 файлів.

file.MYD — дані
file.MYI — індекси
file.frm — схема таблиці

Всі вміють створювати таблиці, створимо нашу тестову таблицю:

create table `table_test` (`id` int(5), `value` varchar(10), INDEX (`id`)) ENGINE=MyISAM;


Суть таблиці, робимо вибірку тексту нікому id який індексований. Все чудово працює, індекси спрощують пошук, вибірка швидка, ніж при їх відсутності. Але нам цього мало, ми хочемо ще швидше! Створюємо іншу таблицю:

create table `table_dbim` (`id` int(5), `value` varchar(10), INDEX (`id`, `value`)) ENGINE=MyISAM;

<habracut/>
Тепер наша вибірка буде йти виключно за індексами, т. до. ми будемо знаходити наше значення в індексі і не буде потреби зчитувати дані з таблиці даних. Один мінус, індекси будуть займати більше місця на диску, а так само додавання кожного нового запису буде перебудовувати всі індекси набагато довше. Але тут кожен повинен розуміти, як працює його база даних і кожна окрема таблиця.

Отже, ми створили таблицю table_dbim, на диск нам впали 3 файлу, нас цікавить файл індексів, під назвою table_dbim.MYI.

Найголовніше. Вам потрібен чистий файл індексів без єдиної записи в таблиці, але з уже проставленими індексами! Нам потрібно зробити копію цього файла на диску. Розмістимо копію в теці /z-hdd:

mv /web/mysql/test/table_dbim.MYI /z-hdd/

Увага, ми файл перемістили, в базі даних його більше немає.

Тепер даний файл нам потрібно розмістити на ramdrive (тут я не буду описувати види і способи його створення, в інеті повно мінлива, кожен вибере собі те, що йому до душі і за завданнями. Все це створюється штатними засобами). У мене ramdrive підключений до /z-ram.

Копіюємо:

cp /z-hdd/table_dbim.MYI /z-ram/

Тепер головне, нам потрібно зробити символічну посилання в базу даних mysql на той файл, що ми помістили на наш ramdrive:

ln-s /z-ram/table_dbim.MYI /web/mysql/test/table_dbim.MYI

Створивши посилання нам потрібно змінити власника цього посилання, інакше мускул не отримає доступ до даних, змінюємо:

chown mysql:mysql /web/mysql/test/table_dbim.MYI

Все чудово, тепер наші індекси в пам'яті, їх створення і читання буде відбуватися в рази швидше, можна почати поповнювати таблицю даними. Але, тепер ми боїмося перезавантажень, відключення живлення і падіння системи… Але це не проблема, самі дані таблиці зберігаються на жорсткому диску, а порожню копію індексів ми зберегли окремо, нам потрібно лише після перезавантаження скопіювати його на створений ramdrive.

У цьому нам допоможуть штатні кошти того ж mysql. Шукаємо файл запуску mysql, він знаходиться тут:

/etc/init.d/mysql

Редагуємо. Шукаємо секцію, виглядає це так:

case "${1:-"}" in
'start')
sanity_checks;
# Start daemon
log_daemon_msg "Starting MySQL database server" "mysqld"
if mysqld_status check_alive nowarn; then
log_progress_msg "already running"
log_end_msg 0
else
(місце до запуску мускула)
# Could be removed during boot
test-e /var/run/mysqld || install-m 755-o mysql-g root-d /var/run/mys$

# Start MySQL!
/usr/bin/mysqld_safe > /dev/null 2 > &1 &

# 6s was reported in #352070 to be too few when using ndbcluster
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
sleep 1
if mysqld_status check_alive nowarn ; then break; fi
log_progress_msg "."
done
if mysqld_status check_alive warn; then
(місце після запуску мускула)
log_end_msg 0
# Start Now mysqlcheck or whatever the admin wants.
output=$(/etc/mysql/debian-start)
[ -n "$output" ] && log_action_msg "$output"
else
log_end_msg 1
log_failure_msg "Please take a look at the syslog"
fi
fi
;;

Нам потрібно в цей код додати 2 речі, копіювання нашого файлу до запуску мускула і перевірку потрібних нам таблиць (або всієї бази даних) після старту мускула.

У коді вище я позначив ці місця (місце до запуску мускула) і (місце після запуску мускула).

Додаємо…

У секцію до:

cp --no-clobber /z-hdd/table_dbim.MYI /z-ram/;

(ключ --no-clobber не дасть замінювати файл, у випадку якщо ми не перезагружались а просто перезапускаємо мускул)

І секцію після:

mysqlcheck --auto-repair --all-databases;

У підсумку наш код виглядає так:

case "${1:-"}" in
'start')
sanity_checks;
# Start daemon
log_daemon_msg "Starting MySQL database server" "mysqld"
if mysqld_status check_alive nowarn; then
log_progress_msg "already running"
log_end_msg 0
else
cp /z-hdd/table_dbim.MYI /z-ram/;
# Could be removed during boot
test-e /var/run/mysqld || install-m 755-o mysql-g root-d /var/run/mys$

# Start MySQL!
/usr/bin/mysqld_safe > /dev/null 2 > &1 &

# 6s was reported in #352070 to be too few when using ndbcluster
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
sleep 1
if mysqld_status check_alive nowarn ; then break; fi
log_progress_msg "."
done
if mysqld_status check_alive warn; then
mysqlcheck --auto-repair --all-databases;
log_end_msg 0
# Start Now mysqlcheck or whatever the admin wants.
output=$(/etc/mysql/debian-start)
[ -n "$output" ] && log_action_msg "$output"
else
log_end_msg 1
log_failure_msg "Please take a look at the syslog"
fi
fi
;;

Ви можете вказати якісь свої параметри відновлення. Так само можете виводити лог не на екран, а в лог-файл, наприклад рядком:

mysqlcheck --auto-repair --all-databases >>/var/log/auto.start.mysql.log;


Вирішив я перевірити як воно буде швидко відновлювати індекси, заповнив таблицю даними, 11млн записів, заповнював циклом i+1 однакові значення в індекс і varchar.
Дані 214,868 КБ
Індекс 177,372 КБ
Всього 392,240 КБ
Запуск бази даних без індексів триває не більше секунди.
Відновлення індексів у мене зайняло 24 секунди.

Запуск бази даних з нормальним файлом індексів (тобто, ми просто перезапустили мускул, файл індексів як лежав у пам'яті так і лежить) триває 3 секунди.

Перевірка бази даних 4 секунди.

Підсумуємо, що ж у нас вийшло, ми скопіювали в якийсь каталог «пустушки» індексів, при старті мускула ми копіюємо ці пустушки на рамдрайв до якого у нас вже створено символічні посилання, після чого запустившись мускул перевіряє цілісність даних, знайшовши порожні індекси він переіндексірует дані таблиці.

Полетіли. Вдалих швидкостей вашого старенького mysql.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.