Menu
▾
▴
Re: [Treebase-devel] TreeBASE disk space
|
From: Jon A. <jon...@du...> - 2010-04-23 13:59:08
|
Thanks Bill. If we fine tune the query, we see that the indices are taking over. I think we may need to revisit these indices. I question the value of an index that is larger than the underlying data!
[jonauman@treebase ~]$ psql -Upostgres treebase -c "SELECT c.relname AS Name,
pg_size_pretty(pg_relation_size(CAST (c.relname AS text))) AS Data,
pg_size_pretty(pg_total_relation_size(CAST (c.relname AS text))-pg_relation_size(CAST (c.relname AS text))) AS Indices,
pg_size_pretty(pg_total_relation_size(CAST (c.relname AS text))) AS Total
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_table_is_visible(c.oid)
ORDER BY c.relpages DESC;"
name | data | indices | total
-----------------------------+------------+------------+------------
matrixelement | 44 GB | 45 GB | 88 GB
matrixcolumn | 548 MB | 371 MB | 919 MB
matrixrow | 245 MB | 69 MB | 314 MB
phylotreenode | 96 MB | 150 MB | 246 MB
taxonvariant | 86 MB | 51 MB | 137 MB
taxon | 44 MB | 36 MB | 80 MB
taxonlabelset_taxonlabel | 38 MB | 55 MB | 93 MB
columnrange | 15 MB | 6856 kB | 22 MB
taxonlabel | 16 MB | 18 MB | 34 MB
charset_colrange | 12 MB | 6104 kB | 18 MB
discretecharstate | 9928 kB | 4016 kB | 14 MB
phylotree | 9536 kB | 3096 kB | 12 MB
study_nexusfile | 7360 kB | 107 MB | 115 MB
sub_taxonlabel | 6616 kB | 8112 kB | 14 MB
citation | 6008 kB | 1120 kB | 7128 kB
phylochar | 2472 kB | 880 kB | 3352 kB
taxonlabelset | 1056 kB | 408 kB | 1464 kB
chargroup_colrange | 944 kB | 520 kB | 1464 kB
analyzeddata | 952 kB | 328 kB | 1280 kB
matrix | 928 kB | 160 kB | 1088 kB
treeblock | 624 kB | 544 kB | 1168 kB
citation_author | 568 kB | 336 kB | 904 kB
sub_treeblock | 576 kB | 560 kB | 1136 kB
analysisstep | 568 kB | 176 kB | 744 kB
charset | 528 kB | 216 kB | 744 kB
charweight_colrange | 424 kB | 256 kB | 680 kB
person | 416 kB | 320 kB | 736 kB
algorithm | 392 kB | 176 kB | 568 kB
analysis | 392 kB | 328 kB | 720 kB
codonpositionset | 376 kB | 168 kB | 544 kB
software | 288 kB | 168 kB | 456 kB
codonnoncoding_colrange | 280 kB | 176 kB | 456 kB
sub_matrix | 280 kB | 312 kB | 592 kB
study | 272 kB | 192 kB | 464 kB
submission | 192 kB | 240 kB | 432 kB
chargroup | 72 kB | 72 kB | 144 kB
codonchar2_colrange | 56 kB | 64 kB | 120 kB
codonchar1_colrange | 48 kB | 64 kB | 112 kB
charweight | 32 kB | 56 kB | 88 kB
help | 40 kB | 64 kB | 104 kB
charpartition | 24 kB | 40 kB | 64 kB
codonchar3_colrange | 24 kB | 56 kB | 80 kB
user | 16 kB | 56 kB | 72 kB
gapmode | 8192 bytes | 16 kB | 24 kB
itemdefinition | 8192 bytes | 16 kB | 24 kB
matrixdatatype | 8192 bytes | 32 kB | 40 kB
matrixkind | 8192 bytes | 16 kB | 24 kB
rowsegment | 16 kB | 64 kB | 80 kB
studystatus | 8192 bytes | 16 kB | 24 kB
versionhistory | 8192 bytes | 40 kB | 48 kB
userrole | 8192 bytes | 16 kB | 24 kB
stateformat | 8192 bytes | 16 kB | 24 kB
polytcount | 8192 bytes | 16 kB | 24 kB
treekind | 8192 bytes | 16 kB | 24 kB
treequality | 8192 bytes | 16 kB | 24 kB
treetype | 8192 bytes | 16 kB | 24 kB
charweightset | 8192 bytes | 16 kB | 24 kB
citation_editor | 8192 bytes | 16 kB | 24 kB
citationstatus | 8192 bytes | 16 kB | 24 kB
treepartition | 0 bytes | 8192 bytes | 8192 bytes
itemvalue | 0 bytes | 8192 bytes | 8192 bytes
treeset | 0 bytes | 8192 bytes | 8192 bytes
treeset_phylotree | 0 bytes | 8192 bytes | 8192 bytes
cstreenode | 0 bytes | 8192 bytes | 8192 bytes
statechangeset | 0 bytes | 8192 bytes | 8192 bytes
ancestralstate | 0 bytes | 16 kB | 16 kB
rightchangeset_charstate | 0 bytes | 0 bytes | 0 bytes
ancstateset | 0 bytes | 8192 bytes | 8192 bytes
stepmatrixelement | 0 bytes | 8192 bytes | 8192 bytes
taxonlabelgroup_taxonlabel | 0 bytes | 0 bytes | 0 bytes
matrixcolumn_itemdefinition | 0 bytes | 8192 bytes | 8192 bytes
geneticcode | 0 bytes | 16 kB | 16 kB
matrix_itemdefinition | 0 bytes | 8192 bytes | 8192 bytes
--More--
On Apr 23, 2010, at 9:27 AM, William Piel wrote:
>
> On Apr 23, 2010, at 8:39 AM, William Piel wrote:
>
>> Is there a way to output relative MB disc space used by different tables?
>
> Running the query below, I see that matrixelement is the biggest memory hog, taking up 81 GB. Is this also where disproportionate growth is coming from? (e.g. comparing this with production?). No other table is measured in GBs.
>
> regards,
>
> Bill
>
>
> SELECT c.relname AS "Relation",
> pg_size_pretty(pg_total_relation_size( CAST (c.relname AS text) )) AS "Relation Size"
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
> AND pg_catalog.pg_table_is_visible(c.oid);
>
> Relation | Relation Size
> -----------------------------+---------------
> distancematrixelement | 8192 bytes
> cstreenode | 8192 bytes
> itemvalue | 8192 bytes
> leftchangeset_charstate | 0 bytes
> geneticcodeset | 16 kB
> help | 96 kB
> nodeattribute | 8192 bytes
> matrixkind | 24 kB
> geneticcode | 16 kB
> matrix_itemdefinition | 8192 bytes
> matrixdatatype | 40 kB
> itemdefinition | 24 kB
> matrixcolumn_itemdefinition | 8192 bytes
> matrixrow | 290 MB
> matrixcolumn | 855 MB
> gapmode | 24 kB
> study_nexusfile | 111 MB
> phylotreenode | 151 MB
> stateset | 8192 bytes
> statemodifier | 8192 bytes
> rightchangeset_charstate | 0 bytes
> rowsegment | 48 kB
> studystatus | 24 kB
> phylochar | 3224 kB
> stateformat | 24 kB
> sub_matrix | 512 kB
> statechangeset | 8192 bytes
> sub_taxonlabel | 14 MB
> sub_treeblock | 1096 kB
> submission | 424 kB
> taxonvariant | 137 MB
> taxonset | 8192 bytes
> taxonlabelgroup | 8192 bytes
> taxonset_taxon | 8192 bytes
> treegroup | 8192 bytes
> typeset | 8192 bytes
> taxonlabelset_taxonlabel | 92 MB
> taxonauthority | 16 kB
> taxon | 79 MB
> taxonlabelset | 1376 kB
> treeattribute | 8192 bytes
> treegroup_phylotree | 8192 bytes
> treekind | 24 kB
> treenodeedge | 8192 bytes
> treepartition | 8192 bytes
> treequality | 24 kB
> treeset_phylotree | 8192 bytes
> treetype | 24 kB
> versionhistory | 48 kB
> userrole | 24 kB
> usertyperrd_colrange | 8192 bytes
> usertype | 8192 bytes
> polytcount | 24 kB
> algorithm | 560 kB
> chargroup_colrange | 1456 kB
> analysis | 688 kB
> discretecharstate | 14 MB
> analysisstep | 720 kB
> phylotree | 8568 kB
> analyzeddata | 1272 kB
> software | 448 kB
> ancestralstate | 16 kB
> study | 424 kB
> ancstateset | 8192 bytes
> chargroup | 144 kB
> charweightset | 24 kB
> citation | 4256 kB
> citation_author | 784 kB
> charpartition | 64 kB
> citation_editor | 24 kB
> charset | 736 kB
> person | 736 kB
> charset_colrange | 17 MB
> charweight | 88 kB
> charweight_colrange | 680 kB
> codonchar3_colrange | 80 kB
> codonnoncoding_colrange | 440 kB
> codonpositionset | 544 kB
> columnrange | 22 MB
> citationstatus | 24 kB
> compound_element | 16 kB
> coderecord_colrange | 8192 bytes
> geneticcoderecord | 8192 bytes
> codonchar1_colrange | 112 kB
> matrix | 1024 kB
> codonchar2_colrange | 112 kB
> taxonlabelpartition | 8192 bytes
> contancstate_value | 0 bytes
> taxonlink | 8192 bytes
> matrixelement | 81 GB
> stepmatrixelement | 8192 bytes
> treeblock | 1152 kB
> user | 40 kB
> taxonlabel | 30 MB
> treeset | 8192 bytes
> taxonlabelgroup_taxonlabel | 0 bytes
> usertyperecord | 8192 bytes
> (97 rows)
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Treebase-devel mailing list
> Tre...@li...
> https://lists.sourceforge.net/lists/listinfo/treebase-devel
-------------------------------------------------------
Jon Auman
Systems Administrator
National Evolutionary Synthesis Center
Duke University
http:www.nescent.org
jon...@ne...
------------------------------------------------------
|
