Обсуждение: Advantages and disadvantages of more than one dbserver on one server
Hello, I want to discuss a little bit about the following situation: I only have one server for my databases.I had to develope on this server and also had to manage several databases, so I can expect problems if I had to restart postgreSQL, if I changed the logging..... What do you think about this solution: For each database I want to let run one server, and the different servers a located in /usr/local/pgsql-733-1 data-dir: /db/pgsql-1 /usr/local/pgsql-733-2 data....................... Is this a good idea? What problems can happen? I compile postgreSQL only with the --prefix flag. What things I should do? Each db got his own user, like postgres1, postgres2 or pgsql7331,....... I sometimes need to log everything, but this is maybe once or twice a week, but I can't restart the server during the day. Daniel
The result of compiling, testing and let postgreSQL crash: Now I know, that I only have advantages if I will let run each database with its own session of pgsql. I can stop/start/restart(/kill) one session and the other works as long as the server is not shutting down or something like the worst case,... I can setting up different users with different rights to each postgresql and also can limit the possibility that users can have access to something they shoudl not get have access. Today memory isn't expensive so with 512MB I can be shure, every user on this server might have enough to work without any timeout or waiting times greater then 2 seconds. Thanks to all which helped. Daniel P.S.: I hope the new release of postgreSQL for netware will be available soon, so the same I can do without compiling. -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org
On Thu, 3 Jul 2003, Daniel Seichter wrote: > Today memory isn't expensive so with 512MB I can be shure, every user on > this server might have enough to work without any timeout or waiting times > greater then 2 seconds. If the database is at all large, you really want more memory than that. I have a really quite large DB, but I am *really* struggling with 1GB -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote: > On Thu, 3 Jul 2003, Daniel Seichter wrote: > > > Today memory isn't expensive so with 512MB I can be shure, every user on > > this server might have enough to work without any timeout or waiting times > > greater then 2 seconds. > > If the database is at all large, you really want more memory than that. > I have a really quite large DB, but I am *really* struggling with 1GB Note that running Postgresql on it's own server is a good idea if it's currently sharing with an application server. While some systems (Zope, PHPNuke stuff) can run in little or no memory, most app servers eat up a lot of memory that postgresql could use for cacheing. We went from 512 Meg to 1.5 Gig and the change was tremendous. That box ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and about 120 Megs of buffer routinely. I'd heartily recommend even a jump from just 1 to 1.5 gig if you can make it. Eventually we're planning on moving our postgresql database to a dedicated server. Basically we'll move all the other stuff off of the current all in one box and leave the database with an older, slightly slow machine all to itself. Funny I'm thinking of dual PIII-750s as slow.
Hello Scott, > Note that running Postgresql on it's own server is a good idea if it's > currently sharing with an application server. While some systems (Zope, > PHPNuke stuff) can run in little or no memory, most app servers eat up a > lot of memory that postgresql could use for cacheing. We only use this server as postgreSQL server, and no apache, no mail no php or something like this works on it. And if the main database grows up to some GB of capacity, we had to migrate to an new server. BTW, Dual 750's will be great for me *g* Daniel
On Thu, 3 Jul 2003, scott.marlowe wrote: > On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote: > > > On Thu, 3 Jul 2003, Daniel Seichter wrote: > > > > > Today memory isn't expensive so with 512MB I can be shure, every user on > > > this server might have enough to work without any timeout or waiting times > > > greater then 2 seconds. > > > > If the database is at all large, you really want more memory than that. > > I have a really quite large DB, but I am *really* struggling with 1GB > > We went from 512 Meg to 1.5 Gig and the change was tremendous. That box > ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and > about 120 Megs of buffer routinely. I'd heartily recommend even a jump > from just 1 to 1.5 gig if you can make it. I want either a seperate (high-ish spec, high memory) box, or to not have to use my workstation as a server :) But I don't think my employers will put any more money into this project until it's fully finished. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote: > On Thu, 3 Jul 2003, scott.marlowe wrote: > > > On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote: > > > > > On Thu, 3 Jul 2003, Daniel Seichter wrote: > > > > > > > Today memory isn't expensive so with 512MB I can be shure, every user on > > > > this server might have enough to work without any timeout or waiting times > > > > greater then 2 seconds. > > > > > > If the database is at all large, you really want more memory than that. > > > I have a really quite large DB, but I am *really* struggling with 1GB > > > > We went from 512 Meg to 1.5 Gig and the change was tremendous. That box > > ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and > > about 120 Megs of buffer routinely. I'd heartily recommend even a jump > > from just 1 to 1.5 gig if you can make it. > > I want either a seperate (high-ish spec, high memory) box, or to not > have to use my workstation as a server :) But I don't think my employers > will put any more money into this project until it's fully finished. Well, always look at memory and drives. If you can add memory to your workstation is will be a much faster server. Adding drives into a RAID1 1+0 or 5 set can provide a performance increase too.
On Thu, 3 Jul 2003, scott.marlowe wrote: > On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote: > > > On Thu, 3 Jul 2003, scott.marlowe wrote: > > > > > We went from 512 Meg to 1.5 Gig and the change was tremendous. That box > > > ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and > > > about 120 Megs of buffer routinely. I'd heartily recommend even a jump > > > from just 1 to 1.5 gig if you can make it. > > > > I want either a seperate (high-ish spec, high memory) box, or to not > > have to use my workstation as a server :) But I don't think my employers > > will put any more money into this project until it's fully finished. > > Well, always look at memory and drives. If you can add memory to your > workstation is will be a much faster server. Adding drives into a RAID1 > 1+0 or 5 set can provide a performance increase too. We'll be looking at more performance boosts once the system is mature. We already have the DB on a dedicated drive, on a dedicated controller. Moving to a RAID 1 config with another 120GB drive would be good. The DB on disk currently takes up about 35GB with only 1 quarters data, but growth with longer timespans should be minimal as the DB is very well normalised. RAID 5 not an option in the current box, as there are not enough bays in the (crappy RM desktop) case, and we'd have to use software RAID or buy a more epensive controller, and probably move to SCSI, which would be expensive enough to justify buying a small dedicated box instead, which would be better in many ways. When ones workstation is also supposed to be a high-availability server, certain things become difficult. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
Hello Sam and Scott > We'll be looking at more performance boosts once the system is mature. > We already have the DB on a dedicated drive, on a dedicated controller. > Moving to a RAID 1 config with another 120GB drive would be good. The DB > on disk currently takes up about 35GB with only 1 quarters data, but > growth with longer timespans should be minimal as the DB is very well > normalised. RAID 5 not an option in the current box, as there are not > enough bays in the (crappy RM desktop) case, and we'd have to use > software RAID or buy a more epensive controller, and probably move to > SCSI, which would be expensive enough to justify buying a small > dedicated box instead, which would be better in many ways. When ones > workstation is also supposed to be a high-availability server, certain > things become difficult. Is there any knowned rate in percent, how much faster a database will be if I would put 1GB instead of 512MB into my server? What RAID do you prefer? A 'simple' RAID1 should be enough or do you prefer RAID5 or what else? Daniel
On Fri, 4 Jul 2003, Daniel Seichter wrote: > Hello Sam and Scott > > We'll be looking at more performance boosts once the system is mature. > > We already have the DB on a dedicated drive, on a dedicated controller. > > Moving to a RAID 1 config with another 120GB drive would be good. The DB > > on disk currently takes up about 35GB with only 1 quarters data, but > > growth with longer timespans should be minimal as the DB is very well > > normalised. RAID 5 not an option in the current box, as there are not > > enough bays in the (crappy RM desktop) case, and we'd have to use > > software RAID or buy a more epensive controller, and probably move to > > SCSI, which would be expensive enough to justify buying a small > > dedicated box instead, which would be better in many ways. When ones > > workstation is also supposed to be a high-availability server, certain > > things become difficult. > Is there any knowned rate in percent, how much faster a database will be if > I would put 1GB instead of 512MB into my server? > What RAID do you prefer? A 'simple' RAID1 should be enough or do you prefer > RAID5 or what else? Well, it really depends on the size of your data set. If you've got a 100 Meg database, then going from 512Meg to 1Gig is likely a waste. As long as your dataset is larger than how much your kernel can cache given the current memory size, then you can see a performance gain from more memory. RAID-1 versus RAID-5 Generally RAID-1 is faster, especially for a smaller number of concurrent users. As your number of concurrent processes rise, you can look at using something like RAID 1+0 / 0+1 to increase the number of platters, or RAID5. While 1+0 / 0+1 will usually be faster, they will provide less storage per disk used, whereas RAID5 will provide better economy (n-1 storage) but slightly lower write performance than many RAID1 or 1+0 setups. RAID5 will, however, provide very good read performance under parallel load, so for data warehouses or mostly read sites, RAID5 is a good choice. For heavy transactional load look at RAID1 or 1+0.