Ad Widget

Collapse

Slow query

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • garic
    started a topic Slow query

    Slow query

    Всем привет!
    После перехода от одного инстанса с БД на кластер стали вылезать slow query

    Code:
    9117:20190109:114021.219 slow query: 18.086959 sec, "commit;"
    9122:20190109:114021.219 slow query: 18.080909 sec, "commit;"
    и следом

    Code:
      9118:20190109:114024.971 slow query: 6.372091 sec, "insert into history (itemid,clock,ns,value) values (188308,1547030368,623078009,0.000000),(103168,1547030368,623801156,0.000000),(139468,1547030368,624807124,0.000000),(51328,1547030368,625343890,0.002148),(243748,1547030368,625732043,0.105750),(260308,1547030368,627470343,0.000000),(182428,1547030368,628174845,0.000000),(57268,1547030368,628282147,0.000000),(174568,1547030368,629356680,0.000000),(71308,1547030368,629385061,53.053777),(157588,1547030368,629551252,0.000000),(123328,1547030368,630388017,0.000000),(194008,1547030368,632170528,0.000000),(73948,1547030368,632215517,0.000000),(90328,1547030368,632720780,51.714590),(234688,1547030368,632822136,0.000000),(188368,1547030368,634200700,0.000000),(172768,1547030368,635604975,0.350254),(203248,1547030368,636992462,0.000000),(40288,1547030368,694893160,0.205000),(254128,1547030368,697402509,0.005546),(87748,1547030368,697733275,0.297091),(257728,1547030368,699365174,0.000000),(158488,1547030368,700689512,0.059416),(126268,1547030368,700767027,0.000000),(245728,1547030368,701949693,11.420784),(187468,1547030368,702140364,0.000000),(160108,1547030368,703031082,0.000000),(41308,1547030368,703342267,2.232500),(123988,1547030368,703358521,0.066016),(190948,1547030368,704503090,2.062929),(195568,1547030368,704644859,0.660990),(122548,1547030368,708721938,89.522502),(238108,1547030368,708853418,0.000000),(228928,1547030368,708943174,0.026404),(224008,1547030368,709008365,0.066010),(235888,1547030368,709574327,29.292641),(173908,1547030368,709631230,3.171043),(44608,1547030368,710995643,0.000000),(67348,1547030368,712001823,0.066050),(70708,1547030368,712051047,0.000000),(202948,1547030368,714232844,0.000000),(70588,1547030368,714607277,0.006601),(239008,1547030368,714819694,0.123264),(221908,1547030368,715852844,0.000066),(250708,1547030368,717514688,0.000000),(190048,1547030368,717712844,0.017425),(135748,1547030368,719979729,0.000066),(258628,1547030368,720799780,0.000000),(73528,1547030368,720829884,99.912154),(185068,1547030368,721019927,0.000000),(150148,1547030368,721406819,0.000066),(136408,1547030368,722766791,0.000000),(141868,1547030368,725185448,29.697816),(98908,1547030368,726208480,0.118787),(69328,1547030368,726863363,0.492500),(239788,1547030368,726955147,0.065993),(145408,1547030368,728556854,0.000000),(121948,1547030368,730883814,0.006599),(229888,1547030368,732314118,0.000000),(42928,1547030368,734060506,0.000000),(241348,1547030368,734186679,0.000000),(176608,1547030368,734549309,0.000000),(130948,1547030368,734704699,5.322283),(178888,1547030368,735796638,0.000000),(186148,1547030368,735920408,0.143743),(259648,1547030368,737007401,0.000000),(85708,1547030368,738733270,0.000000),(71728,1547030368,738849570,0.000000),(256708,1547030368,739117547,3.167095),(253588,1547030368,740098386,0.000000),(103708,1547030368,741147034,0.000000),(86788,1547030368,741151966,99.987183),(142528,1547030368,741915783,0.000000),(187888,1547030368,743087084,0.000000),(255868,1547030368,744891996,0.000000),(189148,1547030368,744989202,76.994174),(133168,1547030368,746727592,1.105073),(227128,1547030368,749423768,0.989581),(38608,1547030368,749505215,1.132500),(219568,1547030368,749633880,0.000000),(200128,1547030368,749651019,44.448305),(141568,1547030368,749844078,0.000000),(201568,1547030368,750146772,0.000000),(57748,1547030368,750585300,0.066839),(201988,1547030368,750712712,0.000000),(148048,1547030368,751115917,0.000000),(88408,1547030368,751218360,0.000000),(200488,1547030368,755471786,0.000000),(154828,1547030368,755592939,0.329837),(188128,1547030368,756068475,0.000000),(86308,1547030368,758578108,0.000000),(102988,1547030368,760061005,0.000000),(250408,1547030368,761356180,86.646270),(139348,1547030368,763260719,0.000000),(235768,1547030368,764843657,0.000000),(193228,1547030368,765053454,151.227786),(106588,1547030368,768692365,0.296795),(136168,1547030368,769355923,99.948632),(229288,1547030368,770251683,0.000000),(231928,1547030368,771419140,0.000000),(89248,1547030368,772647968,0.000000),(140488,1547030368,774303681,0.000000),(144028,1547030368,774304240,0.000000),(178228,1547030368,774318103,32.382766),(144508,1547030368,775250016,0.791432),(41248,1547030368,776160831,0.833656),(136228,1547030368,776957248,0.000000),(244108,1547030368,778006332,1.747651),(83308,1547030368,779367052,0.000000),(165088,1547030368,780849427,0.000132),(254068,1547030368,781581823,0.000000),(165868,1547030368,781590501,0.000000),(44908,1547030368,782209662,18.279249),(235168,1547030368,782446733,0.000000),(194548,1547030368,782606283,0.450375),(159508,1547030368,782695543,0.000000),(184948,1547030368,782776262,0.000000),(152368,1547030368,782932838,0.000000),(76528,1547030368,783689747,0.001253),(157888,1547030368,784151774,0.277084),(126628,1547030368,785361399,0.000000),(133768,1547030368,785443973,0.974134),(85768,1547030368,786775819,0.000000),(228508,1547030368,786882495,0.000000),(155368,1547030368,787237239,0.006594),(196708,1547030368,788009205,0.009759),(190828,1547030368,788157701,0.000000),(75928,1547030368,788966122,11.671413),(57028,1547030368,790330771,0.000000),(190588,1547030368,792526356,0.052749),(258748,1547030368,792925198,0.000000),(45268,1547030368,794613491,0.202016),(171148,1547030368,795978260,0.639556),(173608,1547030368,796223121,0.000000),(223828,1547030368,796704694,82.662973),(162508,1547030368,798456497,0.000000),(90148,1547030368,798777072,6.510695),(257248,1547030368,799099617,0.000000),(173008,1547030368,799202117,1.121768),(77968,1547030368,802206154,0.000000),(156928,1547030368,803604250,0.000000),(131788,1547030368,806246397,25.117236),(258688,1547030368,806874175,0.060913),(182188,1547030368,807253358,6.806580),(161308,1547030368,807536868,0.000000),(244768,1547030368,809284349,0.000000),(124768,1547030368,809432362,0.001253),(150928,1547030368,811183734,0.000000),(157708,1547030368,811894720,0.000000),(77428,1547030368,812792236,0.000000),(104608,1547030368,812882093,0.000000),(143128,1547030368,813351054,0.000000),(259168,1547030368,814181374,0.000000),(169828,1547030368,815120126,0.000000),(179548,1547030368,817070100,0.000000),(247648,1547030368,817374113,0.000000),(177448,1547030368,817484591,5.075404),(240988,1547030368,818463966,92.664032),(183328,1547030368,819412459,0.296608),(203068,1547030368,820044279,0.000000),(230728,1547030368,820253272,0.197738),(253348,1547030368,821121773,0.000000),(128608,1547030368,821656278,0.000000),(254248,1547030368,822748793,0.000000),(169288,1547030368,823797405,83.174279),(267988,1547030368,824766957,7.381729),(127528,1547030368,825032129,0.000000),(198328,1547030368,825636283,0.000000),(198928,1547030368,826442432,0.000000),(232468,1547030368,826987123,10.083663),(166348,1547030368,827979370,0.342713),(129088,1547030368,828507641,0.000000),(144628,1547030368,829231271,92.375894),(246868,1547030368,829432519,0.000000),(74908,1547030368,830542868,0.243842),(237688,1547030368,831686828,3.080966),(161068,1547030368,832219036,0.000000),(85408,1547030368,833912977,0.000000),(157828,1547030368,835726139,0.000000),(112168,1547030368,836128521,0.000000),(77188,1547030368,837078037,0.065898),(259048,1547030368,837270239,0.000000),(239248,1547030368,837325370,0.000000),(197128,1547030368,837428071,0.000000),(234208,1547030368,841833210,0.000000),(175408,1547030368,842698042,0.000000),(151348,1547030368,843701655,0.000000),(229168,1547030368,847334459,0.004217),(232648,1547030368,847481173,280.504685),(70948,1547030368,848089357,0.000000),(78748,1547030368,848916720,0.000000),(261748,1547030368,849948466,0.000000),(247888,1547030368,850649628,0.000000),(90868,1547030368,851667584,0.000000),(138808,1547030368,853620371,1.054124),(156148,1547030368,853763697,0.006588),(256348,1547030368,853870596,0.000000),(222748,1547030368,855782561,0.000066),(221308,1547030368,856596352,0.000000),(116728,1547030368,857697122,99.986572),(239368,1547030368,858639603,0.213509),(112528,1547030368,858766018,0.000000),(81748,1547030368,859789676,0.000000),(90748,1547030368,862428127,0.000000),(163348,1547030368,862728760,0.131751),(245548,1547030368,863450110,0.000000),(200008,1547030368,864891026,0.011396),(163228,1547030368,865698135,0.000000),(123148,1547030368,866330808,0.000000),(35453,1547030368,866594029,0.000237),(35273,1547030368,866594029,0.000263),(35452,1547030368,866594029,0.000000),(35362,1547030368,866594029,0.000000),(35363,1547030368,866594029,0.000297),(35272,1547030368,866594029,0.000000),(154648,1547030368,866740092,0.000000),(187348,1547030368,868349402,0.000000),(149788,1547030368,868965323,63.331936),(92308,1547030368,869537851,0.015743),(56668,1547030368,869900270,0.318431),(230188,1547030368,870465322,0.000000),(63448,1547030368,870785658,0.077437),(143308,1547030368,871357443,0.000000),(257368,1547030368,873991508,1.514875),(138148,1547030368,875907799,0.000000),(238828,1547030368,876680607,89.519246),(230908,1547030368,877215273,0.000000),(69268,1547030368,878354011,0.000000),(152668,1547030368,880472660,0.000000),(257968,1547030368,881040293,0.004153),(252868,1547030368,881540570,0.000000),(244828,1547030368,881672508,0.000000),(75628,1547030368,881709886,0.000000),(47548,1547030368,883044153,96.690712),(174748,1547030368,883070092,0.000000),(178348,1547030368,885070579,1.108517),(186868,1547030368,885625828,2.831720),(239908,1547030368,885749733,0.000000),(226168,1547030368,887432227,0.000000),(174628,1547030368,889636624,0.021269),(197548,1547030368,890718547,0.000000),(102868,1547030368,890836873,0.000000),(125488,1547030368,890923454,0.000000),(139168,1547030368,890999011,0.000000),(68488,1547030368,892288204,3.710740),(72148,1547030368,892524248,0.000000),(52348,1547030368,893308622,0.000000),(63508,1547030368,893527488,0.212500),(197368,1547030368,894342529,0.000000),(186028,1547030368,895287435,67.622274),(135628,1547030368,895434265,0.065845),(124528,1547030368,897547635,0.000000),(250588,1547030368,899374554,0.000000),(248788,1547030368,899706694,0.000000),(177268,1547030368,900915870,0.000000),(225508,1547030368,901520014,0.000000),(140848,1547030368,902107248,0.000000),(148948,1547030368,902233961,0.000000),(220408,1547030368,902927867,0.915180),(225808,1547030368,903091103,0.395034),(69748,1547030368,903598340,0.000000),(125548,1547030368,903717461,5.267328),(87988,1547030368,904120512,2.979194),(155788,1547030368,904362055,0.856747),(44968,1547030368,904869053,0.168750),(77068,1547030368,905063951,0.750548),(201028,1547030368,905398328,39.465134),(172228,1547030368,905603333,0.000000),(181888,1547030368,905836746,33.708904),(127168,1547030368,906121950,0.000000),(64588,1547030368,906216962,1.419542),(250108,1547030368,907534488,10.593472),(253228,1547030368,907590461,0.000000),(66568,1547030368,907598830,0.000000),(104368,1547030368,907723293,0.547768),(156448,1547030368,907761591,11.763177),(73228,1547030368,908005484,5.925162),(255688,1547030368,908105811,0.000000),(261508,1547030368,908537601,0.000000),(61948,1547030368,908940236,6.658730),(60028,1547030368,909031195,0.141875),(40948,1547030368,909037464,0.000000),(64228,1547030368,909231850,1.052235),(178768,1547030368,909323203,0.190926),(170668,1547030368,909609318,0.000000),(162688,1547030368,909950112,0.638333),(233368,1547030368,910051960,0.000000),(128488,1547030368,910114077,0.000197),(243268,1547030368,910218528,1.419887),(72388,1547030368,910572905,0.000000),(76348,1547030368,910576142,0.000000),(110308,1547030368,910581595,0.000000),(253948,1547030368,910644272,0.000000),(221188,1547030368,910647677,0.000000),(136468,1547030368,910649199,0.569335),(78208,1547030368,910653006,0.000000),(164308,1547030368,910662089,0.000000),(145468,1547030368,910698317,0.000000),(153268,1547030368,910733041,0.000000),(82408,1547030368,910741179,0.013179),(218188,1547030368,910742169,0.000000),(234988,1547030368,910756815,0.524951),(82648,1547030368,910771084,0.294549),(218008,1547030368,910810935,0.000000),(193588,1547030368,910865856,0.000000),(193888,1547030368,910870246,0.000000),(86968,1547030368,910871024,0.000000),(77308,1547030368,910871061,1.115197),(148348,1547030368,910902787,2.243353),(88648,1547030368,910947421,0.000000),(103108,1547030368,910983644,0.832141),(87868,1547030368,911036700,0.000000),(184228,1547030368,911085767,0.065896),(234508,1547030368,911143418,99.983826),(175828,1547030368,911202928,0.000000),(81028,1547030368,911264513,0.000000),(130828,1547030368,911381926,2.852183),(248548,1547030368,911589033,34.760547),(120508,1547030368,911641784,0.543139),(225688,1547030368,911643164,0.000000),(248308,1547030368,911799959,99.988403),(192868,1547030368,911800139,0.000000),(84328,1547030368,911870972,40.457582),(57148,1547030368,912074746,95.676523),(183568,1547030368,912392102,0.000000),(62308,1547030368,912712784,0.010480),(122188,1547030368,912857317,0.000000),(191788,1547030368,913048899,0.000000),(229768,1547030368,913313506,0.000000),(222388,1547030368,913340957,115.356704),(37888,1547030368,913463730,0.000000),(228448,1547030368,913745822,0.000000),(90088,1547030368,913769984,0.065836),(201928,1547030368,914023098,0.131674),(143548,1547030368,914059070,0.000000),(231868,1547030368,914076197,0.000000),(120388,1547030368,914137644,0.000000),(148168,1547030368,914160308,0.000000),(178468,1547030368,914162021,50.905467),(256588,1547030368,914162721,0.000000),(58048,1547030368,914298381,0.100000),(65068,1547030368,914310100,0.401875),(89368,1547030368,914360810,0.000000),(168208,1547030368,914382145,0.510202),(164188,1547030368,914770455,0.131670),(68548,1547030368,915071506,0.296500),(125308,1547030368,915328841,0.000000),(202828,1547030368,915342862,0.000000),(248488,1547030368,915344968,0.851897),(230068,1547030368,915357307,0.000000),(143188,1547030368,915359850,0.000000),(41608,1547030368,915399955,0.439277),(90028,1547030368,915419264,0.000000),(127768,1547030368,915427772,0.000000),(165268,1547030368,915433376,0.000000),(244948,1547030368,915514373,0.000000),(133648,1547030368,915520961,0.000000),(124648,1547030368,915722461,0.131674),(252568,1547030368,915769963,0.000000),(178288,1547030368,916446062,0.000000),(227068,1547030368,916777079,0.000000),(90688,1547030368,916800419,0.000000),(132868,1547030368,916800678,0.000000),(86128,1547030368,917558527,0.000000),(195688,1547030368,918015796,0.000000),(200608,1547030368,918475183,0.000000),(240448,1547030368,918859228,0.000000),(38428,1547030368,919099549,65.000000),(224968,1547030368,919109161,936.029902),(256648,1547030368,919245701,0.000000),(44788,1547030368,919537040,45.250000),(172348,1547030368,919887903,0.000000),(170428,1547030368,919913135,1.817017),(217468,1547030368,920229165,0.066623),(223288,1547030368,920268287,0.000000),(260488,1547030368,920306438,0.000000),(76648,1547030368,920322039,0.000000),(239428,1547030368,920361963,1.119149),(87148,1547030368,920518212,0.000000),(159388,1547030368,920666649,0.000000),(187168,1547030368,921040897,0.000000),(172888,1547030368,921254225,0.246881),(185848,1547030368,921479375,30.103034),(37228,1547030368,921537400,0.382820),(218788,1547030368,921678055,0.065832),(139828,1547030368,921779076,2.119782),(140128,1547030368,922070654,0.000000),(67708,1547030368,922271424,0.224723),(106648,1547030368,922663694,0.000066),(51028,1547030368,922702897,0.072500),(174448,1547030368,922977266,0.000000),(99808,1547030368,922992663,0.000000),(194608,1547030368,923113092,0.000000),(231388,1547030368,923200723,0.000000),(192988,1547030368,923413399,0.000000),(222688,1547030368,923445324,0.000000),(168988,1547030368,923662802,0.000000),(177388,1547030368,924127829,0.000000),(104728,1547030368,924192810,0.000000),(104968,1547030368,924254370,0.000000),(64288,1547030368,925158244,0.154375),(99208,1547030368,925412448,0.000000),(236728,1547030368,925430579,0.000000),(261808,1547030368,925575149,0.000000),(169888,1547030368,925584688,0.000000),(138688,1547030368,925614030,0.000000),(232108,1547030368,925704979,0.000000),(268588,1547030368,925993183,98.562608),(195628,1547030368,926162866,0.000000),(252688,1547030368,926365233,1.036840),(177508,1547030368,926410053,0.000000),(153988,1547030368,926495110,0.000000),(227848,1547030368,926633363,20348.642487),(144868,1547030368,926684678,0.000000),(220528,1547030368,927028982,2.007946),(229048,1547030368,927234912,29.229668),(135388,1547030368,927257523,78.083705),(259708,1547030368,928018819,0.000000),(249988,1547030368,928114722,0.000000),(83428,1547030368,928179237,15.602372),(156988,1547030368,928742669,0.011520),(245308,1547030368,929140930,92.668304),(72508,1547030368,929145230,0.000000),(156268,1547030368,929310583,0.296247),(186688,1547030368,929420967,0.065834),(242668,1547030368,929442733,10.204254),(122968,1547030368,929480255,0.000000),(152128,1547030368,929499346,9.957282),(179908,1547030368,929517188,0.000000),(255988,1547030368,929616984,0.000000),(129268,1547030368,929716685,0.000000),(148288,1547030368,930074924,0.000132),(247768,1547030368,930158971,0.000197),(72448,1547030368,930190245,0.000000),(49648,1547030368,930304910,0.136095),(141028,1547030368,930495445,0.002567),(166468,1547030368,930541586,0.296240),(141148,1547030368,930585042,0.000000),(217228,1547030368,930683473,0.000000),(181528,1547030368,930843059,0.032917),(192448,1547030368,930942481,0.002173),(50668,1547030368,930987434,94.767643),(147088,1547030368,931272625,0.000000),(187828,1547030368,931973201,0.000000),(156808,1547030368,932068982,0.000000),(238468,1547030368,932162906,0.000000),(224848,1547030368,932282519,0.000000),(167428,1547030368,932858219,265.346132),(135088,1547030368,932870968,0.000000),(112288,1547030368,932997245,0.000000),(183148,1547030368,933530803,0.000000),(88588,1547030368,933736427,0.000000),(79708,1547030368,934093971,20.094674),(149248,1547030368,934357791,0.131664),(175888,1547030368,934370272,0.000000),(220288,1547030368,934685658,0.296235),(269188,1547030368,934782362,28.391834),(258088,1547030368,935018152,0.000000),(78328,1547030368,935224199,0.000000),(258148,1547030368,935232396,86.571944),(148468,1547030368,935416654,2.600488),(247108,1547030368,935708588,3.209543),(133048,1547030368,935710153,0.000132),(255328,1547030368,935906908,0.002238),(255808,1547030368,935929334,0.000000),(110488,1547030368,936155954,0.000066),(80188,1547030368,936685820,0.131640),(88708,1547030368,936974056,0.065834),(171328,1547030368,937271595,0.000000),(167908,1547030368,937727207,0.065833),(122128,1547030368,937831698,0.000198),(251308,1547030368,938493650,0.000000),(49348,1547030368,938532806,0.103750),(99568,1547030368,938929521,0.000000),(166528,1547030368,939409427,0.004279),(124228,1547030368,940147558,0.000000),(239968,1547030368,940588989,0.000000),(224068,1547030368,940778414,0.263489),(152428,1547030368,940827902,0.000000),(78868,1547030368,941187110,0.000000),(72988,1547030368,942131692,0.000000),(152548,1547030368,942198972,0.000000),(175288,1547030368,942248254,0.005727),(221068,1547030368,942287161,0.000000),(160888,1547030368,942385209,0.000000),(242548,1547030368,942488621,0.000000),(112408,1547030368,942905524,0.000000),(75808,1547030368,942979189,0.000000),(192568,1547030368,943228401,47.397921),(219448,1547030368,943398562,0.135621),(121288,1547030368,944002069,0.065833),(135508,1547030368,944052399,0.006583),(188068,1547030368,944318522,0.000000),(239068,1547030368,944993860,32.075473),(145948,1547030368,945133417,1.217873),(179788,1547030368,945613572,0.000000),(128968,1547030368,946119194,0.000000),(254608,1547030368,946515711,0.000000),(182728,1547030368,946810072,0.855829),(249748,1547030368,946823744,1.034056),(148888,1547030368,947480392,0.000000),(72268,1547030368,947527732,0.296238),(161368,1547030368,947548621,0.000000),(38248,1547030368,947774265,58.499979),(148408,1547030368,948172489,0.000000),(145108,1547030368,948563434,0.000000),(219808,1547030368,948848403,0.000000),(202048,1547030368,948966551,0.000000),(141628,1547030368,949108309,0.065830),(145048,1547030368,949318056,0.000000),(247948,1547030368,949782890,0.000000),(219628,1547030368,950019368,0.000000),(196648,1547030368,950167142,0.000000),(140968,1547030368,950311394,0.000000),(252748,1547030368,950519094,0.002041),(163588,1547030368,950815508,10.140396),(175048,1547030368,951204373,0.006584),(186748,1547030368,951346950,0.000000),(42568,1547030368,951394717,0.004305),(143248,1547030368,951713650,0.263320),(236428,1547030368,951830623,168.287920),(99028,1547030368,952046647,0.000000),(83968,1547030368,952196044,0.000000),(123388,1547030368,952292957,0.000000),(50248,1547030368,952381296,32936120320.000000),(136888,1547030368,952457071,0.026333),(251368,1547030368,952519257,0.000000),(75028,1547030368,952598778,0.000000),(197488,1547030368,952723566,0.000000),(169708,1547030368,953043604,0.002568),(218668,1547030368,953153497,0.013167),(169528,1547030368,953429964,0.000000),(196828,1547030368,954187622,112.412745),(194068,1547030368,954302442,1.744663),(199288,1547030368,954390434,0.000000),(155728,1547030368,954532353,0.000000),(155548,1547030368,954851483,0.296262),(136348,1547030368,955085479,0.000000),(92428,1547030368,955127957,0.000000),(176008,1547030368,955147050,0.000000),(179128,1547030368,955461197,0.000000),(158008,1547030368,955685391,0.000000),(81328,1547030368,955822898,0.938119),(152308,1547030368,955828033,0.000000),(239668,1547030368,956518229,0.223841),(217348,1547030368,956640782,0.000000),(84868,1547030368,956654554,0.000066),(147508,1547030368,956898534,0.000000),(259288,1547030368,957002465,0.000000),(251488,1547030368,957152832,0.000000),(128368,1547030368,957503371,0.131665),(79408,1547030368,957696795,0.092166),(159208,1547030368,957886111,0.000000),(201268,1547030368,958002688,0.000000),(154108,1547030368,958136088,3.027930),(219388,1547030368,958204078,99.986877),(149908,1547030368,958249621,0.006584),(197068,1547030368,958391078,0.000000),(180688,1547030368,958493927,0.000000),(151408,1547030368,958507707,0.131675),(127828,1547030368,958635659,0.000000),(127228,1547030368,958649771,23.486460),(192388,1547030368,958695405,0.000000),(142288,1547030368,958769537,99.987183),(227728,1547030368,958817890,0.000000),(250468,1547030368,958930743,0.006584),(145588,1547030368,959076299,0.065836),(188968,1547030368,959092096,20.342906),(180448,1547030368,959161867,0.000000),(158848,1547030368,959668014,1.497745),(238888,1547030368,960008251,0.098751),(184348,1547030368,960202961,0.000000),(247468,1547030368,960257196,0.000000),(86248,1547030368,960466391,0.296260),(131248,1547030368,963377617,0.144356),(130348,1547030368,965920536,9.913407),(131188,1547030368,969295171,0.002190),(130408,1547030368,971969969,1.197524),(130768,1547030368,975160999,101.930938),(59968,1547030368,975330212,0.413418),(176668,1547030368,977067187,0.115157),(226468,1547030369,16503471,0.000000),(226348,1547030369,16815001,0.013475),(257249,1547030369,22307300,0.000000),(166949,1547030369,29004108,82.890098),(221249,1547030369,38060833,0.000000),(46289,1547030369,39375045,0.000000),(188009,1547030369,54849781,0.000000),(221309,1547030369,58265924,1.253149),(147449,1547030369,64750691,0.000000),(131249,1547030369,72685487,0.001860),(247949,1547030369,83296619,0.000000),(202889,1547030369,88704846,0.000000),(114749,1547030369,105784900,2.985946),(254129,1547030369,156804994,0.000000),(256649,1547030369,160566378,283.500094);
    БД - Percona xtradb cluster, 3 ноды, 16Гб ОЗУ, 8 ядер на ноду. Enterprise SSD диски.
    Кластер за HAproxy
    Zabbix-Server:
    590 хостов, 145К items, 28К триггеров, 2000 NVPS

    База партицирована, история хранится 7 дней, тренды - год.

    На всякий случай конфиг MySQL
    Code:
    wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
    
    wsrep_cluster_name=percona-cluster
    wsrep_cluster_address=gcomm://10.28.20.125,10.28.20.126,10.28.20.127
    
    wsrep_node_name=percona-1
    wsrep_node_address=10.28.20.125
    
    wsrep_sst_method=xtrabackup-v2
    wsrep_sst_auth=user:password
    
    pxc_strict_mode=DISABLED
    
    binlog_format=ROW
    default_storage_engine=InnoDB
    innodb_autoinc_lock_mode=2
    
    innodb_buffer_pool_size=13G
    innodb_buffer_pool_instances=6
    innodb_flush_method=O_DIRECT
    innodb_log_file_size=4G
    innodb_file_per_table=1
    innodb_data_file_path=ibdata1:10M:autoextend:max:3G
    innodb_read_io_threads = 8
    innodb_write_io_threads = 8
    slow_query_log=/var/log/mysql.slow.log
    thread_cache_size=4
    query_cache_limit=1M
    query_cache_size=0
    tmp_table_size=256M
    max_heap_table_size=256M
    table_open_cache=512
    max_connections=2000
    innodb_flush_log_at_trx_commit=0
    innodb_flush_log_at_timeout=5
    join_buffer_size=256k
    read_buffer_size=256k
    read_rnd_buffer_size=256k
    optimizer_switch='index_condition_pushdown=off'
    Здесь был, не помогло.
    Подскажите, как можно победить это?

  • kidoro
    replied
    А нагрзука на HAproxy? Что топ показывает?

    Leave a comment:


  • garic
    replied
    Originally posted by kidoro View Post
    а поронять ноды и посмотреть? 16 Гб не так уж много для баз, тем более у Вас, похоже выборки хорошие. Загрузку по памяти глянуть и ноды по одной поронять.
    Уже пробовал. Загрузка ОЗУ не большая, не более 50-70%
    HAproxy настроен в режиме 1 Active + 2 Backup, переключает за секунду. И разницы нет, к какой именно ноде идет запрос, все равно вылезают slow query

    Leave a comment:


  • kidoro
    replied
    а поронять ноды и посмотреть? 16 Гб не так уж много для баз, тем более у Вас, похоже выборки хорошие. Загрузку по памяти глянуть и ноды по одной поронять.

    Leave a comment:


  • garic
    replied
    UPDATE

    На следующий день, после рестарта кластера появились новые записи в логе:
    Code:
     1179:20190111:164052.267 slow query: 11.952122 sec, "select clock,ns,value from history_uint where itemid=210074 and clock<=1547221240 and clock>1547217640 order by clock desc limit 2"
      1148:20190111:164052.267 slow query: 30.528367 sec, "select clock,ns,value from history_uint where itemid=210073 and clock<=1547221221 and clock>1547217621 order by clock desc limit 2"
      1211:20190111:164052.270 slow query: 27.288327 sec, "select clock,ns,value from history where itemid=144045 and clock<=1547217622 and clock>1547131222 order by clock desc limit 2"
       435:20190111:164052.275 slow query: 62.179710 sec, "select clock,ns,value from history_uint where itemid=39375 and clock<=1547217497 and clock>1547131097 order by clock desc limit 2"
       434:20190111:164052.275 slow query: 22.690742 sec, "select clock,ns,value from history_uint where itemid=69456 and clock<=1547217496 and clock>1547131096 order by clock desc limit 3"
      1203:20190111:164052.304 slow query: 56.743528 sec, "select clock,ns,value from history where itemid=218312 and clock<=1547217589 and clock>1547131189 order by clock desc limit 2"
       854:20190111:164052.358 slow query: 24.373268 sec, "select clock,ns,value from history where itemid=238501 and clock<=1547217625 and clock>1547131225 order by clock desc limit 2"

    Leave a comment:

Announcement

Collapse
No announcement yet.
Working...
X