Linux Archive

Linux Archive (http://www.linux-archive.org/)
-   Debian User (http://www.linux-archive.org/debian-user/)
-   -   MySQL Clustering; Load balancing is not working. (http://www.linux-archive.org/debian-user/248998-mysql-clustering-load-balancing-not-working.html)

"Stackpole, Chris" 02-19-2009 10:12 PM

MySQL Clustering; Load balancing is not working.
 
This is a project of personal experimentation and learning; in other
words, if I goof something up real bad, oh well. I can reinstall.

I wanted to learn more about MySQL clustering so I looked around and
found this guide [1]. It was the newest guide I found (8 months old), so
I decided to give it a shot. I went through the guide with a little
deviation and I end up with mostly the same results as the author. Data
written to one node, shows up real quick on the other node. The
management interface all gives the same results as the author too.
However, the big problem (and reason for my post) is the virtual IP for
the load balancing doesn't work right.

When I try connect to the virtual IP:
$ mysql -h 10.0.1.10 -u ldirector -p
Enter password:
It will sit here for a really long time before giving:
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.10' (111)

Which, if I understand right, means the client isn't getting data back
from the MySQL server. However, if I try to connect directly to one of
the nodes, I get success!

So I started to think about it. What gives? What did I do wrong? Then I
went back step by step to see where I went different from the author.

Step 1) I obviously have different hardware, and the author uses Etch
while I used Lenny (fresh net-install today so it is current stable).

Step 2) Author uses MySQL 5.1.24 compiled from source. I used apt (I am
not afraid to compile but I didn't because apt is awesome :).
$ apt-cache show mysql-server | grep -i version
Version: 5.0.51a-24

Well, I guess I can see the versioning being a problem. Maybe I should
update and compile a newer version. ??

Step 3, 4, & 5) I get the exact same results as author with exception of
trivial things (like our PID's don't match. Whoop-de-do. Of course they
won't. Doesn't affect the outcome.)

Step 6.1) For whatever reason, I can't update the GPG key for
ultramonkey, but I just hit y to install anyway on apt. Also, he
installs libdbd-mysql-perl from a download he gives from CPAN. I had
errors with his commands. Turns out you have to install
libmysqlclient15-dev (not 14) and you have to install make too! Then his
link to the DBD-mysql-3.0002.tar.gz didn't work for me. However, the
link to the newest (I believe) DBD-mysql-3.0008.tar.gz, _did_ work. So I
installed that one. So again, versioning issues?

Step 6.2 - 7) Everything works exactly as the author suggests it should,
/except/ for the last line of:
`mysql -h 10.0.1.10 -u ldirector -p`

As stated before, this fails. I can ping 10.0.1.10. I can even ssh into
10.0.1.10. They both redirect me to the (current) primary load balancer.
If I turn off the primary, I go to the secondary. Also, if I run
`ipvsadm -L -n` I can see the connections show up, even though they time
out. So I don't think it is the virtual part. I think it is the
redirection to the MySQL session.

The only thing I have found in the log files that doesn't look right, is
every once in a while this line is put into the /var/log/ldirectord.log:
Exiting with exit_status 2: configu_error: Configuration Error
The thing is, from everything I can find, it is correct. I posted at the
end of the email.

I would appreciate any feedback people may have.

Thanks!
~Stack~

[1]
http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-m
ysql5.1

$cat /etc/ha.d/ldirector.cf
# Global Directives
checktimeout=10
checkinterval=2
autoreload=no
logfile="local0"
quiescent=yes
virtual=10.0.1.10:3306
service=mysql
real=10.0.1.33:3306 gate
real=10.0.1.34:3306 gate
checktype=negotiate
login="ldirector"
passwd="ldirectorpassword"
database="ldirectordb"
request="SELECT * FROM connectioncheck"
scheduler=wrr


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Jeff D 02-20-2009 03:43 AM

MySQL Clustering; Load balancing is not working.
 
On Thu, 19 Feb 2009, Stackpole, Chris wrote:

