C, PHP, VB, .NET

Представената по-долу статия е написана във вид на реферат от студентката Павлина Темелакиева от Технически Университет – София.

Тема:

„Постигане на висока производителсност
на базата данни чрез разделяне на части“

Павлина Евгениева Темелакиева, ТУ – София , ФКСУ, гр. 63А

Преди няколко години са писани много статтии за „Откритие за отлична производителност”, където се говори срещу разбирането, че кодът на SQL е основният отговорник за производителността в  база от данни. Вместо това, беше изтъкнат факта, че добрият физически дизайн  е сред водещите компоненти за голямо бързодействие на базата от данни. В допълнение е добавено проучване на Oracle, което илюстрира как лошият дизайн е основният виновник за забавянето на базата. В годините след това все още се застава зад мнението, че вески администратор на бази данни, който иска високо бързодействие на базата си трябва да инвестира в добре обмислен и лесен за разбиране физически дизайн, за да може да направи крайния потребител доволен, а не търпеливо изчакващ изпълнението на заявката си( което търпение в много случаи се превръща в изнервяне).
В новите версии на MySQL( от 5.1)  има потенциално оръжие за високо бързодействие на много натоварени обемни бази от данни, наречено средство за разделяне.

Какво е разделяне на части?
Това е техника при физическия дизайн на базата от данни, с която много администратори са добре запознати. Въпреки че разделянето може да бъде използвано за реализацията на много обекти като основната цел е да се намали количеството на четенето на данни за някои операции в SQL, така че цялото време за отговор да бъде намалено.

Има две основни форми на разделяне на части:

Хоризонтално Разделяне – тази форма е разделяне на сегменти на редовете в таблицата. Така да се формират различни групи от физически разположени в редове данни, към които може да се обърнем иднивидуално( една част) или колективно( една към всички части). Всички колони, определени за някоя таблица могат да бъдат намерени във всяка поредица от части и така нито един атрибут на таблицата няма да се изгуби. Пример за хоризонтално разделяне може да бъде таблица, която съдържа 10-годишна история с данни за фактуи , които биват разделени на 10 различни части, всяка от която съдържа  количество данни за една година.

Вертикално Разделяне – тази схема за разделяне обикновено е използвана, за да намали широчината на  целевата таблица като някоя таблица се съедини верикално като по този начин само по-съществени колони от данни ще останат, като всяка част ще съдържа всички колони. Пример за вертикално разделяне може да бъде таблица, чиито колони съдържат голямо количество тескт или са от тип BLOB и не са адресирани често. Те биват разбити на две таблици, които съответно съдържат колоните с най-честа адресация в едната и рядко адресиран текст или BLOB данни в другата.

Преди производителите на бази данни да започнат да създават този метод (най-вече хоризонтален), администраторите на бази данни и модераторите трябваше ръчно да създават отделни структури от таблици, за да запазват желаните части, които или записват излишък от данни, или са свързани заедно, за да сформират един логически родителски обект. Тази практика  е остаряла за по-голямата част от хоризонатлното разделяне, въпреки че все още е подходящо за вертикално разделяне.

Разделяне в MySQL
Едно от нововъведенията в MYSQL е поддръжката на хоризонтално разделяне. Добрите новини за MYSQL и новото попълнение са, че всички основни форми на разделяне се поддържат.

Обхват: това състояние позволява на администраторите да определят специфичен обхват, в който данните са записани. Например даден администратор може да създаде разделена на части таблица, която е сегментирана на три отделни части, които съдържат данни за 1980-та, 1990-та и всичко след 2000 година.
Хеш: това състояние позволява на администраторите да разделят данните, базирани на изчислен хеш ключ, който е определен от една или повече колони в таблицата като крайната цел е равновесно разпределение на данните в частите. Например, даден администратор може да създаде разделена таблица, която има 10 части, базирани на първичния ключ на таблицата.
Ключ: специална форма на Хеша, където MySQL гарантира разпределение на данните през системно- генериран хеш ключ.
Лист: този метод позволява да се сегментират данни, базирани на предефиниран лист от стойности, който самият администратор определя. Например даден администратор може да създаде разделена таблица, която създаде 3 части, формирани от данните за години 2004, 2005, 2006.
Комбиниран(composite): това финално състояние за разделяне позволява на даден администратор да изпълнява подразделяне, когато таблицата първоначално е разделяна. Например обхватното разделяне,след което всяка част бива сегментирана равномерно от друг метод(например хеш).
Паралелно изпълнение на заявки с релционни оператори: освен разделянето на таблици има други – физически – методи за разделяне. В следните параграфи ще бъдат разгледани някои от тях.

