Поставил на выходные выполнение создания индексов для двух полей типа VARCHAR(32)
После выходных индексация так и не завершивась,
MySQL клиент выдал сообщение "Потеряна связь с MySQL Server". Но процесс как висел в памяти так и висит
Обьем файла MYD = 68 ГБ; количество записей - 1млрд 440 млн... версия MySQL 5.5.25
Загрузка процессора составляет около 20-30%
Посоветуйте пожалуйста как настроить MySQL для более быстрой индексации и чтобы она была выполнена на 100%


движок - MyISAM

в my.ini не была прописана временная директория

Запустил следующий SQL запрос :

ALTER TABLE mytable ADD KEY(phoneNumberA,phoneNumberB);

пока скорость записи во временные файлы, которые лежат с текущей базой остается одинаковой, в папке tmpdir создались 4 временных файла (пустые)

Папка tmpdir находится в том же каталоге что и сам MySQL Server

P.S.
Все равно медленно создаются временные файлы, а файлы из папки tmpdir (их 4) пустые.
Загруженность процессора около 20%
Индексируются поля типа VARCHAR(32)

miksoft,

полная загрузка одного ядра двухъядерного процессора Ahtlon 2*2GHZ.

Вначале была загрузка 50% потом через час около 20% а утром на следующий день вижу 10-15% и было видно как винт хорошо так был задействован ( хотя места свободного было предостаточно)

myisam_sort_buffer_size = 128M

Как влияет наличие индекса(ов) на время построения других индексов?

У меня время построения первого индекса заняло 9 часов ( составной по 4 -м полям )
а время построения (!) второго индекса ( составного из 3 х полей, которые есть в первом на позициях 2,3,4 ) заняло 15 часов.

По идее второй индекс должен быстрее первого построится, а он наоборот в 2,5 раза дольше построился.

"Никакой другой нагрузки ни на сервер, ни на MySQL не было?"

Не было! Чисто MySQL Server работал, свободного места на винте в 6 раз больше чем сама база.

"Распартишить таблицу, партиции проиндексировать независимо. Как я понимаю, индексу не требуется ограничение уникальности?"

партиции проиндексировать независимо
- я долго искал как проиндексировать независимо , так и не нашел.

индексу не требуется ограничение уникальности
-не требуется

У меня еще пару вопросов:
Скорость вставки при заполненной базе с каждым разом уменьшается по отношению ко всей таблице с партициями или по отношению ко вставляемому разделу (partition)?

И что может приводить к тому что статус "copying to tmp table" висит более 14 часов при добавлении индекса, когда места на винте , оперативной памяти достаточно и процессор не слабый?
Разделы(Partition) занимают менее 4 ГБ

Не ужели от того что мне захотелось добавить PRIMARY KEY (id,date) для добавления автоинкрементального id (bigint) в Partition table что привело к слишком медленной индексации?

CREATE TABLE `amarecord` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, <------------------ только так мне удалось добавить id AUTO_INCREMENT
`answer` tinyint(1) NOT NULL DEFAULT '0',
`dateOfCall` date NOT NULL DEFAULT '0000-00-00',

`datetime_unix` int(11) unsigned NOT NULL DEFAULT '0',
`phoneNumberA` varchar(32) NOT NULL DEFAULT '-',
`phoneNumberB` varchar(32) NOT NULL DEFAULT '-',
.......

PRIMARY KEY (`id`,`dateOfCall`) <------------------ только так мне удалось добавить id AUTO_INCREMENT

PARTITION BY RANGE ( YEAR(dateOfCall)-1)
SUBPARTITION BY HASH ( MONTH(dateOfCall))
SUBPARTITIONS 13
(
PARTITION p_2010 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p_2011 VALUES LESS THAN (2011) ENGINE = MyISAM,
PARTITION p_2012 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION p_2013 VALUES LESS THAN (2013) ENGINE = MyISAM,
)

После того как я добавил id, статус "copying to tmp table" висел более 14 часов и я остановил процесс:

CREATE INDEX PhoneA_PhoneB_Answer_Datum ON amarecord(PhoneNumberA,PhoneNumberB,answer,datetime_unix) USING BTREE;
CREATE INDEX PhoneB_Answer_Datum ON amarecord(PhoneNumberB,answer,datetime_unix) USING BTREE;

