summaryrefslogtreecommitdiff
path: root/reveal.js/postgresql_tuning.html
diff options
context:
space:
mode:
authorSimeon Simeonov2022-01-26 18:46:50 +0100
committerSimeon Simeonov2022-01-26 18:46:50 +0100
commit29c52d98bf7feb132a1857cce2058ec7134e4b0f (patch)
tree632d989dd16a2cb92820697af49b89f4fca1f9fb /reveal.js/postgresql_tuning.html
parent85c1bd41d6b3b985bb6794ebe066128c51e7c8fd (diff)
Add postgresql_tuning.html python.html timetravel.html and update sqlalchemy.html and demo.html
Diffstat (limited to 'reveal.js/postgresql_tuning.html')
-rw-r--r--reveal.js/postgresql_tuning.html152
1 files changed, 152 insertions, 0 deletions
diff --git a/reveal.js/postgresql_tuning.html b/reveal.js/postgresql_tuning.html
new file mode 100644
index 0000000..d7e3466
--- /dev/null
+++ b/reveal.js/postgresql_tuning.html
@@ -0,0 +1,152 @@
1<!doctype html>
2<html lang="en">
3 <head>
4 <meta charset="utf-8">
5 <title>SQLAlchemy</title>
6 <meta name="author" content="Simeon Simeonov">
7 <meta name="apple-mobile-web-app-capable" content="yes">
8 <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
9 <meta name="viewport" content="width=device-width, initial-scale=1.0">
10
11 <link rel="stylesheet" href="dist/reset.css">
12 <link rel="stylesheet" href="dist/reveal.css">
13
14 <link rel="stylesheet" href="dist/theme/fifty.css" id="theme">
15
16 <!-- Theme used for syntax highlighting of code -->
17 <link rel="stylesheet" href="plugin/highlight/monokai.css" id="highlight-theme">
18 <!-- <link rel="stylesheet" href="plugin/highlight/zenburn.css" id="highlight-theme"> -->
19 </head>
20 <body>
21 <div class="reveal">
22
23 <!-- Any section element inside of this container is displayed as a slide -->
24 <div class="slides">
25
26 <section>
27 <h2>PostgreSQL tuning</h2>
28 <h4>Data Science @ Beryl</h4>
29 </br>
30 <p><small>Simeon Simeonov</small></p>
31 </section>
32
33 <section>
34
35 <section id="fragments">
36 <h2>Agenda</h2>
37 </br>
38 <ul>
39 <span class="fragment"><li>Generic tools for gathering information</li></span>
40 <span class="fragment"><li>Memory settings</li></span>
41 <span class="fragment"><li>Logging and performance reports</li></span>
42 <span class="fragment"><li>Other tools for analysis</li></span>
43 </ul>
44 </section>
45
46 </section>
47
48 <section>
49 <h2>General tools for gathering information</h2>
50 </br>
51 <pre data-id="code-animation">
52 <code class="bash" data-trim type="text/template">
53 # shell
54 # fetch information from the OS
55 cat /proc/cpuinfo
56 cat /proc/meminfo
57 sysctl -a | grep shm # get kernel parameters of interest
58 </code>
59 <code class="sql" data-trim type="text/template">
60 -- SQL
61 -- show the current values of all settings
62 SHOW ALL;
63
64 -- display even more than all...
65 SELECT * FROM pg_settings;
66
67 -- opening postgresql.conf and reading the comments - the old school approach
68 </code>
69 <code class="bash" data-trim type="text/template">
70 # old school: edit postgresql.conf and read the comments
71 </code>
72 </pre>
73 </section>
74
75 <section>
76 <h2>Memory settings</h2>
77 </br>
78 <ul>
79 <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>
80 <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>
81 <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>
82 <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>
83 </ul>
84 </section>
85
86 <section>
87 <h2>Logging and performance reports</h2>
88 </br>
89 <p><em>pgBadger - a fast PostgreSQL log analysis report</em> can be used for general analysis.</p>
90 <ul>
91 <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>
92 <li><em>log_connections</em> - each attempted connection to the server to be logged, as well as successful completion of client authentication</li>
93 <li><em>log_disconnections</em> - provides information similar to <em>log_connections</em>, plus the duration of the session</li>
94 <li><em>log_line_prefix</em> - set to '%t [%p]: user=%u,db=%d,app=%a,client=%h '</li>
95 <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>
96 <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>
97 <li><em>log_autovacuum_min_duration</em> - set to <em>0</em> it logs all autovacuum actions</li>
98 </ul>
99 </section>
100
101 <section>
102 <h2>Other tools for analysis</h2>
103 </br>
104 <ul>
105 <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>
106 <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>
107 </ul>
108 </section>
109
110 <section>
111 <h2>Sources</h2>
112 </br>
113 <p>
114 <a href="https://www.postgresql.org/docs/">https://www.postgresql.org/docs/</a> - The official documentation
115 </p>
116 <p>
117 <a href="https://wiki.postgresql.org">https://wiki.postgresql.org</a> - The official Wiki
118 </p>
119 </section>
120
121 <section>
122 <h1>Q &amp; A</h1>
123 </section>
124
125 </div>
126 </div>
127
128 <script src="dist/reveal.js"></script>
129 <script src="plugin/zoom/zoom.js"></script>
130 <script src="plugin/notes/notes.js"></script>
131 <script src="plugin/search/search.js"></script>
132 <script src="plugin/markdown/markdown.js"></script>
133 <script src="plugin/highlight/highlight.js"></script>
134 <script>
135
136 // Also available as an ES module, see:
137 // https://revealjs.netlify.app/initialization/
138 Reveal.initialize({
139 controls: true,
140 progress: true,
141 center: true,
142 hash: true,
143
144 // Learn about plugins: https://revealjs.netlify.app/plugins/
145 plugins: [ RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight ]
146 });
147 Reveal.configure({ pdfSeparateFragments: false });
148
149 </script>
150
151 </body>
152</html>
generated by cgit v1.3 (git 2.53.0) at 2026-09-26 13:05:42 +0000