Thread: Postgres on VPS - how much is enough?
How big a VPS would I need to run a Postgres DB. I need a Postgres database with about 15 tables that will run on a single virtual private server. The 15 tables will be spread over three tablespaces (5 tables per tablespace) and be accessed by three different applications running on different machines. One application will add about 500 orders per day Another will access this data to create and send about 500 emails per day A third will access this data to create an after-sales survey for at most 500 times per day. What type of VPS would I need to run a database with this type pf load? Is 128 MB ram enough? What percentage of a 2.8 GHz CPU would be required? It's been a long time since I used Postgres. Thanks for any help, Nagita
On 3/6/06, Nagita Karunaratne <nagita.k@gmail.com> wrote: > How big a VPS would I need to run a Postgres DB. > > One application will add about 500 orders per day > Another will access this data to create and send about 500 emails per day > A third will access this data to create an after-sales survey for at > most 500 times per day. > > What type of VPS would I need to run a database with this type pf load? > Is 128 MB ram enough? > What percentage of a 2.8 GHz CPU would be required? My problem with running PG inside of a VPS was that the VPS used a virtual filesystem... basically, a single file that had been formatted and loop mounted so that it looked like a regular hard drive. Unfortunately, it was very slow. The difference between my application and yours is that mine well more than filled the 1GB of RAM that I had allocated. If your data will fit comfortably into RAM then you may be fine. If you really want to know how it will work, try running it yourself. Two projects that make this really easy and free is the colinux project[1] which allows you to run a linux VPS in Windows and the linux-vserver project[2] which is free software that works on pretty much any linux OS. Try it out, tinker with the values and that way you won't have to guess when making your purchase decission. [1] http://www.colinux.org/ Coperative Linux [2] http://linux-vserver.org/ Linux-vserver project -- Matthew Nuzum www.bearfruit.org
Nagita Karunaratne wrote: > How big a VPS would I need to run a Postgres DB. > > I need a Postgres database with about 15 tables that will run on a > single virtual private server. > > The 15 tables will be spread over three tablespaces (5 tables per > tablespace) and be accessed by three different applications running on > different machines. > > One application will add about 500 orders per day > Another will access this data to create and send about 500 emails per day > A third will access this data to create an after-sales survey for at > most 500 times per day. > > What type of VPS would I need to run a database with this type pf load? > Is 128 MB ram enough? > What percentage of a 2.8 GHz CPU would be required? > If the database is going to be larger then the allocated memory, disk I/O is very important. Not all VPS technologies are equal in this regard. (see link below) Like someone else suggested, the best way to know what VPS specs you need is to do your own tests/benchamarks. http://www.cl.cam.ac.uk/Research/SRG/netos/xen/performance.html -Kevin
Thanks for the replies, From personal experience, would you run Postgres on a linux machine (NOT a vps) with 512MB of ram? Assumining I can keep all my data in memory. Thanks, Nagita > My problem with running PG inside of a VPS was that the VPS used a > virtual filesystem... basically, a single file that had been formatted > and loop mounted so that it looked like a regular hard drive. > Unfortunately, it was very slow. The difference between my application > and yours is that mine well more than filled the 1GB of RAM that I had > allocated. If your data will fit comfortably into RAM then you may be > fine. >
On Mon, 6 Mar 2006, Matthew Nuzum wrote: > On 3/6/06, Nagita Karunaratne <nagita.k@gmail.com> wrote: >> How big a VPS would I need to run a Postgres DB. >> > >> One application will add about 500 orders per day >> Another will access this data to create and send about 500 emails per day >> A third will access this data to create an after-sales survey for at >> most 500 times per day. >> >> What type of VPS would I need to run a database with this type pf load? >> Is 128 MB ram enough? >> What percentage of a 2.8 GHz CPU would be required? > > My problem with running PG inside of a VPS was that the VPS used a > virtual filesystem... basically, a single file that had been formatted > and loop mounted so that it looked like a regular hard drive. > Unfortunately, it was very slow. The difference between my application > and yours is that mine well more than filled the 1GB of RAM that I had > allocated. If your data will fit comfortably into RAM then you may be > fine. We host VPSs here (http://www.hub.org) and don't use the 'single file, virtual file system' to put them into ... it must depend on where you host? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On 3/6/06, Marc G. Fournier <scrappy@postgresql.org> wrote: > On Mon, 6 Mar 2006, Matthew Nuzum wrote: > > My problem with running PG inside of a VPS was that the VPS used a > > virtual filesystem... basically, a single file that had been formatted > > and loop mounted so that it looked like a regular hard drive. > > Unfortunately, it was very slow. The difference between my application > > and yours is that mine well more than filled the 1GB of RAM that I had > > allocated. If your data will fit comfortably into RAM then you may be > > fine. > > We host VPSs here (http://www.hub.org) and don't use the 'single file, > virtual file system' to put them into ... it must depend on where you > host? That's true... I hope I didn't imply that I am anti-vps, I run my own servers and one of them is dedicated to doing VPS for different applications. I think they're wonderful. On 3/6/06, Nagita Karunaratne <nagita.k@gmail.com> wrote: > From personal experience, would you run Postgres on a linux machine > (NOT a vps) with 512MB of ram? > > Assumining I can keep all my data in memory. Nagita, It all depends on performance... I have one postgres database that runs on a Pentium 350MHz with 128MB of RAM. It does 1 insert per minute 24 hours per day. Because the load is so low, I can get away with minimal hardware. If your application has a lot of inserts/updates then disk speed is important and can vary greatly from one VPS to another. If your application is not time-critical than this may be a moot point anyway. -- Matthew Nuzum www.bearfruit.org
Clustering solutions for PostgreSQL are currently pretty limited. Slony could be a good option in the future, but it currently only supports Master-Slave replication (not true clustering) and in my experience is a pain to set up and administer. Bizgres MPP has a lot of promise, especially for data warehouses, but it currently doesn't have the best OLTP database performance. So, I had a couple of questions: 1) I have heard bad things from people on this list regarding SANs - but is there a better alternative for a high performance database cluster? (both for redundancy and performance) I've heard internal storage touted before, but then you have to do something like master-master replication to get horizontal scalability and write performance will suffer. 2) Has anyone on this list had experience using Ingres R3 in a clustered environment? I am considering using Ingres R3's built-in clustering support with a SAN, but am interested to know other people's experiences before we start toying with this possibility. Any experience with the Ingres support from Computer Associates? Good/bad? Jeremy
On Mon, Mar 06, 2006 at 01:14:45PM -0400, Marc G. Fournier wrote: > We host VPSs here (http://www.hub.org) and don't use the 'single file, > virtual file system' to put them into ... it must depend on where you > host? Yeah, but aren't you also using FreeBSD jails? AFAIK linux doesn't have an equivalent to jail; all their VPS stuff actually brings up a full-blown copy of linux, kernel and all. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Please don't steal threds; post a new email rather than replying to an existing thread. On Mon, Mar 06, 2006 at 02:58:32PM -0500, Jeremy Haile wrote: > Clustering solutions for PostgreSQL are currently pretty limited. Slony > could be a good option in the future, but it currently only supports > Master-Slave replication (not true clustering) and in my experience is a > pain to set up and administer. Bizgres MPP has a lot of promise, > especially for data warehouses, but it currently doesn't have the best > OLTP database performance. > > So, I had a couple of questions: > 1) I have heard bad things from people on this list regarding SANs - but > is there a better alternative for a high performance database cluster? > (both for redundancy and performance) I've heard internal storage > touted before, but then you have to do something like master-master > replication to get horizontal scalability and write performance will > suffer. PostgreSQL on a SAN won't buy you what I think you think it will. It's essentially impossible to safely run two PostgreSQL installs off the same data files without destroying your data. What a SAN can buy you is disk-level replication, but I've no experience with that. > 2) Has anyone on this list had experience using Ingres R3 in a clustered > environment? I am considering using Ingres R3's built-in clustering > support with a SAN, but am interested to know other people's experiences > before we start toying with this possibility. Any experience with the > Ingres support from Computer Associates? Good/bad? Can you point us at more info about this? I can't even find a website for Ingress... I'd be careful about OSS-based clusters. Everyone I've seen has some limitations, some of which are pretty serious. There are some that are command-based clustering/replication, but that raises some serious potential issues with non-deterministic functions among other things. Continuent seems to have done a good job dealing with this, but there's still some gotchas you need to be aware of. Then there's things like MySQL cluster, which requires that the entire database fits in memory. Well, if the database is in memory, it's going to be pretty dang fast to begin with, so you're unlikely to need scaleability across machines. Basically, truely enterprise-class clustering (and replication) are extremely hard to do, which is why this is pretty much exclusively the realm of the 'big 3' at this point. Slony-II could seriously change things when it comes out, though it still won't give you the data guarantees that a true syncronous multi-master setup does. But it will hopefully offer multi-master syncronous type behavior with the performance of an async database, which would be a huge leap forward. Perhaps if you posted your performance requirements someone could help point you to a solution that would meet them. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, 2006-03-07 at 13:00 -0600, Jim C. Nasby wrote: ... > PostgreSQL on a SAN won't buy you what I think you think it will. It's > essentially impossible to safely run two PostgreSQL installs off the > same data files without destroying your data. What a SAN can buy you is > disk-level replication, but I've no experience with that. It is possible to run two instances against the same SAN using tools such as RedHat's Cluster Suite. We use that in-house as a cheap alternative for Oracle clustering, although we're not using it for our PostgreSQL servers yet. It's not for load balancing, just active/passive fault tolerance. -- Mark Lewis
On Tue, Mar 07, 2006 at 11:20:50AM -0800, Mark Lewis wrote: > On Tue, 2006-03-07 at 13:00 -0600, Jim C. Nasby wrote: > > ... > > > PostgreSQL on a SAN won't buy you what I think you think it will. It's > > essentially impossible to safely run two PostgreSQL installs off the > > same data files without destroying your data. What a SAN can buy you is > > disk-level replication, but I've no experience with that. > > It is possible to run two instances against the same SAN using tools > such as RedHat's Cluster Suite. We use that in-house as a cheap > alternative for Oracle clustering, although we're not using it for our > PostgreSQL servers yet. It's not for load balancing, just > active/passive fault tolerance. True, but the OP was talking about scaleability, which is not something you get with this setup. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On 7/3/06 18:45, "Jim C. Nasby" <jnasby@pervasive.com> wrote: > On Mon, Mar 06, 2006 at 01:14:45PM -0400, Marc G. Fournier wrote: >> We host VPSs here (http://www.hub.org) and don't use the 'single file, >> virtual file system' to put them into ... it must depend on where you >> host? > > Yeah, but aren't you also using FreeBSD jails? AFAIK linux doesn't have > an equivalent to jail; all their VPS stuff actually brings up a > full-blown copy of linux, kernel and all. No, linux vserver is equivalent to a jail - and they work superbly imho. developer.pgadmin.org is just one such VM that I run. http://www.linux-vserver.org/ Regards, Dave.
On 3/7/06, Dave Page <dpage@vale-housing.co.uk> wrote: > On 7/3/06 18:45, "Jim C. Nasby" <jnasby@pervasive.com> wrote: > > On Mon, Mar 06, 2006 at 01:14:45PM -0400, Marc G. Fournier wrote: > >> We host VPSs here (http://www.hub.org) and don't use the 'single file, > >> virtual file system' to put them into ... it must depend on where you > >> host? > > > > Yeah, but aren't you also using FreeBSD jails? AFAIK linux doesn't have > > an equivalent to jail; all their VPS stuff actually brings up a > > full-blown copy of linux, kernel and all. > > No, linux vserver is equivalent to a jail - and they work superbly imho. > developer.pgadmin.org is just one such VM that I run. > > http://www.linux-vserver.org/ > > Regards, Dave. I can confirm this. I've been using linux-vserver for years. It is a very up-to-date and active project that is extremely responsive and helpful to users of all experience levels. -- Matthew Nuzum www.bearfruit.org