Thread: Question about load balance
Hello ppl, I read in internet and in this mailing list, when some one asking about load balance, most of the answers is: pgpool. I want to asking how stable is pgpool ? How much query can handle ? What load average ? Im looking for something multi master solution. Thanks, Hristo
On 06/07/2012 10:26 PM, Condor wrote: > Hello ppl, > > I read in internet and in this mailing list, when some one asking > about load balance, > most of the answers is: pgpool. > I want to asking how stable is pgpool ? How much query can handle ? > What load average ? > Im looking for something multi master solution. PgPool-II doesn't offer mutli-master operation. Not much does. Multi-master is very difficult to get right, and even harder to make fast. Are you really sure it's what you want? Failover is often a much, MUCH simpler and more efficient approach. -- Craig Ringer
Am 08.06.2012 00:39, schrieb Craig Ringer:
The only multimaster replication solution for PostgreSQL I know of, is Bucardo.
http://bucardo.org/wiki/Bucardo
Though I second what Craig Ringer wisely says; it's difficult to get it right and fast.
Best Regards.
http://pglearn.blogspot.mx/
twitter: @sqlhotfix
On 06/07/2012 10:26 PM, Condor wrote:Hello ppl,
I read in internet and in this mailing list, when some one asking about load balance,
most of the answers is: pgpool.
I want to asking how stable is pgpool ? How much query can handle ? What load average ?
Im looking for something multi master solution.
PgPool-II doesn't offer mutli-master operation. Not much does.
Multi-master is very difficult to get right, and even harder to make fast.
Are you really sure it's what you want? Failover is often a much, MUCH simpler and more efficient approach.
--
Craig Ringer
The only multimaster replication solution for PostgreSQL I know of, is Bucardo.
http://bucardo.org/wiki/Bucardo
Though I second what Craig Ringer wisely says; it's difficult to get it right and fast.
Best Regards.
http://pglearn.blogspot.mx/
twitter: @sqlhotfix
On 2012-06-08 08:39, Craig Ringer wrote: > On 06/07/2012 10:26 PM, Condor wrote: >> Hello ppl, >> >> I read in internet and in this mailing list, when some one asking >> about load balance, >> most of the answers is: pgpool. >> I want to asking how stable is pgpool ? How much query can handle ? >> What load average ? >> Im looking for something multi master solution. > > PgPool-II doesn't offer mutli-master operation. Not much does. > > Multi-master is very difficult to get right, and even harder to make > fast. > > Are you really sure it's what you want? Failover is often a much, > MUCH simpler and more efficient approach. > > -- > Craig Ringer No, Im not sure, just looking how to make load balance. I have a small database around 20 gb, but I expect to join another database on different scheme and Im looking for solution about load balance or some cache mechanism. Bad part is one row from db is read once in a month, in worst scenario 3-4 times in month and I think cache is not good option, but I don't have idea how will work. H.
On 06/10/12 11:26 PM, Condor wrote: > Im not sure, just looking how to make load balance. I have a small > database around 20 gb, > but I expect to join another database on different scheme and Im > looking for solution > about load balance or some cache mechanism. Bad part is one row from > db is read once > in a month, in worst scenario 3-4 times in month and I think cache is > not good option, > but I don't have idea how will work. whats the problem you're trying to solve? so far, doesn't sound like you have anything that a decent database server couldn't handle easily. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 2012-06-11 09:35, John R Pierce wrote: > On 06/10/12 11:26 PM, Condor wrote: >> Im not sure, just looking how to make load balance. I have a small >> database around 20 gb, >> but I expect to join another database on different scheme and Im >> looking for solution >> about load balance or some cache mechanism. Bad part is one row from >> db is read once >> in a month, in worst scenario 3-4 times in month and I think cache >> is not good option, >> but I don't have idea how will work. > > > whats the problem you're trying to solve? so far, doesn't sound > like you have anything that a decent database server couldn't handle > easily. > > > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast Maybe my fault that I have divided the issues into two separate e-mail, one for load balance and one bound by rules on how to bind together two bases of different schemes. As I wrote my base is small, and the server keeps a small load average, but if bind together the two databases since both must use the recording and reading 3-4 tables only, I seek advice if the server load is too much what I could do to it landed. If I run load balance with pgpool how stable will be my system, I run stream replication but I see when master send data to slave and in this time I query slave server, slave server break query. Did I will have same problems with pgpool. Basically I want to be prepared what options I have if this happens. H.
On 06/11/12 12:11 AM, Condor wrote: > I seek advice if the server load is too much what I could do to it > landed. I recommend a faster server for this. more CPU cores, more memory, faster storage. that will take you a LONG ways, much simpler than complex and fragile database cluster schemes -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 2012-06-11 10:23, John R Pierce wrote: > On 06/11/12 12:11 AM, Condor wrote: >> I seek advice if the server load is too much what I could do to it >> landed. > > I recommend a faster server for this. more CPU cores, more memory, > faster storage. that will take you a LONG ways, much simpler than > complex and fragile database cluster schemes > > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast Yes, I now but these parameters can't be increase forever. It's can but isn't cheep. For that reason I looking some other ways. H.
On 06/11/12 2:11 AM, Condor wrote: > Yes, I now but these parameters can't be increase forever. It's can > but isn't cheep. > For that reason I looking some other ways. why don't you worry about that when you get there, rather than before you even start? I've got a 2U dual xeon X5660 server w/ 48GB ram, and built in 20 x 15k raid10 that can handle like 5000 TPS-C style transactions/second, (pg_bench). these are update transactions. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 2012-06-11 21:03, John R Pierce wrote: > On 06/11/12 2:11 AM, Condor wrote: >> Yes, I now but these parameters can't be increase forever. It's can >> but isn't cheep. >> For that reason I looking some other ways. > > > why don't you worry about that when you get there, rather than before > you even start? May be because some times when some one start a new business does not have 20k $ for a new server and resource of the server is enough for the moment and as I planed is enough for this year. My question was how stable is pgpool, what problems I can expect, and pure curiosity what is the technique for managing large databases. CPU and memory to the second coming or are there other techniques for scattering applications on other servers. > > I've got a 2U dual xeon X5660 server w/ 48GB ram, and built in 20 x > 15k raid10 that can handle like 5000 TPS-C style transactions/second, > (pg_bench). these are update transactions. > > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast H.
On 06/11/12 12:17 PM, Condor wrote: > May be because some times when some one start a new business does not > have 20k $ for > a new server and resource of the server is enough for the moment and > as I planed > is enough for this year. and when you start a new business, you don't lease a campus large enough for 10,000 employees, you deal with that when you need it. if your app actually ends up needing to scale to google size, plan on having to redesign it a few times. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Tue, Jun 12, 2012 at 5:17 AM, Condor <condor@stz-bg.com> wrote: > On 2012-06-11 21:03, John R Pierce wrote: >> >> On 06/11/12 2:11 AM, Condor wrote: >>> >>> Yes, I now but these parameters can't be increase forever. It's can but >>> isn't cheep. >>> For that reason I looking some other ways. >> >> why don't you worry about that when you get there, rather than before >> you even start? > > May be because some times when some one start a new business does not have > 20k $ for > a new server and resource of the server is enough for the moment and as I > planed Postgres performance is pretty awesome even on a cheap laptop. I've done thrash testing on a basic unit that forms the backbone of our dev/test system, and also on a similar laptop that has a couple hundred dollars of SSD replacing its standard hard drive, and both of them can handle more TPS than you would think to look at them. (I don't actually have database-level TPS ratings for them, but they managed 5-10K items per second of conceptual throughput - each "item" involving quite a bit of processing.) Put it onto some real server hardware, even just $1K or so, and you'll have something that you can upgrade for as long as you need to. ChrisA
On 2012-06-11 22:47, John R Pierce wrote: > On 06/11/12 12:17 PM, Condor wrote: >> May be because some times when some one start a new business does >> not have 20k $ for >> a new server and resource of the server is enough for the moment and >> as I planed >> is enough for this year. > > and when you start a new business, you don't lease a campus large > enough for 10,000 employees, you deal with that when you need it. > > if your app actually ends up needing to scale to google size, plan on > having to redesign it a few times. > > > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast --- cut --- My question was how stable is pgpool, what problems I can expect, and pure curiosity what is the technique for managing large databases. CPU and memory to the second coming or are there other techniques for scattering applications on other servers. --- cut --- I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. H.
> My question was how stable is pgpool, what problems I can > expect, and pure curiosity what is the technique for managing large > databases. > CPU and memory to the second coming or are there other techniques for > scattering > applications on other servers. > > > --- cut --- > > I think I'm trying to learn information what is the technique for > managing large databases > not to philosophize what was my server. You'd better to subscribe pgpool-general@pgpool.net list (http://www.pgpool.net/mediawiki/index.php/Mailing_lists) and post this kind of question. There are many people who are serious about pgpool in the list. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Hi, > I think I'm trying to learn information what is the technique for managing > large databases > not to philosophize what was my server. In this case it starts to get very specific about what you are trying to accomplish. Transactional databases offer a lot guarantees, which makes it hard to "just add another machine to the cluster", this isn't a webserver ;) - Clemens
On 06/11/12 11:29 PM, Condor wrote: > I think I'm trying to learn information what is the technique for > managing large databases > not to philosophize what was my server. you handle large databases with a lot of fast disk, and memory, this gets you into the terabytes. clustering/load balancing would not do for this, other than needing MORE fast disk (N replicas require N times the disk system of one database). clustering can provide active/slave failover for high availability, or it can provide replicas for balancing read queries. updates have to be made to all the replicas, so they wont be any faster than a single server (in fact, will be slower due to the overhead of replication -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 2012-06-12 10:48, John R Pierce wrote: > On 06/11/12 11:29 PM, Condor wrote: >> I think I'm trying to learn information what is the technique for >> managing large databases >> not to philosophize what was my server. > > you handle large databases with a lot of fast disk, and memory, this > gets you into the terabytes. > > clustering/load balancing would not do for this, other than needing > MORE fast disk (N replicas require N times the disk system of one > database). clustering can provide active/slave failover for high > availability, or it can provide replicas for balancing read queries. > updates have to be made to all the replicas, so they wont be any > faster than a single server (in fact, will be slower due to the > overhead of replication > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast Thanks, I thought so, but I was not sure whether this is a better option. H.