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