Postgresql – How to configure postgresql-9.2 to know blk_write_time in pg_stat_database

postgresql-9.2statistics

Interested in the procedure for collecting block write statistics of PostgreSQL 9.2.

Steps followed for configuration, could get blk_read time but not blk_write_time

CREATE EXTENSION adminpack; and CREATE EXTENSION dblink;

Changed postgresql.conf file

# - Query/Index Statistics Collector -

track_activities = on
track_counts = on
track_io_timing = on
track_functions = all           # none, pl, all
track_activity_query_size = 1024    # (change requires restart)
update_process_title = on
stats_temp_directory = 'pg_stat_tmp'

# - Background Writer -

bgwriter_delay = 200ms          # 10-10000ms between rounds
bgwriter_lru_maxpages = 100     # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 2.0       # 0-10.0 multipler on buffers scanned/round

Restarted Postgres Server
Performed around 2000 inserts to table
But could not view the blk_write_time.

Best Answer

configuration above is correct. Before viewing the blk_write_time, do a vacuum. If you still see a 0 try increasing the inserts from 2000 to say 1000000.

Step 5: vacuum; Step 6: select datname, blk_write_time from pg_stat_database;

Related Solutions

Postgresql 9.2 won’t start after rerunning installer

Sorry, I don't have enough rep to participate in the comments yet, but I use PostgreSQL a lot on Linux and Windows.

I ran into this same problem the other day. I ran the 9.1.7 installer and it broke my clusters. As Mike Christensen said, the key is running pg_ctl START in a command window to get a useful error message.

I got the command to use from the Services panel. When you view properties on your cluster definition, you'll see the pg_ctl command to start the service. Copy that command and paste it into the command window, then change the "runservice" parameter to "start", and use runas.exe to launch the command as the postgres user (9.1 and below) or LOCALSERVICE account (later versions).

It should already contain the path to your cluster data directory. This is what mine looks like:

C:/PROGRA~1/PostgreSQL/9.1/bin/pg_ctl.exe runservice -N "PostgreSQL Dev Cluster" -D "C:/PROGRA~1/PostgreSQL/9.1/data"

Check the error message you get from that command, and check the database logs also.

In my case I had two problems. First, I couldn't get the installer to work until I ran it under postgres local admin account. But when I did that, my domain account no longer had privileges. I had to re-grant myself full control on PostgreSQL directory tree. ( Edit CR: Don't mess with permissions on the PostgreSQL data directory unless you know exactly what you are doing, you are likely to make any problem much worse not better.)

Then I tried starting it again and got a different message indicating port 5432 was already in use. This turned out to be true. Somehow the installer had left an orphaned postgres.exe process open on port 5432, even though the Services panel showed that service as not started.

I had to use netstat -ano to find the PID and kill the orphan from task manager. Then everything started normally.

Hope that helps.

Brian

Postgresql – Postgres 9.1 statistics in pg_stat_database

It seems that pg_stat_database is thoroughly described in the manual.

As to the increasing xact_commit. You might be have AUTOCOMMIT=on. PostgreSQL will always run all your queries in a transaction. In case AUTOCOMMIT=on, COMMIT will issued after each statement executed.

In your case you have 2 statements:

previous SELECT * FROM pg_stat_database;
your test statement, like SELECT 1

You might try the following scenario:

open 2 sessions, S1 and S2;
S1: SET autocommit TO off;
S1: SELECT * FROM pg_stat_database;
S1: SELECT * FROM pg_stat_database;
S2: SELECT 1;
S1: SELECT * FROM pg_stat_database;
S2: SET autocommit TO off;
S2: SELECT 1;
S1: SELECT * FROM pg_stat_database;

You can continue playing around yourself.

Best Answer

Related Solutions

Postgresql 9.2 won’t start after rerunning installer

Postgresql – Postgres 9.1 statistics in pg_stat_database

Related Question