Previous Entry Share Next Entry
partition tables
1
white_thesis wrote in ru_mysql
У меня появился повод поразбираться с этой фичей. На слабой машине нужна работа с большой таблицей 120кк строк, 14G байт . Записи - объекты с географическими координатами, выборка обычно делается по очень компактной области координат и иногда по идентификатору объекта. Таблица используется только на чтение. Мускуль 5.5

То есть просто напрашивается на разделение на N таблиц по диапазону одной из координат.
create table qwe
(
ident int not null comment 'уникальный код объекта'
lat decimal (10,8) not null comment 'широта',
lng decimal (11,8) unsigned not null comment 'долгота',
...
unique (ident),
index (lat),
index (lng)
)
partition by range (lat)
...

CREATE TABLE `total` (
`ra` decimal(11,8) unsigned NOT NULL COMMENT 'deg; right ascension at epoch J2000.0 (ICRS)',
`de` decimal(10,8) NOT NULL COMMENT 'deg; declination epoch J2000.0 (ICRS)',
`ra_mas` int(10) unsigned NOT NULL COMMENT 'mas; right ascension at epoch J2000.0 (ICRS)',
`de_mas` int(11) NOT NULL COMMENT 'mas; south pole distance epoch J2000.0 (ICRS)',
`magm` smallint(5) unsigned DEFAULT NULL COMMENT '(2) millimag ; UCAC fit model magnitude',
`maga` smallint(5) unsigned DEFAULT NULL COMMENT '(2) millimag ; UCAC aperture magnitude',
`sigmag` tinyint(3) unsigned DEFAULT NULL COMMENT '(3) 1/100 mag; error of UCAC magnitude',
`objt` tinyint(3) unsigned NOT NULL COMMENT '(4) object type',
`cdf` tinyint(3) unsigned NOT NULL COMMENT '(5) combined double star flag',
`sigra` tinyint(3) unsigned DEFAULT NULL COMMENT '(6) mas ; s.e. at central epoch in RA (*cos Dec)',
`sigdc` tinyint(3) unsigned DEFAULT NULL COMMENT '(6) mas ; s.e. at central epoch in Dec',
`na1` tinyint(3) unsigned NOT NULL COMMENT 'total # of CCD images of this star',
`nu1` tinyint(3) unsigned NOT NULL COMMENT '(7) # of CCD images used for this star',
`cu1` tinyint(3) unsigned NOT NULL COMMENT '# catalogs (epochs) used for proper motions',
`cepra` smallint(5) unsigned NOT NULL COMMENT '0.01 yr ; central epoch for mean RA, minus 1900',
`cepdc` smallint(5) unsigned NOT NULL COMMENT '0.01 yr ; central epoch for mean Dec,minus 1900',
`pmrac` smallint(6) DEFAULT NULL COMMENT '(8) 0.1 mas/yr; proper motion in RA*cos(Dec)',
`pmdc` smallint(6) DEFAULT NULL COMMENT '0.1 mas/yr; proper motion in Dec',
`sigpmr` tinyint(3) unsigned DEFAULT NULL COMMENT '(9) 0.1 mas/yr; s.e. of pmRA * cos Dec',
`sigpmd` tinyint(3) unsigned DEFAULT NULL COMMENT '(9) 0.1 mas/yr; s.e. of pmDec',
`pts_key` int(10) unsigned DEFAULT NULL COMMENT '(10) 2MASS unique star identifier',
`j_m` smallint(6) DEFAULT NULL COMMENT 'millimag ; 2MASS J magnitude',
`h_m` smallint(6) DEFAULT NULL COMMENT 'millimag ; 2MASS H magnitude',
`k_m` smallint(6) DEFAULT NULL COMMENT 'millimag ; 2MASS K_s magnitude',
`icqflg_j` tinyint(3) unsigned DEFAULT NULL COMMENT '(11) 2MASS cc_flg*10 + ph_qual flag for J',
`icqflg_h` tinyint(3) unsigned DEFAULT NULL COMMENT '(11) 2MASS cc_flg*10 + ph_qual flag for H',
`icqflg_k` tinyint(3) unsigned DEFAULT NULL COMMENT '(11) 2MASS cc_flg*10 + ph_qual flag for K_s',
`e2mpho_j` tinyint(3) unsigned DEFAULT NULL COMMENT '(12) 1/100 mag; error 2MASS J magnitude',
`e2mpho_h` tinyint(3) unsigned DEFAULT NULL COMMENT '(12) 1/100 mag; error 2MASS H magnitude',
`e2mpho_k` tinyint(3) unsigned DEFAULT NULL COMMENT '(12) 1/100 mag; error 2MASS K_s magnitude',
`apasm_b` smallint(6) DEFAULT NULL COMMENT '(13) millimag ; B magnitude from APASS',
`apasm_v` smallint(6) DEFAULT NULL COMMENT '(13) millimag ; V magnitude from APASS',
`apasm_g` smallint(6) DEFAULT NULL COMMENT '(13) millimag ; g magnitude from APASS',
`apasm_r` smallint(6) DEFAULT NULL COMMENT '(13) millimag ; r magnitude from APASS',
`apasm_i` smallint(6) DEFAULT NULL COMMENT '(13) millimag ; i magnitude from APASS',
`apase_b` tinyint(4) DEFAULT NULL COMMENT '(14) 1/100 mag ; error of B magnitude from APASS',
`apase_v` tinyint(4) DEFAULT NULL COMMENT '(14) 1/100 mag ; error of V magnitude from APASS',
`apase_g` tinyint(4) DEFAULT NULL COMMENT '(14) 1/100 mag ; error of g magnitude from APASS',
`apase_r` tinyint(4) DEFAULT NULL COMMENT '(14) 1/100 mag ; error of r magnitude from APASS',
`apase_i` tinyint(4) DEFAULT NULL COMMENT '(14) 1/100 mag ; error of i magnitude from APASS',
`gcflg` tinyint(3) unsigned DEFAULT NULL COMMENT '(15) Yale SPM g-flag*10 c-flag',
`icf` tinyint(3) unsigned DEFAULT NULL COMMENT '(16) FK6-Hipparcos-Tycho source flag',
`icf_ac2` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) AC2000 catalog match flag',
`icf_agk2` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) AGK2 Bonn catalog match flag',
`icf_akg2` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) AKG2 Hamburg catalog match flag',
`icf_zone` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) Zone Astrog. catalog match flag',
`icf_bb` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) Black Birch catalog match flag',
`icf_lick` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) Lick Astrog. catalog match flag',
`icf_npm` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) NPM Lick catalog match flag',
`icf_spm` tinyint(3) unsigned DEFAULT NULL COMMENT '(17) SPM YSJ1 catalog match flag',
`leda` tinyint(3) unsigned DEFAULT NULL COMMENT '(18) LEDA galaxy match flag',
`x2m` tinyint(3) unsigned DEFAULT NULL COMMENT '(19) 2MASS extend.source flag',
`rnm` int(10) unsigned NOT NULL COMMENT '(20) unique star identification number',
`zn2` smallint(5) unsigned DEFAULT NULL COMMENT '(21) zone number of UCAC2 (0 = no match)',
`rn2` int(10) unsigned DEFAULT NULL COMMENT '(21) running record number along UCAC2 zone',
KEY `ra` (`ra`),
KEY `de` (`de`),
KEY `rnm` (`rnm`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
/*!50100 PARTITION BY KEY (ra)
PARTITIONS 100 */


На практике я, разумеется, столкнулся с непонятным поведением.
К примеру, мускуль не желает делать дробную таблицу, покуда есть unique индекс по идентификатору. Просто index - приемлет. Не, ну в данном случае мне никто не мешает заменить тип индекса. Это мелочи, хотя и странные.

Что гораздо хуже - многократно выросли требования к оперативке при добавлении записей из старой таблицы (обычной) в новую (сегментированную). То есть до такой степени, что данные добавить невозможно. 2 гига опертивки на компе, всё отдано для mysqld - ему мало. Думаю, что при выборке будет та же проблема.

Видимо я не понимаю как мускуль управляется с такими таблицами и в частности с индексами, НЕ использованными для сегментирования.

http://dev.mysql.com/doc/refman/5.5/en/partitioning.html перечитал, ответов либо не нашел либо не понял.

  • 1

Как записываете? Запрос покажите


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

insert into total select * from zn0
insert into total select * from zn10
...
и так далее

Я вот про это:

> Что гораздо хуже - многократно выросли требования к оперативке при добавлении записей из старой таблицы (обычной) в новую (сегментированную). То есть до такой степени, что данные добавить невозможно. 2 гига опертивки на компе, всё отдано для mysqld - ему мало. Думаю, что при выборке будет та же проблема.

Что такое select * from zn0? Это старая 14G таблица? Куда вы раньше из неё все строки разом выбирали?

Вообще разбиения работают хорошо, когда можно обратиться только к одному из них вместо всех сразу. То есть помогают в случае SELECT ... WHERE ... или UPDATE ... WHERE ...

И это, MyISAM не нужно использовать. С 14 гигами тем более.

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

Нет, почему? Тупо искать одну среди миллиона строк дешевле, чем искать ту же строку среди миллиарда.

а помянутая ниже file_per_table такого эффекта не дает?

Edited at 2016-06-17 07:51 pm (UTC)

А как она даст такой эффект? Была таблица на миллиард строк в shared tablespace, переместили её в individual tablespace. Миллиард как был миллиардом, так и остался.

С разбиениями же всё прозрачно: пользователь общается с одной таблицей, а для MySQL - это условно несколько.

а, ну да. спасибо, что-то не сообразил сразу.

Нет, старая таблица разбита вручную на 18 кусков по широте
zn0 - один из них

А кого лучше?

Inno в этом смысле меня пугает. tablespace отчаянно пухнет от каждого чиха. Вот сейчас у меня ни одной таблицы innodb нет, но файл для tablespace аж 178 мегабайт.

>> когда можно обратиться только к одному из них вместо всех сразу.

Как раз тот самый случай. Выборки будут почти всегда вида
where (ra between ... ) and (de between ...)
То есть затрагивать 1, а в редких случаях 2 соседствующие части.

Ну, иногда будет запрос по идентификатору.

Я правильно понимаю, что индекс по прочим колонкам - не используемым в разбиении таблицы - в каждом сегменте таблицы будет свой собственный, независимый?

InnoDB лучше. Опцию innodb_file_per_table нужно использовать, тогда shared tablespace пухнуть не будет.

> Я правильно понимаю, что индекс по прочим колонкам - не используемым в разбиении таблицы - в каждом сегменте таблицы будет свой собственный, независимый?

В общем да. То есть он будет создан для каждого разбиения, но в SHOW INDEXES, например, он будет интерпретироваться как единый. И я подозреваю, что оптимизатором тоже. В случае MyISAM это вообще наглядно видно:

sveta@Thinkie:~/build/mysql-5.7/mysql-test$ ls -l var/mysqld.1/data/test/
total 2036
-rw-rw---- 1 sveta sveta 12517 Haz 17 22:17 total.frm
-rw-rw---- 1 sveta sveta 508 Haz 17 22:17 total.par
-rw-rw---- 1 sveta sveta 0 Haz 17 22:17 total#P#p0.MYD
-rw-rw---- 1 sveta sveta 1024 Haz 17 22:17 total#P#p0.MYI
-rw-rw---- 1 sveta sveta 0 Haz 17 22:17 total#P#p10.MYD
-rw-rw---- 1 sveta sveta 1024 Haz 17 22:17 total#P#p10.MYI
-rw-rw---- 1 sveta sveta 0 Haz 17 22:17 total#P#p11.MYD
-rw-rw---- 1 sveta sveta 1024 Haz 17 22:17 total#P#p11.MYI
-rw-rw---- 1 sveta sveta 0 Haz 17 22:17 total#P#p12.MYD
...

*.MYI - это файлы индексов.

А почему бы не использовать Spatial?

В данном конкретном случае потому, что я не в курсе как это применять.
Кажется, это имеет отношение к выборке именно по двумерным координатам, но до сих пор я в детали не вник.

Для начала я хочу избежать единого файла-таблицы размером в 14 гигабайт

  • 1
?

Log in