Thread: pgpool2 load balancing not working
Sorry if this is the wrong list, but I've been stuck for a couple days now. I tried pgpool-general but that list appears to not like me. I'm not getting any posts and my post hasn't shown up in the archives. I have a Python/Django app that will require database load balancing at some point in the near future. In the meantime I'm trying to learn to implement pgpool on a local virtual machine setup. I have 4 Ubuntu 12.04 VMs: 192.168.1.80 <- pool, pgppool2 installed and accessible 192.168.1.81 <- db1 master 192.168.1.82 <- db2 slave 192.168.1.83 <- db3 slave I have pgpool-II version 3.1.1 and my database servers are running PostgreSQL 9.1. I have my app's db connection pointed to 192.168.1.80:9999 and it works fine. The problem is when I use Apache ab to throw some load at it, none of SELECT queries appear to be balanced. All the load goes to my db1 master. Also, very concerning is the load on the pool server itself, it is really high compared to db1, maybe an average of 8-10 times higher. Meanwhile my db2 and db3 servers have a load of nearly zero, they appear to only be replicating from db1, which isn't very load intensive for my tests with ab. ab -n 300 -c 4 -C 'sessionid=80a5fd3b6bb59051515e734326735f80' http://192.168.1.17/contacts/ That drives the load on my pool server up to about 2.3. Load on db1 is about 0.4 and load on db2 and db3 is nearly zero. Can someone take a look at my pgpool.conf and see if what I'm doing wrong? http://pastebin.com/raw.php?i=wzBc0aSp I'm starting to think maybe it has something to do with Django wrapping every request in a transaction by default, but when the transaction only has SELECTs, shouldn't that be load balanced just fine? Makes my stomach hurt to think I may have to turn off auto-commit and manually commit transactions all throughout my code :( Still hoping it's a pgpool setup issue, since it's my first time setting it up and all. Thanks. -- Greg Donald
On Fri, Jan 4, 2013 at 3:42 PM, Greg Donald <gdonald@gmail.com> wrote: > Sorry if this is the wrong list, but I've been stuck for a couple days > now. I tried pgpool-general but that list appears to not like me. > I'm not getting any posts and my post hasn't shown up in the archives. Specifically which address are you sending to? I'm on the official list, and it seems to work fine for me. > > I have a Python/Django app that will require database load balancing > at some point in the near future. In the meantime I'm trying to learn > to implement pgpool on a local virtual machine setup. > > I have 4 Ubuntu 12.04 VMs: > > 192.168.1.80 <- pool, pgppool2 installed and accessible > 192.168.1.81 <- db1 master > 192.168.1.82 <- db2 slave > 192.168.1.83 <- db3 slave > > I have pgpool-II version 3.1.1 and my database servers are running > PostgreSQL 9.1. > > I have my app's db connection pointed to 192.168.1.80:9999 and it works fine. > > The problem is when I use Apache ab to throw some load at it, none of > SELECT queries appear to be balanced. All the load goes to my db1 > master. Also, very concerning is the load on the pool server itself, > it is really high compared to db1, maybe an average of 8-10 times > higher. Meanwhile my db2 and db3 servers have a load of nearly zero, > they appear to only be replicating from db1, which isn't very load > intensive for my tests with ab. > > ab -n 300 -c 4 -C 'sessionid=80a5fd3b6bb59051515e734326735f80' > http://192.168.1.17/contacts/ > > That drives the load on my pool server up to about 2.3. Load on db1 > is about 0.4 and load on db2 and db3 is nearly zero. > > Can someone take a look at my pgpool.conf and see if what I'm doing wrong? > > http://pastebin.com/raw.php?i=wzBc0aSp Nothing is jumping out at me as blatantly wrong, although it seems kinda weird that each of your database servers is listening on a different port #. > > > I'm starting to think maybe it has something to do with Django > wrapping every request in a transaction by default, but when the > transaction only has SELECTs, shouldn't that be load balanced just > fine? Makes my stomach hurt to think I may have to turn off > auto-commit and manually commit transactions all throughout my code :( > Still hoping it's a pgpool setup issue, since it's my first time > setting it up and all. I've never done anything with Django, but this seems like a good possibility that the transactions are causing pgpool to get confused and assume that every query requires write access. What might be more useful is for you to post your actual pgpool log somewhere, as that might contain a clue of what is going wrong.
> Sorry if this is the wrong list, but I've been stuck for a couple days > now. I tried pgpool-general but that list appears to not like me. > I'm not getting any posts and my post hasn't shown up in the archives. I am the admin of the list. Please contact me directly if you need help on this. > I have a Python/Django app that will require database load balancing > at some point in the near future. In the meantime I'm trying to learn > to implement pgpool on a local virtual machine setup. > > I have 4 Ubuntu 12.04 VMs: > > 192.168.1.80 <- pool, pgppool2 installed and accessible > 192.168.1.81 <- db1 master > 192.168.1.82 <- db2 slave > 192.168.1.83 <- db3 slave > > I have pgpool-II version 3.1.1 and my database servers are running > PostgreSQL 9.1. > > I have my app's db connection pointed to 192.168.1.80:9999 and it works fine. > > The problem is when I use Apache ab to throw some load at it, none of > SELECT queries appear to be balanced. All the load goes to my db1 > master. Also, very concerning is the load on the pool server itself, > it is really high compared to db1, maybe an average of 8-10 times > higher. Meanwhile my db2 and db3 servers have a load of nearly zero, > they appear to only be replicating from db1, which isn't very load > intensive for my tests with ab. > > ab -n 300 -c 4 -C 'sessionid=80a5fd3b6bb59051515e734326735f80' > http://192.168.1.17/contacts/ > > That drives the load on my pool server up to about 2.3. Load on db1 > is about 0.4 and load on db2 and db3 is nearly zero. > > Can someone take a look at my pgpool.conf and see if what I'm doing wrong? > > http://pastebin.com/raw.php?i=wzBc0aSp > > > I'm starting to think maybe it has something to do with Django > wrapping every request in a transaction by default, but when the > transaction only has SELECTs, shouldn't that be load balanced just > fine? Makes my stomach hurt to think I may have to turn off > auto-commit and manually commit transactions all throughout my code :( > Still hoping it's a pgpool setup issue, since it's my first time > setting it up and all. Yes, your guess is correct. In replication mode, the SELECTs should not be in an explicit transaction if you want them load balanced (if you run pgpool-II in streaming replication mode, the restriction is not applied). Is it possible to turn off auto-start-transaction of Django? Pgpool-II already wraps every write SQL statements in a trasaction. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp