0

We use IBM Storwize V3700 as storage solution in our company, it runs 3 storage volumes on RAID 5. The storage is used not only for DB's, but also for general VM storage and a shared filesystem available for the whole company.

My understanding is that ACID databases in general (and PostgreSQL in particular) operates better if you have consistent IOPS and the WAL log and data storage in separate disks. Such a configuration is not possible in generic storage solutions. AFAIK Databases do not operate well in heavy-write/heavy-read scenarios when the filesystem is on RAID 5 but I can't find any straight article explaining it.

I want to move one Postgres DB out of the generic storage for performance reasons because the volumes are used to serve other VM's and services so I'll have the 'bad neighbor' effect in IOPS, but I need to back up my understanding.

David Hofmann
  • 131
  • 6
  • 1
    "Databases do not operate well in heavy-write/heavy-read scenarios when the filesystem is on RAID 5" They'll work just fine if you set up your RAID5 array with a proper segment/stripe size to match the size of IO operations that your DB does, properly align the LUN(s) on the array so that the IO operations line up with RAID stripes, and dedicate the entire RAID array to the DB and ONLY the DB. So you can't set up a 21-drive RAID5/6 array, partition it into 37 different misaligned LUNs used by everyone, and expect good DB performance. That's why enterprise-grade DBs support raw devices. – Andrew Henle Oct 20 '16 at 12:40

1 Answers1

1

Pretty low end solution. Let's get some common sense here:

My understanding is that ACID databases in general (and PostgreSQL in particular) operates better if you have consistent IOPS and the WAL log and data storage in separate disks. Such a configuration is not possible in generic storage solutions.

And that is a reason that for example MS has long favoured local discs - besides the ability to have a lot more bandwidth and cost efficiency. That said, it is totally doable. I once worked with a customer having 160 dedicated hard discs in a SAN - it all is a matter of putting the money up to the needs.

AFAIK Databases do not operate well in heavy-write/heavy-read scenarios when the filesystem is on RAID 5 but I can't find any straight article explaining it.

Define well. In 2016 generally databases work against SSD when performanc is needed as any hard disc is not even second choice.

I want to move one Postgres DB out of the generic storage for performance reasons

Makes. No. Sense.

Buy local SSD. Even with SATA based SSD you get a LOT more performance out of local discs than any not ridiculously expensive SAN solution. Assuming "generic storage" means some low end NAS device - sorry, but this is not high performance to what most most performance oriented desktops use. YOu need to sit down, look at current technology and then do the math. Generally: Databases on SSD.

TomTom
  • 51,649
  • 7
  • 54
  • 136
  • Thanks for the quick answer. I know SSD is a must, thanks for the remark though as I haven't mentioned it. Regarding the move out of the storage for performance reasons is because the storage is shared among other VMs and services. So my DB will have the 'bad neighbor' effect of cloud VMs competing for IOPS. – David Hofmann Oct 20 '16 at 12:12
  • But that is YOUR decision. I run multiple virtualization servers and on some I have local discs dedicated to VM's running databases. Obviously those are not standard desktop systems - I have multiple 2U rack systems that handle 24+2 hard discs for that reason. – TomTom Oct 20 '16 at 12:15
  • You got the point I needed. Local discs `dedicated to VM's running databases`. Thank you! – David Hofmann Oct 20 '16 at 12:16
  • Moving the DB from generic storage does make sense if access to the actual physical drives used to store the data is shared with others. If that's the case, the LUNs presented to the DB server are likely misaligned, too, and it's almost certain the underlying RAID array isn't set up to match the IO pattern the DB performs. If that's the case, then yes, running it on local SSDs will almost certainly be a LOT faster. – Andrew Henle Oct 20 '16 at 12:45
  • No, it does not. If you need perfrmance for a lower price the solution is to add local discs. – TomTom Oct 20 '16 at 16:39