Enabling MySQL Slave on a production cluster

[rustynail]

Hi All,

Vicibox: 7
asterisk: 11.25.1-vici
SVN: 2780

We are running a cluster with 1 DB, 1 WEB and 4 TEL servers. Everything is running smoothly but sometimes we experience high spikes of load and all agents get disconnected at the same time. We checked and suspect that DB server is taking the beating, even having 16GB ram it utilizes 20-30% swap space.
As there is no mysql installed on any other server but the DB server only so we were thinking to install mysql on WEB server or any one TEL server and make it slave DB. Then we can run reports off that slave DB to minimize load on main DB server.
Can you please guide what steps to be taken in adding slave DB role on existing production server in a cluster without breaking current setup. Is installing MySQL and configuring it manually for replication ok? and will it work in current scenario? We don't want to add a new server in cluster for this task only.

Cheers!

[macaruchi]

You need another server for reports. MySQL is so slow and you will have problems with the speed. You can add another server, VPS, just for Mysql Slave and to take all reports from this. I had the same problem and solved in this way.

BTW: You have the TEL servers in the same company , on the cloud? I am moving to this scenario 2 TEL servers, 1 web and DB. but I want different company using internet. How do you have your configuration ?

[rustynail]

Thanks for the reply, That's good idea but as some of our TEL servers can handle the load of slave and for another cluster we are already using this scenario and that is working fine so I wanted to do same on this cluster. Can you please guide about the steps I should follow to manually add slave config to a TEL server and connect it to master DB. I am too much careful as this is a production server and don't want anything to go wrong.
I am using physical servers on the same Vlan from a hosting provider and this scenario seems working fine. You can also try it.

Cheers!

[rustynail]

Hi,
Its been some time since my post is unanswered. Can anyone shed some light on the above requirement please....

Here I rephrase it ... we have 5 TEL, 1 DB, 1 WEB servers in a cluster... As load is growing and DB server is single quad core processor so it becomes stressed. This was a poor design but as servers were added and removed randomly on requirement of client so we couldn't do much about it.
Now I wan't to enable mysql on one of the TEL server and make it as slave DB.. below are the steps I have thought

1. Enable/start Mysql service on TEL server.
2. Edit its my.cnf file to set slave server id accordingly
3. Configure slave settings by using command CHANGE MASTER TO MASTER_HOST,,,,,,,,,,,,,,,
4. Start the slave server by issuing command START SLAVE on slave server.

If anything is left then please point it out or suggest correct steps. As I have to perform this on production cluster so will there be any harm expected if anything with replication goes wrong or dialer will keep performing as usual ?

Thanks

[rustynail]

Hi,
Its been some time since my post is unanswered. Can anyone shed some light on the above requirement please....

Here I rephrase it ... we have 5 TEL, 1 DB, 1 WEB servers in a cluster... As load is growing and DB server is single quad core processor so it becomes stressed. This was a poor design but as servers were added and removed randomly on requirement of client so we couldn't do much about it.
Now I wan't to enable mysql on one of the TEL server and make it as slave DB.. below are the steps I have thought

1. Enable/start Mysql service on TEL server.
2. Edit its my.cnf file to set slave server id accordingly
3. Configure slave settings by using command CHANGE MASTER TO MASTER_HOST,,,,,,,,,,,,,,,
4. Start the slave server by issuing command START SLAVE on slave server.

If anything is left then please point it out or suggest correct steps. As I have to perform this on production cluster so will there be any harm expected if anything with replication goes wrong or dialer will keep performing as usual ?

Thanks

Can anyone help me here. I need to prepare it before monday (tomorrow) so as to lessen the burden on my DB server... any help will be appreciated.

[mflorell]

There are A LOT of tutorials on setting up a MySQL slave server out there if you just google them. Depending on what machine you will use for your MySQL slave server, the instructions may be different. We usually recommend using the same kind of machine you are using for your master, but you can use a much lower powered machine, and even a different operating system(like Windows) if you have to for a MySQL slave.

Another step to take to speed things up on your master server is to archive logs if you haven't done so already. The "ADMIN_archive_log_tables.pl" script can help you do this.

[rrb555]

There is a Database slave expert setup in Vicibox installer. You may want to try that also. I too also trying this and was able to slave my test server on Vicibox 8 installs. I will be testing next to slave my VIcibox 5 install using this method.

[rustynail]

There are A LOT of tutorials on setting up a MySQL slave server out there if you just google them. Depending on what machine you will use for your MySQL slave server, the instructions may be different. We usually recommend using the same kind of machine you are using for your master, but you can use a much lower powered machine, and even a different operating system(like Windows) if you have to for a MySQL slave.

Another step to take to speed things up on your master server is to archive logs if you haven't done so already. The "ADMIN_archive_log_tables.pl" script can help you do this.

Thanks for your valuable feedback matt, Actually what I wanted to ask was not setting up entirely new slave but to utilize an existing dialer which has mysql dormant and what exact settings to change update in astguiconf and vicidial so as not to affect/break anything. Anyways our client requested to attach a new server and I was able to make it slave with expert setup.
Yes I regularly use admin archive script to archive tables but still data is so huge that it periodically (many times on daily basis) spikes the load on db resulting in agents going on pause and having double calls etc... there are currently 250 agents login and client wants to expand it to 400 agents.

I am using vicibox 7.0.4 on 2x quad core cpu with 16gb ram and ssd with raid 1 configuration on db and other servers. there are 2 web and 6 dialer servers attached with same DB. Any clue how to improve things?
We have already optimised mysql with below settings.

mysql -uroot -e "set global query_cache_type='OFF'"
mysql -uroot -e "set global query_cache_size=0"
mysql -uroot -e "set global key_buffer_size = 1073741824"
mysql -uroot -e "set global max_connections = 2000"
mysql -uroot -e "set global max_heap_table_size = 67108864"
mysql -uroot -e "set global table_open_cache = 8192"

waiting for your valuable feedback.
Thanks again.

[rrb555]

Seems like I am currently using the same technique. I created a new server using vicibox-express. Using that server configured database slave. And that's it when there is a changes on the main server, changes will be reflected to my slave server

cheers!

[rustynail]

Exactly! but the problem is that with increasing number of servers and agents, the DB starts getting load. Although the load avg remains max at 2.5 with dual quad core cpu and no swap is used with 16gb ram so it should run fine. IO wait is also not present and I have optimized the mysql for high load but still its taking load and mtop shows queries going to red color and that is when agents face auto pause and other issues.

Any solution?

[frequency]

Exactly! but the problem is that with increasing number of servers and agents, the DB starts getting load. Although the load avg remains max at 2.5 with dual quad core cpu and no swap is used with 16gb ram so it should run fine. IO wait is also not present and I have optimized the mysql for high load but still its taking load and mtop shows queries going to red color and that is when agents face auto pause and other issues.

Any solution?

Tables locks..You will need a slave server to fetch reports or user stats. Maybe you are also opening campagin for changing dial level, that can also lead to table lock when the list details on bottom of the campaign are loaded. it will also lead to table locks on most occassions. Just wanted to confirm if you are using SSD with raid card..

[rustynail]

Thanks for reply. we have already moved reports to slave server and archiving tables weekly. This has greatly reduced load and this now happens very less but still present. Dialer now has almost 14 TEL Servers with 2 web servers and 1 master and 1 slave DB.