Всъщност разделянето на данни е първата стъпка в разделянето на части на релационните графи. Основната идея е да се използват паралелни потоци от данни, вместо да се пишат отделни оператори за това. Този подход позволява използването на непроменяни, съществуващи практики, чрез които да се приложат релационните оператори паралелно. Всеки релационен оператор има серия от входни портове, на които всеки ред от таблицата „пристига” и изходящ порт, от който излиза целият поток от данни, пратени от даден оператор. Паралелното поддържане на поток от данни се реализира чрез разделяне и съединяване на потоци от данни в тези последователности от портове. Този метод позволява използването на съществуваща последователност от оператори, за да се поддържа паралелно управление.

Разглеждаме обхождане на релация А, която е разделена в рамките на три диска във фрагменти съответно A0, A1 и A2. Това обхождане може да бъде осъществено като обхождане на три оператора за сканиране, които изпращат отговрите си до общ оператор за изход. Изпълнителят на паралелната заявка създава троен сканиращ процес(фиг. 1) и и ги насочва да почерпят ресурс от три различни входни потока(A0, A1, A2). Той също ги насочва към общ възел. Всеки скан може да работи на отделен процесор или диск. И така първият основен паралелен оператор е merge, който може да комбинира няколко даннови потока в единствен последователен поток. Операторът за присъединяване (merge) се грижи да насочи данните в една точка.

Ако многоетаптна паралелна операция трябва да се изпълнява паралелно, то данните отново трябва да бъдат насочени(разделени) на няколко независими потока. Т.нар split-operator се използва, за да раздели или да клонира поток от редове в таблицата, произведени от релационните оператори. Операторът за разделяне превръща една или повече величини от резултантните редове в таблиците в поредица от процеси(фиг. 2).

Като пример, засягащ двата разделящи оператора е показана следната SQL заявка  и фигура 3:

Да предположим, че трите процеса са използвани, за да изпълнят join оператора, а 5 други процеса изпълняват двата сканиращи оператора – три сакниращи части за релация А, докато други 2 части се сканират в релаация В. Всяка от трите сканиращи части от релация А ще има същия обединяващ оператор, изпращащ всички редове в интервала „A-H” към порт 1 на join процес 0, всички „I-Q” към порт 1 на join процес 1 и всички „R-Z” към порт 1 на  join процес 2.

Подобно на това двата B сканиращи възли имат същия обединяващ оператор с изклчючение на това, че техните изходи са обединени от порт 1, а не от 0 за всеки join процес. Всеки join  процес вижда всяка поредица от входни потоци А(левите сканиращи възли) от порт 0 и съответно поредица от входни потоци Б(десните сканиращи възли) от порт 1. Изходите на всеки join са поред разделени на три потока, базирани на  някакъв разделителен критерий на релацията C.  За да се изясни този пример, разглеждаме първия join процес във фигура 3(процесор 5, портове 0 и 1, процес 3). Той ще получи всички релации А и редовете в интервала  „А-Н”, обединени в един поток на порт 0 и всички редове „А-Н” обединени в един поток от порт 1. Ще ги присъедини, използвайки хеш-join, sort-merge join или друг.

Ако всеки от тези процеси са на независим процесор с независимо записващо устройство, ще има малки смущения измежду тях. Операторът за разделяне тук е само даден като пример. Други оператори за разделяне биха могли да дублират входен поток информация или да го раздели чрез хеш.

Операторите за просъединяване и разделяне имат значителен контрол и буферна конструкция. Това предотвратява някой оператор да стигне твърде далеч при изчисляването. Когато буферът на оператора за разделяне се препълни, той забавя релационния оператор докато се освободи място на изхода за данните.

Има много ползи от разделянето на части, но двете основни предимства са:

Растяща производителност: по време на претърсващите операции – оптимизаторът на MySQL знае коя част съдържа данните, които ще задоволят някоя специфична заявка и ще има достъп само до оне необходими части по време на изпълнението на самата заявка. Например една таблица с един милион редове може да бъде разбита на 10 различни части с обхватния метод, така че всяка част да съдържа 100,000 реда. Ако заявката разбере, че ? трябват данни от само една от частите и все пак е необходимо преглеждане на цялата таблица, то ще бъдат сканирани само 100,000 реда вместо 1 милион. Очевидно трябва да бъде по-лесно за MySQL да обходи 100,000 реда вместо 1 милион, следователно заявката ще се изпълни много по-скоро. Същата полза произлиза от необходимостта да бъдат индексирани и отделните шасти, на които бива разделена таблицата, т.е. създават се локални индекси за отделните таблици. В крайна сметка става възможно маркирането на разделените таблици  през различни физически пътища, специфизирайки  директорията на отделните части. Това позволява физическото I/O  съравнование да бъде намалено, когато множество части бъдат достигнати по едно и също време.

