On Thu, Jun 4, 2009 at 4:58 PM, Bill Moran <wmo...@potentialtech.com> wrote:
> In response to Jennifer Trey <jennifer.t...@gmail.com>: > > Bill, you wrote earlier : > > > > " > > Additionally, this convinces me further that you're chasing the wrong > > problem. The stats collector writes tiny bits of information to disk > > every time you execute a command. If your system is slow because of this > > tiny bit of I/O, then something else is wrong. Either your system is > > already near its max capacity and this is pushing it over the edge, or > > you're fixing the wrong problem. > > " > > > > If this was true, that is that only small bits should be written, why is > the > > total write size each time around 57kB (for 15 write ops)? Thats also the > > size of the file pgstat.tmp. At this time, there is for that posgres > process > > 33,330 I/O Writes, with a total size of 129,221,526 Bytes. > > In a previous message you posted a snippet of your postgresql.conf file > that showed you still had a lot of the stats logging turned on. As noted > in the docs, the default values for many of those settings is on, so the > fact that they're commented out means they're taking their default values. > I'm guessing that you haven't actually turned them off yet. > Thank you, I apologize for being a little slow :) Here is a new snippet of my file, #------------------------------------------------------------------------------ # RUNTIME STATISTICS #------------------------------------------------------------------------------ # - Query/Index Statistics Collector - track_activities = off track_counts = off update_process_title = off # - Statistics Monitoring - log_parser_stats = off log_planner_stats = off log_executor_stats = off log_statement_stats = off #------------------------------------------------------------------------------ # AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ #autovacuum = on # Enable autovacuum subprocess? 'on' # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on 2009/04/08 13:33:58 autovacuum = off # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least that time. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses #autovacuum_naptime = 1min # time between autovacuum runs # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on 2009/04/08 13:33:58 autovacuum_naptime = 60 # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on 2009/04/08 13:33:58 autovacuum_vacuum_threshold = 1000 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on 2009/04/08 13:33:58 autovacuum_analyze_threshold = 250 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on 2009/04/08 13:33:58 autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on 2009/04/08 13:33:58 autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit I restarded the app and now I have gotten rid of most of the writes. Great :) It is still writing, but this time its only doing it once per query. It seems like its not repeating it self. If I run a query thats not been run before it seems to do the writes, but only the first time.. I don't mind this at all and reading the Run-Time statistics I am guessing that it was one of those parameters that was causing this. Possibly the first one on your link Bill. Could I possibly turn on AutoVacuum and turn on only track_counts? I am going to try it out.. Looks fine otherwise? > > > I turned off AutoVacuum, and restarted PG but this was still going on. > > That's not going to change the behaviour of this problem if you've still > got the stats monitoring turned on. > > > I would like to move the PGdata to the pictures disk. > > > > " > > You can just pick up the data directory and relocate it, then config > > PostgreSQL to look for the data directory in the new location, or create > > a symlink. > > " > > > > Where can I find that file? I found out that its the pgdata variable but > > couldn't find out what file it was. > > What file? If you want to move the database, then you need to pick up > the entire directory with all its files and subdirectories. > I thought this got solved by the other thread but I wasn't right again :P. I was referring to the location of the variable or parameter that points postgres to the new location. If I move the data folder I am guessing that Postgres is not going to find it unless i tell it the new location. I am working on this. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > Thanks once again / Jennifer
