Discussion :

[thierryinfo2012]

bonjour a tous

je rencontre un Pb d'espace disque sur mon instance de production dans laque| j'aurai besoin de récupérer plus d'espace disque si je tape une petit requête pour récupérer la taille de mes tables ,je viens d'avoir cette résultat

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
                     relation                      |         size (data)          | tablespace
----------------------------------------------------+------------------------------+------------
 public.Production                   | 334 GB (421 MB data)         |
 archive.Production_2017_03          | 74 GB (69 MB data)           |
 archive.Production_2017_07          | 72 GB (53 MB data)           |
 archive.Production_2017_06          | 66 GB (54 MB data)           |
 archive.Production_2017_04          | 65 GB (55 MB data)           |
 archive.Production_2017_05          | 63 GB (55 MB data)           |
 archive.Production_2017_02          | 58 GB (56 MB data)           |
 archive.Production_2017_01          | 57 GB (56 MB data)           |
 archive.Production_2016_12          | 54 GB (48 MB data)           |
 archive.Production_2016_11          | 48 GB (45 MB data)           |
 archive.Production_2016_10          | 45 GB (42 MB data)           |
 archive.Production_2016_09          | 28 GB (30 MB data)           |
 archive.Production_2016_07          | 28 GB (35 MB data)           |
 archive.Production_2016_06          | 27 GB (35 MB data)           |
 archive.Production_2016_08          | 24 GB (30 MB data)           |
 archive.Production_2017_08          | 21 GB (16 MB data)           |

est ce que normale d'avoir ce différence entre la taille du data et la taille des indexes par exemple la table "public.Production" a une taille de 334 GB alors que la data ne dépasse pas le 421 MB

Serai t'il possible d'optimiser ça

merci pour vos aides

[SQLpro]

Il n'est par rare d'avoir des tables dans lesquelles la taille des index est plus importante que la taille des données, en particulier lorsque la base est mal modélisée (tables obèses). À me lire : https://blog.developpez.com/sqlpro/p...mances_petites.
D'autre part, les tables se fragmente à l'usage lors des mies à jour (INSERT, UPDATE, DELETE) et en particulier dans PG ou la gestion du MCVCC est assez mal faite, car les différentes versions de lignes résultantes de l'application du verrouillage optimiste sont stockées à l'intérieur des pages... Dans ce dernier cas il faut utiliser l'utilitaire VACUUM pour faire le ménage....

A +

[alassanediakite]

Salut

...ou la gestion du MCVCC est assez mal faite, car les différentes versions de lignes résultantes de l'application du verrouillage optimiste sont stockées à l'intérieur des pages... Dans ce dernier cas il faut utiliser l'utilitaire VACUUM pour faire le ménage....

Solution adoptée par PostgreSQL, Firebird/Interbase. (I)
Solution de SQL SERVER les anciennes versions dans tempdb. (II)
Solution de ORACLE et MySQL dans un espace nommé UNDO. (III)

1. dans toutes les solutions une purge des données obsolètes est obligatoire
2. pour les index
   
   
   

   1. Pour I, les entrées indexes sont dupliqués avec les nouvelles versions mais à la lecture seules les lignes visibles seront considérées. Un inconvenant certes, qui d'ailleurs a été un argument pour uber de laisser PostgreSQL au profit de MySQL
   2. Pour III, les index passent par l'index de la clé primaire. Il faut aussi savoir que le passage par deux index n'est pas sans coût

@+

[SQLpro]

...
Solution de SQL SERVER les anciennes versions dans tempdb. (II)

1. dans toutes les solutions une purge des données obsolètes est obligatoire

Ben non, car la base tempdb se "purge" automatiquement et dynamiquement en permanence. Il n'y a donc aucune opération manuelle à faire (il n'y a même pas de commande prévu à cet effet) contrairement à Oracle (gestion des UNDO log) ou PG (utilisation de VACUUM). J'ai mis purge entre parenthèse, car il ne purge pas tout dans le sens ou il réutilise les pages obsolète si nécessaire dans des fins d'optimisation (la création de pages coutant plus cher que la réutilisation de pages déjà formatées). Enfin la tempdb est détruite et recréée lors du démarrage du service SQL Server.

