John Arbash Meinel wrote:
John Allgood wrote:
I think maybe I didn't explain myself well enough. At most we will service 200-250 connections across all the 9 databases mentioned. The database we are building is for a trucking company. Each of the databases represents a different division. With one master database that everything is updated to. Most of the access to the database is by simple queries. Most of the IO intensive stuff is done when revenue reports are generated and when we have our month/year end processing. All the trucking loads that are mark as delivered are transferred to our master database during night time processing. All that will be handled using custom scripts. Maybe I have given a better explanation of the application. my biggest concern is how to partition the shared storage for maximum performance. Is there a real benifit to having more that one raid5 partition or am I wasting my time.
Thanks
John Allgood - ESC
If you read the general advice statements, it's actually better to not use raid5, but to use raid10 (striping and mirroring). Simply because raid5 writing is quite poor.
Also, if you have the disks, the next best improvement is to move pg_xlog onto it's own set of disks. I think that gets as much as 25% improvement by itself. pg_xlog is an append process, which must complete before the actual data gets updated, so giving it it's own set of spindles reduces seek time, and lets the log be written quickly. I think there is even some benefit to making pg_xlog be a solid state disk, as it doesn't have to be huge, but having high I/O rates can remove it as a bottleneck. (I'm not positive how large pg_xlog gets, but it is probably small compared with the total db size, and I think it can be flushed periodically as transactions are completed.)
I'm not sure what you are considering "shared storage". Are you thinking that all the machines will be mounting a remote drive for writing the DB? They should all have their own local copy (multiple masters on the same database is not supported).
I think it is possible to get better performance by having more raid systems. But it is application dependent. If you know that you have 2 tables that are being updated often and independently, then having each one on it's own raid would allow better concurrency.
But it sounds like in your app you get concurrency by having a bunch of remote databases, which then do bulk updates on the master database. I think if you are careful to have each of the remote dbs update the master at a slightly different time, you could probably get very good transaction rates.
John =:->
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
