Bill, I suspect it is fragmentation of some sort. Vacuum times sometimes shoot up, it may be table fragmentation. What kind of tables? We have 2 of them which experience lots of adds and deletes only. No updates. So a typical day experiences record adds a few dozen times on the order of 2.5 million. And deletes once daily. Each of these tables has about 3 btree indexes. So, I am suspecting fragmentation, whatever that means, of the tables and indexes. I recover a couple of percentage points of a 73 GB SCSI disk when I run a REINDEX n those tables.
Yudhvir ========= On 5/14/07, Bill Moran <[EMAIL PROTECTED]> wrote:
In response to "Y Sidhu" <[EMAIL PROTECTED]>: > My immediate problem is to decrease vacuum times. Don't take this as being critical, I'm just trying to point out a slight difference between what you're doing and what you think you're doing: Your problem is not decreasing vacuum times. You _think_ that the solution to your problem is decreasing vacuum times. We don't know what your actual problem is, and "decreasing vacuum times" may not be the correct solution to it. Please describe the _problem_. Is vacuum causing performance issues while it's running? I mean, if vacuum takes a long time to run, so what -- what is the actual _problem_ caused by vacuum taking long to run. You may benefit by enabling autovacuum, or setting vacuum_cost_delay to allow vacuum to run with less interference to other queries (for example). Some details on what you're doing and what's happening would be helpful, such as the output of vacuum verbose, details on the size of your database, your hardware, how long vacuum is taking, what you feel is an acceptable length of time, your PG config. > On 5/14/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > > On Mon, May 14, 2007 at 12:02:03PM -0700, Y Sidhu wrote: > > > I am sorry about this Jim, please understand that I am a newbie and am > > > trying to solve long vacuum time problems and get a handle on speeding > > up > > > queries/reports. I was pointed to pg_stats and that's where I am at now. > > I > > > > Well, I have no idea what that person was trying to convey then. What > > are you trying to look up? Better yet, what's your actual problem? > > > > > have added this into my conf file: > > > stats_start_collector TRUE stats_reset_on_server_start FALSE > > > stats_command_string TRUE > > > However, these being production servers, I have not enabled these: > > > stats_row_level stats_block_level > > FYI, stats_command_string has a far larger performance overhead than any > > of the other stats commands prior to 8.2. > > > > > Yes, I have re-started the server(s). It seems like I query tables to > > get > > > the info. If so, are there any queries written that I can use? > > > > > > Thanks for following up on this with me. > > > > > > Yudhvir > > > > > > === > > > On 5/14/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > > > > > >On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote: > > > >> The stats_block_level and stats_row_level are NOT enabled. The > > question > > > >is > > > >> how to use pg_stats. Do I access/see them via the ANALYZE command? or > > > >using > > > >> SQL. I cannot find any document which will get me started on this. > > > > > > > >Ok, we're both confused I think... I thought you were talking about the > > > >pg_stat* views, which depend on the statistics collector (that's what > > > >the stats_* parameters control). > > > > > > > >That actually has nothing at all to do with pg_stats or pg_statistics. > > > >Those deal with statistics about the data in the database, and not > > about > > > >statistics from the engine (which is what the pg_stat* views do...). > > > > > > > >If you want to know about pg_stats, take a look at > > > >http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html... > > > >but normally you shouldn't need to worry yourself about that. Are you > > > >trying to debug something? > > > > > > > >Information about the backend statistics can be found at > > > >http://www.postgresql.org/docs/8.2/interactive/monitoring.html -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023
-- Yudhvir Singh Sidhu 408 375 3134 cell