> I wanted to learn more about MySQL clustering so I looked around and
> found this guide [1]. It was the newest guide I found (8 months old), so
> I decided to give it a shot. I went through the guide with a little
> deviation and I end up with mostly the same results as the author. Data
> written to one node, shows up real quick on the other node. The
> management interface all gives the same results as the author too.
> However, the big problem (and reason for my post) is the virtual IP for
> the load balancing doesn't work right.
>
> When I try connect to the virtual IP:
> $ mysql -h 10.0.1.10 -u ldirector -p
> Enter password:
> It will sit here for a really long time before giving:
> ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.10' (111)
>

For most of this, you don't need to follow the instructions there to a
tee, where ever possible I just use what ever is in apt, most of it
version agnostic. The real important thing, it just the methods.

> Step 2) Author uses MySQL 5.1.24 compiled from source. I used apt (I am
> not afraid to compile but I didn't because apt is awesome :).
> $ apt-cache show mysql-server | grep -i version
> Version: 5.0.51a-24
>
> Well, I guess I can see the versioning being a problem. Maybe I should
> update and compile a newer version. ??
>

> Step 6.1) For whatever reason, I can't update the GPG key for
> ultramonkey, but I just hit y to install anyway on apt. Also, he
> installs libdbd-mysql-perl from a download he gives from CPAN. I had
> errors with his commands. Turns out you have to install
> libmysqlclient15-dev (not 14) and you have to install make too! Then his
> link to the DBD-mysql-3.0002.tar.gz didn't work for me. However, the
> link to the newest (I believe) DBD-mysql-3.0008.tar.gz, _did_ work. So I
> installed that one. So again, versioning issues?
>

>From what I remember, ultramonkey is kind of dead. All you really need to
install is ldirectord for the load balancing and heartbeat for failover,
both of which are in debians repositories. So, I would just uninstall
everything you installed from ultramonkey, remove them from your sources
list and just install heartbeat and ldirector debians rep.

for the DBD-mysql, you can just install libdbd-mysql-perl, that will
provide what you need.

> Step 6.2 - 7) Everything works exactly as the author suggests it should,
> /except/ for the last line of:
> `mysql -h 10.0.1.10 -u ldirector -p`
>
> As stated before, this fails. I can ping 10.0.1.10. I can even ssh into
> 10.0.1.10. They both redirect me to the (current) primary load balancer.
> If I turn off the primary, I go to the secondary. Also, if I run
> `ipvsadm -L -n` I can see the connections show up, even though they time
> out. So I don't think it is the virtual part. I think it is the
> redirection to the MySQL session.
>
> The only thing I have found in the log files that doesn't look right, is
> every once in a while this line is put into the /var/log/ldirectord.log:
> Exiting with exit_status 2: configu_error: Configuration Error
> The thing is, from everything I can find, it is correct. I posted at the
> end of the email.
>
> I would appreciate any feedback people may have.
>
> Thanks!
> ~Stack~
>
> [1]
> http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-m
> ysql5.1
>
> $cat /etc/ha.d/ldirector.cf
> # Global Directives
> checktimeout=10
> checkinterval=2
> autoreload=no
> logfile="local0"
> quiescent=yes
> virtual=10.0.1.10:3306
> service=mysql
> real=10.0.1.33:3306 gate
> real=10.0.1.34:3306 gate
> checktype=negotiate
> login="ldirector"
> passwd="ldirectorpassword"
> database="ldirectordb"
> request="SELECT * FROM connectioncheck"
> scheduler=wrr

your config looks ok. The one other thing I would check is the entries in
your /etc/sysctl.conf to turn off arp. That sounds like that may be the
main issue here



--
8 out of 10 Owners who Expressed a Preference said Their Cats Preferred Techno.


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

"Stackpole, Chris" 02-23-2009 03:36 PM

MySQL Clustering; Load balancing is not working.
 
Sorry for the long delay. Just how weekends go sometimes...

> From: Jeff D [mailto:fixedored@gmail.com]
> Sent: Thursday, February 19, 2009 10:43 PM
> Subject: Re: MySQL Clustering; Load balancing is not working.
>
[snip]
> your config looks ok. The one other thing I would check is the
entries in
> your /etc/sysctl.conf to turn off arp. That sounds like that may be
the
> main issue here

I used the entries that were in the tutorial[1] for arp. I just did a
straight cut and paste. Is there something else I should have done?
__________________________________________________ __
# Enable configuration of arp_ignore option
net.ipv4.conf.all.arp_ignore = 1

