Le partitionnement : comment s'y prendre

Une base de données ou un sous-ensemble d'une base de données peut être réparti sur plusieurs serveurs ; chaque partie ainsi constituée est appelée une partition. Stratus et Teradata ont développé les premières bases de données à traitement massivement parallèle (MPP) avec leur propre matériel spécialisé. Oracle a créé la première base de données MPP portable base de données MPP fonctionner sur du matériel provenant de différents fournisseurs. Oracle, Mainframe DB2 et Snowflake utilisent une architecture « shared-everything », dans laquelle chaque instance de base de données a accès à l'intégralité de la base de données partitionnée et où la propriété dynamique des blocs de données doit être gérée par un gestionnaire de verrous distribués complexe.

D'autres fournisseurs adoptent une approche plus simple de base de données fédérée, dans laquelle chaque instance de base de données n'a accès qu'à sa propre partition. Vous pouvez recourir au partitionnement sur un seul nœud si vous avez des requêtes limitées par les opérations d'E/S. Dans ce cas, vous pouvez répartir explicitement vos données entre différents canaux d'E/S et périphériques de stockage.

Par exemple, si vous disposez d'un serveur puissant qui doit effectuer un balayage complet d'une très grande table, vous pouvez partitionner le serveur sur plusieurs disques physiques afin de contourner les contraintes physiques d'un seul périphérique, telles que la bande passante du bus et les temps de recherche initiaux lents dus au temps de rotation des disques physiques. Le partitionnement répartit les opérations d'E/S afin d'optimiser la processeur et les temps de balayage globaux.

Avantages du partitionnement des données

Les principales raisons qui poussent les entreprises à partitionner une base de données physique sont l'amélioration des performances, de la disponibilité et équilibrage de charge. Les performances sont améliorées grâce à l'exécution parallèle des requêtes sur un plus grand nombre de processeurs qu'un seul serveur. La disponibilité est améliorée en limitant l'impact d'une panne de serveur à la seule partition de données qu'il gère. Les serveurs de bases de données prenant en charge le traitement parallèle créent requête qui répartissent la charge de travail entre les nœuds de serveurs en réseau afin de répartir le traitement et de renvoyer les résultats plus rapidement.

Par exemple, si vous disposez d'une application de tarification mondiale qui doit être très réactive, vous pouvez répartir ses données entre trois centres de données géographiquement distincts. Comme une base de données locale contient les tarifs régionaux, elle sera plus réactive qu'une base de données mondiale unique, et la disponibilité s'en trouvera améliorée, car la maintenance effectuée en dehors des heures de pointe sur le serveur américain n'aura pas d'incidence sur la disponibilité des serveurs en Europe, au Moyen-Orient et en Afrique (EMEA) ainsi qu'en Asie.

Lorsque vos applications deviennent trop gourmandes pour un seul serveur, vous n'avez pas besoin d'en provisionner un plus puissant pour suivre le rythme. Si votre système de base de données fonctionne sur un système en cluster, l'ajout d'un nœud supplémentaire plus petit peut s'avérer plus rentable. Dans le cloud, vous pouvez provisionner des serveurs et du stockage à la demande faire face aux pics de charge.

gestion des données dans le cloud

On-premise data centers must provision servers with physical constraints such as the data center’s size. Public cloud platforms from Amazon, Microsoft, and Google, provide more capacity than an in-house data center. Actian Data Platform runs on public cloud platforms; storage is decoupled from compute resources, so compute power can grow and contract based on demand, independent of physical partitions.

Quand partitionner une table

Le partitionnement complique la gestion des bases de données ; vous ne devriez donc y avoir recours que si vous ne parvenez pas à obtenir des performances suffisantes sans partitionnement. Utilisez le partitionnement lorsque vous devez adapter les performances de la base de données pour support volumes de transactions et des requêtes très volumineuses.

Stratégies de partitionnement

Il existe de nombreuses façons de partitionner les données, en fonction des besoins de l'application et du contenu des données. Si la performance est l'objectif principal, il convient de répartir la charge de manière uniforme afin d'optimiser le débit. Par exemple, vous pouvez configurer 64 partitions par serveur et utiliser un hachage calculé pour répartir les données de manière homogène, ou bien les répartir selon un schéma de type « round-robin ». Si les données ont une valeur de clé naturellement aléatoire avec une cardinalité élevée, elles peuvent être regroupées en plages de valeurs par partition. En général, les données sont partitionnées horizontalement pour répartir la charge de travail plusieurs serveurs. Dans le cas d'un partitionnement vertical, vous pouvez décider de diviser vos données en trois régions globales, de sorte à disposer d'une table de haut niveau contenant des valeurs globales. Des tables détaillées situées en dessous, correspondant à une région géographique, peuvent support l'autonomie support .

