Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


BMC Control-M PostgreSQL Skydive

BMC Control-M User Group June 2013 - Sydney | Melbourne

VPMA Global Services

on 4 May 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of BMC Control-M PostgreSQL Skydive

BMC Control-M User Group | June 2013
Sydney | Melbourne

Internal Architecture
BMC Control-M PostgreSQL Skydive
Database Processes
System Catalog
Refers to the schema metadata, such as information about tables and columns, and internal bookkeeping information.
They are regular tables a.k.a system tables (e.g. pg_tables, pg_user, pg_locks etc...)
For example, CREATE DATABASE inserts a row into the pg_database catalog and actually creates the database on disk
Running a SELECT query
Parser: checks the query syntax and builds it into a query tree
Transformation: performs system catalog lookups on the query tree; interprets table, function, and operator names in the query (transaction control commands such as BEGIN and ROLLBACK can bypass this step)
Rewriter: performs further transformation on the query tree based on the rule and view definitions
Planner/optimizer: creates optimal execution plan for the (rewritten) query tree, e.g. sequential scan vs index scan
Executor: executes the plan tree, fetch/store data and provide query result to client
Running an UPDATE query
Stores modified copy of table page in Shared Buffer
Records information about the change into WAL (appending data mode – no lock)
Flushing“Dirty” data pages from shared buffer to disk
WAL has reached the specified max number of checkpoint segment
When checkpoint timeout occurs
Also happens if the shared buffer is full

Host based client access
Server side: $HOME/pgsql/data/pg_hba.conf; define access rules – TYPE, DB, USER, ADDRESS, METHOD
Client side: $HOME/pgsql/etc/pg_service.conf (PGSYSCONFDIR=/opt/ctmsrv/pgsql/etc)
Start and Stop the DB Server
pg_ctl start [-w] [-D DATADIR ] [-s] [-l FILENAME ] [-o "OPTIONS"]
pg_ctl stop [-W] [-D DATADIR ] [-s] [-m SHUTDOWN-MODE ]
pg_ctl restart [-w] [-D DATADIR ] [-s] [-m SHUTDOWN-MODE ] [-o "OPTIONS"]
pg_ctl status [-D DATADIR]
Use “kill -9” to kill PostgreSQL can result in corrupted data and shared buffer may “stuck” in memory; Use pg_ctl kill <pid> if have to
Never delete pg_xlog even when the database is down
Running “ctmdbopt” utility is not required
Statistic collection and calculation is handled by Statistic Collector and Auto Vacuum
Documentation CAR has been flagged with BMC support

To recover or reuse disk space occupied by updated or deleted rows
To update data statistics used by the PostgreSQL query planner (ANALYZE)
To protect against loss of very old data due to transaction ID wraparound (reassigns XID FrozenXID)
Two types of VACUUM
VACUMM (lazy vacumm)
VACUUM FULL (perform against a particular table after mass deletion)

Run VACUUM on a particular table
Operations such as UPDATE or DELETE do not immediately remove the old version of the row
After adding or deleting a large number of rows (e.g. auditing table), issue a VACUUM ANALYZE command for the affected table to update the system catalogs with the results of all recent changes
Frees physical disk usage and allows the PostgreSQL query planner to make better choices in planning queries
TRUNCATE removes the entire content of the table immediately
VACUUM or VACUUM FULL is not required to reclaim the now-unused disk space
Free Space Map (FSM)
Tracks the locations of unused space in the database
If FSM is too small, free space not stored in the map cannot be re-used
Performance Tuning
“shared_buffers” parameter
Default for LARGE installation is 800MB
Can be increased according to system RAM, but increase with caution - set the value too big can also reduce performance
Requires corresponding increase in checkpoint_segments
By modifying $HOME/pgsql/data/postgresql.conf (DB restart is required)
“checkpoint_segments” parameter
Maximum number of log file segments between automatic WAL checkpoints (CTM Default is 10)
Larger number causes checkpoints to occur less often, therefore reduce disk I/O overhead but can increase memory usage and the time required for after-crash recovery
Modify $HOME/pgsql/data/postgresql.conf (DB restart is required)
“checkpoint_timeout “ parameter
Maximum time between automatic WAL checkpoints, in seconds (default is 300 seconds).
Larger number causes checkpoints to occur less often, therefore reduce disk I/O overhead but can increase memory usage and the time required for after-crash recovery
Add into $HOME/pgsql/data/postgresql.conf (DB restart is required)
“pg_xlog” and data file
Mount the two directories onto separate physical disks to improve performance

Hot Backup and Restore
Tips and Tricks
Running archiving doesn't really affects the database performance (I/O overheads only)
Hot backup files can be restored onto another machine - update "hostname" and "port number" is required
The ???.???.backup file under the archive log directory must also be backed up for successful recovery
Direct DB Login: Use the command psql -d <database> <emuser> from the \postgresql\bin directory
Help: psql --help or \help or \?
Show version: SELECT version(); or psql -V
List all databases: Use psql -l -d <database> <user> or \l
List all tables: \dt
Show system table: \dS
Examining a table: \d <table_name>
Get all database information: \dd
Query NULL fields: IS NOT NULL or IS NULL
Save query output to file: \w <output_filename>
Redirect query output to file: psql -d <database> <user> -o <output_filename>
Loading a query: \i <query_filename>
Execute non-interactive SQL script: psql -d <database> <user> -f <sql_filename>
Formatting query output: \pset <parameter> <value>
User passwords are stored in pg_shadow System Table (sa access only)
Any user can have superuser rights
PostgreSQL uses Transaction Blocks, like MS-SQL Server and Sybase
Commit Example:
booktown=# BEGIN;
booktown=# UPDATE subjects SET location = NULL
booktown-# WHERE id = 12;
booktown=# SELECT location FROM subjects WHERE id = 12;

(1 row)

booktown=# COMMIT;
Notice the BEGIN; and COMMIT; commands
To Rollback, use ROLLBACK; instead of COMMIT;
Rollback Example:
booktown=# BEGIN;
booktown=# SELECT * FROM subjects WHERE id = 12;
id | subject | location
12 | Religion |
(1 row)

booktown=# UPDATE subjects SET location = 'Sunset Dr'
booktown-# WHERE id = 12;
booktown=# SELECT * FROM subjects WHERE id = 12;
id | subject | location
12 | Religion | Sunset Dr
(1 row)

booktown=# ROLLBACK;
booktown=# SELECT * FROM subjects WHERE id = 12;
id | subject | location
12 | Religion |
(1 row)
Notice the BEGIN; and ROLLBACK; commands
Postmaster Daemon
Supervisory daemon process, creates and monitors background processes
Listening on requests sent from the client library, handles authentication
For authorized client, a dedicated server process will be spawned to handle the request
Background Processes
Writer Process – Writes shared buffer into database file
Logger Process – Writes database activities into log file
WAL Logger Process – Writes every change made to the database's data files into WAL file (pg_xlog)
Statistics Collector – Collects and reports information about server activities (count accesses to tables and indexes)
Auto Vacuum Process (launcher and multiple worker threads*) – Monitors table activity and performs VACUUM and ANALYZE when necessary to recovery space and generate statistics.
Server Process
Direct communication with the client, exits when the client connect is closed
Executes client queries, provide results (Parser, Transformation, Rewriter, Planner/optimizer, Executor)

Cost-Based Vacuum Delay
Allows VACUMM and ANALYZE to sleep if I/O impact is too high
Default value is 0
Max memory to be used during maintenance operations
Can improve VACUMM performance
Total memory usage = autovacuum_max_workers * maintenance_work_mem
Full transcript