--
1G6
52 route de bischwiller
67300 Schiltigheim
Société de Services et de Formations en Logiciels Libres
http://1g6.biz
Tél : 06 64 63 70 35

De: "Martin Kersten" <Martin.Kersten@cwi.nl>
À: users-list@monetdb.org
Envoyé: Mercredi 29 Octobre 2014 15:08:06
Objet: Re: detect fragmented tables

On 29/10/14 15:00, Pierre-Adrien Coustillas wrote:
> thank you for your reply.
>
> I understand that in the storagemodel table (), the heapsize column shows the maximum size that can be used.
The actual footprint for a given table (e.g. schemas) is:

select sum(columnsize) + sum(heapsize)+ sum(hashes) + sum(imprints) from storage where "table" = 'schemas';
> This size may also be obtained by multiplying the table storage columns "count" and "typewitdh"
> This size should not logically be exceeded.
> To detect fragmented columns do you think of this request ?
>
> select "table","column","type","count",typewidth,columnsize,(heapsize/1021/1024) as mega,heapsize/(count*typewidth) as taux,heapsize,count*typewidth as heapsize_max FROM sys.storage WHERE heapsize > count*typewidth AND heapsize > 100000 AND typewidth>0
> AND count >0 ORDER BY taux ;
>
> The bottom lines are the fields to be defragmented
>
> Example with my database:
>
> | src__at__tra__visites_agregees | idvisite | varchar | 47178937 | 1 | 188715748 | 321 | 7 | 335609856 | 47178937 |
> | src__at__tra__visites_thomas | idvisiteur | varchar | 6310678 | 19 | 25242712 | 902 | 7 | 943718400 | 119902882 |
> | test_integrite_tra | idvisite | varchar | 19681703 | 2 | 78726812 | 266 | 7 | 278921216 | 39363406 |
> | src__at__tra__visites | idvisite | varchar | 23134243 | 2 | 92536972 | 316 | 7 | 331350016 | 46268486 |
> | src_inbox__at__tra__visites | idvisite | varchar | 22812014 | 2 | 91248056 | 310 | 7 | 324337664 | 45624028 |
> | src__at__lm__visites | idvisite | varchar | 331519688 | 1 | 1326078752 | 3513 | 11 | 3673489408 | 331519688 |
> | test_integrite | idvisite | varchar | 394962662 | 1 | 3159701296 | 4200 | 11 | 4392091648 | 394962662 |
> | sc_septembre | idvisiteur | varchar | 24155689 | 19 | 193245512 | 7224 | 16 | 7553548288 | 458958091 |
> | sc_aout | idvisiteur | varchar | 29586396 | 19 | 236691168 | 14449 | 26 | 15107031040 | 562141524 |
> | src__at__tra__visites_thomas | idvisite | varchar | 6310678 | 2 | 25242712 | 402 | 33 | 420675584 | 12621356 |
> +--------------------------------------+---------------------+---------+-----------+-----------+------------+-------+------+-------------+--------------+
>
> Pierre
>
> --
> 1G6
> 52 route de bischwiller
> 67300 Schiltigheim
> Société de Services et de Formations en Logiciels Libres
> http://1g6.biz
> Tél : 06 64 63 70 35
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *De: *"Martin Kersten" <Martin.Kersten@cwi.nl>
> *À: *users-list@monetdb.org
> *Envoyé: *Vendredi 24 Octobre 2014 14:59:36
> *Objet: *Re: detect fragmented tables
>
> On 24/10/14 10:37, Pierre-Adrien Coustillas wrote:
> > Hello
> Hi
> >
> > Before working with optimizer, Stethoscope and Tomograph, first I have to defragment the fragmented tables.
> > How to detect fragmented tables?
> The heap size is larger then the expected count would indicate.
> > Write a script that reads heapsize column?
> The storagemodel could give you hints on expected sizes.
> >
> > select *, (heapsize / 1021/1024/1024) as giga from sys.storage heapsize ORDER BY DESC LIMIT 20;
> > What do you think?
> select *, (heapsize / 1021/1024/1024) as giga from sys.storage order by heapsize desc limit 20;
> >
> > or a more specific sql query by reading the column "count", "typewidth", "ColumnSize" and "heapsize"?
> >
> regards, Martin
> > thank
> >
> > Pierre
> >
> > --
> > 1G6
> > 52 route de bischwiller
> > 67300 Schiltigheim
> > Société de Services et de Formations en Logiciels Libres
> > http://1g6.biz
> > Tél : 06 64 63 70 35
> >
> >
> >
> > _______________________________________________
> > users-list mailing list
> > users-list@monetdb.org
> > https://www.monetdb.org/mailman/listinfo/users-list
> >
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
>
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list