Partitionner une table par mois

Version imprimable

12/05/2015, 16h50
vinch999

Partitionner une table par mois
Salut,

J'aimerais faire un exercice, je ne vois pas vraiment comment m'y prendre:

Soit une table de logs contenant une colonne de type datetime.

Donc :
Code:

1 2 3 4 5 6 7 CREATE TABLE dbo.my_log_table ( id int identity(1,1), log_date datetime2(3), log_text varchar(255) );
On souhaite garder 3 mois (90 jours) de données, à tout moment afin de ne pas faire exploser le volume :P. Sachant que l'on a un volume conséquent (du genre 500 go par mois)
J'aimerais partitionner la table par mois (donc 3 fichiers)

Je dois supprimer les vieilles données jour par jour , on va dire , si log_date < GETDATE() - 90 jours, et splitter la table sur 3 fichiers:

Je ne vois pas comment répartir les données selon la date.

Avez vous des idées de comment gérer cela?
12/05/2015, 19h17
Invité

On dirait une question de la certification MS !

Regarde ici pour commencer : https://msdn.microsoft.com/en-us/library/ms188730.aspx

Non je n'ai pas vu ça dans une certif, c'est un besoin pour un client :)

J'ai fini par trouver quelque chose :

J'ai créé 3 filegroups, 3 fichiers .

Ensuite :

Code:

1
2
3
4
5
6
7
CREATE PARTITION FUNCTION PF_PartitionThis (int) 
	AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11,12) ;
GO
CREATE PARTITION SCHEME PS_PartitionThis
    AS PARTITION PF_PartitionThis
    TO (PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3,PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3, PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3,PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3, PartitionThis_FG_1) ;
GO

J'ai rajouté une colonne mois dans la table :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE FUNCTION dbo.get_month (@date_value datetime) RETURNS int
WITH SCHEMABINDING AS
BEGIN
 RETURN MONTH(@date_value)
END
 
CREATE TABLE dbo.my_log_table
(
    id int identity(1,1),
    log_date datetime,
    log_text varchar(255),
	mois as dbo.get_month(log_date) PERSISTED,
	CONSTRAINT pk_my_log_table PRIMARY KEY (mois,id)
)
ON PS_PartitionThis (mois);

J'ai fait quelques insert :

Code:

INSERT INTO dbo.my_log_table(log_date,log_text) VALUES (GETDATE()-20,'mon_log')

Pour vérifier le résultat :

Code:

1
2
3
4
5
6
7
8
SELECT
p.partition_number AS PartitionNumber,
f.name AS PartitionFilegroup,
p.rows AS NumberOfRows
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'my_log_table'

Vous en pensez quoi?

13/05/2015, 10h51
SQLpro

J'en pense que c'est de la connerie ! En effet, si on vous avait demandé une partition par jour vous auriez fait 31 partitions ?

Réfléchissez un peu, un mois n'est pas indépendant d'une année, tout comme un jour est relatif à un mois et une année !

En sus comme vous avez des dates il faut mettre des date dans votre fonction de partitionnement et il faudra créer régulièrement de nouvelles entrées à chaque fin de mois, ceci pouvant être fait automatiquement par une tâche planifiée dans l'Agent SQL.

A +
13/05/2015, 11h31
vinch999

Sachant que j'aurai au max 3 mois de données (je dois supprimer chaque jour les données < j-90), quel est l'intérêt de faire comme vous dites?
En faisant comme je propose, je n'ai pas besoin de recréer de nouvelles entrées dans la fonction de partitionnement.

Ma méthode serait mauvaise d'un point de vue performance? (suppression des vieilles données, refresh des index,etc.) ?

Disons que j'ai un disque de 500 GB.
Par mois , 150 GB de data.
Comment feriez-vous? Combien de files?
13/05/2015, 13h44
SQLpro

Déjà le fait d'avoir :

Code:

mois as dbo.get_month(log_date) PERSISTED

est hautement stupide alors que :

Code:

MOIS MONTH(log_date) PERSISTED

suffit et utilise une fonction intégrée, bien plus rapide qu'une UDF ! Et puis quel intérêt de réinventer la roue ? Masochisme ou stupidité ???

En sus un partitionnement par DATE vous évite cette redondance qui augmente la volumétrie de votre table; Je suppose que si vous partitionnez c'est pour des raisons de volume ? Donc est-ce intelligent de rajouter du volume à votre table ?

Enfin, pour des raisons de performances liées à d'hypothétique requêtes, l'intérêt de partitionner est que vos utilisateurs qui utilise des requêtes puissent n'activer que certaines partitions et non pas toutes ! Or en choisissant un critère de partitionnement qui n'est jamais exprimé dans aucune requête l'optimiseur ne pourra pas n'exciter qu'une seule partition !

Bref, vous avez tout faux !

A +
13/05/2015, 14h16
vinch999

En effet , le calcul du mois via une fonction est débile, j'ai fait un copier coller d'un site en testant sans corriger.

D'un point de vue redondance ça me couterait 1 octet à la ligne en tinyint.... Soit 1,86 GB pour 2 milliards de lignes , pas de quoi en chier une pendule.

Soit, je vais réétudier la question d'une feuille blanche(je débute en partitioning)
13/05/2015, 14h18
SQLpro

Citation:

Envoyé par vinch999

Soit, je vais réétudier la question d'une feuille blanche(je débute en partitioning)

Oui, mais les performances seront toujours aussi pourries voir plus encore !
Or je suppose que vous voulez partitionner pour accélérer les recherches....
Votre méthode va les complexifier et donc faire exactement l'inverse du but recherché....

Et vous n'êtes pas au bout de vos surprises... Quid de la clef primaire, quid de l'index clustered...

Un partitionnement cela s'étudier sur une maquette ayant le volume réel de la base, avec les requêtes réelles de production...

A +