Опростено  ръководене на данните – разделянето позволява на администраторите да имат повече контрол над това как данните са управлявани във вътрешността на базата. Създавайки части, администраторъ опростява начинът на изпълнение на някои операции с данните. Например, той може да изтрие специфични части в една таблица, докато останалите части остават недокоснати. По-натам, частите се поддържат автоматично от MySQL, така че администраторът не трябва ръчно да разделя и поддържа хоризонтално разделяне на части за някоя таблица. Например администраторът на базата може да създаде таблица с история, която запазва данни за клиенти, които са разделени според някакъв годишен критерий. Тези части са автоматично приложени и разделени от сървъра на базите данни с никаква намеса да администратора.

Разделянето на части в действие:
От гледна точка на дизайна за подобряване на бързодействието на базата основно сме заинтересовани . Използвайки подходящо разделянето на части, драматично може да се подобри работата на базата. Следният пример ще илюстрира това:
За да видим ползата от разделянето на части, ще създадем идентична MyISAM таблици, които съдържат информация, свързана с дати, но нека да отделим една, а другата да оставим като стандартна хийп таблица. За нашата отделена таблица ще направим разделяне, базирано на полето година:

CREATE TABLE part_tab (
	c1 int default NULL ,
	c2 varchar(30) default NULL ,
	c3 date default NULL ,
) engine=myisam
PARTITION BY RANGE (year(c3)) (
	PARTITION p0 VALUES LESS THAN (1995),
	PARTITION p1 VALUES LESS THAN (1996) ,
	PARTITION p2 VALUES LESS THAN (1997) ,
	PARTITION p3 VALUES LESS THAN (1998) ,
	PARTITION p4 VALUES LESS THAN (1999) ,
	PARTITION p5 VALUES LESS THAN (2000) ,
	PARTITION p6 VALUES LESS THAN (2001) ,
	PARTITION p7 VALUES LESS THAN (2002) ,
	PARTITION p8 VALUES LESS THAN (2003) ,
	PARTITION p9 VALUES LESS THAN (2004) ,
	PARTITION p10 VALUES LESS THAN (2010),
	PARTITION p11 VALUES LESS THAN MAXVALUE
);

Трябва да се забележи това, че създадохме части за специфична година и завършихме с една, побираща в себе си всички част, която да събере всички данни, които не попдат в никоя друга част. Сега нека създадем таблица с огледален образ в MyISAM, която да не е разделена:

create table no_part_tab(
	c1 int(11) default NULL,
	c2 varchar(30) default NULL,
	c3 date default NULL
) engine=myisam;

Сега нека създадем процедура, която ще запълни нашата разделена таблица с 8 милиона реда, които разпределят данните по равно между двете части. Веднъж запълнена, ще сложим същите данни в нашата клонирана, неразделена на части MyISAM таблица:

DELIMITER //

CREATE PROCEDURE load_part_tab()
BEGIN
	DECLARE v INT DEFAULT 0;
	WHILE v < 8000000
	DO
		INSERT INTO part_tab
		VALUES (v,'testing partitions',
				adddate('1995-01-01',
				(rand(v)*36520) mod 3652));
		SET v = v + 1;
	END WHILE;
END
//
Query OK, 0 rows affected (0.00 sec)

DELIMITER ;

CALL load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)

INSERT INTO no_part_tab
SELECT * FROM part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000  Duplicates: 0  Warnings: 0

С вече готовите таблици нека да видим какъв ще бъде резултатът от двете таблици, последвани от пояснения – неразделяната на части първо и след това тази, която разделихме:

SELECT count(*)
FROM no_part_tab
WHERE c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (38.30 sec)

SELECT count(*)
FROM part_tab
WHERE c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (3.88 sec)

Лесно се заблеязва, че достъпът до разделената таблица доставя до 90% по-бърз отговор в сравнение с неразделената.

В заключение можем да изтъкнем още някои предимства на разделянето на части:

• Всички акумулатори за запазване поддържат разделяне(MyISAM, InnoDB, Archive и т.н.);
• Поддържането на индекси за разделени таблици включва локални индекси, които копират всяка част в схема 1:1. С други думи, ако разделената таблица има 10 части, то локалните индекси за тази таблица също ще съдържат 10 части;
• Всички SHOW команди поддържат отговор от разделени таблици и индекс метадата;

Използвани източници:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
http://msdn.microsoft.com/en-us/library/aa177979(SQL.80).aspx
http://www.tdan.com/i016fe03.htm