Méthodes de partitionnement

Les bases de données support ou les configurations MPP proposent plusieurs méthodes de partitionnement. Voici une sélection de ces méthodes :

• Partitionnement par plage – divise les lignes en fonction d'une plage de valeurs de clé. Si votre application effectue des analyses des ventes par région, il peut être judicieux de répartir les données par plages de codes postaux.
• Partitionnement par liste – organise les données en fonction d'une liste de valeurs. Par exemple, les valeurs de la liste comprenant « Écosse », « Angleterre », « Irlande du Nord » et « Pays de Galles » pourraient être regroupées dans une partition nommée « Royaume-Uni ».
• Le partitionnement par colonnes – sert à diviser une table de grande envergure en colonnes : les colonnes statiques sont stockées dans une table, tandis que les colonnes plus dynamiques sont placées dans une autre table et sur un autre serveur, une vue étant utilisée pour les relier en un seul objet. Une base de données en colonnes peut être considérée comme partitionnée verticalement, chaque colonne constituant une table.
• Partitionnement « Round Robin » : insère les nouvelles lignes dans une partition différente, selon un ordre séquentiel et répétitif.
• Partitionnement par hachage – génère un identifiant de partition aléatoire sous forme d'entier à partir d'une valeur calculée, qui peut utiliser une clé basée sur la valeur insérée.
• Le partitionnement par clé s'apparente au partitionnement par hachage, à la différence qu'il peut prendre en compte plusieurs valeurs de colonnes pour déterminer la partition cible.
• Partitionnement composite– permet d'imbriquer plusieurs niveaux de partitionnement. Par exemple, le premier niveau de partitionnement pourrait être basé sur des plages, et le deuxième niveau pourrait être de type « round robin » au sein de ces plages.

Élagage par division

L'élagage des partitions désigne une technique d'optimisation qui permet requête une requête l'évaluation de requête partitions requête les performances. Par exemple, si vous ne souhaitez requête plage de codes postaux donnée, vous pouvez spécifier une clause WHERE indiquant les limites inférieure et supérieure. requête générera alors une clause IN contenant uniquement les valeurs comprises dans cette plage, en ignorant les autres partitions.

Actian Data Platform Partitioning

Partitioning is an essential strategy in deploying the Actian Data Platform as it is architected for parallelism. Actian Data Platform is designed to operate efficiently, making the best use of cluster resources when at least one table in any nontrivial (joining) query is partitioned. Lack of appropriate partitioning can impact performance, so you should consider implementing partitioning where appropriate.

Partitioning in the Actian Data Platform distributes rows of a table among sub-tables (partitions). A partitioning scheme determines which rows are sent to which partitions.

Le partitionnement est géré automatiquement. Pour définir une table avec un schéma de partitionnement explicite, utilisez l'option PARTITION= dans la clause WITH de l'instruction CREATE TABLE. Le partitionnement automatique étant le comportement par défaut, il n'est pas nécessaire de spécifier explicitement la clause WITH PARTITION.

Actian Data Platform supports two partitioning schemes which define a rule (distribution scheme) for assigning rows to partitions. Conceptually, a dimension defines a set of logical partitions. The following distribution types are available:

• HASH –Répartit les lignes de manière uniforme entre les partitions à l'aide d'une valeur de hachage (plutôt que de manière aléatoire). À partir d'une valeur de la colonne de partitionnement, une requête déterminer quelle partition contient les lignes présentant cette valeur. Ainsi, une requête limiter sa recherche à un sous-ensemble de partitions. HASH dépend des données et nécessite la clause ON.
• AUTOMATIQUE –(Par défaut) Répartit les lignes de manière aléatoire entre les partitions.

Le schéma de répartition peut être une valeur par défaut définie au niveau du système. Les noms de partition logiques facultatifs doivent être uniques pour chaque table. Un même nom de partition peut apparaître dans d'autres tables partitionnées. Si un nom de partition est omis, le système génère un nom (sous la forme iipartnn).

Syntaxe de partitionnement

