Hello Before working with optimizer, Stethoscope and Tomograph , first I have to defragment the fragmented tables. How to detect fragmented tables ? Write a script that reads heapsize column ? select * , ( heapsize / 1021/1024/1024) as giga from sys.storage heapsize ORDER BY DESC LIMIT 20; What do you think ? or a more specific sql query by reading the column "count ", " typewidth ", " ColumnSize " and " heapsize " ? 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
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
thank you for your reply.
I understand that in the storagemodel table (), the heapsize column shows the maximum size that can be used.
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
----- Mail original -----
De: "Martin Kersten"
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
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"
*À: *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
There are no columns " hashes" and " imprints" in the storage table.
I use the Jan2014-SP2 version
This is for OCT2014 version?
--
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
----- Mail original -----
De: "Martin Kersten"
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"
*À: *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
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
participants (2)
-
Martin Kersten
-
Pierre-Adrien Coustillas