Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some directions on where to start improving.

Here is a picture of how the situation looked the past 3 months:

enter image description here

As you can see, update operations accounts for most of the disk utilization. Here is another picture of how the situation looks in a more detailed 3 hour window:

enter image description here

As you can see, the peak write rate is around 20MB/s

Software The server is running ubuntu 12.04 and postgresql 9.2. The type of updates are small updated typically on individual rows identified by ID. E.g. UPDATE cars SET price=some_price, updated_at = some_time_stamp WHERE id = some_id. I have removed and optimized indexes as much as I think is possible, and the servers configuration (both linux kernel and postgres conf) is pretty optimized as well.

Hardware The hardware is a dedicated server with 32GB ECC ram, 4x 600GB 15.000 rpm SAS disks in a RAID 10 array, controlled by an LSI raid controller with BBU and a Intel Xeon E3-1245 Quadcore processor.


  • Is the performance seen by the graphs reasonable for a system of this caliber (read/writes)?
  • Should I hence focus on doing a hardware upgrade or investigate deeper into the software (kernel tweaking, confs, queries etc.)?
  • If doing a hardware upgrade, is the number of disks key to performance?


I have now upgraded my database server with four intel 520 SSDs instead of the old 15k SAS disks. I'm using the same raid controller. Things has improved quite a lot, as you can see from the following the peak I/O performance has improved around 6-10 times - and that's great!. enter image description here However, I was expecting something more like a 20-50 times improvement according to the answers and the I/O capabilities of the new SSDs. So here goes another question.

New question Is there something in my current configuration, that is limiting the I/O performance of my system (where is the bottleneck)?

My configurations:


data_directory = '/var/lib/postgresql/9.2/main'
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/'
listen_addresses = ', localhost'
port = 5432
unix_socket_directory = '/var/run/postgresql'
wal_level = hot_standby
synchronous_commit = on
checkpoint_timeout = 10min
archive_mode = on
archive_command = 'rsync -a %p postgres@ </dev/null'
max_wal_senders = 1
wal_keep_segments = 32
hot_standby = on
log_line_prefix = '%t '
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
default_statistics_target = 100
maintenance_work_mem = 1920MB
checkpoint_completion_target = 0.7
effective_cache_size = 22GB
work_mem = 160MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 7680MB
max_connections = 400 


# sysctl config
# ipv6 settings (no autoconfiguration)
# Updated according to postgresql tuning
vm.dirty_ratio = 10
vm.dirty_background_ratio = 1
vm.swappiness = 0
vm.overcommit_memory = 2
kernel.sched_autogroup_enabled = 0
kernel.sched_migration_cost = 50000000


# Shared memory settings for PostgreSQL
# Note that if another program uses shared memory as well, you will have to
# coordinate the size settings between the two.
# Maximum size of shared memory segment in bytes
#kernel.shmmax = 33554432
# Maximum total size of shared memory in pages (normally 4096 bytes)
#kernel.shmall = 2097152
kernel.shmmax = 8589934592
kernel.shmall = 17179869184
# Updated according to postgresql tuning

Output of MegaCli64 -LDInfo -LAll -aAll

Adapter 0 -- Virtual Drive Information:
Virtual Drive: 0 (Target Id: 0)
Name                :
RAID Level          : Primary-1, Secondary-0, RAID Level Qualifier-0
Size                : 446.125 GB
Sector Size         : 512
Is VD emulated      : No
Mirror Data         : 446.125 GB
State               : Optimal
Strip Size          : 64 KB
Number Of Drives per span:2
Span Depth          : 2
Default Cache Policy: WriteBack, ReadAhead, Direct, Write Cache OK if Bad BBU
Current Cache Policy: WriteBack, ReadAhead, Direct, Write Cache OK if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy   : Disk's Default
Encryption Type     : None
Is VD Cached: No
share|improve this question
(1).If the columns are not indexed, you can take advantage of HOT (see;a=blob;f=src/…). (2). Try with synchronous_commit = off, after reading the docs at (3). What does your configuration look like? Eg. results of this query: SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); – bma Sep 16 '13 at 16:04
@bma Let me add the warning about synchronous_commit: 'Asynchronous commit is an option that allows transactions to complete more quickly, at the cost that the most recent transactions may be lost if the database should crash.' – dezso Sep 17 '13 at 12:33
@dezso I should have been more explicit about the section of the docs I was referring to. Thanks for the clarification. The reason I brought that up was because one of our servers under high load (using Hibernate) was suffering so I made an informed decision to switch to async commit and 40% of the load virtually disappeared. Not recommended for most systems, but in certain cases it can buy you time between server upgrades and application changes. – bma Sep 17 '13 at 14:50
up vote 8 down vote accepted

If doing a hardware upgrade, is the number of disks key to performance?

Yes, as a hard disc - even SAS - has a head that takes time to move.

Want a HUGH upgrade?

Kill the SAS discs, go to SATA. Plug in SATA SSD - enterprise level, like the Samsung 843T.

Result? You can do around 60.000 (that is 60 thousand) IOPS per drive.

This is the reason SSD are killers in DB space and so much cheaper than any SAS drive. Phyiscal spinning disc just can not keep up with the IOPS capabilities of discs.

Your SAS discs were a mediocre choise to start with (too large to get a lot of IOPS) For a higher use database (more smaller discs would mean a lot more IOPS), but at the end SSD are the game changer here.

Regarding software / kernel. Any decent database will do a lot of IOPS and flush the buffers. THe log file needs to be WRITTEN for basic ACID conditions to be guaranteed. The only kernel tunes you could do would invalidate your transactional integrity - partially you CAN get away with that. The Raid controller in write back mode will do that - confirm the write as flushed even if it is not - but it can do so because the BBU is assumed to safe the day when power fails. Anything you do higher up in the kernel - better know you can live with the negative side effects.

At the end, databases need IOPS, and you may be surprised to see how tiny your setup is compared to some others here. I have seen databaes with 100+ discs just to get the IOPS they needed. But really, today, you buy SSD and go for size on them - they are so superior in IOPS capabilities, it makes no sense to fight this game with SAS drives.

And yes, your IOPS numbers do not look bad for the hardware. Within what I would expect.

share|improve this answer

If you can afford it, put pg_xlog on a separate RAID 1 pair of drives on its own controller with battery-backed RAM configured for write-back. This is true even if you need to use spinning rust for pg_xlog while everything else is on SSD.

If you use SSD, make sure it has a super-capacitor or other means to persist all cached data on power failure.

In general, more spindles means more I/O bandwidth.

share|improve this answer
Unfortunatly I only have four hdd slots in the raid controller. Why would you put the pg_xlogs on a separate array, if all other disks were SSD's? – Niels Kristian Sep 17 '13 at 7:26
@NielsKristian I think I'm right when I say that in most systems the transaction log is the most heavily written thing on disc. Also, writing it happens basically parallel with writing the data. The two cannot be done at the same time with a HDD as the head is either on pg_xlog or on the data folders. (Serious simplification alert!) Separating the two tasks splits the IO burden on two independent disks/arrays. – dezso Sep 17 '13 at 12:39
In addition to what @dezso said, hard drives are OK for storage dedicated to WAL because access is almost entirely sequential, so seek time is minimal.. – kgrittn Sep 17 '13 at 22:05

Assuming you're running on Linux, be sure to set the disk IO scheduler.

From past experience, switching to noop will give you a fairly substantial speed up (especially for SSD).

Changing IO scheduler can be done on the fly with no downtime.

ie. echo noop > /sys/block//queue/scheduler

See : for more information.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.