Économisez un centime sur les gros volumes dans PostgreSQL

Poursuivant le sujet de l'enregistrement de flux de données volumineux soulevé par article précédent sur le partitionnement, nous examinerons ici les manières dont vous pouvez réduire la taille « physique » des données stockées dans PostgreSQL et leur impact sur les performances du serveur.

Nous parlerons de Paramètres TOAST et alignement des données. « En moyenne », ces méthodes n'économiseront pas trop de ressources, mais sans modifier du tout le code de l'application.

Cependant, notre expérience s'est avérée très productive à cet égard, puisque le stockage de presque toutes les surveillances, de par sa nature, est principalement en ajout uniquement en termes de données enregistrées. Et si vous vous demandez comment apprendre à la base de données à écrire sur le disque à la place 200MB / s moitié moins - s'il vous plaît sous chat.

Petits secrets du big data

Par profil d'emploi notre service, ils lui volent régulièrement depuis les antres paquets de texte.

Et depuis Complexe VLSIdont la base de données que nous surveillons est un produit multi-composants avec des structures de données complexes, puis des requêtes pour des performances maximales ça se passe comme ça « multi-volumes » avec une logique algorithmique complexe. Ainsi, le volume de chaque instance individuelle d'une requête ou du plan d'exécution résultant dans le journal qui nous parvient s'avère « en moyenne » assez important.

Examinons la structure de l'une des tables dans lesquelles nous écrivons des données « brutes » - c'est-à-dire que voici le texte original de l'entrée du journal :

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

Un panneau typique (déjà sectionné, bien sûr, il s'agit donc d'un modèle de section), où le plus important est le texte. Parfois assez volumineux.

Rappelons que la taille « physique » d'un enregistrement dans un PG ne peut pas occuper plus d'une page de données, mais la taille « logique » est une tout autre affaire. Pour écrire une valeur volumétrique (varchar/text/bytea) dans un champ, utilisez Technologie TOAST:

PostgreSQL utilise une taille de page fixe (généralement 8 Ko) et n'autorise pas les tuples à s'étendre sur plusieurs pages. Il est donc impossible de stocker directement des valeurs de champs très volumineuses. Pour surmonter cette limitation, les grandes valeurs de champ sont compressées et/ou réparties sur plusieurs lignes physiques. Cela se produit inaperçu pour l'utilisateur et a peu d'impact sur la plupart du code du serveur. Cette méthode est connue sous le nom de TOAST...

En fait, pour chaque table comportant des champs « potentiellement volumineux », automatiquement une table appariée avec « slicing » est créée chaque « grand » enregistrement en segments de 2 Ko :

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Autrement dit, si nous devons écrire une chaîne avec une valeur « grande » data, alors le véritable enregistrement aura lieu non seulement à la table principale et son PK, mais aussi à TOAST et son PK.

Réduire l’influence de TOAST

Mais la plupart de nos disques ne sont toujours pas si gros, devrait tenir dans 8 Ko - Comment puis-je économiser de l'argent là-dessus ?..

C'est là que l'attribut nous vient en aide STORAGE dans la colonne du tableau :

