Ускорение 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;

Теперь наша выборка будет идти исключительно по индексам, т.к. мы будем находить наше значение в индексе и не будет нужды считывать данные из таблицы данных. Один минус, индексы будут занимать больше места на диске, а так же добавление каждой новой записи будет перестраивать все индексы намного дольше. Но тут каждый должен понимать, как работает его база данных и каждая отдельная таблица.

Итак, мы создали таблицу 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

Редактируем. Ищем секцию start, выглядит это так:

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
                # Now start 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
                # Now start 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.

Автор: iillyyaa

Источник