[HACKERS] Fwd: PostgreSQL Memory Management
Hi, Please find my original message below. This was originally posted to pgsql-general, but didn't get any response. Thanks, -Umar -- Forwarded message -- From: Umar Farooq umarf...@gmail.com Date: Mon, Mar 9, 2009 at 12:39 PM Subject: PostgreSQL Memory Management To: pgsql-gene...@postgresql.org Hi All, I have a few general question about PGSQL's internals of memory management and I think this list is appropriate for that. I am interested to find out: (1) how does PostgreSQL internally use physical memory allocated to it? (2) what are different type of allocation units? (3) what is a particular allocation unit used for e.g., to store log-tail, query plans, procedure cache etc.? I've already gone through README file in /backend/src/util/mmgr. Thanks, -Umar
[HACKERS] Performance Monitoring
Hello All,Recently, I have been involved in some work that requires me to monitor low level performance counters for pgsql. Specifically, when I execute a particular query I want to be able to tell how many system calls get executed on behalf of that query and time of each sys call. The idea is to provide a break down of the total query running time in terms of CPU time (user+system) and IO (wait time + service time) and any other contributing factors.I have been using various performance monitoring tool in linux including vmstat, mpstat, iostat and strace(to monitor system calls). Surprisingly, no matter what type of query I execute, when I use strace to monitor the system calls generated they turn out to be the same for ALL sorts of queries. Now this could happen because (a) the tool (strace) is not robust (b) something the pgsql code is doing that eludes strace. At this point I have been unable to narrow it down. Any help in this regard will be greatly appreciated. Also if somebody knows a better way of achieving the same goal please let me know.Thanks for reading.-Umar _ Live Earth is coming. Learn more about the hottest summer event - only on MSN. http://liveearth.msn.com?source=msntaglineliveearthwlm
[HACKERS] Estimating seq_page_fetch and random_page_fetch
Hi, How can we accrately estimate the seq_page_fetch and random_page_fetch costs from outside the postgres using for example a C routine. Using a very simple program in C, I have two sets of files. I want to access one set randomly i.e. pulling data from random locations within the files. The second set of files is accessed sequentially. The goal here is to approximate the disk I/O cost for a random page fetch and a sequential page fetch respectively. I am using low-level(unbuffered) C routines i.e. read/write and lseek ( for positioning file pointer), the read/write buffer size is 8k (to match the size of postgres page), and Linux is the host OS. We all know that linux is a heavily cached OS, for that very reason I am using sets of files instead of a single file, in a hope that whenever a new file from a set of files is accessed for the first time, it will NOT be in the OS cache, thus giving accurate results of actually fetching the file pages from the physical disk. And also the host is restarted before running the experiment so as to force a cold-cache start. I am hoping somebody could point me in the right direction. Thanks -Umar
Re: [HACKERS] Estimating seq_page_fetch and random_page_fetch
Re: [HACKERS] Estimating seq_page_fetch and random_page_fetchThanks a lot for your replies. The suggestions have proved much useful. Ayush, I'm curious to see your C program, thanks. Here is a related but different issue. I started looking at the postgres optimizer/planner code a month back to modify it for the purposes of experiments that I need to conduct. The EXPLAIN command prints the total costs i.e both CPU + I/O however, for my purposes I need these two costs to be separated i.e. instead of getting one cost displayed, I want cpu cost and io cost displayed separated when i run EXPLAIN on a particular query. Till now I haven't been able to figure out a 'clean' way of doing this. Can anyone tell me how much time should I expect to spend making such a change ? and from where should I start ? costsize.c ? I have another question. Looking at the optimizer code, it pretty much looks insensitive to the memory factor. The only parameters being utilized are the effective_cache_size ( in estimating index cost only) and work_mem for (sort, aggregation, groups, hash/merge joins). Are these the only memory factors that DIRECTLY effect the cost estimates of the planner/optimizer? Again your help is appreciated. -Umar - Original Message - From: Luke Lonergan To: Gregory Stark ; Tom Lane ; Ayush Parashar Cc: Umar Farooq Minhas ; pgsql-hackers@postgresql.org Sent: Thursday, March 08, 2007 2:16 PM Subject: Re: [HACKERS] Estimating seq_page_fetch and random_page_fetch Adding to this: Ayush recently wrote a C program that emulates PG IO to do this analysis, and we came out with (predictably) a ratio of sequential/random of 20-50 (for a single user). This is predictable because the random component is fixed at the access time of a single hard drive no matter how many disks are in an array, while the sequential scales nearly linearly with the number of drives in the array. So, you can estimate random using 8-12ms per random access, and sequential as 1/(number of disks X 60-130MB/s). Ayush, can you forward your C program? - Luke Msg is shrt cuz m on ma treo -Original Message- From: Gregory Stark [mailto:[EMAIL PROTECTED] Sent: Thursday, March 08, 2007 12:37 PM Eastern Standard Time To: Tom Lane Cc: Umar Farooq Minhas; pgsql-hackers@postgresql.org Subject:Re: [HACKERS] Estimating seq_page_fetch and random_page_fetch Tom Lane [EMAIL PROTECTED] writes: Umar Farooq Minhas [EMAIL PROTECTED] writes: How can we accrately estimate the seq_page_fetch and = random_page_fetch costs from outside the postgres using for example a = C routine. Use a test case larger than memory. Repeat many times to average out noise. IIRC, when I did the experiments that led to the current random_page_cost of 4.0, it took about a week before I had numbers I trusted. When I was running tests I did it on a filesystem where nothing else was running. Between tests I unmounted and remounted it. As I understand it Linux associates the cache with the filesystem and not the block device and discards all pages from cache when the filesystem is unmounted. That doesn't contradict anything Tom said, it might be useful as an additional tool though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