• ÉLARGI permet à la fois la compression et le stockage séparé. Ce option standard pour la plupart des types de données compatibles TOAST. Il tente d'abord d'effectuer une compression, puis la stocke en dehors de la table si la ligne est encore trop grande.
• PRINCIPAL permet la compression mais pas le stockage séparé. (En fait, un stockage séparé sera toujours effectué pour ces colonnes, mais uniquement en dernier recours, lorsqu'il n'existe aucun autre moyen de réduire la chaîne pour qu'elle tienne sur la page.)

En fait, c'est exactement ce dont nous avons besoin pour le texte - compressez-le autant que possible, et s'il ne rentre pas du tout, mettez-le dans TOAST. Cela peut être fait directement à la volée, avec une seule commande :

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Comment évaluer l'effet

Étant donné que le flux de données change chaque jour, nous ne pouvons pas comparer des chiffres absolus, mais en termes relatifs. part plus petite Nous l'avons écrit dans TOAST - tant mieux. Mais il y a ici un danger : plus le volume « physique » de chaque enregistrement individuel est grand, plus l'index devient « large », car nous devons couvrir davantage de pages de données.

section avant les changements:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

section après des changements:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

En fait, nous commencé à écrire sur TOAST 2 fois moins souvent, qui a déchargé non seulement le disque, mais aussi le CPU :

Je noterai que nous sommes également devenus plus petits en « lecture » du disque, pas seulement en « écriture » - puisque lors de l'insertion d'un enregistrement dans une table, il faut aussi « lire » une partie de l'arborescence de chaque index afin de déterminer son position future en eux.

Qui peut bien vivre sur PostgreSQL 11

Après la mise à jour vers PG11, nous avons décidé de continuer le « réglage » de TOAST et avons remarqué qu'à partir de cette version, le paramètre devenait disponible pour le réglage. toast_tuple_target:

Le code de traitement TOAST se déclenche uniquement lorsque la valeur de ligne à stocker dans la table est supérieure à TOAST_TUPLE_THRESHOLD octets (généralement 2 Ko). Le code TOAST compressera et/ou déplacera les valeurs des champs hors de la table jusqu'à ce que la valeur de la ligne devienne inférieure à TOAST_TUPLE_TARGET octets (valeur variable, également généralement 2 Ko) ou que la taille ne puisse pas être réduite.

Nous avons décidé que les données dont nous disposons habituellement sont soit « très courtes » soit « très longues », nous avons donc décidé de nous limiter à la valeur minimale possible :

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Voyons comment les nouveaux paramètres ont affecté le chargement du disque après la reconfiguration :

Pas mal! Moyenne la file d'attente sur le disque a diminué environ 1.5 fois, et le disque « occupé » est de 20 pour cent ! Mais peut-être que cela a affecté le processeur ?

Au moins, ça n'a pas été pire. Cependant, il est difficile de juger si même de tels volumes ne peuvent toujours pas augmenter la charge moyenne du processeur. 5%.

En changeant la place des termes, la somme... change !

Comme vous le savez, un centime permet d'économiser un rouble, et avec nos volumes de stockage, il s'agit de 10 To/mois même une petite optimisation peut donner un bon profit. C'est pourquoi nous avons prêté attention à la structure physique de nos données - comment exactement champs « empilés » à l’intérieur de l’enregistrement chacun des tableaux.

Parce qu'à cause de alignement des données c'est simple affecte le volume résultant:

De nombreuses architectures assurent l'alignement des données sur les limites des mots machine. Par exemple, sur un système x32 86 bits, les entiers (type entier, 4 octets) seront alignés sur une limite de mot de 4 octets, tout comme les nombres à virgule flottante double précision (virgule flottante double précision, 8 octets). Et sur un système 64 bits, les valeurs doubles seront alignées sur les limites des mots de 8 octets. C'est une autre raison d'incompatibilité.

En raison de l'alignement, la taille d'une ligne du tableau dépend de l'ordre des champs. Habituellement, cet effet n'est pas très visible, mais dans certains cas, il peut entraîner une augmentation significative de la taille. Par exemple, si vous mélangez des champs char(1) et entiers, il y aura généralement 3 octets perdus entre eux.

Commençons par les modèles synthétiques :

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

D'où viennent quelques octets supplémentaires dans le premier cas ? C'est simple - smallint de 2 octets aligné sur la limite de 4 octets avant le champ suivant, et quand c'est le dernier, il n'y a rien ni besoin d'aligner.

En théorie, tout va bien et vous pouvez réorganiser les champs à votre guise. Vérifions-le sur des données réelles en utilisant l'exemple d'une des tables dont la section quotidienne occupe 10-15 Go.

Structure initiale :

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

Section après avoir modifié l'ordre des colonnes - exactement mêmes champs, juste un ordre différent:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

Le volume total de la section est déterminé par le nombre de « faits » et dépend uniquement de processus externes, divisons donc la taille du tas (pg_relation_size) par le nombre d'enregistrements qu'il contient - c'est-à-dire que nous obtenons taille moyenne de l'enregistrement réellement stocké:

Moins 6% de volume, Super!

Mais bien sûr, tout n'est pas si rose - après tout, dans les index, nous ne pouvons pas changer l'ordre des champs, et donc « en général » (pg_total_relation_size) ...

...toujours là aussi économisé 1.5%sans changer une seule ligne de code. Oui oui!

Je note que l'option ci-dessus pour organiser les champs n'est pas le fait qu'elle soit la plus optimale. Parce que vous ne voulez pas « déchirer » certains blocs de champs pour des raisons esthétiques - par exemple, quelques (pack, recno), qui est le PK de cette table.

En général, déterminer la disposition « minimale » des champs est une tâche de « force brute » assez simple. Par conséquent, vous pouvez obtenir des résultats encore meilleurs avec vos données que les nôtres – essayez-le !

Source: habr.com