Oleg Alexeev <oalexeev@gmail.com> writes: > * it is varchar columns, 256 and 32 symbols length > * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 > * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, > commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min, > checkpoint_completion_target = 0.7 > * postgres 9.2.3 installed via yum repository for version 9.2 > * 64 bit Centos 6, installed and updated from yum repository
fsync off? Have you had any power failures or other system crashes? ext4 is *way* more prone than ext3 was to corrupt data when fsync is disabled, because it caches and reorders writes much more aggressively.
> Database located on software md raid 1 based on two SSD disks array. Ext4 > filesystem. Database is master node.
Meh. I quote from the RHEL6 documentation (Storage Administration Guide, Chapter 20: Solid-State Disk Deployment Guidelines):
> Red Hat also warns that software RAID levels 1, 4, 5, and 6 are not > recommended for use on SSDs.
The part of the docs I'm looking at only asserts that performance is bad, but considering that it's a deprecated combination, it may well be that there are data-loss bugs in there. I'd certainly suggest making sure you are on a *recent* kernel. If that doesn't help, reconsider your filesystem choices.
(Disclaimer: I work for Red Hat, but not in the filesystem group, so I don't necessarily know what I'm talking about. But I have the feeling you have chosen a configuration that's pretty bleeding-edge for RHEL6.)