A +

[alassanediakite]

Salut
Salut
Ce n'est pas parce que c'est automatique que ça ne se fait.
Pour information, PostgreSQL procède à des VACCUM automatiques, il ne restitue les pages vides à l'OS que si vous choisissez l'option FULL de la commande VACCUM.

il n'y a même pas de commande prévu à cet effet

Simple question, que fait alors DBCC?
@+

[Waldar]

Je réponds sur Oracle DB quant aux deux points ci-après.
Je simplifie afin de ne pas avoir à parler des mécanismes d'archive et restauration - les Undo / Redo Log.

Solution de ORACLE et MySQL dans un espace nommé UNDO. (III)

Ça n'a vraiment rien à voir, le UNDO étant une zone mémoire.

Si on fait une mise à jour d'une table, Oracle :
- identifie les blocs concernés par la mise à jour
- regarde en mémoire s'ils ne sont pas déjà présents
- si non, on les récupère du stockage
- effectue une copie de ces blocs dans le UNDO
- modifie les blocs en zone mémoire
- si le traitement est validé, il y a une écriture asynchrone de ces blocs sur le stockage, et les blocs du UNDO sont indiqués comme étant réutilisables
- si le traitement est annulé, les blocs du UNDO remplacent ceux de la zone mémoire.

Dans tous les cas, ça dure le temps de la transaction. Il n'y a pas de MVCC et donc pas de Vaccum à faire sur Oracle.
C'est un vrai problème de la technologie que SQLPro a raison de soulever, il n'est pas anodin, et pour avoir passé des vacuum sur tes tables de plusieurs To, ce n'est pas la panacée.

Pour III, les index passent par l'index de la clé primaire. Il faut aussi savoir que le passage par deux index n'est pas sans coût

