SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 16.06.2016 17:25:41

w-thesis
Участник
Зарегистрирован: 16.06.2016
Сообщений: 5

партиции и первичный ключ

Hello

Решило я поразбираться с partitioning, т.к. есть повод: надо на слабой машинке задействовать таблицу на 10 гигабайт с 120кк записей.
Мускуль 5.5, винда.

Почитал мануал, покурил форумы. Вроде всё понятно.
На практике обнаружилась пара проблем.
Мускуль упорно требует, чтобы все используемые для разбиения столбцы были увязаны в primary key.
И это при любом методе разбиения - hash, key, range - без разницы.
Хотя из http://dev.mysql.com/doc/refman/5.5/en/ … oning.html этого не следует.
И кинуть эти колонки в первичный ключ я не могу - условию уникальности они не удовлетворяют.

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

Вопрос: так и должно быть или это мои местные криворукие проблемы?

Неактивен

 

#2 16.06.2016 22:33:29

w-thesis
Участник
Зарегистрирован: 16.06.2016
Сообщений: 5

Re: партиции и первичный ключ

UPD
Методом тыка было установлено, что мешало наличие ключа UNIQUE по одному из полей.
Притом, что ключ INDEX по этому же полю - не мешает.
Дурдом какой-то.

Проблема с нехваткой памяти осталась.

SQL query:  INSERT INTO total
SELECT * FROM zs80 ORDER BY ra, de
MySQL said:  #5 - Out of memory (Needed 16384 bytes)

В zs80 500к записей всего-то.
Памяти мускуля отдано более, чем много.

Неактивен

 

#3 17.06.2016 22:54:02

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: партиции и первичный ключ

Может быть MySQL отдали больше памяти, чем есть в наличии на машине и поэтому так получается. Попробуйте уменьшить память, которую отдаете MySQL.

Ограничения на ключ описаны здесь:
https://dev.mysql.com/doc/refman/5.7/en … -keys.html

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

Неактивен

 

#4 24.06.2016 02:59:32

w-thesis
Участник
Зарегистрирован: 16.06.2016
Сообщений: 5

Re: партиции и первичный ключ

С unique индексами разобрался, спасибо.

Пробовал менять объем отданной мускулю памяти. Без улучшения. Системный монитор показывает, что свободная еще есть, более 300 мег свободно.

Таблица содержит объекты с координатами, выборка идет по диапазону координат (участок).
Для ускорения выборки нужно, чтобы объекты с близкими координатами располагались в одном разделе и поближе друг к другу. То есть partition by range (координата).

Вроде бы всё работает, кроме одного: выборка по ID объекта выполняется неприемлимо долго. Разбиение range, ID объекта в разбиении не участвует, индекс ID индивидуален для каждого слайса. Поиск по ID  уходит в себя минут на 5 ожесточенного журчания диском.

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

Неактивен

 

#5 24.06.2016 08:15:04

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: партиции и первичный ключ

А большой прирост производительности дает Partitioning? Что если от него отказаться?

Неактивен

 

#6 24.06.2016 15:30:53

w-thesis
Участник
Зарегистрирован: 16.06.2016
Сообщений: 5

Re: партиции и первичный ключ

Выборка по случайной площадке 12*12 минут
для сплошной таблицы: ~1 сек
для разбитой на 360 кусков по 1 градусу:  ~0.1 сек
Разница есть, хотя не принципиальная

Неактивен

 

#7 24.06.2016 15:46:25

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: партиции и первичный ключ

Разница в 10 раз - серьзный аргумент. Нужно оценивать производительность в целом, учитывая другие запросы.

Неактивен

 

#8 24.06.2016 15:59:56

w-thesis
Участник
Зарегистрирован: 16.06.2016
Сообщений: 5

Re: партиции и первичный ключ

В целом пока не могу, в ближайшее время это будет использоваться крайне редко и только оператором в качестве справочника. ля этой задачи время выборки в 1 секунду вполне нормально.
Будет ли это использоваться для других задач - пока не ясно, но несомненно зависит от многих факторов - в том числе достигнутого быстродействия.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson