1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>Introduction to cryptographic primitives</title>
<meta name="author" content="Simeon Simeonov"/>
<meta name="mobile-web-app-capable" content="yes"/>
<meta name="mobile-web-app-status-bar-style" content="black-translucent"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<link rel="stylesheet" href="dist/reset.css"/>
<link rel="stylesheet" href="dist/reveal.css"/>
<!-- <link rel="stylesheet" href="dist/theme/black.css" id="theme"/> -->
<link rel="stylesheet" href="dist/theme/statnett.css" id="theme"/>
<!-- Theme used for syntax highlighting of code -->
<link rel="stylesheet" href="plugin/highlight/monokai.css" id="highlight-theme"/>
<!-- <link rel="stylesheet" href="plugin/highlight/zenburn.css" id="highlight-theme"/> -->
</head>
<body>
<div class="reveal">
<!-- Any section element inside of this container is displayed as a slide -->
<div class="slides">
<section>
<h2>PostgreSQL tuning</h2>
<h4>Data Science @ Beryl</h4>
</br>
<p><small>Simeon Simeonov</small></p>
</section>
<section>
<section id="fragments">
<h2>Agenda</h2>
</br>
<ul>
<span class="fragment"><li>Generic tools for gathering information</li></span>
<span class="fragment"><li>Memory settings</li></span>
<span class="fragment"><li>Logging and performance reports</li></span>
<span class="fragment"><li>Other tools for analysis</li></span>
</ul>
</section>
</section>
<section>
<h2>General tools for gathering information</h2>
</br>
<pre data-id="code-animation">
<code class="bash" data-trim type="text/template">
# shell
# fetch information from the OS
cat /proc/cpuinfo
cat /proc/meminfo
sysctl -a | grep shm # get kernel parameters of interest
</code>
<code class="sql" data-trim type="text/template">
-- SQL
-- show the current values of all settings
SHOW ALL;
-- display even more than all...
SELECT * FROM pg_settings;
-- opening postgresql.conf and reading the comments - the old school approach
</code>
<code class="bash" data-trim type="text/template">
# old school: edit postgresql.conf and read the comments
</code>
</pre>
</section>
<section>
<h2>Memory settings</h2>
</br>
<ul>
<li><em>shared_buffers</em> - how much memory is dedicated to PostgreSQL to use for caching data - for a system with 1GB or more of RAM, a reasonable starting value for <em>shared_buffers</em> is 25% of the system memory (128MB -> 1GB)</li>
<li><em>effective_cache_size</em> - how much memory we expect to be available in the OS and PostgreSQL buffer caches, not an allocation - used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not - 1/2 of total memory would be a normal conservative setting (4GB -> 8GB)</li>
<li><em>work_mem</em> - the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files - for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies (4MB -> 8MB)</li>
<li><em>maintenance_work_mem</em> - the maximum amount of memory to be used by maintenance operations, such as <em>VACUUM</em> and <em>CREATE INDEX</em>. It's safe to set this value significantly larger than <em>work_mem</em> (64MB -> 256MB)</li>
</ul>
</section>
<section>
<h2>Logging and performance reports</h2>
</br>
<p><em>pgBadger - a fast PostgreSQL log analysis report</em> can be used for general analysis.</p>
<ul>
<li><em>log_checkpoints</em> - checkpoints and restartpoints are logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them</li>
<li><em>log_connections</em> - each attempted connection to the server to be logged, as well as successful completion of client authentication</li>
<li><em>log_disconnections</em> - provides information similar to <em>log_connections</em>, plus the duration of the session</li>
<li><em>log_line_prefix</em> - set to '%t [%p]: user=%u,db=%d,app=%a,client=%h '</li>
<li><em>log_lock_waits</em> - log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance</li>
<li><em>log_temp_files</em> - when set to <em>0</em>, a log entry is emitted for each temporary file when it is deleted</li>
<li><em>log_autovacuum_min_duration</em> - set to <em>0</em> it logs all autovacuum actions</li>
</ul>
</section>
<section>
<h2>Other tools for analysis</h2>
</br>
<ul>
<li><em>ANALYZE</em> - collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.</li>
<li><em>VACUUM</em> - reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. (<em>VACUUM</em> vs. <em>VACUUM FULL</em>)</li>
</ul>
</section>
<section>
<h2>Sources</h2>
</br>
<p>
<a href="https://www.postgresql.org/docs/">https://www.postgresql.org/docs/</a> - The official documentation
</p>
<p>
<a href="https://wiki.postgresql.org">https://wiki.postgresql.org</a> - The official Wiki
</p>
</section>
<section>
<h1>Q & A</h1>
</section>
</div>
</div>
<script src="dist/reveal.js"></script>
<script src="dist/plugin/zoom.js"></script>
<script src="dist/plugin/notes.js"></script>
<script src="dist/plugin/search.js"></script>
<script src="dist/plugin/markdown.js"></script>
<script src="dist/plugin/highlight.js"></script>
<script>
// Also available as an ES module, see: https://revealjs.com/initialization/
// import Reveal from 'reveal.js';
// import RevealZoom from 'reveal.js/plugin/zoom';
// import RevealNotes from 'reveal.js/plugin/notes';
// import RevealSearch from 'reveal.js/plugin/search';
// import RevealMarkdown from 'reveal.js/plugin/markdown';
// import RevealHighlight from 'reveal.js/plugin/highlight';
Reveal.initialize({
controls: true,
progress: true,
center: true,
hash: true,
// Learn about plugins: https://revealjs.com/plugins/
plugins: [RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight],
});
</script>
</body>
</html>
|
