how to do profile for pg?
Hi jacktby,
PostgreSQL is literally a large and complicated program in C. Thus it
can be profiled as such. E.g. you can use `perf` and build flamegraphs
using `perf record`. Often pgbench is an adequate tool to compare
before and after results.There are many other tools available
depending on what exactly you want to profile - CPU, lock contention,
disk I/O, etc. People write books (plural) on the subject. Personally
I would recommend "System Performance, Enterprise and the Cloud, 2nd
Edition" and "BPF Performance Tools" by Brendan Gregg.
--
Best regards,
Aleksander Alekseev
but I need a quick demo to see the memory profiling or CPU profiling. I hope a blog or a video which is better for me. Thanks.<br/><br/><br/><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="ntes-mailmaster-quote" style="padding-top: 1px; padding-bottom: 1px" >
<div style=" margin-top: 20px; margin-bottom: 12px; font-size: 14px; line-height: 1.25; color: #89898c; " >---- Replied Message ----</div>
<div style=" margin-bottom: 12px; font-size: 13px; line-height: 1.25; color: #2c2c2e; padding: 2px; border-radius: 8px; background-color: #f0f0f0; " >
<table width="100%" cellpadding="0" cellspacing="10" border="0">
<tr>
<td valign="top" style=" width: 4em; font-size: 13px; line-height: 1.25; color: #89898c; white-space: nowrap; " >From</td>
<td valign="top" style=" font-size: 13px; line-height: 1.25; color: #2c2c2e; word-break: break-all; " ><a class="mail-from" style="color: #1c83eb; text-decoration: none" href="mailto:aleksander@timescale.com" >Aleksander Alekseev<aleksander@timescale.com></a></td>
</tr>
<tr>
<td valign="top" style=" width: 4em; font-size: 13px; line-height: 1.25; color: #89898c; white-space: nowrap; " >Date</td>
<td class="mail-date" valign="top" style=" font-size: 13px; line-height: 1.25; color: #2c2c2e; word-break: break-all; " >09/21/2023 22:02</td>
</tr>
<tr style="">
<td valign="top" style=" width: 4em; font-size: 13px; line-height: 1.25; color: #89898c; " >To</td>
<td valign="top" style=" font-size: 13px; line-height: 1.25; color: #2c2c2e; word-break: break-all; " ><a class="mail-to" style="color: #1c83eb; text-decoration: none" href="mailto:pgsql-hackers@lists.postgresql.org" >pgsql-hackers<pgsql-hackers@lists.postgresql.org></a></td>
</tr>
<tr style="">
<td valign="top" style=" width: 4em; font-size: 13px; line-height: 1.25; color: #89898c; " >Cc</td>
<td valign="top" style=" font-size: 13px; line-height: 1.25; color: #2c2c2e; word-break: break-all; " ><a class="mail-cc" style="color: #1c83eb; text-decoration: none" href="mailto:jacktby@gmail.com" >jacktby@gmail.com</a></td>
</tr>
<tr>
<td valign="top" style=" width: 4em; font-size: 13px; line-height: 1.25; color: #89898c; " >Subject</td>
<td class="mail-subject" valign="top" style=" font-size: 13px; line-height: 1.25; color: #2c2c2e; word-break: break-all; " >Re: how to do profile for pg?</td>
</tr>
</table>
</div>
<div>Hi jacktby,
<br/>
<br/>PostgreSQL is literally a large and complicated program in C. Thus it
<br/>can be profiled as such. E.g. you can use `perf` and build flamegraphs
<br/>using `perf record`. Often pgbench is an adequate tool to compare
<br/>before and after results.There are many other tools available
<br/>depending on what exactly you want to profile - CPU, lock contention,
<br/>disk I/O, etc. People write books (plural) on the subject. Personally
<br/>I would recommend "System Performance, Enterprise and the Cloud, 2nd
<br/>Edition" and "BPF Performance Tools" by Brendan Gregg.
<br/>
<br/>--
<br/>Best regards,
<br/>Aleksander Alekseev
<br/></div>
</div>
</body>
</html>
Hi,
but I need a quick demo to see the memory profiling or CPU profiling. I hope a blog or a video which is better for me. Thanks
Well, then I guess you better hurry with reading these books :)
There is no shortcut I'm afraid. One of the first things that Brendan
explains is how to do benchmarks *prorerly*. This is far from being
trivial and often you may be measuring not something you want. E.g.
you may think that you are profiling CPU while in fact there is a lock
contention and CPU is not even a bottleneck. Another thing worth
considering which is often neglected is to make sure your optimization
doesn't cause any digradations under different workloads.
Last but not least you should be mindful of different configuration
parameters of PostgreSQL - shared_buffers, synchronous_commit = off,
to name a few, and also understand the architecture of the system
quite well. In this context I recommend Database System Concepts, 7th
Edition by Avi Silberschatz et al and also CMU Intro to Database
Systems [1]https://www.youtube.com/playlist?list=PLSE8ODhjZXjZaHA6QcxDfJ0SIWBzQFKEG and CMU Advanced Database Systems [2]https://www.youtube.com/playlist?list=PLSE8ODhjZXjYzlLMbX3cR0sxWnRM7CLFn courses.
[1]: https://www.youtube.com/playlist?list=PLSE8ODhjZXjZaHA6QcxDfJ0SIWBzQFKEG
[2]: https://www.youtube.com/playlist?list=PLSE8ODhjZXjYzlLMbX3cR0sxWnRM7CLFn
--
Best regards,
Aleksander Alekseev
This talk by D. Dolgov
https://www.postgresql.eu/events/pgconfeu2022/sessions/session/3861/slides/325/Dmitrii_Dolgov_PGConf_EU_2022.pdf
might be insightful. Or not, because you need to fill in a lot of
blanks. Maybe you can find a recording of that talk somewhere, if
you're lucky.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
#error "Operator lives in the wrong universe"
("Use of cookies in real-time system development", M. Gleixner, M. Mc Guire)
Hi,
This talk from Andres seems to have some relevant information for you:
https://www.youtube.com/watch?v=HghP4D72Noc
--
David Geier
(ServiceNow)