Discussion :

[babak67]

Bonjour

J'ai un site pas très peu fréquenté mais avec une base mariadb avec une grosse table qui comporte beaucoup de données. Tous les jours il y a au moins 45000 entrées qui se rajoutent. Pour le moment y a 2.5 million entrées et tout va bien mais je voudrais savoir jusqu'où elle peut aller ?
Dois je supprimer des enregistrements ? si oui dois-je réorganiser l'indexation ? optimiser?
Quelle sont les commandes pour vérifier l'état de la base par rapport à sa pérformance ?

merci pour votre réponse

[Artemus24]

Salut babak67.

Pourquoi ne pas créer des partitions ?
Ainsi pour les lignes les plus anciennes, vous pourriez les archiver.

@+

[escartefigue]

Dois je supprimer des enregistrements ? si oui dois-je réorganiser l'indexation ? optimiser?

Les suppressions ne peuvent être opérées qu'après vérification des contraintes réglementaires et des contraintes métier.
La durée de rétention seule ne suffit pas forcément, il faut interroger vos correspondants métier pour connaitre les critères de purge.

Ensuite, le partitionnement est effectivement une bonne solution pour améliorier les performances sur les tables à forte volumétrie.
Autre piste possible qui peut être bien sur combinée avec le partitionnement : une table pour les lignes "en activité" (celles intérogées par les traitements) et une autre pour les lignes d'archive
Là encore il faut connaitre les critères métiers pous savoir comment une ligne passe de l'une à l'autre table. Une vue sera créée pour les quelques traitements qui ont besoin de lire à la vois les lignes actives et les archives.

[babak67]

Merci Artemus24, merci escartefigue pour vos réponses.

En faites j'ai presque 1400 enregistrements qui sont affichés par défauts. Le reste sont utilisés pour calculer les statistiques tous les 36 du mois.
D'après ce que je comprend je peux partitionner la table et stocker les partitions anciennes au fur et à mesure ?
Puis je partitionner à chaud svp?

[babak67]

Merci les gars je viens de partitionner la table à des tables d'un mois ce qui donnera 30X4500 par table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
 
alter table Data 
partition by range (to_days(rcvd_time))
 
(partition p_invalid_date values less than (0)
 
,partition p_201807 values less than (to_days('2018-07-01 00:00:00'))
 
,partition p_201808 values less than (to_days('2018-08-01 00:00:00'))
 
,partition p_201809 values less than (to_days('2018-09-01 00:00:00'))
 
,partition p_201810 values less than (to_days('2018-10-01 00:00:00'))
 
,partition p_201811 values less than (to_days('2018-11-01 00:00:00'))
 
,partition p_201812 values less than (to_days('2018-12-01 00:00:00'))
 
,partition p_201901 values less than (to_days('2019-01-01 00:00:00'))
 
,partition p_201902 values less than (to_days('2019-02-01 00:00:00'))
 
,partition p_201903 values less than (to_days('2019-03-01 00:00:00'))
 
,partition p_201904 values less than (to_days('2019-04-01 00:00:00'))
 
,partition p_201905 values less than (to_days('2019-05-01 00:00:00'))
 
,partition p_max_future values less than MAXVALUE);

Qu'en pensez vous ?

[escartefigue]

Si je comprends bien, la colonne de partitionnement est une colonne de type (var)char que vous convertissez en date

Très dangereux et contre-productif :

• vous n'avez aucune garantie que ça fonctionne à 100%, aucun contrôle par traitement n'est aussi fiable que celui fait par le SGBD sur le respect du type de colonne
• vous perdez l'usage de toutes les fonctions de date
• vous encombrez inutilement la base de données

A corriger dans la mesure du possible

[babak67]

Qu'est ce qui vous fait dire ca ? la colonne est en type date.

[Artemus24]

