Re: detect fragmented tables

30 Oct 2014


      On 10/30/2014 10:45 AM, Pierre-Adrien Coustillas wrote:
...
There are no columns "hashes"and "imprints" in the storage table.
I use the Jan2014-SP2 version
This is for OCT2014 version?
Oops, yes.
--
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" 
*À: *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
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list