Hvorfor et innstikk spørring og til ta så lang tid å fullføre Stack Overflow

Hvorfor et innstikk spørring og til ta så lang tid å fullføre Stack Overflow fil spyles disk

Dette er et ganske enkelt problem. Sette inn data i tabellen normalt fungerer fint, bortsett fra et par ganger hvor innsatsen spørringen tar noen få sekunder. (Jeg er ikke prøver å bulk sette inn data.) Så jeg opp en simulering for innsatsen prosess for å finne ut hvorfor innsatsen søket tidvis tar mer enn 2 sekunder for å kjøre. Joshua antydet at indeksfilen kan justeres; Jeg fjernet id (primærnøkkel felt), men forsinkelsen fortsatt skjer.

Jeg har en MyISAM tabell: daniel_test_insert (denne tabellen starter helt tom):

Jeg setter data inn i det, og noen ganger en innsats spørring tar gt; 2 sekunder for å kjøre. Det er ingen leser på denne tabellen – skriver Only, i serie, av en enkelt gjenget program.

Jeg kjørte nøyaktig samme spørringen 100.000 ganger for å finne hvorfor spørringen occasionall tar lang tid. Så langt ser det ut til å være en tilfeldig hendelse.

Dette søket for eksempel tok 4.194 sekunder (en svært lang tid for et innstikk):

(Dette er en forkortet versjon av SHOW PROFIL kommando, jeg kastet ut kolonnene som var alle null.)

Nå oppdateringen har utrolig mange kontekst brytere og mindre sidefeil. Opened_Tables øker ca 1 per 10 sekunder på denne databasen (ikke går tom for table_cache plass)

Maskinvare: 32 gigabyte RAM / 8 kjerner @ 2,66; raid 10 SCSI harddisker (SCSI II.)

Jeg har hatt harddisker og RAID-kontroller spørres: Ingen feil blir rapportert. CPUer er ca 50% ledig.

iostat -x 5 (rapporter mindre enn 10% utnyttelse for harddisker) topp rapport last gjennomsnitt ca 10 for 1 minutt (normalt for våre db maskin)

Swap har 156k brukt (32 gigs av ram)

Jeg er på et tap å finne ut hva som forårsaker denne forestillingen lag. Dette skjer ikke på våre lav belastning slaver, bare på vår høye laste. Dette skjer også med minne og InnoDB tabeller. Er det noen som har noen forslag? (Dette er et produksjonssystem, slik at ingenting eksotiske!)

Jeg har lagt merke til det samme fenomenet på mine systemer. Spørringer som normalt tar et millisekund vil plutselig ta 1-2 sekunder. Alle mine saker er enkle, enkelt tabell INSERT / UPDATE / BYTT uttalelser — ikke på noen SELECT. Ingen belastning, låsing, eller tråden bygge opp er tydelig.

Jeg hadde mistanke om at det er grunn til å rydde ut skitne sider, rødming endringer disk, eller noen skjulte mutex, men jeg har ennå til å begrense det ned.

  • Server load – ingen sammenheng med høy belastning
  • Motor – skjer med InnoDB / MyISAM / Minne
  • MySQL Query Cache – skjer enten det er på eller av
  • Logg rotasjoner – ingen sammenheng i hendelser

Den eneste andre observasjonen jeg har på dette punktet er avledet fra det faktum jeg kjører samme db på flere maskiner. Jeg har en tung lesning program så jeg bruker et miljø med replikering – det meste av lasten er på slavene. Jeg har lagt merke til at selv om det er minimal belastning på master, oppstår fenomenet mer der. Selv om jeg ser ingen låse problemer, kanskje det er InnoDB / Mysql har problemer med (tråd) samtidighet? Husk at oppdateringene på slave vil være singel gjenger.

MySQL Verion 5.1.48

Jeg tror jeg har en leder for problemet på min sak. På noen av mine servere, la jeg merke til dette fenomenet på seg mer enn de andre. Å se hva som var forskjellig mellom de ulike servere, og tweaking ting rundt, ble jeg ledet til MySQL InnoDB systemvariabelen innodb_flush_log_at_trx_commit.