# When an arp request is received on eth0, only respond if that address
is
# configured on eth0. In particular, do not respond if the address is
# configured on lo
net.ipv4.conf.eth0.arp_ignore = 1

# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_ignore = 1


# Enable configuration of arp_announce option
net.ipv4.conf.all.arp_announce = 2

# When making an ARP request sent through eth0 Always use an address
that
# is configured on eth0 as the source address of the ARP request. If
this
# is not set, and packets are being sent out eth0 for an address that is
on
# lo, and an arp request is required, then the address on lo will be
used.
# As the source IP address of arp requests is entered into the ARP cache
on
# the destination, it has the effect of announcing this address. This
is
# not desirable in this case as adresses on lo on the real-servers
should
# be announced only by the linux-director.
net.ipv4.conf.eth0.arp_announce = 2

# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_announce = 2
__________________________________________________ __

Thanks for the help!
~Stack~

[1]
http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-m
ysql5.1-p3


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

"Stackpole, Chris" 02-23-2009 03:40 PM

MySQL Clustering; Load balancing is not working.
 
Trying my post again. The formatting came out all weird for some unknown
reason.

> From: Jeff D [mailto:fixedored@gmail.com]
> Sent: Thursday, February 19, 2009 10:43 PM
> Subject: Re: MySQL Clustering; Load balancing is not working.
>
[snip]
> your config looks ok. The one other thing I would check is the
entries in
> your /etc/sysctl.conf to turn off arp. That sounds like that may be
the
> main issue here
I used the entries that were in the tutorial[1] for arp. I just did a
straight cut and paste. Is there something else I should have done?
__________________________________________________ __
# Enable configuration of arp_ignore option
net.ipv4.conf.all.arp_ignore = 1

# When an arp request is received on eth0, only respond if that address
is
# configured on eth0. In particular, do not respond if the address is
# configured on lo
net.ipv4.conf.eth0.arp_ignore = 1

# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_ignore = 1

# Enable configuration of arp_announce option
net.ipv4.conf.all.arp_announce = 2

# When making an ARP request sent through eth0 Always use an address
that
# is configured on eth0 as the source address of the ARP request. If
this
# is not set, and packets are being sent out eth0 for an address that is
on
# lo, and an arp request is required, then the address on lo will be
used.
# As the source IP address of arp requests is entered into the ARP cache
on
# the destination, it has the effect of announcing this address. This
is
# not desirable in this case as adresses on lo on the real-servers
should
# be announced only by the linux-director.
net.ipv4.conf.eth0.arp_announce = 2

# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_announce = 2
__________________________________________________ __

Thanks for the help!
~Stack~

[1]
http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-m
ysql5.1-p3


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

"Stackpole, Chris" 02-23-2009 03:48 PM

MySQL Clustering; Load balancing is not working.
 
My apologies everyone. My mail client is ultra-failing at posting this
email for some reason. I don't think it liked the hash signs.

> From: Jeff D [mailto:fixedored@gmail.com]
> Sent: Thursday, February 19, 2009 10:43 PM
> Subject: Re: MySQL Clustering; Load balancing is not working.
[snip]
>
> your config looks ok. The one other thing I would check is the
entries in
> your /etc/sysctl.conf to turn off arp. That sounds like that may be
the
> main issue here

What I was trying to say in my other emails, which came out in jibberish
with lines crunched together, was that for my arp settings I just copied
and pasted from the article. Are those the correct settings I should be
using?

It is at the bottom of the page in section 6.5.
http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-m
ysql5.1-p3

Thanks!
~Stack~


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

"Stackpole, Chris" 02-23-2009 04:18 PM

MySQL Clustering; Load balancing is not working.
 
The problem I was having is now fixed. I started Googling (<- sp?? Meh,
oh well.) for other guides to see if I could spot some difference. It
took me a while, but I found a guide where others were having the same
problem I did. Eventually the answer was linked to from yet another
location. I won't bother linking to all the various places of
discussion, but here is the post with the final solution.
http://www.howtoforge.com/forums/showpost.php?p=60388&postcount=5

So now everything appears to be working. Hooray! Now to go beat it up to
see if I can break it again and learn something valuable* from it :-D.

Thanks for the help!
~Stack~

* If you have suggestions/ideas of something I could do to further
educate myself in these matters, please post!


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org


All times are GMT. The time now is 09:10 AM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.