La définition d'une partition de table se présente sous la forme suivante :

PARTITION = (dimension)

La syntaxe pour chaque dimension de partition est la suivante :

dimension = règle partitionspec{, partitionspec}

règle

Définit le type de schéma de répartition utilisé pour attribuer les lignes aux partitions. Les valeurs valides sont les suivantes :

HASH ON colonne{, colonne}

Répartit les lignes de manière uniforme entre les partitions en fonction d'une valeur de hachage.

sur colonne{,colonne}

définit les colonnes sur lesquelles la table doit être partitionnée.

AUTOMATIQUE

(Par défaut) Répartit les lignes de manière aléatoire entre les partitions.

partition

Définit le nombre de partitions et, éventuellement, leurs noms :

partitionspec = PARTITIONS PAR DÉFAUT | [nn] PARTITION[S] [ ( nom{, nom} ) ]

où :

PARTITIONS PAR DÉFAUT

Utilise le nombre de partitions par défaut configuré pour des performances optimales en fonction de la taille de votre entrepôt.

L'instruction renvoie une erreur si la valeur de partition par défaut n'est pas définie.

nn

Il s'agit du nombre de partitions ; la valeur par défaut est 1 si ce paramètre n'est pas spécifié.

nom

Identifie la partition. Lorsqu'il y a au moins deux partitions, une liste de noms séparés par des virgules peut remplacer la valeur par défaut.

Par défaut : iipartNN

Recommandations relatives aux tables partitionnées

Vous devriez choisir la clé de partition parmi les colonnes dont les valeurs sont uniformes, par exemple les clés primaires ou étrangères. Si vous prévoyez d'exécuter de nombreuses requêtes joignant les tables A et B selon la condition A.fk_col = B.col, les clés de partitionnement appropriées pour A et B sont respectivement fk_col et col.

Il est recommandé d'utiliser une partition par cœur et par nœud :

nombre_de_partitions = nombre_de_nœuds * K

où K est un diviseur du nombre de cœurs physiques par nœud.

La création d'un index sur les colonnes définissant une relation étrangère n'est pas autorisée lorsque les tables jointes par cette relation n'ont pas le même nombre de partitions ou ne sont pas partitionnées sur les colonnes (ou un sous-ensemble correspondant) utilisées pour la relation de clé étrangère. Par exemple :

Ce qui suit est autorisé :

CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL,
c i4 NOT NULL)
WITH PARTITION=(HASH ON a,c 2 PARTITIONS);
ALTER TABLE X ADD CONSTRAINT pk_x PRIMARY KEY (a,c);

CREATE TABLE Y (c i4 NOT NULL,
d i4 NOT NULL,
e i4)
WITH PARTITION=(HASH ON d,e 2 PARTITIONS);

ALTER TABLE Y ADD CONSTRAINT fk_y FOREIGN KEY(d,e) REFERENCES X(a,c);

CREATE INDEX idx_y ON Y(d,e);

Il est également possible d'utiliser les clés de partitionnement « c » pour X et « e » pour Y.

Le schéma de partitionnement par défaut AUTOMATIC répartit les lignes de manière aléatoire et uniforme entre les partitions. Contrairement aux tables à répartition par hachage, il n'est pas garanti que les lignes ayant des valeurs identiques soient affectées à la même partition. Par conséquent, le système doit généralement réorganiser les données avant de traiter la requête. Par exemple, la jointure de deux tables partitionnées automatiquement nécessite généralement un réagencement des lignes. Cette étape supplémentaire peut nuire requête .

Le partitionnement AUTOMATIQUE doit être utilisé dans les cas suivants :

• Lorsque insight pour créer une bonne clé de hachage. Autrement dit, lorsque :
  • Il n'y a pas de clé de jointure évidente.
  • Il n'existe pas de bonne méthode pour répartir la table (données arbitraires) à l'aide d'un hachage.
  • Cette table ne partage aucune clé de jointure commune avec d'autres tables.
  • Cette table est une table de transit temporaire.
• Lors de la définition d'une table à partitionnement automatique, étape préalable à la création d'une clé de hachage efficace, vous pouvez utiliser la commande CREATE STATISTICS, puis SELECT pour obtenir les valeurs minimales et maximales des colonnes ainsi que leur nombre (COUNT), ce qui vous permettra de mieux choisir les colonnes à utiliser comme clé de distribution HASH.

Schéma de répartition par défaut

