Hello all, I've spent some time looking through previous posts regarding postgres and SSD drives and have also been reading up on the subject of SSDs in general elsewhere. Some quick background: We're currently looking at changing our basic database setup as we migrate away from some rather old (circa 2006 or so) hardware to more current gear. Our previous setup basically consisted of two types of 1U boxes - dual-xeon with a low-end Adaptec RAID card paired with two U320 or U160 10K SCSI drives for database and light web frontend and mail submission duties and single P4 and dual PIII boxes with commodity drives handling mail deliveries. Most of the delivery servers have been migrated to current hardware (single and dual quad-core xeons, 4 WD RE3 SATA drives, FreeBSD w/ZFS), and we've also moved a handful of the db/web services to one of these servers just to see what a large increase in RAM and CPU can do for us. As we sort of expected, the performance overall was better, but there's a serious disk IO wall we hit when running nightly jobs that are write heavy and long-running. There are currently four db/web servers that have a combined total of 133GB of on-disk postgres data. Given the age of the servers, the fact that each only has two drives on rather anemic RAID controllers with no cache/BBU, and none has more than 2GB RAM I think it's a safe bet that consolidating this down to two modern servers can give us better performance, allow for growth over the next few years, and let us split db duties from web/mail submission. One live db server, one standby. And this is where SSDs come in. We're not looking at terabytes of data here, and I don't see us growing much beyond our current database size in the next few years. SSDs are getting cheap enough that this feels like a good match - we can afford CPU and RAM, we can't afford some massive 24 drive monster and a pile of SAS drives. The current db boxes top out at 300 IOPS, the SATA boxes maybe about half that. If I can achieve 300x4 IOPS (or better, preferably...) on 2 or 4 SSDs, I'm pretty much sold. >From what I've been reading here, this sounds quite possible. I understand the Intel 320 series are the best "bargain" option since they can survive an unexpected shutdown, so I'm not going to go looking beyond that - OCZ makes me a bit nervous and finding a clear list of which drives have the "supercapacitor" has been difficult. I have no qualms about buying enough SSDs to mirror them all. I am aware I need to have automated alerts for various SMART stats so I know when danger is imminent. I know I need to have this box replicated even with mirrors and monitoring up the wazoo. Here's my remaining questions: -I'm calling our combined databases at 133GB "small", fair assumption? -Is there any chance that a server with dual quad core xeons, 32GB RAM, and 2 or 4 SSDs (assume mirrored) could be slower than the 4 old servers described above? I'm beating those on raw cpu, quadrupling the amount of RAM (and consolidating said RAM), and going from disks that top out at 4x300 IOPS with SSDs that conservatively should provide 2000 IOPS. -We're also finally automating more stuff and trying to standardize server configs. One tough decision we made that has paid off quite well was to move to ZFS. We find the features helpful to admin tasks outweigh the drawbacks and RAM is cheap enough that we can deal with its tendency to eat RAM. Is ZFS + Postgres + SSDs a bad combo? -Should I even be looking at the option of ZFS on SATA or low-end SAS drives and ZIL and L2ARC on SSDs? Initially this intrigued me, but I can't quite get my head around how the SSD-based ZIL can deal with flushing the metadata out when the whole system is under any sort of extreme write-heavy load - I mean if the ZIL is absorbing 2000 IOPS of metadata writes, at some point it has to get full as it's trying to flush this data to much slower spinning drives. -Should my standby box be the same configuration or should I look at actual spinning disks on that? How rough is replication on the underlying storage? Would the total data written on the slave be less or equal to the master? Any input is appreciated. I did really mean for this to be a much shorter post... Thanks, Charles -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
