Menu
▾
▴
treebase-devel — TreeBASE developer mailing list
You can subscribe to this list here.
| 2009 |
Jan
|
Feb
|
Mar
(1) |
Apr
(41) |
May
(41) |
Jun
(50) |
Jul
(14) |
Aug
(21) |
Sep
(37) |
Oct
(8) |
Nov
(4) |
Dec
(135) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2010 |
Jan
(145) |
Feb
(110) |
Mar
(216) |
Apr
(101) |
May
(42) |
Jun
(42) |
Jul
(23) |
Aug
(17) |
Sep
(33) |
Oct
(15) |
Nov
(18) |
Dec
(6) |
| 2011 |
Jan
(8) |
Feb
(10) |
Mar
(8) |
Apr
(41) |
May
(48) |
Jun
(62) |
Jul
(7) |
Aug
(9) |
Sep
(7) |
Oct
(11) |
Nov
(49) |
Dec
(1) |
| 2012 |
Jan
(17) |
Feb
(63) |
Mar
(4) |
Apr
(13) |
May
(17) |
Jun
(21) |
Jul
(10) |
Aug
(10) |
Sep
|
Oct
|
Nov
|
Dec
(16) |
| 2013 |
Jan
(10) |
Feb
|
Mar
(1) |
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2014 |
Jan
(5) |
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
(5) |
Nov
|
Dec
|
| 2015 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
(1) |
Nov
|
Dec
|
|
From: Hilmar L. <hl...@ne...> - 2010-04-23 15:55:27
|
It is not unusual at all for the combined indices on a table to take up as much space as the table itself. Especially so for highly connected tables, i.e., tables where almost all columns are either primary key or foreign key columns, as is by design the atomic element table in vertical storage, such as the matrixelement table. In fact, it is common practice to estimate total space needed by a database by doubling the amount of space needed by the data, unless a substantial fraction of the data is in LOBs (which can be big but are never indexed). That is because in typical databases the one or two tables driving the overall space requirements are also the most highly indexed ones. So I see nothing out of the ordinary or unusual or unexpected here - I don't think it's worth spending time on right now. Let's just add more space. -hilmar On Apr 23, 2010, at 11:16 AM, Jon Auman wrote: > It's specifically an issue with the matrixelement index. The table > is 44 GB and the index is 45GB. > > You could run two different queries against the database using > "EXPLAIN ANALYZE SELECT ..." > The first query would run against the index, the second would run > after we drop the index, and then compare results > > Of course, we should do this on dev or staging. I'd be happy to > coordinate. > > -Jon > > > On Apr 23, 2010, at 10:45 AM, William Piel wrote: > >> >> On Apr 23, 2010, at 9:58 AM, Jon Auman wrote: >> >>> If we fine tune the query, we see that the indices are taking over >> >> Hmm.. at some point we added a bunch of indices in a effort to >> improve performance. I wonder if the alarming growth from 50GB to >> 90GB occurred as a results of adding indices? In which case this >> increase would be a "one-off" big jump, and therefore future growth >> would, hopefully, not be so alarming. >> >> Is there a way to know which indices have a valuable improvement in >> performance and are just wasting disk space? >> >> bp >> >> >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> 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... > ------------------------------------------------------ > > > > > ------------------------------------------------------------------------------ > _______________________________________________ > Treebase-devel mailing list > Tre...@li... > https://lists.sourceforge.net/lists/listinfo/treebase-devel -- =========================================================== : Hilmar Lapp -:- Durham, NC -:- informatics.nescent.org : =========================================================== |
|
From: Jon A. <jon...@du...> - 2010-04-23 15:16:27
|
It's specifically an issue with the matrixelement index. The table is 44 GB and the index is 45GB. You could run two different queries against the database using "EXPLAIN ANALYZE SELECT ..." The first query would run against the index, the second would run after we drop the index, and then compare results Of course, we should do this on dev or staging. I'd be happy to coordinate. -Jon On Apr 23, 2010, at 10:45 AM, William Piel wrote: > > On Apr 23, 2010, at 9:58 AM, Jon Auman wrote: > >> If we fine tune the query, we see that the indices are taking over > > Hmm.. at some point we added a bunch of indices in a effort to improve performance. I wonder if the alarming growth from 50GB to 90GB occurred as a results of adding indices? In which case this increase would be a "one-off" big jump, and therefore future growth would, hopefully, not be so alarming. > > Is there a way to know which indices have a valuable improvement in performance and are just wasting disk space? > > bp > > > > ------------------------------------------------------------------------------ > _______________________________________________ > 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... ------------------------------------------------------ |
|
From: William P. <wil...@ya...> - 2010-04-23 14:50:16
|
On Apr 23, 2010, at 9:58 AM, Jon Auman wrote: > If we fine tune the query, we see that the indices are taking over Hmm.. at some point we added a bunch of indices in a effort to improve performance. I wonder if the alarming growth from 50GB to 90GB occurred as a results of adding indices? In which case this increase would be a "one-off" big jump, and therefore future growth would, hopefully, not be so alarming. Is there a way to know which indices have a valuable improvement in performance and are just wasting disk space? bp |
|
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...
------------------------------------------------------
|
|
From: William P. <wil...@ya...> - 2010-04-23 13:27:47
|
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)
|
|
From: Hilmar L. <hl...@ne...> - 2010-04-23 13:19:17
|
On Apr 23, 2010, at 8:39 AM, William Piel wrote: > On Apr 23, 2010, at 6:37 AM, Jon Auman wrote: > >> I just exported the tomcat logs to another filesystem. It is >> recording every single query. One log was 1/2 a GB. Do we need to >> record every query, or can I change the logging to only log errors? > > Logging only errors seems okay with me. Does anyone else have an > opinion? That should be fine, I think. -hilmar -- =========================================================== : Hilmar Lapp -:- Durham, NC -:- informatics.nescent.org : =========================================================== |
|
From: William P. <wil...@ya...> - 2010-04-23 12:39:37
|
On Apr 23, 2010, at 6:37 AM, Jon Auman wrote: > I just exported the tomcat logs to another filesystem. It is recording every single query. One log was 1/2 a GB. Do we need to record every query, or can I change the logging to only log errors? Logging only errors seems okay with me. Does anyone else have an opinion? > Secondly, the database is growing by leaps and bounds. I'm almost out of disk space for that as well. I need to schedule downtime very soon to extend the volume. The db has grown from 50 GB two months ago to over 90GB. Do you antiicipate this trend to continue? Is there junk in there that can be removed? Yikes. That's weird. I have no reason to believe that the trend won't continue -- it's not as though we recently did something special that won't be repeated. I wonder if trash is collecting somewhere? Is there a way to output relative MB disc space used by different tables? If so, could you run it on stage and production (seeing as stage was a snapshot of production a week or two ago)? -- and then we could check out what table is growing out of control. thanks, bp |
|
From: William P. <wil...@ya...> - 2010-04-22 19:11:06
|
On Apr 22, 2010, at 2:55 PM, Vladimir Gapeyev wrote: > Do you want to see it on treebase-dev first or on production right away? It can go right away because we've been testing it on our deployment at the Peabody. bp |
|
From: Vladimir G. <vla...@du...> - 2010-04-22 18:55:52
|
Do you want to see it on treebase-dev first or on production right away? --VG On Apr 22, 2010, at 2:32 PM, William Piel wrote: > > Hi guys, > > Can we do a new build? > > Several improvements include: > > - our analysis GUI is now compatible with IE8 > - our analysis step download is working again, this time a single > taxon block is referenced by both input and output objects, and > taxon labels are sorted so that PAUP does not scramble names and > matrix rows. > - charsets and codonpossets now reference specific character blocks > - TreeBASE ids are now presented as URIs > > regards, > > Bill > > > > ------------------------------------------------------------------------------ > _______________________________________________ > Treebase-devel mailing list > Tre...@li... > https://lists.sourceforge.net/lists/listinfo/treebase-devel |
|
From: William P. <wil...@ya...> - 2010-04-22 18:32:59
|
Hi guys, Can we do a new build? Several improvements include: - our analysis GUI is now compatible with IE8 - our analysis step download is working again, this time a single taxon block is referenced by both input and output objects, and taxon labels are sorted so that PAUP does not scramble names and matrix rows. - charsets and codonpossets now reference specific character blocks - TreeBASE ids are now presented as URIs regards, Bill |
|
From: Hilmar L. <hl...@ne...> - 2010-04-20 18:29:09
|
Guys, the late poster submission for ISMB ends Fri next week. Unless we want to abandon our previous plan that I would present a TreeBASE2 poster there, we need to get working on a compelling abstract now. Bill said he has a start from before? Can you seed a Google Doc with that or send it to me? -hilmar -- =========================================================== : Hilmar Lapp -:- Durham, NC -:- informatics.nescent.org : =========================================================== |
|
From: Rutger V. <rut...@gm...> - 2010-04-15 21:46:17
|
> Comments are welcome. Let me know if anyone's middle initials need fixing. Rutger A. Vos, if you don't mind. -- Dr. Rutger A. Vos School of Biological Sciences Philip Lyle Building, Level 4 University of Reading Reading RG6 6BX United Kingdom Tel: +44 (0) 118 378 7535 http://www.nexml.org http://rutgervos.blogspot.com |
|
From: Vladimir G. <vla...@du...> - 2010-04-15 21:07:57
|
On Apr 15, 2010, at 4:35 PM, William Piel wrote: > I won't do any more INSERT statements (until I get tutored by > Hilmar) but I assume it is okay for me to do UPDATE and DELETE ones? Right, I was only worried about hand-generated PKs. -vg |
|
From: Vladimir G. <vga...@ne...> - 2010-04-15 21:05:53
|
All Treebase instances now send password reminder emails. Anyone who installs TB SVN#689 or later should add tb2/SmtpHost JNDI parameter to their server. As usual, see https://sourceforge.net/apps/mediawiki/treebase/index.php?title=Deployment_by_a_sysadmin #Configure_Tomcat_for_the_Treebase_application or treebase-web/src/main/webapp/META-INF/context.xml.example for details. This fixes SF#2977860 --Vladimir |
|
From: William P. <wil...@ya...> - 2010-04-15 20:35:38
|
On Apr 15, 2010, at 3:42 PM, Vladimir Gapeyev wrote: > This patch is now in and Treebase is back online. fabulous -- thanks. I won't do any more INSERT statements (until I get tutored by Hilmar) but I assume it is okay for me to do UPDATE and DELETE ones? thanks, bp |
|
From: William P. <wil...@ya...> - 2010-04-15 20:32:00
|
On Apr 15, 2010, at 3:58 PM, Hilmar Lapp wrote: > Note that software bazaar submissions aren't due before June. > > -hilmar Oh! whoops. Well, I wrote something up anyway... see attached. Please let me know if your Comments are welcome. Let me know if anyone's middle initials need fixing. bp |
|
From: Vladimir G. <vla...@du...> - 2010-04-15 19:42:12
|
This patch is now in and Treebase is back online. --VG On Apr 15, 2010, at 2:38 PM, Vladimir Gapeyev wrote: > > On Apr 15, 2010, at 2:12 PM, William Piel wrote: > >>> >>> If you can truly say this is the first and last request of this kind >> >> Yes I can, in that for all future updates I'll use Hilmar's fancy >> SQL (once I learn from him how to do that). >> >> In terms of your fte vs mine, how would you compare the effort >> required to reset Hibernate's two sequences (taxonvariant_id and >> taxon_id) at new levels vs the effort required to edit my 100+ lines >> of SQL? It took me from 8 PM to 2 AM last night to write my SQL >> list (with several episodes of Mad Men running in the background). > > I agree that we should give you direct update access to these three > tables, with proper precautions. However, I am not the one who makes > decisions on this. > > As for this data patch, we will execute it as soon as I can grab Jon > for an hour. Treebase will be down while we do that. > > Please hold on developing similar patches until we decide how to > handle them. > > --VG > > ------------------------------------------------------------------------------ > Download Intel® Parallel Studio Eval > Try the new software tools for yourself. Speed compiling, find bugs > proactively, and fine-tune applications for parallel performance. > See why Intel Parallel Studio got high marks during beta. > http://p.sf.net/sfu/intel-sw-dev > _______________________________________________ > Treebase-devel mailing list > Tre...@li... > https://lists.sourceforge.net/lists/listinfo/treebase-devel |
|
From: Vladimir G. <vga...@ne...> - 2010-04-15 18:38:24
|
On Apr 15, 2010, at 2:12 PM, William Piel wrote: >> >> If you can truly say this is the first and last request of this kind > > Yes I can, in that for all future updates I'll use Hilmar's fancy > SQL (once I learn from him how to do that). > > In terms of your fte vs mine, how would you compare the effort > required to reset Hibernate's two sequences (taxonvariant_id and > taxon_id) at new levels vs the effort required to edit my 100+ lines > of SQL? It took me from 8 PM to 2 AM last night to write my SQL > list (with several episodes of Mad Men running in the background). I agree that we should give you direct update access to these three tables, with proper precautions. However, I am not the one who makes decisions on this. As for this data patch, we will execute it as soon as I can grab Jon for an hour. Treebase will be down while we do that. Please hold on developing similar patches until we decide how to handle them. --VG |
|
From: William P. <wil...@ya...> - 2010-04-15 18:12:24
|
On Apr 15, 2010, at 12:56 PM, Vladimir Gapeyev wrote: > check that the PKids did not yet run past the ones that your script assigns I see -- so you need to do a "SELECT max(taxon_id) from taxon" and check that it is less than my starting PK. That sounds pretty easy to me -- I think I gave a fairly wide birth to insure that ongoing activity would not catch up to my starting PKs. > If ubio IDs are not unique enough Unfortunately they are not because animal and plant names can share the exact same string, and uBio issues namebankids for each unique string. > If you can truly say this is the first and last request of this kind Yes I can, in that for all future updates I'll use Hilmar's fancy SQL (once I learn from him how to do that). In terms of your fte vs mine, how would you compare the effort required to reset Hibernate's two sequences (taxonvariant_id and taxon_id) at new levels vs the effort required to edit my 100+ lines of SQL? It took me from 8 PM to 2 AM last night to write my SQL list (with several episodes of Mad Men running in the background). bp |
|
From: Vladimir G. <vla...@du...> - 2010-04-15 16:56:29
|
On Apr 15, 2010, at 12:04 PM, William Piel wrote:
> So you're saying that there is nothing equivalent to:
> SELECT pg_catalog.setval('taxa_taxa_id_seq', 80500, true);
> ... to tell Hibernate's sequencing system to reset to a higher value?
>
> There is no way to reset Hibernate's autoincrement value to a next
> higher one? I'm kind of surprised by that.
There is. But to make that work reliably with your script we'll need
first shut everybody out of the database and check that the PKids did
not yet run past the ones that your script assigns. If you can
truly say this is the first and last request of this kind, we can do
it today, Hilmar-permitting ;-)
>> Can you modify the script that the INSERT statements do not specify
>> the PK values?
>
> Absolutely -- however in some cases we use the new ID as FK for
> another insert or update. So those cases would have be be filtered
> out, and perhaps put in a perl script where "get last id" can be
> stored in a scalar for use in subsequent statements.
Wouldn't the lookup of newly assigned PKs via their ubio IDs that I
suggested work? If ubio IDs are not unique enough, then what Hilmar
has mentioned may work...
> (So... yet another advantage of Navicat is that I get to see the
> autogenerated id right away for use in subsequent edits).
Agreed, provided we can ensure that Navicat cannot modify the PK
fields directly (I'd guess, Postres has field-level access control,
but I am not sure.)
--VG
|
|
From: Hilmar L. <hl...@ne...> - 2010-04-15 16:20:21
|
On Apr 15, 2010, at 12:04 PM, William Piel wrote: > Absolutely -- however in some cases we use the new ID as FK for > another insert or update. So those cases would have be be filtered > out, and perhaps put in a perl script where "get last id" can be > stored in a scalar for use in subsequent statements. (So... yet > another advantage of Navicat is that I get to see the autogenerated > id right away for use in subsequent edits). Bill - this can all be scripted in SQL only. No Perl needed. We can talk about how. -hilmar -- =========================================================== : Hilmar Lapp -:- Durham, NC -:- informatics.nescent.org : =========================================================== |
|
From: William P. <wil...@ya...> - 2010-04-15 16:04:46
|
On Apr 15, 2010, at 10:26 AM, Vladimir Gapeyev wrote:
> As you anticipated, inserting new PKs will confuse Hibernate, since the PKs the update creates are higher than
> the current values in the sequences that generate the PKs under normal
> operation. The latter will catch up and clash eventually.
So you're saying that there is nothing equivalent to:
SELECT pg_catalog.setval('taxa_taxa_id_seq', 80500, true);
... to tell Hibernate's sequencing system to reset to a higher value?
There is no way to reset Hibernate's autoincrement value to a next higher one? I'm kind of surprised by that.
> Can you modify the script that the INSERT statements do not specify
> the PK values?
Absolutely -- however in some cases we use the new ID as FK for another insert or update. So those cases would have be be filtered out, and perhaps put in a perl script where "get last id" can be stored in a scalar for use in subsequent statements. (So... yet another advantage of Navicat is that I get to see the autogenerated id right away for use in subsequent edits).
your thoughts?
bp
|
|
From: Rutger V. <rut...@gm...> - 2010-04-15 15:24:18
|
Maybe it is just the timezone I'm in but I feel like I'm about to turn into a pumpkin. In half an hour I'm going to upload the attached abstract to the registration site. OK? On Thu, Apr 15, 2010 at 4:20 PM, Hilmar Lapp <hl...@ne...> wrote: > > On Apr 15, 2010, at 11:17 AM, Rutger Vos wrote: > >> here's my opening sentence right now: TreeBASE[1] is a >> database of peer-reviewed phylogenetic data and metadata. >> >> 1. http://treebase.org, source code available at >> http://sf.net/projects/treebase/ under a BSD license. >> >> So it's the first footnote. I suppose that's OK? > > > Yes. > > -hilmar > -- > =========================================================== > : Hilmar Lapp -:- Durham, NC -:- informatics.nescent.org : > =========================================================== > > > > -- Dr. Rutger A. Vos School of Biological Sciences Philip Lyle Building, Level 4 University of Reading Reading RG6 6BX United Kingdom Tel: +44 (0) 118 378 7535 http://www.nexml.org http://rutgervos.blogspot.com |
|
From: Hilmar L. <hl...@ne...> - 2010-04-15 15:21:10
|
On Apr 15, 2010, at 11:17 AM, Rutger Vos wrote: > here's my opening sentence right now: TreeBASE[1] is a > database of peer-reviewed phylogenetic data and metadata. > > 1. http://treebase.org, source code available at > http://sf.net/projects/treebase/ under a BSD license. > > So it's the first footnote. I suppose that's OK? Yes. -hilmar -- =========================================================== : Hilmar Lapp -:- Durham, NC -:- informatics.nescent.org : =========================================================== |
|
From: Rutger V. <rut...@gm...> - 2010-04-15 15:17:27
|
Great, thanks, here's my opening sentence right now: TreeBASE[1] is a database of peer-reviewed phylogenetic data and metadata. 1. http://treebase.org, source code available at http://sf.net/projects/treebase/ under a BSD license. So it's the first footnote. I suppose that's OK? On Thu, Apr 15, 2010 at 4:11 PM, Hilmar Lapp <hl...@ne...> wrote: > > On Apr 15, 2010, at 10:55 AM, Rutger Vos wrote: > >> I know there's a requirement to have an open source license, I'm not sure >> that means I'd have to mention it in the abstract. > > Yes, you should mention it in the abstract. (The documentation states that.) > If you don't, reviewers (or committee members) have to go looking for it. > >> If I do, would it be OK to just say TreeBASE is released under a >> "BSD-style license"? > > Why not say "BSD license"? Isn't it a BSD license? "BSD-style" sounds more > wishy-washy than it is. > > -hilmar > > -- > =========================================================== > : Hilmar Lapp -:- Durham, NC -:- informatics.nescent.org : > =========================================================== > > > > -- Dr. Rutger A. Vos School of Biological Sciences Philip Lyle Building, Level 4 University of Reading Reading RG6 6BX United Kingdom Tel: +44 (0) 118 378 7535 http://www.nexml.org http://rutgervos.blogspot.com |
9 messages has been excluded from this view by a project administrator.
