Re: PostgreSQL+ZFS
Atif CEYLAN put forth on 1/1/2011 3:58 PM:
> On 01/01/2011 06:24 PM, Stan Hoeppner wrote:
>> How much data? Total GB?
> ~300 GB
>> Are you currently short of space?
> no, don't need more space.
Perfect. :)
>> Are you currently short of IOPS capacity?
> yes
Got it.
>> How many concurrent transactions?
> minimum 100-200 transactions, maximum 800-1000 concurrent transactions.
>> What types of transactions?
> usually update and insert
Write heavy.
>> What is a "large postgresql database system"? What exactly do you mean
>> by this? Does large mean heavy transaction load? Or does it simply
>> mean lots of data housed? Or is it simply BS?
> heavy transaction load.
Cool. If you don't need more than 300GB of space the answer is easy.
Get one of these 120,000 random write IOPS 360GB RevoDrive PCIe x4 cards
and put everything on it, db files, transaction logs, all of it. For
less than $1200 USD you'll get the IOPS performance of an 800 disk, FC
15k rpm RAID 10 fiber channel SAN array from EMC, costing about $2
million USD. Your latency will be an order of magnitude lower though
because the flash is connected directly to your PCIe bus. The only
thing such a SAN setup would have that you won't is dozens of terabytes
of space and more link throughput, neither of which you need.
You only need the additional IOPS, not the space, so you save $2 million
and get superior performance to boot. This is the true power and
economy of SSD technology, and how its price should be evaluated, not
dollars per gigabyte, but dollars per IOPS and dollars per watt. The $$
spent on the electric bill for a year of running that EMC array with its
many racks of disk trays would buy you dozens of these RevoDrive cards.
http://www.newegg.com/Product/Product.aspx?Item=N82E16820227662
http://www.ocztechnology.com/products/solid-state-drives/pci-express/revodrive/ocz-revodrive-x2-pci-express-ssd-.html
120,000 4k random write IOPS (overkill)
400 MB/s sustained write throughput (overkill)
PCI Express x4 interface
This is not a drive, but a PCB solution. Supreme reliability, just like
a motherboard. No mirroring or RAID required. Simply snapshot the
filesystem and dump it to tape or D2D using differential backup.
This card works fine with Linux if you have a recent kernel, even though
OCZ targets the desktop with this model. The 512GB Z-Drive card they
target at "servers and workstations" has only 1/10th the write IOPS
capability of the RevoDrive 380, and is $600 more expensive. As far as
I can tell the Z-Drive has no advantage, but possibly official technical
support.
Also, I recommend using the XFS filesystem due to its superior direct IO
performance with databases. Configure PGSQL to use direct IO. When you
make the XFS filesystem, consume the entire drive, creating 36
allocation groups. Refer to "man mkfs.xfs". This will maximize
parallel IOPS throughput to the SSD. Buy this card and do these things,
and you will be absolutely stunned by the performance you get out of it.
This storage card with XFS on top should easily handle 100,000 inserts
_per second_ if you have enough CPU horsepower to drive that load.
If you go this route, please let us know how well it works for you. I'm
sure many here would be eager to know. Well, others than myself. ;)
--
Stan
Reply to: