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

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

[mysqld]
port=3306

basedir="C:/MySQL/"
datadir="D:/MySQLData/"
tmpdir="D:/MySQLData/tmp"

default-character-set=cp1251
default-storage-engine=MyISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=128
max_allowed_packet=32M
query_cache_size=512M
table_cache=256
tmp_table_size=512M
thread_cache_size=64
thread_concurrency=8

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


Особенно интересует параметр sort_buffer_size, можно его также как и
myisam_sort_buffer_size=1024M
key_buffer_size=1024M

выставить в 1024M?

Всего установлено 4 GB памяти, кэш HDD = 64 MB

Почему спрашиваю, переустановил mysql-5.5.32-winx64, ini файл нечаянно удалил, задал такие настройки и при построении индекса после 2-3 минут загрузка процессора падает с 25 до 1-2%, при этом сильно грузится HDD. более часа статус висит copying to tmp table.
У меня всё время так - загружен процессор на 25% ну и обьем занятой памяти то увеличивался на 1GB то уменьшался, и индексация выполнялась за 9 часов.

А после переустановки - папка tmp пуста , хотя при старте MySQL должны создастся временные файлы. Думаю в папке tmp кроется проблема
Размер таблиц около 4 GB

Места на диске предостаточно.
Как убедиться что tmpdir действительно задействован, если размер папки = 0?

Почему спрашиваю - сначала используется память - по чуть чуть достигает предела в 1 ГБ,  - потом обьем занятой памяти падает до первоначального ,  загрузка процессора падает с 25 до 2% - потом грузится сильно винт. Статус процесса - "copying to tmp table" - и так больше часа.

Может это и правильно, сначала все 4 гиговые таблицы (а их более 50-ти) куда-то скопирует и потом только перейдет в статус  "Repair by sorting"

"PARTITIONING может также увеличить скорость работы с большими таблицами (при условии, что работает pruning)"
А что такое pruning?

"PARTITINONING приведет к тому, что индексы будут создаваться последовательно над каждым куском"

То есть будет строится индекс для отдельного раздела (partition) или над всей виртуальной таблицей?
Почему спрашиваю - пришел сегодня посмотреть: вставились мои 2000 файлов или нет, смотрю - 1254 файл, в процессах статус висит 17 минут, это получается 17 минут он добавляется в таблицу, странно как то, а впереди еще 746 файлов!

В самом начале, вот что проиcходило:
первый файл вставлялся около 35 секунд . 31-ый файл- более минуты.
Но потом  ведь должен заполняться новый раздел и время вставки не должно нарастать:

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,
....
PARTITION p_2039 VALUES LESS THAN (2039) ENGINE = MyISAM);

Почему 1254 файл так долго вставлялся?
Можно настроить создание индекса для каждого раздела(фрагмента) или построение делается автоматически?

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

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

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



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, отдельно для каждого фрагмента или для всей таблицы в целом?
Как строить отдельно индекс для конкретного раздела отдельно, если это вообще возможно?

Отредактированно VladimirPivovar (05.07.2013 17:36:50)

Пришел в выводу, что придется самому вручную создавать таблицы на каждый месяц заполнять и строить индекс, тогда я смогу проиндексировать ту таблицу, которую действительно необходимо проиндексировать, а не все все все 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.


http://my.safaribooksonline.com/book/-/ … /id3544352

Приветствую коллеги!
Поскольку тут про партиции - не стал создавать новую тему. Вопросы вот такие у меня )

1. Как создать разбитую на партиции таблицу с разбиением по месяц+год? Везде где читаю разбивают либо по годам, либо по месяцам (то есть 12 партиции, в каждую из которых попадают строки всех январей всех лет, например). А нужно именно 2013_01, 2013_02.... 2015_12 и т.п.
2. Поле с датой для такого разбивания должно быть какого типа? DATE? я так понмиаю, где то прочитал, что timestamp не подходит без дополнительных танцев?
3. чтобы не придумывать алгоритмов для постоянного обновления списка партиций ALTER TABLE ADD PARTITION, можно ли их при создании таблицы нагенерить далеко в будущее? Или какие то ограничения есть?

Заранее спасибо

1. Не совсем понял про поле. Зачем отдельное, какого оно типа? Как будет выглядеть запрос создания таблицы с партициями по этому полю?

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

А сам запрос на создание как будет выглядеть, если поле, по корорым делаются партиции типа DATE?

PARTITION BY RANGE ( _вот тут что_? ) (
    PARTITION p2010_1 VALUES LESS THAN (2010-01),
    PARTITION p2010_2 VALUES LESS THAN (2010-02),
    PARTITION p2010_3 VALUES LESS THAN (2010-03),
    .....
    PARTITION p2037_1 VALUES LESS THAN (2037-01),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Отредактированно Shopen (16.04.2015 18:16:58)

vasya написал:

create table t (
id int not null,
d date not null)
partition by range columns(d) (
partition p0 values less than ('2015-01-01'),
partition p1 values less than ('2015-02-01'),
partition p2 values less than ('2015-03-01'),
partition p3 values less than ('2015-04-01'),
partition p4 values less than ('2015-05-01'),
partition p5 values less than maxvalue
);

Mysql на такой запрос ругается:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'columns(d) (

Эм.. у меня более старая версия - 5.1.50-community-log

Вот так (как по идее надо) не работает тоже:

VALUES value must be of same type as partition function near '),

Отредактированно Shopen (20.04.2015 15:34:06)