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