Quant aux index ça dépend, mais de manière générale c'est faux.
Les index sous Oracle référencent directement les RowId (adresse physique) des lignes concernées.
La seule exception sont pour les tables organisées en index (l'équivalent des tables clustered de SQL-Server), mais c'est une fonctionnalité méconnue et dès lors très peu utilisée.

[alassanediakite]

Salut

J... Il n'y a pas de MVCC et donc pas de Vaccum à faire sur Oracle.

Voici ce que dit wikipedia.
@+

[SQLpro]

Salut

Voici ce que dit wikipedia.
@+

Allasan vous confondez MVCC et verrouillage optimiste.
MVCC est un algorithme de gestion du verrouillage optimiste basé sur le contrôle de concurrence de multiples version de "tuples". Il existe d'autres algorithmes. SQL Server n'utilise pas MVCC... C'est juste une connerie de wikimerdia ! En effet SQL Server utilise le concept original de SNAPSHOT ISOLATION tiré des recherches originales de Gray et Bernstein (inventeur de la journalisation des transactions des SRGBDR). Pour information, Bernstein travaille pour Microsoft depuis la fin des années 90...
http://www.cs.usyd.edu.au/~fekete/te...eSI-Fekete.pdf
ce doc explique les différents algorithmes de verrouillage optimiste de Oracle, PG et SQL Server en détail, et se base pour SQL Server sur "Microsoft Research", le pôle R&D de MS.

En complément ce papier montre que Oracle et SQL Server travaillent en mode SNAPSHOT :
http://www.vldb.org/pvldb/vol8/p2146-levandoski.pdf
Et non pas en "pur" MVCC. D'ailleurs le papier pointe que l'utilisation de MVCC interdit d'autres niveaux d'isolation, ce qui fait que PG est limité dans les différents niveaux d'isolation...
"Concurrency control to provide serializable transactions using multiple versions has been explored as well. For instance, Cahill et al explore adding a lock manager in Postgres that otherwise used versions to support only snapshot isolation [3]."
[3] M. J. Cahill, U. Rohm, and A. D. Fekete. Serializable Isolation for Snapshot Databases. In Sigmod, 2008, pp. 729 – 738.

Ce qui est bien confirmé dans la doc PG :
In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.
Extrait de : https://www.postgresql.org/docs/9.5/...ction-iso.html

CQFD !

Quand à DBCC c'est un utilitaire pour piloter le moteur de stockage et vous n'y trouverez rien pour nettoyer tempdb ni les versions de lignes liées au verrouillage optimiste....
Si ma parole ne vous suffit pas (comme vous la mettez en doute systématiquement...) vous pouvez lister les commandes de DBCC en lançant un :

Code :

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

DBCC HELP ('?')

Et pour avoir la syntaxe d'une commande faire :

Code :

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

DBCC HELP ('MaCommande DBCC')

Au lieu de tout lorgner avec une lunette façonné par PostGreSQL en croyant que c'est l'évangile, commencez par apprendre les fondements des SGBDR. Je vous recommande en premier le recueil de textes fondateurs des SGBDR disponible chez Mogan Kaufman "Readings in Database systemes"
https://www.amazon.com/Readings-Data.../dp/1558605231

A +

[alassanediakite]

Salut

Allasan

Alassane!

vous confondez MVCC et verrouillage optimiste.
MVCC est un algorithme de gestion du verrouillage optimiste basé sur le contrôle de concurrence de multiples version de "tuples". Il existe d'autres algorithmes. SQL Server n'utilise pas MVCC...

blanc bonnet, bonnet blanc...

C'est juste une connerie de wikimerdia !

c'est évident, c'est toi le maitre de la connaissance et de l'insulte

Pour information, Bernstein travaille pour Microsoft depuis la fin des années 90...

un détail. Mais je dirais que Tom lane est sur PostgreSQL depuis le début.

D'ailleurs le papier pointe que l'utilisation de MVCC interdit d'autres niveaux d'isolation, ce qui fait que PG est limité dans les différents niveaux d'isolation...

Très mal dit. C'est cela votre problème. Vous dénaturer l'information pour discréditer les autres produits.
PostgreSQL n'autorise pas seulement et seulement le niveau READ UNCOMMITTED. Mais je vous en prie dites-nous à quoi bon l'avoir.

Ce qui est bien confirmé dans la doc PG :
In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.
Extrait de : https://www.postgresql.org/docs/9.5/...ction-iso.html

dites le en français svp

comme vous la mettez en doute systématiquement...

ça c'est bien dommage, il fut un moment je ne jurais que par SQLpro.
Mais sachez que je ne mets pas en doute vos paroles c'est plutôt votre intension que je tente de mettre à nu et votre prétention que j'ai du mal à supporter.

Au lieu de tout lorgner avec une lunette façonné par PostGreSQL en croyant que c'est l'évangile

remplacez PostgreSQL (au lieu de PostGreSQL svp) par SQL Server et dites moi qui se sentira concerné.

commencez par apprendre les fondements des SGBDR.

c'est ma passion et heureusement que je ne me limite plus à "SQLpro dit..."
@+

[SQLpro]

Salut
Alassane!
blanc bonnet, bonnet blanc...

Je comprends maintenant pourquoi tu confonds beaucoup de choses....

un détail. Mais je dirais que Tom lane est sur PostgreSQL depuis le début.

Légère différence... Il y en a un qui est un chercheur et qui a reçut l'équivalent du Nobel de math avec le prix turing et l'autre qui est un développeur. En sus tu te trompes, Tom Lane n'a commencé de participer à postGres que tardivement; PG ayant été créé par Stonebarker en 1986, Tom Lane n'ayant rejoint PostGreSQL que vers 2000... Pour info dans les références de livre que je t'ais donné, tu trouvera un papier original de Stonebraker sur PG... (

Très mal dit. C'est cela votre problème. Vous dénaturer l'information pour discréditer les autres produits.
PostgreSQL n'autorise pas seulement et seulement le niveau READ UNCOMMITTED. Mais je vous en prie dites-nous à quoi bon l'avoir.

Désolé mais votre phrase est incompréhensible.
mais si le niveau d'islation READ UNCOMMITTED existe c'est qu'il y a des raisons, et de l'intérêt. Relisez les artciles que j'ai écrit à ce sujet et notamment :
http://sqlpro.developpez.com/isolation-transaction/

A +