[Treebase-devel] Server sluggishness

[Jon A. <jon...@du...>]

I've noticed a lot of hung queries on the production treebase server over the last couple of weeks. The hung queries consume all available memory and CPU cycles. I propose that I put timeout limit on queries, so that postgres will automatically kill them if they go on to long. I propose 10 minutes. Does that seem reasonable?

FYI. the stuck query is always as follows:

select matrixcolu0_.MATRIXCOLUMN_ID as MATRIXCO1_26_10_, matrixcolu0_.VERSION as
VERSION26_10_, matrixcolu0_.PHYLOCHAR_ID as PHYLOCHAR3_26_10_,
matrixcolu0_.MATRIX_ID as MATRIX4_26_10_, matrixcolu0_.STATEFORMAT_ID as
STATEFOR5_26_10_, phylochar1_.PHYLOCHAR_ID as PHYLOCHAR2_15_0_,
phylochar1_.VERSION as VERSION15_0_, phylochar1_.Description as Descript4_15_0_,
phylochar1_.LowerLimit as LowerLimit15_0_, phylochar1_.UpperLimit as
UpperLimit15_0_, phylochar1_.TYPE as TYPE15_0_, characterm2_.MATRIX_ID as
MATRIX2_6_1_, characterm2_.VERSION as VERSION6_1_, characterm2_.TB_MatrixID as
TB4_6_1_, characterm2_.MATRIXDATATYPE_ID as MATRIXD18_6_1_,
characterm2_.Description as Descript5_6_1_, characterm2_.GapSymbol as
GapSymbol6_1_, characterm2_.MATRIXKIND_ID as MATRIXKIND19_6_1_,
characterm2_.MissingSymbol as MissingS7_6_1_, characterm2_.NexusFileName as
NexusFil8_6_1_, characterm2_.Published as Published6_1_, characterm2_.STUDY_ID
as STUDY20_6_1_, characterm2_.Symbols as Symbols6_1_,
characterm2_.TAXONLABELSET_ID as TAXONLA21_6_1_, characterm2_.Title as
Title6_1_, characterm2_.ANCSTATESET_ID as ANCSTAT22_6_1_,
characterm2_.CODONPOSITIONSET_ID as CODONPO23_6_1_, characterm2_.CHARSET_ID as
CHARSET24_6_1_, characterm2_.TYPESET_ID as TYPESET25_6_1_,
characterm2_.CHARWEIGHTSET_ID as CHARWEI26_6_1_, characterm2_.nChar as
nChar6_1_, characterm2_.nTax as nTax6_1_, characterm2_.Aligned as Aligned6_1_,
characterm2_.CaseSensitive as CaseSen17_6_1_, characterm2_.MATRIXTYPE as
MATRIXTYPE6_1_, matrixdata3_.MATRIXDATATYPE_ID as MATRIXDA1_27_2_,
matrixdata3_.VERSION as VERSION27_2_, matrixdata3_.PHYLOCHAR_ID as
PHYLOCHAR4_27_2_, matrixdata3_.Description as Descript3_27_2_,
phylochar4_.PHYLOCHAR_ID as PHYLOCHAR2_15_3_, phylochar4_.VERSION as
VERSION15_3_, phylochar4_.Description as Descript4_15_3_, phylochar4_.LowerLimit
as LowerLimit15_3_, phylochar4_.UpperLimit as UpperLimit15_3_, phylochar4_.TYPE
as TYPE15_3_, ancstatese5_.ANCSTATESET_ID as ANCSTATE1_5_4_,
ancstatese5_.VERSION as VERSION5_4_, ancstatese5_.Title as Title5_4_,
codonposit6_.CodonPositionSet_ID as CodonPos1_14_5_, codonposit6_.VERSION as
VERSION14_5_, codonposit6_.Title as Title14_5_, excludedch7_.CHARSET_ID as
CHARSET2_9_6_, excludedch7_.VERSION as VERSION9_6_, excludedch7_.MATRIX_ID as
MATRIX5_9_6_, excludedch7_.Title as Title9_6_, characterm8_.MATRIX_ID as
MATRIX2_6_7_, characterm8_.VERSION as VERSION6_7_, characterm8_.TB_MatrixID as
TB4_6_7_, characterm8_.MATRIXDATATYPE_ID as MATRIXD18_6_7_,
characterm8_.Description as Descript5_6_7_, characterm8_.GapSymbol as
GapSymbol6_7_, characterm8_.MATRIXKIND_ID as MATRIXKIND19_6_7_,
characterm8_.MissingSymbol as MissingS7_6_7_, characterm8_.NexusFileName as
NexusFil8_6_7_, characterm8_.Published as Published6_7_, characterm8_.STUDY_ID
as STUDY20_6_7_, characterm8_.Symbols as Symbols6_7_,
characterm8_.TAXONLABELSET_ID as TAXONLA21_6_7_, characterm8_.Title as
Title6_7_, characterm8_.ANCSTATESET_ID as ANCSTAT22_6_7_,
characterm8_.CODONPOSITIONSET_ID as CODONPO23_6_7_, characterm8_.CHARSET_ID as
CHARSET24_6_7_, characterm8_.TYPESET_ID as TYPESET25_6_7_,
characterm8_.CHARWEIGHTSET_ID as CHARWEI26_6_7_, characterm8_.nChar as
nChar6_7_, characterm8_.nTax as nTax6_7_, characterm8_.Aligned as Aligned6_7_,
characterm8_.CaseSensitive as CaseSen17_6_7_, characterm8_.MATRIXTYPE as
MATRIXTYPE6_7_, typeset9_.TYPESET_ID as TYPESET1_37_8_, typeset9_.VERSION as
VERSION37_8_, typeset9_.Title as Title37_8_, charweight10_.CHARWEIGHTSET_ID as
CHARWEIG1_11_9_, charweight10_.VERSION as VERSION11_9_, charweight10_.Title as
Title11_9_ from MATRIXCOLUMN matrixcolu0_ left outer join PHYLOCHAR phylochar1_
on matrixcolu0_.PHYLOCHAR_ID=phylochar1_.PHYLOCHAR_ID left outer join MATRIX
characterm2_ on matrixcolu0_.MATRIX_ID=characterm2_.MATRIX_ID left outer join
MATRIXDATATYPE matrixdata3_ on
characterm2_.MATRIXDATATYPE_ID=matrixdata3_.MATRIXDATATYPE_ID left outer join
PHYLOCHAR phylochar4_ on matrixdata3_.PHYLOCHAR_ID=phylochar4_.PHYLOCHAR_ID left
outer join ANCSTATESET ancstatese5_ on
characterm2_.ANCSTATESET_ID=ancstatese5_.ANCSTATESET_ID left outer join
CodonPositionSet codonposit6_ on
characterm2_.CODONPOSITIONSET_ID=codonposit6_.CodonPositionSet_ID left outer
join CHARSET excludedch7_ on characterm2_.CHARSET_ID=excludedch7_.CHARSET_ID
left outer join MATRIX characterm8_ on
excludedch7_.MATRIX_ID=characterm8_.MATRIX_ID left outer join TYPESET typeset9_
on characterm2_.TYPESET_ID=typeset9_.TYPESET_ID left outer join CHARWEIGHTSET
charweight10_ on characterm2_.CHARWEIGHTSET_ID=charweight10_.CHARWEIGHTSET_ID
where matrixcolu0_.MATRIX_ID=$1 and matrixcolu0_.COLUMN_ORDER=$2

-Jon
 
-------------------------------------------------------
Jon Auman
Systems Administrator
National Evolutionary Synthesis Center
Duke University
http:www.nescent.org
jon...@ne...
------------------------------------------------------