, потому так не должно быть.

И это построение выполнялось уже при PRIMARY KEY (`id`,`dateOfCall`).

Вопрос, почему наличие PRIMARY KEY (`id`,`dateOfCall`) так сильно повлияло на построение новых индексов.
И как строится индекс при Partitioning, отдельно для каждого фрагмента или для всей таблицы в целом?
Как строить отдельно индекс для конкретного раздела отдельно, если это вообще возможно?

База 270 ГБ, винт 3 ТБ, 64 MB кэш
3 млрд 275 млн записей
4 ГБ оперативной памяти.

my.ini
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=1024M
key_buffer_size=1024M
read_rnd_buffer_size=32M
sort_buffer_size=64M


Индекс строится нормально если у меня не создан PRIMARY KEY (`id`,`dateOfCall`), но мне нужен id AUTOINCREMENT, хочу Sphinx еще настроить под MySQL. а Sphinx-у надо id.

А если добавлять новые индексы, то по документации мне придется кроме полей PhoneNumberA,PhoneNumberB,answer,datetime_unix,

в конец дописывать `id`,`dateOfCal`

CREATE INDEX PhoneA_PhoneB_Answer_Datum ON amarecord(PhoneNumberA,PhoneNumberB,answer,datetime_unix,id,dateOfCall) USING BTREE;
CREATE INDEX PhoneB_Answer_Datum ON amarecord(PhoneNumberB,answer,datetime_unix,id,dateOfCall) USING BTREE;

иначе в папке tmp ничего создается а винт дико напрягается и статус "copying to tmp table" висит 14 часов

Думаю что для каждого Partition придется еще прописать пути:

DATA DIRECTORY='F:/MySQL/data/'
DATA DIRECTORY='F:/MySQL/index/'

Потому что не знаю как строится индекс один для всей таблицы или для каждого Partition поотдельности.

Каждый partition за месяц занимает менее 4 GB.

Но если статус "Repair by sorting" висит более 3 часов, осмелюсь предположить что строится общий индекс, иначе я бы увидел время намного меньшее.
А мне еще новые данные надо добавлять в проиндексированную таблицу, вот и не знаю как быстро будут добавляться через LOAD DATA INFILE

Поэтому еще раз повторю вопросы:

1) Как влияет наличие индекса(ов) на время построения других индексов?

У меня время построения первого индекса заняло 9 часов ( составной по 4 -м полям )
а время построения (!) второго индекса ( составного из 3 х полей, которые есть в первом на позициях 2,3,4 ) заняло 15 часов.

По идее второй индекс должен быстрее первого построится, а он наоборот в 2,5 раза дольше построился.

2) Как строится индекс при Partitioning, отдельно для каждого раздела (Partitition) или для всей таблицы в целом?

3) Как строить отдельно индекс для конкретного раздела отдельно, если это вообще возможно?

поправлю себя:

DATA DIRECTORY='F:/MySQL/data/'
INDEX DIRECTORY='F:/MySQL/index/'

Пришел в выводу, что придется самому вручную создавать таблицы на каждый месяц заполнять и строить индекс, тогда я смогу проиндексировать ту таблицу, которую действительно необходимо проиндексировать, а не все все все Partition. Увы, не нашел для себя выгоды: добавил 12 млн записей к 3 млрд:

ALTER TABLE table_name DISABLE KEYS;
LOAD DATA ... INFILE ... ;
ALTER TABLE table_name ENABLE KEYS;

И уже как полдня идет переиндексация, я так понял, что она займет чуть больше, чем построение первоначальных индексов ( около 24 часов)

А я хотел только переиндексации одного Partition, не более...

Я нашел один вариант, но поможет ли он мне?

WARNING:
Again, this is an undocumented, unsupported technique. Use it at your own risk, and back up your data first.

Here are the steps you’ll need to take:

1) Create a table of the desired structure, but without any indexes.
2) Load the data into the table to build the .MYD file.
3) Create another empty table with the desired structure, this time including the indexes. This will create the .frm and .MYI files you need.
4) Flush the tables with a read lock.
5) Rename the second table’s .frm and .MYI files, so MySQL uses them for the first table.
6) Release the read lock.
7) Use REPAIR TABLE to build the table’s indexes. This will build all indexes by sorting, including the unique indexes.
This procedure can be much faster for very large tables.