Salut à tous.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`         timestamp(6)      NOT NULL,
  `val`        tinyint unsigned  NOT NULL,
  `lib`        varchar(255)      NOT NULL,
  `year`       smallint unsigned GENERATED ALWAYS AS (year(id))  stored,
  `month`      tinyint  unsigned GENERATED ALWAYS AS (month(id)) stored,
  PRIMARY KEY (`id`,`year`,`month`),
  INDEX `IDX` (`year`,`month`,`val`)
) ENGINE=Innodb
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
 
  PARTITION BY RANGE COLUMNS (`year`,`month`)
  (
    PARTITION p2017s01 values less than (2017,02),
    PARTITION p2017s02 values less than (2017,03),
    PARTITION p2017s03 values less than (2017,04),
    PARTITION p2017s04 values less than (2017,05),
    PARTITION p2017s05 values less than (2017,06),
    PARTITION p2017s06 values less than (2017,07),
    PARTITION p2017s07 values less than (2017,08),
    PARTITION p2017s08 values less than (maxvalue,maxvalue)
  )
--------------
 
--------------
commit
--------------
 
--------------
insert into `test` (`id`,`val`,`lib`) values
  ('2017-01-01- 15:00:00.000001', 05,'un'),        ('2017-01-01- 15:00:00.000002',07,'deux'),       ('2017-01-01- 15:00:00.000003',06,'trois'),
  ('2017-02-01- 15:00:00.000001', 07,'quatre'),    ('2017-02-01- 15:00:00.000002',06,'cinq'),       ('2017-02-01- 15:00:00.000003',05,'six'),
  ('2017-03-01- 15:00:00.000001', 06,'sept'),      ('2017-03-01- 15:00:00.000002',05,'huit'),       ('2017-03-01- 15:00:00.000003',07,'neuf'),
  ('2017-04-01- 15:00:00.000001', 05,'dix'),       ('2017-04-01- 15:00:00.000002',07,'onze'),       ('2017-04-01- 15:00:00.000003',06,'douze'),
  ('2017-05-01- 15:00:00.000001', 07,'treize'),    ('2017-05-01- 15:00:00.000002',06,'quatorze'),   ('2017-05-01- 15:00:00.000003',05,'quinze'),
  ('2017-06-01- 15:00:00.000001', 06,'seize'),     ('2017-06-01- 15:00:00.000002',05,'dix-sept'),   ('2017-06-01- 15:00:00.000003',07,'dix-huit'),
  ('2017-07-01- 15:00:00.000001', 05,'dix_neuf'),  ('2017-07-01- 15:00:00.000002',07,'vingt'),      ('2017-07-01- 15:00:00.000003',06,'vingt-et-un'),
  ('2017-08-01- 15:00:00.000001', 07,'vingt-deux'),('2017-08-01- 15:00:00.000002',06,'vingt-trois'),('2017-08-01- 15:00:00.000003',05,'vingt-quatre')
--------------
 
--------------
select * from `test`
--------------
 
+----------------------------+-----+--------------+------+-------+
| id                         | val | lib          | year | month |
+----------------------------+-----+--------------+------+-------+
| 2017-01-01 15:00:00.000001 |   5 | un           | 2017 |     1 |
| 2017-01-01 15:00:00.000002 |   7 | deux         | 2017 |     1 |
| 2017-01-01 15:00:00.000003 |   6 | trois        | 2017 |     1 |
| 2017-02-01 15:00:00.000001 |   7 | quatre       | 2017 |     2 |
| 2017-02-01 15:00:00.000002 |   6 | cinq         | 2017 |     2 |
| 2017-02-01 15:00:00.000003 |   5 | six          | 2017 |     2 |
| 2017-03-01 15:00:00.000001 |   6 | sept         | 2017 |     3 |
| 2017-03-01 15:00:00.000002 |   5 | huit         | 2017 |     3 |
| 2017-03-01 15:00:00.000003 |   7 | neuf         | 2017 |     3 |
| 2017-04-01 15:00:00.000001 |   5 | dix          | 2017 |     4 |
| 2017-04-01 15:00:00.000002 |   7 | onze         | 2017 |     4 |
| 2017-04-01 15:00:00.000003 |   6 | douze        | 2017 |     4 |
| 2017-05-01 15:00:00.000001 |   7 | treize       | 2017 |     5 |
| 2017-05-01 15:00:00.000002 |   6 | quatorze     | 2017 |     5 |
| 2017-05-01 15:00:00.000003 |   5 | quinze       | 2017 |     5 |
| 2017-06-01 15:00:00.000001 |   6 | seize        | 2017 |     6 |
| 2017-06-01 15:00:00.000002 |   5 | dix-sept     | 2017 |     6 |
| 2017-06-01 15:00:00.000003 |   7 | dix-huit     | 2017 |     6 |
| 2017-07-01 15:00:00.000001 |   5 | dix_neuf     | 2017 |     7 |
| 2017-07-01 15:00:00.000002 |   7 | vingt        | 2017 |     7 |
| 2017-07-01 15:00:00.000003 |   6 | vingt-et-un  | 2017 |     7 |
| 2017-08-01 15:00:00.000001 |   7 | vingt-deux   | 2017 |     8 |
| 2017-08-01 15:00:00.000002 |   6 | vingt-trois  | 2017 |     8 |
| 2017-08-01 15:00:00.000003 |   5 | vingt-quatre | 2017 |     8 |
+----------------------------+-----+--------------+------+-------+
--------------
SELECT  PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
  FROM  INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'test'
--------------
 
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p2017s01       | NULL              |          3 |
| p2017s02       | NULL              |          3 |
| p2017s03       | NULL              |          3 |
| p2017s04       | NULL              |          3 |
| p2017s05       | NULL              |          3 |
| p2017s06       | NULL              |          3 |
| p2017s07       | NULL              |          3 |
| p2017s08       | NULL              |          3 |
+----------------+-------------------+------------+
--------------
explain select *
from `test`
where `year`  = 2017
  and `month` = 5
  and `val`   = 6
--------------
 
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test  | p2017s05   | ref  | IDX           | IDX  | 4       | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
--------------
select *
from `test`
where `year`  = 2017
  and `month` = 5
  and `val`   = 6
--------------
 
+----------------------------+-----+----------+------+-------+
| id                         | val | lib      | year | month |
+----------------------------+-----+----------+------+-------+
| 2017-05-01 15:00:00.000002 |   6 | quatorze | 2017 |     5 |
+----------------------------+-----+----------+------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+