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