Transcripció de l'informe de 2015 d'Alexey Lesovsky "Immersió profunda en les estadístiques internes de PostgreSQL"
Exempció de responsabilitat de l'autor de l'informe: Tinc nota que aquest informe té la data de novembre de 2015: han passat més de 4 anys i ha passat molt de temps. La versió 9.4 que es parla a l'informe ja no és compatible. Durant els darrers 4 anys, s'han llançat 5 nous llançaments en els quals han aparegut moltes innovacions, millores i canvis pel que fa a les estadístiques, i part del material està obsolet i no rellevant. Mentre revisava, he intentat marcar aquests llocs per no enganyar-vos al lector. No vaig reescriure aquests llocs, n'hi ha molts i, com a resultat, sortirà un informe completament diferent.
El SGBD PostgreSQL és un mecanisme enorme, i aquest mecanisme consta de molts subsistemes, el treball coordinat dels quals afecta directament el rendiment del SGBD. Durant el funcionament, es recullen estadístiques i informació sobre el funcionament dels components, que permet avaluar l'eficàcia de PostgreSQL i prendre mesures per millorar el rendiment. Tanmateix, hi ha molta d'aquesta informació i es presenta d'una forma força simplificada. Processar aquesta informació i interpretar-la de vegades és una tasca completament no trivial, i el "zoològic" d'eines i utilitats pot confondre fàcilment fins i tot un DBA avançat.
Bona tarda Em dic Aleksey. Com va dir Ilya, parlaré de les estadístiques PostgreSQL.
Estadístiques d'activitat de PostgreSQL. PostgreSQL té dues estadístiques. Estadístiques d'activitat, que es comentaran. I estadístiques del planificador sobre la distribució de dades. Parlaré específicament de les estadístiques d'activitat de PostgreSQL, que ens permeten jutjar el rendiment i millorar-lo d'alguna manera.
Us explicaré com utilitzar eficaçment les estadístiques per resoldre una varietat de problemes que tingueu o pugueu tenir.
Què no apareixerà a l'informe? A l'informe, no tocaré les estadístiques del planificador, perquè. aquest és un tema independent per a un informe separat sobre com s'emmagatzemen les dades a la base de dades i com el planificador de consultes té una idea sobre les característiques qualitatives i quantitatives d'aquestes dades.
I no hi haurà ressenyes d'eines, no compararé un producte amb un altre. No hi haurà publicitat. Deixem-ho caure.
Vull mostrar-vos que utilitzar les estadístiques és útil. És necessari. Utilitzeu-lo sense por. Tot el que necessitem és SQL senzill i un coneixement bàsic d'SQL.
I parlarem de quines estadístiques triar per resoldre problemes.
Si mirem PostgreSQL i executem una ordre al sistema operatiu per veure els processos, veurem una "caixa negra". Veurem alguns processos que fan alguna cosa, i pel seu nom podem imaginar aproximadament què estan fent allà, què estan fent. Però, de fet, això és una caixa negra, no podem mirar dins.
Podem veure la càrrega de la CPU top, podem veure la utilització de la memòria per part d'algunes utilitats del sistema, però no podrem mirar dins de PostgreSQL. Per a això necessitem altres eines.
I continuant més enllà, us diré on es passa el temps. Si representem PostgreSQL en forma d'esquema d'aquest tipus, llavors serà possible respondre on es passa el temps. Aquestes són dues coses: és el processament de les sol·licituds dels clients de les aplicacions i les tasques en segon pla que realitza PostgreSQL per mantenir-lo en funcionament.
Si comencem a mirar a l'extrem superior esquerre, podem veure com es processen les sol·licituds dels clients. La sol·licitud prové de l'aplicació i s'obre una sessió de client per continuar treballant. La sol·licitud es passa al planificador. El planificador crea un pla de consultes. L'envia més per a l'execució. Hi ha algun tipus de dades d'E/S de bloc associades a taules i índexs. Les dades necessàries es llegeixen dels discs a la memòria en una àrea especial anomenada "búfers compartits". Els resultats de la consulta, si són actualitzacions, supressió, es registren al registre de transaccions a WAL. Algunes dades estadístiques entran al registre o al col·lector d'estadístiques. I el resultat de la sol·licitud es retorna al client. Després d'això, el client pot repetir-ho tot amb una nova sol·licitud.
Què tenim amb les tasques en segon pla i els processos en segon pla? Tenim diversos processos que mantenen la base de dades en funcionament en un mode de funcionament normal. Aquests processos també es tractaran a l'informe: es tracta de buit automàtic, punt de control, processos relacionats amb la rèplica, escriptor de fons. En parlaré de cadascun d'ells a mesura que vagi informant.
Quins problemes hi ha amb les estadístiques?
- Molta informació. PostgreSQL 9.4 proporciona 109 mètriques per visualitzar dades estadístiques. Tanmateix, si la base de dades emmagatzema moltes taules, esquemes, bases de dades, llavors totes aquestes mètriques s'hauran de multiplicar pel nombre corresponent de taules, bases de dades. És a dir, encara hi ha més informació. I és molt fàcil ofegar-s'hi.
- El següent problema és que les estadístiques estan representades per comptadors. Si mirem aquestes estadístiques, veurem comptadors en constant augment. I si ha passat molt de temps des que es van restablir les estadístiques, veurem milers de milions de valors. I no ens diuen res.
- No hi ha història. Si teniu algun tipus de fallada, alguna cosa va caure fa 15-30 minuts, no podreu utilitzar les estadístiques i veure què va passar fa 15-30 minuts. Això és un problema.
- La manca d'una eina integrada a PostgreSQL és un problema. Els desenvolupadors del nucli no proporcionen cap utilitat. No tenen res així. Només donen estadístiques a la base de dades. Fes-lo servir, fes-li una sol·licitud, el que vulguis i després fes-ho.
- Com que no hi ha cap eina integrada a PostgreSQL, això provoca un altre problema. Moltes eines de tercers. Cada empresa que té mans més o menys directes està intentant escriure el seu propi programa. I com a resultat, la comunitat té moltes eines que podeu utilitzar per treballar amb estadístiques. I en algunes eines hi ha algunes característiques, en altres eines no hi ha altres característiques, o hi ha algunes característiques noves. I sorgeix una situació que cal utilitzar dues, tres o quatre eines que se superposen i tenen funcions diferents. Això és molt desagradable.
Què se'n desprèn d'això? És important poder fer estadístiques directament per no dependre dels programes, o millorar d'alguna manera aquests programes tu mateix: afegiu algunes funcions per obtenir el vostre benefici.
I necessiteu coneixements bàsics d'SQL. Per obtenir algunes dades de les estadístiques, cal fer consultes SQL, és a dir, cal saber com es fan la selecció, la unió.
Les estadístiques ens diuen diverses coses. Es poden dividir en categories.
- La primera categoria són els esdeveniments que tenen lloc a la base de dades. Aquí és quan es produeix algun esdeveniment a la base de dades: una consulta, un accés a una taula, autovacuum, commits, tot això són esdeveniments. Els comptadors corresponents a aquests esdeveniments s'incrementen. I podem seguir aquests esdeveniments.
- La segona categoria són les propietats d'objectes com ara taules, bases de dades. Tenen propietats. Aquesta és la mida de les taules. Podem seguir el creixement de les taules, el creixement dels índexs. Podem veure canvis en la dinàmica.
- I la tercera categoria és el temps dedicat a l'esdeveniment. La petició és un esdeveniment. Té la seva pròpia mesura específica de durada. Va començar aquí, aquí va acabar. El podem seguir. Ja sigui el moment de llegir un bloc des del disc o escriure. Aquestes coses també es fan un seguiment.
Les fonts de les estadístiques es presenten de la següent manera:
- A la memòria compartida (shared buffers) hi ha un segment per col·locar-hi dades estàtiques, també hi ha aquells comptadors que s'incrementen constantment quan es produeixen certs esdeveniments, o sorgeixen alguns moments en el funcionament de la base de dades.
- Tots aquests comptadors no estan disponibles per a l'usuari i ni tan sols estan disponibles per a l'administrador. Són coses de baix nivell. Per accedir-hi, PostgreSQL proporciona una interfície en forma de funcions SQL. Podem fer seleccions seleccionades mitjançant aquestes funcions i obtenir algun tipus de mètrica (o conjunt de mètriques).
- Tanmateix, no sempre és convenient utilitzar aquestes funcions, de manera que les funcions són la base de les vistes (VIEWS). Són taules virtuals que proporcionen estadístiques sobre un subsistema específic o sobre algun conjunt d'esdeveniments de la base de dades.
- Aquestes vistes integrades (VIEWS) són la principal interfície d'usuari per treballar amb estadístiques. Estan disponibles de manera predeterminada sense cap configuració addicional, podeu utilitzar-los immediatament, mirar-los i agafar informació d'allà. I també hi ha aportacions. Les aportacions són oficials. Podeu instal·lar el paquet postgresql-contrib (per exemple, postgresql94-contrib), carregar el mòdul necessari a la configuració, especificar-ne els paràmetres, reiniciar PostgreSQL i podeu utilitzar-lo. (Nota. Depenent de la distribució, en les versions recents de contrib el paquet forma part del paquet principal).
- I hi ha contribucions no oficials. No es subministren amb la distribució estàndard de PostgreSQL. S'han de compilar o instal·lar com a biblioteca. Les opcions poden ser molt diferents, depenent del que hagi creat el desenvolupador d'aquesta contribució no oficial.
Aquesta diapositiva mostra totes aquelles vistes (VIEWS) i algunes d'aquestes funcions que estan disponibles a PostgreSQL 9.4. Com podem veure, n'hi ha molts. I és bastant fàcil confondre's si ho estàs experimentant per primera vegada.
Tanmateix, si fem la foto anterior Как тратится время на PostgreSQL i compatible amb aquesta llista, obtenim aquesta imatge. Cada vista (VISUALITZACIONS), o cada funció, la podem utilitzar amb un propòsit o un altre per obtenir les estadístiques adequades quan tenim PostgreSQL en execució. I ja podem obtenir informació sobre el funcionament del subsistema.
El primer que mirarem és pg_stat_database. Com podem veure, aquesta és una representació. Conté molta informació. La informació més variada. I ens dóna un coneixement molt útil del que estem passant a la base de dades.
Què podem treure d'allà? Comencem per les coses més senzilles.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;El primer que podem mirar és el percentatge d'accés a la memòria cau. El percentatge d'accés a la memòria cau és una mètrica útil. Us permet estimar quantes dades es prenen de la memòria cau dels buffers compartits i quantes es llegeixen del disc.
És clar que com més cops de memòria cau tinguem, millor. Avaluem aquesta mètrica com a percentatge. I, per exemple, si tenim un percentatge d'aquestes visites de memòria cau superior al 90%, això és bo. Si cau per sota del 90%, aleshores no tenim prou memòria per mantenir el cap calent de dades a la memòria. I per utilitzar aquestes dades, PostgreSQL es veu obligat a accedir al disc i això és més lent que si les dades es llegeixin des de la memòria. I cal pensar en augmentar la memòria: augmentar els buffers compartits o augmentar la memòria de ferro (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Què més es pot treure d'aquesta presentació? Podeu veure les anomalies que es produeixen a la base de dades. Què es mostra aquí? Hi ha commits, rollbacks, creació de fitxers temporals, la seva mida, bloquejos i conflictes.
Podem utilitzar aquesta sol·licitud. Aquest SQL és bastant senzill. I podem veure aquestes dades per nosaltres mateixos.
I aquí teniu els valors de llindar. Mirem la proporció de commits i rollbacks. Commits és una confirmació correcta de la transacció. Rollbacks és un rollback, és a dir, la transacció va fer alguna feina, va tensar la base de dades, va considerar alguna cosa i després es va produir un error i es descarten els resultats de la transacció. és a dir el nombre de rollbacks que augmenta constantment és dolent. I d'alguna manera hauríeu d'evitar-los i editar el codi perquè això no passi.
Els conflictes estan relacionats amb la replicació. I també s'han d'evitar. Si teniu algunes consultes que s'executen a la rèplica i sorgeixen conflictes, haureu d'analitzar aquests conflictes i veure què passa. Els detalls es poden trobar als registres. I resol els conflictes perquè les sol·licituds d'aplicació funcionin sense errors.
Els bloquejos també són una mala situació. Quan les sol·licituds competeixen per recursos, una sol·licitud va accedir a un recurs i va prendre el bloqueig, la segona sol·licitud va accedir al segon recurs i també va prendre el bloqueig, i després ambdues sol·licituds van accedir als recursos de l'altra i es van bloquejar esperant que el veí alliberés el bloqueig. Aquesta també és una situació problemàtica. Cal abordar-los a nivell de reescriptura d'aplicacions i serialització de l'accés als recursos. I si veieu que els vostres punts morts augmenten constantment, heu de mirar els detalls dels registres, analitzar les situacions que s'han produït i veure quin és el problema.
Els fitxers temporals (temp_files) també són dolents. Quan una sol·licitud d'usuari no té prou memòria per acollir les dades operatives i temporals, es crea un fitxer al disc. I totes les operacions que podria realitzar en un buffer temporal a la memòria comencen a realitzar-se ja al disc. És lent. Això augmenta el temps d'execució de la consulta. I el client que va enviar una sol·licitud a PostgreSQL rebrà una resposta una mica més tard. Si totes aquestes operacions es fan a la memòria, Postgres respondrà molt més ràpid i el client esperarà menys.
pg_stat_bgwriter: aquesta vista descriu el funcionament de dos subsistemes de fons PostgreSQL: checkpointer и background writer.
Per començar, analitzem els punts de control, els anomenats. checkpoints. Què són els punts de control? Un punt de control és una posició al registre de transaccions que indica que tots els canvis de dades realitzats al registre es sincronitzen correctament amb les dades del disc. El procés, depenent de la càrrega de treball i la configuració, pot ser llarg i consisteix principalment a sincronitzar pàgines brutes en memòries intermèdies compartides amb fitxers de dades del disc. Per a què serveix? Si PostgreSQL estigués accedint al disc tot el temps i agafant dades d'allà i escrivint dades a cada accés, seria lent. Per tant, PostgreSQL té un segment de memòria, la mida del qual depèn dels paràmetres de la configuració. Postgres assigna dades operatives en aquesta memòria per a un posterior processament o consulta. En el cas de sol·licituds de canvi de dades, es modifiquen. I obtenim dues versions de les dades. Un està a la memòria i l'altre al disc. I periòdicament cal sincronitzar aquestes dades. Necessitem que el que es canvia a la memòria es sincronitzi amb el disc. Això requereix un punt de control.
El punt de control passa per buffers compartits, marca les pàgines brutes que són necessàries per al punt de control. A continuació, s'inicia el segon pas a través dels buffers compartits. I les pàgines que estan marcades per a punt de control, ja les sincronitza. Així, les dades ja estan sincronitzades amb el disc.
Hi ha dos tipus de punts de control. S'executa un punt de control en el temps d'espera. Aquest punt de control és útil i bo - checkpoint_timed. I hi ha punts de control sota demanda - checkpoint required. Aquest punt de control es produeix quan tenim un registre de dades molt gran. Hem gravat molts registres de transaccions. I PostgreSQL creu que cal sincronitzar tot això el més ràpidament possible, fer un punt de control i seguir endavant.
I si mires les estadístiques pg_stat_bgwriter i mira què tens checkpoint_req és molt més gran que checkpoint_timed, llavors això és dolent. Per què dolent? Això vol dir que PostgreSQL està sota estrès constant quan necessita escriure dades al disc. Checkpoint per timeout és menys estressant i s'executa segons el calendari intern i, per dir-ho, s'allarga en el temps. PostgreSQL té la capacitat de fer una pausa en el treball i no esforçar el subsistema del disc. Això és útil per a PostgreSQL. I les sol·licituds que s'executen durant el punt de control no experimentaran estrès pel fet que el subsistema del disc estigui ocupat.
I hi ha tres paràmetres per ajustar el punt de control:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Permeten controlar el funcionament dels punts de control. Però no m'atendré a ells. La seva influència és una qüestió a part.
Advertència: La versió 9.4 considerada a l'informe ja no és rellevant. En les versions modernes de PostgreSQL, el paràmetre checkpoint_segments substituït per paràmetres min_wal_size и max_wal_size.
El següent subsistema és l'escriptor de fons − background writer. Què està fent? Funciona constantment en un bucle sense fi. Escaneja pàgines en memòries intermèdies compartides i esborra les pàgines brutes que troba al disc. D'aquesta manera, ajuda el punt de control a fer menys feina durant el punt de control.
Per a què més es necessita? Proporciona la necessitat de pàgines netes als buffers compartits si de sobte es requereixen (en grans quantitats i immediatament) per acollir dades. Suposem que s'ha produït una situació en què es requereixen pàgines netes per completar una sol·licitud i ja es troben a les memòries intermèdies compartides. Postgres backend només els agafa i els utilitza, no ha de netejar res ell mateix. Però si de sobte no hi ha aquestes pàgines, el backend s'atura i comença a buscar pàgines per esborrar-les al disc i prendre-les per a les seves pròpies necessitats, cosa que afecta negativament el temps de la sol·licitud que s'està executant. Si veus que tens un paràmetre maxwritten_clean gran, això vol dir que l'escriptor de fons no està fent la seva feina i cal augmentar els paràmetres bgwriter_lru_maxpagesperquè pugui fer més feina en un cicle, esborra més pàgines.
I un altre indicador molt útil és buffers_backend_fsync. Els backends no fan fsync perquè és lent. Passen fsync al punt de control de la pila d'IO. El punt de control té la seva pròpia cua, processa fsync periòdicament i sincronitza pàgines a la memòria amb fitxers del disc. Si la cua del punt de control és gran i plena, llavors el backend es veu obligat a fer fsync ell mateix i això alenteix el backend, és a dir, el client rebrà una resposta més tard del que podria. Si veieu que teniu aquest valor superior a zero, això ja és un problema i cal parar atenció a la configuració de l'escriptor de fons i també avaluar el rendiment del subsistema de disc.
Advertència: _El text següent descriu les vistes estadístiques associades a la replicació. La majoria dels noms de vistes i funcions s'han canviat de nom a Postgres 10. L'essència dels canvis de nom era substituir xlog en wal и location en lsn en noms de funcions/visualitzacions, etc. Exemple particular, funció pg_xlog_location_diff() va ser rebatejat a pg_wal_lsn_diff()._
Aquí també tenim moltes coses. Però només necessitem articles relacionats amb la ubicació.
Si veiem que tots els valors són iguals, això és ideal i la rèplica no queda enrere del mestre.
Aquesta posició hexadecimal aquí és la posició al registre de transaccions. Augmenta constantment si hi ha alguna activitat a la base de dades: insercions, eliminacions, etc.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Si aquestes coses són diferents, llavors hi ha algun tipus de retard. El retard és el retard de la rèplica respecte al mestre, és a dir, les dades difereixen entre els servidors.
Hi ha tres motius per al retard:
- És el subsistema de disc que no pot gestionar les escriptures de sincronització de fitxers.
- Són possibles errors de xarxa, o sobrecàrrega de xarxa, quan les dades no tenen temps d'arribar a la rèplica i aquesta no la pot reproduir.
- I el processador. El processador és un cas molt rar. I això ho he vist dues o tres vegades, però això també pot passar.
I aquí hi ha tres consultes que ens permeten utilitzar les estadístiques. Podem estimar quant es registra al nostre registre de transaccions. Hi ha una funció així pg_xlog_location_diff i podem estimar el retard de replicació en bytes i segons. També fem servir el valor d'aquesta vista (VIEWs) per a això.
Nota: _En lloc de pg_xlog_locationdiff(), podeu utilitzar l'operador de resta i restar una ubicació d'una altra. Còmode.
Amb un retard, que és en segons, hi ha un moment. Si no hi ha activitat al mestre, la transacció va ser allà fa uns 15 minuts i no hi ha cap activitat, i si mirem aquest retard a la rèplica, veurem un retard de 15 minuts. Això val la pena recordar-ho. I pot provocar un estupor quan veieu aquest retard.
pg_stat_all_tables és una altra vista útil. Mostra estadístiques en taules. Quan tenim taules a la base de dades, hi ha alguna activitat amb ella, algunes accions, podem obtenir aquesta informació des d'aquesta vista.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;El primer que podem mirar són exploracions de taules seqüencials. El nombre en si després d'aquests passatges no és necessàriament dolent i no indica que hem de fer alguna cosa ja.
Tanmateix, hi ha una segona mètrica: seq_tup_read. Aquest és el nombre de files retornades de l'exploració seqüencial. Si el nombre mitjà supera els 1, 000, 10, 000, això ja és un indicador que potser haureu de construir un índex en algun lloc perquè els accessos siguin per índex, o bé és possible optimitzar les consultes que utilitzen aquestes exploracions seqüencials de manera que això no passa. era.
Un exemple senzill: diguem que una sol·licitud amb un OFFSET i un LÍMIT grans val la pena. Per exemple, s'escanegen 100 files d'una taula i després es prenen 000 files necessàries i es descarten les files escanejades anteriors. Aquest també és un cas dolent. I aquestes peticions s'han d'optimitzar. I aquí hi ha una consulta SQL tan senzilla, on podeu veure-la i avaluar els números rebuts.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Les mides de les taules també es poden obtenir utilitzant aquesta taula i utilitzant funcions addicionals pg_total_relation_size(), pg_relation_size().
En general, hi ha metaordres dt и di, que podeu utilitzar a PSQL i també veure mides de taules i índexs.
Tanmateix, l'ús de funcions ens ajuda a mirar les mides de les taules, fins i tot tenint en compte índexs, o sense tenir en compte índexs, i ja fer algunes estimacions en funció del creixement de la base de dades, és a dir, com creix amb nosaltres, amb quina intensitat, i ja treu algunes conclusions sobre l'optimització de mida.
Activitat d'escriure. Què és un disc? Vegem l'operació UPDATE – l'operació d'actualització de files de la taula. De fet, l'actualització és de dues operacions (o fins i tot més). Això és inserir una nova versió de fila i marcar la versió antiga de fila com a obsoleta. Més tard, l'aspirador automàtic arribarà i netejarà aquestes versions obsoletes de les línies, marca aquest lloc com a disponible per a la seva reutilització.
A més, actualitzar no es tracta només d'actualitzar una taula. Encara és una actualització d'índex. Si teniu molts índexs a la taula, amb l'actualització també caldrà actualitzar tots els índexs en què participen els camps actualitzats a la consulta. Aquests índexs també tindran versions de fila obsoletes que caldrà netejar.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;I pel seu disseny, UPDATE és una operació pesada. Però es poden fer més fàcils. Menja hot updates. Van aparèixer a la versió 8.3 de PostgreSQL. I això què és? Aquesta és una actualització lleugera que no fa que es tornin a crear índexs. És a dir, hem actualitzat el registre, però només s'ha actualitzat el registre de la pàgina (que pertany a la taula) i els índexs encara apunten al mateix registre de la pàgina. Hi ha una mica d'una lògica de treball tan interessant, quan arriba un buit, llavors té aquestes cadenes hot es reconstrueix i tot continua funcionant sense actualitzar els índexs, i tot passa amb menys malbaratament de recursos.
I quan tinguis n_tup_hot_upd gran, està molt bé. Això vol dir que prevalen les actualitzacions lleugeres i això ens és més barat quant a recursos i tot està bé.
ALTER TABLE table_name SET (fillfactor = 70);Com augmentar el volum hot updateov? Podem utilitzar fillfactor. Determina la mida de l'espai lliure reservat quan s'omple una pàgina d'una taula mitjançant INSERT. Quan les insercions van a la taula, omplen completament la pàgina, no hi deixen espais buits. A continuació, es destaca una pàgina nova. Les dades es tornen a omplir. I aquest és el comportament predeterminat, fillfactor = 100%.
Podem establir el factor d'emplenament al 70%. És a dir, amb insercions, s'assignava una pàgina nova, però només s'omplia el 70% de la pàgina. I ens queda un 30% de reserva. Quan necessiteu fer una actualització, el més probable és que passi a la mateixa pàgina i la nova versió de la fila s'ajustarà a la mateixa pàgina. I es farà hot_update. Això fa que sigui més fàcil escriure a les taules.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Cua de buit automàtic. Autovacuum és un subsistema per al qual hi ha molt poques estadístiques a PostgreSQL. Només podem veure a les taules de pg_stat_activity quants buits tenim en aquest moment. No obstant això, és molt difícil entendre quantes taules a la cua té en moviment.
Nota: _Des de Postgres 10, la situació amb el seguiment del buit ha millorat molt: ha aparegut la vista pg_stat_progressbuit, la qual cosa simplifica molt el tema de la supervisió de l'autobuit.
Podem utilitzar aquesta consulta simplificada. I podem veure quan s'ha de fer el buit. Però, com i quan hauria de començar el buit? Aquestes són les versions antigues de les cordes de les quals vaig parlar abans. S'ha actualitzat, s'ha inserit la nova versió de la fila. Ha aparegut una versió obsoleta de la cadena. Taula pg_stat_user_tables hi ha aquest paràmetre n_dead_tup. Mostra el nombre de files "mortes". I tan bon punt el nombre de files mortes s'hagi superat un cert llindar, un buit automàtic arribarà a la taula.
I com es calcula aquest llindar? Aquest és un percentatge molt específic del nombre total de files de la taula. Hi ha un paràmetre autovacuum_vacuum_scale_factor. Defineix el percentatge. Suposem que 10% + hi ha un llindar de base addicional de 50 línies. I què passa? Quan tenim més files mortes que "10% + 50" de totes les files de la taula, posem la taula al buit automàtic.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Tanmateix, hi ha un punt. Llindars bàsics per als paràmetres av_base_thresh и av_scale_factor es poden assignar individualment. I, en conseqüència, el llindar no serà global, sinó individual per a la taula. Per tant, per calcular, cal utilitzar trucs i trucs. I si us interessa, podeu mirar l'experiència dels nostres companys d'Avito (l'enllaç de la diapositiva no és vàlid i s'ha actualitzat al text).
Van escriure per que tingui en compte aquestes coses. Hi ha un drap de peus en dos llençols. Però considera correctament i amb força eficàcia ens permet valorar on necessitem molt buit per a les taules, on hi ha poc.
Què podem fer al respecte? Si tenim una llarga cua i l'autoaspirador no pot fer front, podem augmentar el nombre de treballadors del buit, o simplement fer que el buit sigui més agressiu.perquè s'accioni abans, processa la taula en trossos petits. I així la cua disminuirà. - El més important aquí és controlar la càrrega dels discs, perquè. El buit no és gratuït, tot i que amb l'arribada dels dispositius SSD / NVMe, el problema s'ha fet menys notable.
pg_stat_all_indexes són estadístiques sobre índexs. Ella no és gran. I en podem obtenir informació sobre l'ús d'índexs. I, per exemple, podem determinar quins índexs tenim addicionals.
Com ja he dit, update no només és actualitzar taules, també actualitzar índexs. En conseqüència, si tenim molts índexs a la taula, quan actualitzem les files de la taula, també s'han d'actualitzar els índexs dels camps indexats i si tenim índexs no utilitzats per als quals no hi ha exploracions d'índexs, llavors ens pengen com a llast. I cal desfer-se'n. Per a això necessitem un camp idx_scan. Només mirem el nombre d'exploracions d'índex. Si els índexs tenen zero exploracions durant un període relativament llarg d'emmagatzematge d'estadístiques (almenys 2-3 setmanes), és probable que siguin índexs dolents, ens hem de desfer.
Nota: Quan cerqueu índexs no utilitzats en el cas de clústers de replicació en temps real, heu de comprovar tots els nodes del clúster, perquè les estadístiques no són globals, i si l'índex no s'utilitza al mestre, es pot utilitzar en rèpliques (si hi ha una càrrega).
Dos enllaços:
Aquests són exemples de consulta més avançats sobre com cercar índexs no utilitzats.
El segon enllaç és una consulta força interessant. Hi ha una lògica molt no trivial. El recomano per a la seva revisió.
Què més s'hauria de resumir amb índexs?
-
Els índexs no utilitzats són dolents.
-
Ocupen espai.
-
Alentir les operacions d'actualització.
-
Treball addicional per al buit.
Si eliminem els índexs no utilitzats, només millorarem la base de dades.
La següent vista és pg_stat_activity. Aquest és un anàleg de la utilitat ps, només a PostgreSQL. Si ps'Ohm, mira els processos al sistema operatiu, doncs pg_stat_activity us mostrarà l'activitat dins de PostgreSQL.
Què podem treure d'allà?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Podem veure l'activitat global que s'està produint a la base de dades. Podem fer un nou desplegament. Allà va explotar tot, no s'accepten connexions noves, s'aboquen errors a l'aplicació.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Podem executar una consulta com aquesta i veure el percentatge total de connexions respecte al límit màxim de connexions i veure qui tenim més connexions. I en aquest cas donat, veiem aquest usuari cron_role va obrir 508 connexions. I alguna cosa li va passar. Cal tractar-ho i veure. I és molt possible que es tracti d'un nombre anòmal de connexions.
Si tenim una càrrega OLTP, les consultes haurien de ser ràpides, molt ràpides i no hi hauria d'haver consultes llargues. Tanmateix, si hi ha peticions llargues, a curt termini no hi ha res de què preocupar-se, però a la llarga, les consultes llargues perjudiquen la base de dades, augmenten l'efecte d'inflor de les taules quan es produeix la fragmentació de les taules. S'han d'eliminar tant les consultes inflores com les llargues.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Tingueu en compte: amb aquesta sol·licitud, podem definir sol·licituds i transaccions llargues. Utilitzem la funció clock_timestamp() per determinar el temps de treball. Les peticions llargues que hem trobat, les podem recordar, executar-les explain, mirar plans i optimitzar d'alguna manera. Filmem les llargues peticions actuals i continuem vivint.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Les transaccions incorrectes estan inactives en transaccions i inactives en transaccions (avortades).
Què vol dir? Les transaccions tenen diversos estats. I un d'aquests estats pot prendre en qualsevol moment. Hi ha un camp per definir estats state en aquesta vista. I l'utilitzem per determinar l'estat.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';I, com he dit més amunt, aquests dos estats inactiu en transacció i inactiu en transacció (avortat) són dolents. Què és això? És quan l'aplicació va obrir una transacció, va fer algunes accions i va fer el seu negoci. La transacció continua oberta. Es penja, no hi passa res, necessita una connexió, es bloqueja en les files canviades i fins i tot augmenta l'inflor d'altres taules, a causa de l'arquitectura del motor transaccional Postrges. I aquestes transaccions també s'han de disparar, perquè són perjudicials en general, en qualsevol cas.
Si veieu que en teniu més de 5-10-20 a la vostra base de dades, haureu de preocupar-vos i començar a fer alguna cosa amb ells.
Aquí també fem servir per al temps de càlcul clock_timestamp(). Filmem transaccions, optimitzem l'aplicació.
Com he dit anteriorment, els bloquejos són quan dues o més transaccions competeixen per un o un grup de recursos. Per a això tenim un camp waiting amb valor booleà true o false.
És cert: això vol dir que el procés està esperant, cal fer alguna cosa. Quan un procés està esperant, el client que va iniciar el procés també està esperant. El client del navegador s'asseu i també espera.
Advertència: _A partir de Postgres 9.6, el camp waiting eliminat i substituït per dos camps informatius més wait_event_type и wait_event._
Què fer? Si veieu la veritat durant molt de temps, hauríeu de desfer-vos d'aquestes peticions. Acabem de disparar aquestes transaccions. Escrivim als desenvolupadors allò que cal optimitzar d'alguna manera perquè no hi hagi cap cursa pels recursos. I llavors els desenvolupadors optimitzen l'aplicació perquè això no passi.
I és un cas extrem, però potencialment no fatal aparició de bloquejos. Dues transaccions han actualitzat dos recursos, després hi accedeixen de nou, ja a recursos oposats. PostgreSQL en aquest cas pren i dispara la transacció mateixa perquè l'altra pugui continuar treballant. Aquesta és una situació sense sortida i ella no s'entén. Per tant, PostgreSQL es veu obligat a prendre mesures extremes.
I aquí hi ha dues consultes que us permeten fer un seguiment dels bloquejos. Utilitzem la vista pg_locks, que us permet fer el seguiment de panys pesats.
I el primer enllaç és el mateix text de la sol·licitud. És bastant llarg.
I el segon enllaç és un article sobre panys. És útil per llegir, és molt interessant.
Aleshores, què veiem? Veiem dues peticions. Transacció amb ALTER TABLE és una transacció de bloqueig. Va començar, però no va acabar, i l'aplicació que va publicar aquesta transacció està fent altres coses en algun lloc. I la segona petició és l'actualització. Espera que acabi la taula d'alter abans de continuar el seu treball.
Així és com podem esbrinar qui va tancar a qui, qui està subjectant a qui, i podem tractar-ho encara més.
El següent mòdul és pg_stat_statements. Com he dit, és un mòdul. Per utilitzar-lo, cal carregar la seva biblioteca a la configuració, reiniciar PostgreSQL, instal·lar el mòdul (amb una ordre) i després tindrem una nova vista.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Què podem treure d'allà? Si parlem de coses senzilles, podem prendre el temps mitjà d'execució de la consulta. El temps creix, la qual cosa significa que PostgreSQL respon lentament i cal fer alguna cosa.
Podem veure les transaccions d'escriptura més actives a la base de dades que canvien les dades als buffers compartits. Vegeu qui hi actualitza o elimina dades.
I només podem mirar diferents estadístiques per a aquestes consultes.
Nosaltres pg_stat_statements s'utilitza per elaborar informes. Reiniciem les estadístiques un cop al dia. Acumulem-ho. Abans de restablir les estadístiques la propera vegada, elaborem un informe. Aquí teniu un enllaç a l'informe. Podeu veure-ho.
Què estem fent? Calculem les estadístiques globals de totes les sol·licituds. A continuació, per a cada consulta, comptem la seva contribució individual a aquesta estadística global.
I què podem veure? Podem veure el temps total d'execució de totes les sol·licituds d'un tipus determinat en el fons de totes les altres peticions. Podem mirar l'ús de CPU i E/S en relació amb la imatge general. I ja per optimitzar aquestes peticions. Estem creant consultes principals basades en aquest informe i ja estem reflexionant sobre què hem d'optimitzar.
Què tenim darrere de les escenes? Encara hi ha algunes presentacions que no vaig tenir en compte, perquè el temps és limitat.
Hi pgstattuple també és un mòdul addicional del paquet estàndard de contribucions. Permet avaluar bloat taules, les anomenades. fragmentació de la taula. I si la fragmentació és gran, cal eliminar-la, utilitzar diferents eines. I funció pgstattuple funciona durant molt de temps. I com més taules, més temps funcionarà.
La següent contribució és pg_buffercache. Us permet inspeccionar els buffers compartits: amb quina intensitat i per a quines taules s'utilitzen les pàgines de memòria intermèdia. I només us permet mirar els buffers compartits i avaluar què hi passa.
El següent mòdul és pgfincore. Permet realitzar operacions de taula de baix nivell mitjançant una trucada al sistema mincore(), és a dir, us permet carregar la taula en memòries intermèdies compartides o descarregar-la. I permet, entre altres coses, inspeccionar la memòria cau de la pàgina del sistema operatiu, és a dir, quant ocupa la taula a la memòria cau de la pàgina, en buffers compartits, i simplement permet avaluar la càrrega de la taula.
El següent mòdul és pg_stat_kcache. També utilitza la trucada del sistema getrusage(). I l'executa abans i després d'executar la sol·licitud. I en les estadístiques obtingudes, ens permet estimar quant va gastar la nostra sol·licitud en E/S de disc, és a dir, operacions amb el sistema de fitxers i analitzar l'ús del processador. Tanmateix, el mòdul és jove (khe-khe) i per al seu treball requereix PostgreSQL 9.4 i pg_stat_statements, que he esmentat anteriorment.
-
La capacitat d'utilitzar les estadístiques és útil. No necessiteu programari de tercers. Pots mirar, veure, fer alguna cosa, actuar.
-
Utilitzar les estadístiques és fàcil, és senzill SQL. Vau recollir una sol·licitud, la vau compilar, la vau enviar, la vau mirar.
-
Les estadístiques ajuden a respondre preguntes. Si teniu preguntes, recorreu a les estadístiques: mireu, treu conclusions, analitzeu els resultats.
-
I experimentar. Moltes peticions, moltes dades. Sempre podeu optimitzar alguna consulta existent. Podeu fer la vostra pròpia versió de la sol·licitud que us convingui millor que l'original i utilitzar-la.
Referències
Els enllaços vàlids que es van trobar a l'article, en funció dels quals, estaven a l'informe.
L'autor escriu més
(cat)
El col·lector d'estadístiques
Funcions d'administració del sistema
Mòduls Contrib
Utilitats SQL i exemples de codi SQL
Gràcies a tots per la vostra atenció!
Font: www.habr.com