Jeg fant doc litt vanskelig å lese, men innodb_flush_log_at_trx_commit kan ta verdiene 1,2,0:

  • For en, er loggen buffer spyles til loggfilen for hver begå, og loggfilen er blitt skrevet til disk for hver begår.
  • For to, er loggen buffer spyles til loggfilen for hver begå, og loggfilen er blitt skrevet til disk omtrent hvert 1-2 sekunder.
  • For 0, er loggen buffer tømmes i loggfilen hvert sekund, og loggfilen er spylt på disk hvert sekund.

Effektivt, i rekkefølge (1,2,0), som rapportert og dokumentert, du skal få med økt ytelse i handelen for økt risiko.

Når det er sagt, jeg fant at serverne med innodb_flush_log_at_trx_commit = 0 utførte verre (dvs. med 10-100 ganger mer "lange oppdateringer") Enn serverne med innodb_flush_log_at_trx_commit = 2. Videre ting umiddelbart forbedret på de dårlige tilfeller når jeg byttet den til 2 (merk at du kan endre det på sparket).

Så spørsmålet mitt er, hva er ditt satt til? Legg merke til at jeg ikke skylde på denne parameteren, men heller fremheve at det er sammenheng er relatert til dette problemet.

Jeg hadde dette problemet ved hjelp av InnoDB tabeller. (Og InnoDB indeksene er enda tregere å skrive om enn MyISAM)

Jeg antar at du gjør flere andre spørsmål om noen andre tabeller, slik at problemet ville være at MySQL har til å håndtere disk skriver i filer som får større og trenger å bevilge ekstra plass til disse filene.

Hvis du bruker MyISAM tabeller jeg sterkt foreslå å bruke

kommando; MyISAM er sensasjonelt raskt med dette (selv med primærnøkler) og filen kan formateres som csv, og du kan angi kolonnenavnene (eller du kan sette NULL som verdien for autoincrement feltet).

Det første tipset jeg vil gi deg, er å deaktivere autocommit funksjonalitet og enn forplikte manuelt.

Dette fordeler ytelse fordi indeksen buffer er spylt på disk bare én gang, etter at alle INSERT-setninger er ferdig. Normalt vil det være så mange index buffer flush som det er INSERT-setninger.

Men propably beste du kan gjøre, og hvis det er mulig i søknaden din, du gjør en bulk innsats med en enkelt velger.

Dette gjøres via Vector innbinding og det er den raskeste måten du kan gå.

Men tenk på dette alternativet bare hvis parameter vektor binding er mulig med mysql driveren du bruker.

Ellers ville jeg pleier til første mulighet og låse tabellen for hver 1000 innsatser. Ikke låse den for 100k innstikk, fordi you’l få en buffer overflow.

Vel, jeg er ikke i stand til å få nøyaktige målinger på IO. Men jeg har kjørt "iostat -x 5" og jeg har ikke sett noe om 5-10% mens du kjører. Det kan være midlertidige pigger som kan være skjult på grunn "gjennomsnitt". Jeg har ingen anelse om hvordan å søke i systemet for en finere målinger andre enn å se vmstat, som heller ikke har gitt noe nyttig informasjon – Daniel 25 september ’10 på 16:47

Jeg har sett saker i SQLServer der ytelse gjør merkelige ting – når det ikke er min kode (lol) det ususally er disken delsystem. SQLServer har en recovery intervall parameter der SQLServer spyler ting til disk. Hvis du virkelig pounding i innleggene og utvinning intervallet blir truffet, er ytelsen rammet hardt for litt som bobler opp til programmet som noen innsats tar lang tid, og det har faktisk ingenting å gjøre med innsatsen i det hele tatt, men noen interne housekeeping i SQLServer – kan også være at disken kanal – spindler og / eller diskkontrollere er bare på 100% – bigtang 27 september ’10 på 12:27

For å sjekke om disken oppfører seg dårlig, og hvis du er i Windows, kan du lage en batch cmd fil som skaper 10.000 filer:

lagre den i en temp dir, som test.cmd

Aktiver kommandoutvidelser kjører CMD med / E: ON parameter

Deretter kjøre batch og se om tiden mellom den første og den siste ut filen variere i løpet av sekunder eller minutter.

På Unix / Linux kan du skrive en similare shell script.

Kilde: stackoverflow.com

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert. Obligatoriske felt er merket med *

1 × one =