Vous pouvez définir un schéma de partitionnement par défaut lors de la création de tables partitionnées. Ce paramètre par défaut peut être remplacé au niveau de la session à l'aide de l'instruction SET PARTITION_SCHEME. Vous pouvez attribuer un nombre par défaut de partitions lors de la création de tables partitionnées. Spécifiez WITH PARTITION = (règle ON colonne DEFAULT PARTITIONS) lors de la création ou de la modification de tables. La table sera partitionnée en fonction du nombre de partitions configuré. Le paramètre de nombre de partitions par défaut peut être remplacé au niveau de la session à l'aide de l'instruction SET PARTITION_PARTS.

Exemples d'utilisation de CREATE TABLE

Pour Google Cloud, créez une table avec un partitionnement par défaut de type AUTOMATIC :

CREATE TABLE client (
id_client INT NOT NULL DEFAULT 0,
code_postal CHAR(5) NOT NULL)

Créer une table sans partitionnement :

CREATE TABLE client (
id_client INT NOT NULL DEFAULT 0,
code_postal CHAR(5) NOT NULL)
WITH NOPARTITION;

Créer une table partitionnée par HASH comportant 16 partitions réparties en fonction de la colonne « emp_no » :

CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (HASH ON emp_no
16 PARTITIONS);

Créez une table partitionnée par HASH en utilisant le nombre de partitions par défaut :

CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (HASH ON emp_no DEFAULT PARTITIONS);

Créez une table dans laquelle le numéro de sécurité sociale est chiffré à l'aide du chiffrement AES 128 bits. N'ajoutez pas de sel (SALT) à la valeur (c'est-à-dire n'ajoutez pas 16 octets de bits aléatoires au champ pour brouiller davantage la valeur chiffrée) :

CREATE TABLE socsectab (
  fname CHAR(10),
  lname CHAR(20),
  socsec CHAR(11) ENCRYPT NOSALT )
  WITH ENCRYPTION=AES128, PASSPHRASE='c'est un secret', NOPARTITION;

Créez une table dans laquelle les données de la colonne c2, qui contient les données relatives aux salaires, sont chiffrées à l'aide du chiffrement AES 256 bits. Un sel est ajouté au champ par défaut :

CREATE TABLE t1 (
  c1 CHAR(20) NOT NULL,
  c2 MONEY ENCRYPT)
  WITH ENCRYPTION=AES256, PASSPHRASE='decoder ring', NOPARTITION;

Créez une table avec un index min-max échantillonné sur deux colonnes :

CREATE TABLE sales_fact (
sales_date ANSIDATE,
value INTEGER2,
quantity FLOAT8)
WITH MINMAX=(sales_date, quantity), MINMAX_SAMPLES;

Créez un tableau dans lequel les colonnes « Adresse » et « Salaire » sont masquées :

CREATE TABLE employee(
name VARCHAR(20),
address VARCHAR(20) MASKED,
salary FLOAT MASKED AS 0);

Créer une table partitionnée dont les lignes sont réparties automatiquement (c'est-à-dire de manière aléatoire) :

CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH
PARTITION = (AUTOMATIC 8 PARTITIONS);

Créez la table « movies » sans partitionnement :

CREATE TABLE films AS SELECT * FROM cinéma WITH NOPARTITION;

Créez la table « books » avec un partitionnement AUTOMATIC par défaut :

CREATE TABLE livres AS SELECT * FROM titres;

Actian et la plateforme d'intelligence des données

La plateformeActianData Intelligencea été spécialement conçue pour aider les organisations à unifier, gérer et comprendre leurs données dans des environnements hybrides. Elle regroupe métadonnées , gouvernance, la traçabilité, le contrôle de la qualité et l'automatisation au sein d'une seule et même plateforme. Cela permet aux équipes de savoir d'où proviennent les données, comment elles sont utilisées et si elles répondent aux exigences internes et externes.

Grâce à son interface centralisée, Actian offre insight en temps réel insight les structures et les flux de données, ce qui facilite l'application des politiques, la résolution des problèmes et la collaboration entre les services. La plateforme aide également à replacer les données dans leur contexte métier, permettant ainsi aux équipes de les exploiter de manière plus efficace et responsable. La plateforme Actian est conçue pour s'adapter à l'évolution des écosystèmes de données, garantissant une utilisation cohérente, intelligente et sécurisée des données à l'échelle de l'entreprise.Demandez votre démonstration personnalisée.