Decodificação do relatório de 2014 por Ilya Kosmodemyansky "Como o PostgreSQL funciona com o disco".
Parte do post, é claro, está desatualizada, mas aqui estão os aspectos fundamentais do PostgreSQL ao trabalhar com o disco, que ainda são relevantes.
Discos, memória, preço, processador - nessa ordem, os administradores que compram uma máquina para um banco de dados examinam as características do servidor. Como essas características são interconectadas? Por que exatamente eles?
O relatório explicará por que um disco de banco de dados é necessário em geral, como o PostgreSQL interage com ele e quais são os recursos do PostgreSQL em comparação com outros bancos de dados.
Hardware, configurações do sistema operacional, sistema de arquivos e PostgreSQL: como e por que escolher uma boa configuração, o que fazer se a configuração do hardware não for ótima e quais erros podem tornar inútil o controlador RAID mais caro. Uma fascinante jornada ao mundo das baterias, páginas sujas e limpas, SSDs bons e ruins, programações de monitoramento e pesadelos avermelhados para os administradores de sistema.
Meu nome é Ilya Kosmodemyansky. Trabalho na consultoria PostgreSQL. Estou fazendo uma variedade de coisas relacionadas ao Postgres, seu desempenho e similares.
, Postgres, . - Write Ahead log, IO PostgreSQL, . , , -, PostgreSQL.
:
?
PostgreSQL?
, .
. ? , ?
PostgreSQL.
. PostgreSQL , .
- -, . , , , . , .
- , Write Ahead Log, . Write Ahead Log, . Write Ahead Log .
- – checkpoint, WAL , .
PostgreSQL :
PostgreSQL autovacuum. , . PostgreSQL demon autovacuum. – . , .
PostgreSQL pg_clog. . PostgreSQL pg_clog. OLTP, . , , RAM-. RAM-. RAM- , , PostgreSQL . . , .
tmp, , – . , , , . ., . - work_mem ( , PostgreSQL) , . , , explain analyze – , . .
, – checkpoints, , . «pik» — . .
? Oracle, Log Writer DBwriter, , , PostgreSQL fsync. .
Fsync – UNIX. fsync , - . . , checkpoints, .
? PostgreSQL shared_buffers, , . . . , , . : update, insert tuple , .
checkpoint ? , , WAL, .
, . WAL COMMIT , .
WAL , , checkpoint. . Checkpoint , WAL fsync .
shared_buffers , , shared_buffers, . - .
, . , , , . . iostat.
? . , , , shared buffers. . - , .
checkpoint, pdflush . IO . , 100 % . , fsync , . , , . , select , .
pg_stat_bgwriter, , checkpoint , checkpoints . . .
pg_stat_bgwriter. , , . , . - - . , , . , , - .
, . , - .
, , . , , , PostgreSQL. . - , , . , - , , . . . .
, ?
hardware. - , - .
-, RAID-. RAID-? RAID- – , , . , , 100 / , , , . CPU.
CPU, , software RAID , , .
RAID- . . . . . , .
? . , fsync , . fsync , . , . , , , , , fsync checkpoints . , RAID, .
RAID , . . . - . - RAID , , . megaraid perc, - , . , . . .
, HP RAID-, . , , , , . , . . , , . .
RAID . . . write back cache. io mode direct. – , PostgreSQL fsyng .
Disk Write Cache Mode. , . SSD , , , , , , .
, , SAS. . seek , . . , .
SSD. SSD , . , SSD . SSD, SATA , , . , .
SSD only , PostgreSQL . , SSD , Write Ahead Log, temp, , SSD, WAL – SSD. SSD. . , . , . .
RAID, , , , 10, - 5- 6-. , -, RAID . , -, , . Stripe , . stripe , .
, . , , , - , . RAID – . SATA - , off, , , .
, , , , , – . , , . . , .
. ?
-, c noatime , , . , . barrier, xfs ext4 .
? , , , inodes, inodes . , , . Linux scall, . , inodes, .
128 GB shared buffers, RAID-. . . , Debian , , .
, , zfs, , partition , partition . , , . . . . production xfs ext4 Linux, .
, . . . , . .
- , : vm.dirty_ratio=20 vm.dirty_background_ratio=10. .
? Pdflush, , , 10 20 % . . , 128 GB , 20 % . RAID – 1-2 GB, . , 512 MB. , . , , pdflush , .
( Linux PostgreSQL : vm.dirty_background_ratio 5% ~ 25% .)
vm.dirty_bytes. . - , . , . , .
RAID, , , , - . .
postgresql.conf, checkpoints , .
Wal_buffers – , PostgreSQL. - , , checkpoint. , , , - -.
, flush , checkpoint segment - - . 256. - 1000 checkpoints, , . , checkpoint, , 48 MB , 4 GB. RAID-.
checkpoints -, - . checkpoint_timeout . , -, . checkpoints . pg_stat_bgwriter 0, , checkpoints.
. . Checkpoints -, - . , - , , . checkpoint , , -.
, checkpoint_completion_target, 0,7-0,9. checkpoint, . . . , checkpoints, checkpoint, - .
0,1, , 10 % , checkpoints, . - , .
, ?
PostgreSQL , pg_test_fsync. , , hardware iops, checkpoints PostgreSQL, - .
( ), , . , - . , . , -, . . .
, , , .
. , . . . hardware , , .
hack, . , .
checkpoint PostgreSQL bgwriter, . . . .
? checkpoint . . Bgwriter , , . . , .
checkpoint . bgwriter . checkpoint.
, . , , . . 10 000, 1 000 10. . , checkpoint bgwriter. checkpoints update, insert, , . , .
? PostgreSQL – autovacuum. autovacuum . , autovacuum , , . , . .
, . : 40 MB select 30 - . PostgreSQL, .
. , autovacuum, .
Autovacuum . ? : autovacuum_vacuum_scale_factor autovacuum_analyze_scale_factor. 20 %. 0,02? , . 20 % . , PostgreSQL updates, insert tuple delete. Delete – delete, . tuple , autovacuum .
20 % . , , . . overhead , . , , , , ddl, autovacuum. .
- . , 0,001-0,01. . . 0,001 , autovacuum . (: autovacuum_vacuum_scale_factor autovacuum_analyze_scale_factor 5% — https://habr.com/ru/post/501516/) autovacuum , . . . , autovacuum , . overhead - . ., autovacuums .
? autovacuum. , . . 98-100 % , , , , . , 0,1 % autovacuum. , , . . , , . , autovacuum, , 20-30-50 %. autovacuum, , . , – 10-20, , work_mem , autovacuums .
autovacuum_analyze_scale_factor. , analyze. , , 32- . , 50 % , . , 0,02-0,03-0,04. , demon , , , .
, autovacuum ?
, , delete, tuple . xmax, . . transaction id, tuple .
* , autovacuum ?
, . , – . , , pg_catalog, . , pg_catalog’ . . .. . , , . , , .
! - PostgreSQL ? , KVM VirtIO ?
PostgreSQL , - - latency . , , , checkpoint . IO .
, , , , . , , - . , . - LibvitIO. - . . , .
, . . . , fsync. fsync datasync?
. pg_test_fsync , fsync , . postgresql.conf , . .
. . , SSD, HDD write cache. - ? write cache writes .
. , , , . - , , , .
, , , , RAID . , . - storages. . , - , .
. . - pgtune , . . RAIDs . ., - ? . . , RAID - , .
. . , , . , shared buffers - , work_mem , . . , . PostgreSQL , , , , , , linux- . Linux PostgreSQL . , PostgreSQL wizard – , . -: , . , , .
PostgreSQL BSD? ?
. FreeBSD , , Linux . – huge pages. Huge pages BSD . PostgreSQL, 9.4 , Linux. . . , , overhead . , workload. sync . , 3.3 , . PostgreSQL MySQL. , .
, BSD, , , . , - Oracle IBM . . . FreeBSD . , BSD .
, , ? read only , ?
inserts , . , , , . , , insert, , . , , , - time base inventory, timestamp’. , , , , -, .
, , - , - , - .
read only , , SSD, SAS’, SSD tablespace. , , tablespace SSD, , SSD. random page cost . . selects. .
?
A história é mais detalhada. Por exemplo, você tem uma tabela particionada. E você mantém a cabeça quente por 3-4 semanas, ou seja, quanto você realmente precisa fazer para essa análise. Tudo o que vem a seguir, você, por exemplo, entra em colapso em uma partição grande e tenta não acessá-la novamente, para não interferir na E / S. Ou você despeja essa última tabela e a leva para outra máquina que não possui cargas OLTP on-line para essa agregação. E se você precisar obter esses dados, vá lá e direcione essas solicitações para lá, para não estragar a imagem na base principal.