Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Tuning Linux for Databases
Search
Alexey Lesovsky
October 15, 2016
Education
0
48
Tuning Linux for Databases
Slides from my talk at HDConf 2016 Minsk, Belarus
Alexey Lesovsky
October 15, 2016
Tweet
Share
More Decks by Alexey Lesovsky
See All by Alexey Lesovsky
PostgreSQL Scaling Usecases
lesovsky
0
100
Patroni failure stories, or How to crash yout PostgreSQL cluster
lesovsky
0
420
PostgreSQL High Availability in 2019
lesovsky
0
360
Top developer's mistakes when working with PostgreSQL
lesovsky
1
140
Let's Pull the Plug On the Autovacuum (EN)
lesovsky
0
1.2k
Troubleshooting PostgreSQL for Developers (RU)
lesovsky
2
350
Troubleshooting PostgreSQL Streaming Replication
lesovsky
0
960
Call of Postgres: Advanced Operations. Part I.
lesovsky
0
64
Call of Postgres: Advanced Operations. Part II.
lesovsky
0
75
Other Decks in Education
See All in Education
Dashboards - Lecture 11 - Information Visualisation (4019538FNR)
signer
PRO
1
2.1k
Pydantic(AI)とJSONの詳細解説
mickey_kubo
0
150
技術勉強会 〜 OAuth & OIDC 入門編 / 20250528 OAuth and OIDC
oidfj
5
1.4k
Implicit and Cross-Device Interaction - Lecture 10 - Next Generation User Interfaces (4018166FNR)
signer
PRO
2
1.7k
ThingLink
matleenalaakso
28
4.1k
【品女100周年企画】Pitch Deck
shinagawajoshigakuin_100th
0
2.5k
2025年度春学期 統計学 第2回 統計資料の収集と読み方(講義後配付用) (2025. 4. 17)
akiraasano
PRO
0
110
2025年度春学期 統計学 第14回 分布についての仮説を検証する ー 仮説検定(1) (2025. 7. 10)
akiraasano
PRO
0
120
2025/06/05_読み漁り学習
nag8
0
160
2025年度春学期 統計学 第5回 分布をまとめるー記述統計量(平均・分散など) (2025. 5. 8)
akiraasano
PRO
0
190
Interaction - Lecture 10 - Information Visualisation (4019538FNR)
signer
PRO
0
2k
OpenSourceSummitJapanを運営してみた話
kujiraitakahiro
0
740
Featured
See All Featured
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
3.1k
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.4k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.8k
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
21
1.3k
Testing 201, or: Great Expectations
jmmastey
43
7.6k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
126
53k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
234
17k
The Power of CSS Pseudo Elements
geoffreycrofte
77
5.9k
The Illustrated Children's Guide to Kubernetes
chrisshort
48
50k
Rails Girls Zürich Keynote
gr2m
95
14k
What's in a price? How to price your products and services
michaelherold
246
12k
Become a Pro
speakerdeck
PRO
29
5.4k
Transcript
None
About PostgreSQL DBA. Linux system administrator. PostgreSQL-Consulting.com: • 24/7 support.
• Audit, performance optimizations. • Consulting and Training. • Monitoring and Emergency. • Capacity planning. Slides: https://goo.gl/awmZ2H
Agenda RDBMS on Linux, why? Databases and Resources. OS subsystems.
CPU, Process scheduling, Power saving policies. Memory, VM, NUMA, Huge pages. Storage, File Systems, Input/Output. Other misc.
Why Linux? Linux is a good choice: • Active development
& Community support. • A lot of features & Fast implementation. • Stable & Mature & Durable.
Databases & Resources Concurrency Query speed Sort, group, hash,... OS
page cache DB buffer pool Local process cache DB data files Transaction Log Cold start CPU Memory Storage
Databases & Resources CPU Scheduling NUMA Power Saving Virtual Memory
NUMA Huge Pages File Systems Storage I/O CPU Memory Storage
Resources CPU scheduler. Virtual memory and NUMA. Huge pages. File
systems. Storage IO. Power saving policy. Others.
CPU scheduling CPU scheduler responsible for proper processes planning: Sysctl:
• kernel.sched_migration_cost_ns = 5000000 (default: 500000). • kernel.sched_autogroup_enabled = 0 (default: 1). http://www.postgresql.org/message-id/50E4AAB1.9040902@optionshouse.com http://kernelnewbies.org/Linux_2_6_38#head-59575a6aeafa38490226a560ee02de89829a5b20
CPU scheduling CPU scheduler responsible for proper processes planning: Sysctl:
• kernel.sched_migration_cost_ns = 5000000 (default: 500000). • kernel.sched_autogroup_enabled = 0 (default: 1). http://www.postgresql.org/message-id/50E4AAB1.9040902@optionshouse.com http://kernelnewbies.org/Linux_2_6_38#head-59575a6aeafa38490226a560ee02de89829a5b20 Be aware on Ubuntu: 12.04 #1055222 and 14.04 #1422016. Use noautogroup kernel param instead of sysctl.conf.
Virtual Memory What is it? Allocator, Caching, Dirty pages and
Writeback.
Virtual Memory
Virtual Memory Sysctl: vm.dirty_background_ratio & vm.dirty_ratio = disable it. vm.dirty_background_bytes
& vm.dirty_bytes = depends on ... RAID cache size, 64MB/128MB otherwise
Virtual Memory Out-of-memory & OOM-Killer Sysctl: vm.swappiness = 1 (default:
60)
NUMA S — Socket C — CPU core M —
Memory bank
NUMA BIOS: enable memory node interleaving. Kernel boot: numa=off. numactl
utility. Sysctl: • vm.zone_reclaim_mode = 0 (default: 0). • kernel.numa_balancing = 0 (default: 0).
Huge Pages Huge pages vs. Transparent huge pages. Huge pages
are supported by many RDBMS. Always disable transparent huge pages.
Huge Pages Huge pages vs. Transparent huge pages. Huge pages
are supported by many RDBMS. Always disable transparent huge pages. /etc/rc.local: • echo never > /sys/kernel/mm/transparent_hugepage/enabled • echo never > /sys/kernel/mm/transparent_hugepage/defrag
Filesystems Ext3 vs Ext4 vs XFS: what is better? Filesystem
Barriers.
Filesystems Ext3 vs Ext4 vs XFS: what is better? Filesystem
Barriers. Disable Write Cache: • hdparm -W0 /dev/device • MegaCli64 -LDSetProp -DisDskCache -Lall -aALL
Filesystems Ext3 vs Ext4 vs XFS: what is better? Filesystem
Barriers. Disable Write Cache: • hdparm -W0 /dev/device • MegaCli64 -LDSetProp -DisDskCache -Lall -aALL Hardware RAID + BBU = barrier=0 (disable). Software RAID = barrier=1 (enable).
Filesystems Ext3 vs Ext4 vs XFS: what is better? Filesystem
Barriers. Disable Write Cache: • hdparm -W0 /dev/device • MegaCli64 -LDSetProp -DisDskCache -Lall -aALL Hardware RAID + BBU = barrier=0 (disable). Software RAID = barrier=1 (enable). Enterprise SSD with Power Loss Protection = barrier=0 (disable).
Storage IO SATA/SAS vs SSD. IO elevators.
Storage IO SATA/SAS vs SSD. IO elevators: • noop: SSD,
PCIe SSD, hi-end storages. • deadline: RAID, SATA/SAS. • cfq: good default. • none (multi-queue block IO): SSD, PCIe SSD.
Storage IO SATA/SAS vs SSD. IO elevators: • noop: SSD,
PCIe SSD, hi-end storages. • deadline: RAID, SATA/SAS. • cfq: good default. • none (multi-queue block IO): SSD, PCIe SSD. # echo 'elevator_name' > /sys/block/<device>/queue/scheduler kernel boot: elevator=<name> /sys/block/*/queue/: rotational, rq_affinity, read_ahead_kb
Power Saving Policy Drivers: acpi_cpufreq vs. intel_pstate. scaling_governor.
Power Saving Policy Drivers: acpi_cpufreq vs. intel_pstate. scaling_governor: • /sys/devices/system/cpu/cpuX/cpufreq/scaling_available_governors
• /sys/devices/system/cpu/cpuX/cpufreq/scaling_governor
Power Saving Policy Drivers: acpi_cpufreq vs. intel_pstate. scaling_governor: • /sys/devices/system/cpu/cpuX/cpufreq/scaling_available_governors
• /sys/devices/system/cpu/cpuX/cpufreq/scaling_governor acpi_cpufreq + performance. intel_pstate + powersave.
Misc: Clocksources What is clocksource? acpi_pm vs. hpet vs. tsc.
/sys/devices/system/clocksource/clocksource0/available_clocksource. /sys/devices/system/clocksource/clocksource0/current_clocksource.
Summary Linux is a good choice for RDBMS: Modern, Universal,
Flexible, Stable. Adapt Linux for your workloads. Test → Change → Test → Commit/Rollback.
Questions? Alexey Lesovsky lesovsky@pgco.me PostgreSQL-Consulting.com: Data maintenance at its best
https://postgresql-consulting.com