PokerTracker

Multiple Disk Spindles

The disk head moves around quite a bit during database activity. If too many read/write requests are made, the drive can become saturated, causing poor performance. (Vmstat and sar can provide information on the amount of activity on each disk drive.)

One solution to disk saturation is to move some of the POSTGRESQL data files to other disks. Remember, moving the files to other filesystems on the same disk drive does not help. All filesystems on a drive use the same disk heads.

Database access can be spread across disk drives in several ways:

Moving Databases
initlocation allows you to create databases on different drives.
Moving Tables
Symbolic links allow you to move tables and indexes to different drives. Movement should only be done while POSTGRESQL is shut down. Also, POSTGRESQL doesn't know about the symbolic links, so if you delete the table and recreate it, it will be created in the default location for that database. In 7.1, pg_database.oid and pg_class.relfilenode map database, table, and index names to their numeric file names.
Moving Indexes
Symbolic links allow moving indexes to different drives from their heap tables. This allows an index scan to be performed on one disk while a second disk performs heap lookups.
Moving Joins
Symbolic links allow the movement of joined tables to separate disks. If tables A and B are joined, lookups of table A can be performed on one drive while lookups of table B can be done on a second drive.
Moving Write-Ahead Log
Symbolic links can be used to move the pg_xlog directory to a different disk drive. (Pg_xlog exists in POSTGRESQL releases 7.1 and later.) Unlike other writes, POSTGRESQL log writes must be flushed to disk before completing a transaction. The cache cannot be used to delay these writes. Having a separate disk for log writes allows the disk head to stay on the current log cylinder so writes can be performed without head movement delay. (You can use the postgres -F parameter to prevent log writes from being flushed to disk, but an operating system crash may require a restore from backup.)

Other options include the use of RAID disks to spread a single filesystem across several drives. Mirroring can slow down database writes, but will speed database reads because data can be retrieved from either drive. Many sites use RAID 1+0 or RAID 0+1 because it offers the advantage of striping for performance, and mirroring for reliability. RAID 5 is faster for six or more drives. Ideally, RAID 5 will have battery-backed cache so writes can be flushed to disk in an efficient manner rather than slowing down the application while writes hit the platters.

MiamiVIce80 wrote:subdirectories from /data/

- base -contains the actual data HDD1
- global -no idea what it is for - any hint? HDD1
- pc_clog - looks like empty and never used HDD1
- pc_log - log of databaseaction HDD2
- pg_multixact - looks like empty and never used HDD1
- pg_subtrans - looks like empty and never used HDD1
- pg_ tblspc - looks like empty and never used HDD1
- pc_twophase - looks like empty and never used HDD1
- pg_xlog - log of databaseaction HDD2

• data/base = local database files
• data/global = global database files containing cluster-wide tables such as pg_database
• data/pg_clog = Transaction commit status data
• data/pg_log = PostgreSQL server log files
• data/pg_multixact = Multi-transaction status data used for row locks
• data/pg_subtrans = sub-transaction status data
• data/pg_tblspc = symbolic links to tablespaces
• data/pg_twophase = state files for prepared transactions
• data/pg_xlog = Write Ahead Log files