[PERFORM] Measuring Lock Performance
Does anybody know if it is possible to use the statistics collected by PostgreSQL to do the following, and how? - view all locks held by a particular PostgreSQL session (including how to determine the session ID#) - determine effect of lock contention on overall database performance, as well as the extent to which contention varies with overall database traffic I am using version 8.0.2 on Windows 2003. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Need Some Suggestions
You are correct, in that these tables are not large (50,000 records), but their effect on performance is noticeable. Plain VACUUM (no freeze, full, etc) does the trick well, but I am unable to figure a way to call the 'plain vanilla version' of VACUUM via a PostgreSQL trigger function (does not allow it). Using the Windows scheduler (schtask, somewhat like Unix cron) is an option, but not a good one, as it takes too much out of the platform to run. My client does not use strong platforms, so I have to be concerned about that. VACUUM is a minimum impact on performance when running. I believe it would be much better to be able to call VACUUM out of a function, the same way in which other SQL commands are used. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Friday, October 07, 2005 3:53 AM To: Lane Van Ingen Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Need Some Suggestions Lane Van Ingen wrote: I have an application that is prone to sudden, unscheduled high bursts of activity, and I am finding that the application design permits me to detect the activity bursts within an existing function. The bursts only affect 3 tables, but degradation becomes apparent after 2,000 updates, and quite significant after 8,000 updates. Hmm - assuming your free-space settings are large enough, it might be adequate to just run a vacuum on the 3 tables every 5 minutes or so. It sounds like these are quite small tables with a lot of activity, so if there's not much for vacuum to do it won't place too much load on your system. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Need Some Suggestions
I have an application that is prone to sudden, unscheduled high bursts of activity, and I am finding that the application design permits me to detect the activity bursts within an existing function. The bursts only affect 3 tables, but degradation becomes apparent after 2,000 updates, and significant after 8,000 updates. I already know that a plain vacuum (without full, analyze, or free options) solves my problem. Since vacuum is classified in the documentation as an SQL command, I tried to call it using a trigger function on one the tables (they all have roughly the same insert / update rate). However, I just found out that vacuum cannot be called by a function. Vacuums done by a scheduler at 3AM in the morning are adequate to handle my non-peak needs otherwise. autovacuum sounds like it would do the trick, but I am on a WINDOWS 2003 environment, but I have Googled up messages that it still has various problems (in Windows) which won't be resolved until 8.1 is out. But I have a problem NOW, and the application is deployed around the world. QUESTION: Is there anyway anyone knows of to permit me to execute an operating system program (even vacuumdb) or possibly to add a C function to the library which would allow me to do this (I am not a C programmer, but have access to some persons who are)? Very important to me for performance reasons. Does anybody have some suggestions on the best path for me to take? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] How to Trigger An Automtic Vacuum on Selected Tables
I am running version 8.0.1 on Windows 2003. I have an application that subjects PostgreSQL to sudden bursts of activity at times which cannot be predicted. The bursts are significant enough to cause performance degradation, which can be fixed by a 'vacuum analyze'. I am aware of the existence and contents of tables like pg_class. QUESTION: I would like to trigger a vacuum analyze process on a table whenever it gets a large enough burst of activity to warrant it. Using the data in pg_class (like the number of pages the system found the last time it was vacuumed / analyzed), I would like to compare those statistics to current size, and trigger a vacuum/analyze on a table if needed. Does anyone know of any available tools, or an approach I could use, to determine what the CURRENT SIZE is ? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Is There Any Way ....
... to do the following: (1) Make a table memory-resident only ? (2) Set up user variables in memory that are persistent across all sessions, for as long as the database is up and running ? (3) Assure that a disk-based table is always in memory (outside of keeping it in memory buffers as a result of frequent activity which would prevent LRU operations from taking it out) ? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Is There A Windows Version of Performance Tuning Documents?
I have in my possession some performance tuning documents authored by Bruce Momjian, Josh Berkus, and others. They give good information on utilities to use (like ipcs, sar, vmstat, etc) to evaluate disk, memory, etc. performance on Unix-based systems. Problem is, I have applications running on Windows 2003, and have worked mostly on Unix before. Was wondering if anyone knows where there might be a Windows performance document that tells what to use / where to look in Windows for some of this data. I am thinking that I may not seeing what I need in perfmon or the Windows task manager. Want to answer questions like: How much memory is being used for disk buffer cache? How to I lock shared memory for PostgreSQL (if possible at all)? How to determine if SWAP (esp. page-in) activity is hurting me? Does Windows use a 'unified buffer cache' or not? How do I determine how much space is required to do most of my sorts in RAM? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
