MySQL Clustering / Load Balancing / Replication

[randy_delgado_03]

Good Day Vicidial Team,

First thanks vicidial team esp. matt / william who always shed some light.

Currently we are running smoothly on our multi server setup using vicibox redux 3.1.9 SVN but in the long run, i know we must have a backup / failover for atleast 99.9% uptime.

I've tried replication(Master/Slave) on MySQL DB. It went fine until we tried it on the live production. It seems like when you login even if it just couple of agents, the DB/Tables locks and the web cannot connect to it and it takes too long to respond.

Anyone ever tried some Clustering / Load Balancing / Replication that works like a charm in a 100 seater production?

Any suggestion / recommendation would be highly appreciated ...

[mflorell]

Master/slave replication show cause only minimal load on your master server. We have clients with 350+ agents that have no problem having more than one slave server running in fact.

[randy_delgado_03]

Thanks matt, unfortunately when i followed the documentation of MySQL's site, the result was not very satisfactory.

Would you mind lending a couple of configs / online reference on how to make Master/Slave replication work smoothly on vicidial?

Thanks ...

[mflorell]

First, please detail the hardware you are using for your MySQL master and slave servers.

[randy_delgado_03]

Here's my hardware info Matt and im using the same server specs for Master and Slave:

cat /proc/cpuinfo

processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i7-2600K CPU @ 3.40GHz
stepping : 7
cpu MHz : 1600.000
cache size : 8192 KB
physical id : 0
siblings : 8
core id : 0
cpu cores : 4
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt aes xsave avx lahf_lm ida arat tpr_shadow vnmi flexpriority ept vpid
bogomips : 6785.07
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

cat /proc/meminfo

MemTotal: 8176828 kB
MemFree: 99564 kB
Buffers: 97756 kB
Cached: 6568848 kB
SwapCached: 120 kB
Active: 4869384 kB
Inactive: 2898832 kB
Active(anon): 930272 kB
Inactive(anon): 190120 kB
Active(file): 3939112 kB
Inactive(file): 2708712 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 4200444 kB
SwapFree: 4196772 kB
Dirty: 4444 kB
Writeback: 0 kB
AnonPages: 1101552 kB
Mapped: 27300 kB
Shmem: 18760 kB
Slab: 246240 kB
SReclaimable: 219148 kB
SUnreclaim: 27092 kB
KernelStack: 2536 kB
PageTables: 12048 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 8288856 kB
Committed_AS: 1409568 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 145432 kB
VmallocChunk: 34359584756 kB
HardwareCorrupted: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 8000 kB
DirectMap2M: 8364032 kB

dmidecode

# dmidecode 2.10
SMBIOS 2.4 present.
37 structures occupying 1176 bytes.
Table at 0x000F0100.

Handle 0x0000, DMI type 0, 24 bytes
BIOS Information
Vendor: Award Software International, Inc.
Version: F2
Release Date: 02/22/2011
Address: 0xE0000
Runtime Size: 128 kB
ROM Size: 4096 kB
Characteristics:
PCI is supported
PNP is supported
BIOS is upgradeable
BIOS shadowing is allowed
Boot from CD is supported
Selectable boot is supported
EDD is supported
5.25"/360 kB floppy services are supported (int 13h)
5.25"/1.2 MB floppy services are supported (int 13h)
3.5"/720 kB floppy services are supported (int 13h)
3.5"/2.88 MB floppy services are supported (int 13h)
Print screen service is supported (int 5h)
8042 keyboard services are supported (int 9h)
Serial services are supported (int 14h)
Printer services are supported (int 17h)
CGA/mono video services are supported (int 10h)
ACPI is supported
USB legacy is supported
BIOS boot specification is supported
Targeted content distribution is supported

Handle 0x0001, DMI type 1, 27 bytes
System Information
Manufacturer: Gigabyte Technology Co., Ltd.
Product Name: P67A-UD3P-B3
Version:
Serial Number:
UUID: 00000000-0000-0000-0000-1C6F65C9C6C4
Wake-up Type: Power Switch
SKU Number:
Family:

cat /etc/fstab | grep disk

/dev/disk/by-id/ata-WDC_WD1002FAEX-00Z3A0_WD-WCATR4922523-part1 swap swap defaults 0 0
/dev/disk/by-id/ata-WDC_WD1002FAEX-00Z3A0_WD-WCATR4922523-part2 / ext3 defaults 1 1

df -ah

Filesystem Size Used Avail Use% Mounted on
/dev/sda2 913G 14G 854G 2% /
proc 0 0 0 - /proc
sysfs 0 0 0 - /sys
debugfs 0 0 0 - /sys/kernel/debug
devtmpfs 3.9G 220K 3.9G 1% /dev
tmpfs 3.9G 4.0K 3.9G 1% /dev/shm
devpts 0 0 0 - /dev/pts
fusectl 0 0 0 - /sys/fs/fuse/connections
/proc 0 0 0 - /var/lib/ntp/proc

[mflorell]

So you are using a single quad-core CPU and a single ATA hard-drive and you expect this machine to support over 100 users?

I would say that is what is causing your problems.

For 100 agents we would recommend a minimum of the CPU/RAM that you have(quad-core CPU/8GB RAM) in addition to a caching hardware RAID controller with a RAID 10 and 7200 SATA enterprise level drives(Seagate ES or WD Raptor) or SAS or SCSI drives.

[randy_delgado_03]

So you are using a single quad-core CPU and a single ATA hard-drive and you expect this machine to support over 100 users?

I would say that is what is causing your problems.

For 100 agents we would recommend a minimum of the CPU/RAM that you have(quad-core CPU/8GB RAM) in addition to a caching hardware RAID controller with a RAID 10 and 7200 SATA enterprise level drives(Seagate ES or WD Raptor) or SAS or SCSI drives.

Sorry matt let me rephrase that, im using a quad core i7 with hyper threading so that makes it with 8 cores and using a raid 0 western digital black caviar and a 8 gig of memory with dual channel capability.

I just posted the preliminary info since the results are too long (^_^) ...

Thanks Matt ...

[mflorell]

Hyper-threading does not magically create new cores, and you should probably disable it. What it does in simple terms is create new virtual cores that operate at a limited capacity, so instead of 4 cores at 100%, you have 8 cores operating at about 60%.

[randy_delgado_03]

Oh thanks Matt noted, however if i operate it for 20 agents, the replication will work right?

Coz when i tried the replication i only let 5 agents login and it hangs even if they're only logging in, but if i turned off the replication it can handle up to 50+ agents with no problems ...

[mflorell]

I have no idea what the cause of that issue could be. I have never seen that happen before, and we have set up replication at some small rooms without seeing that issue.

[randy_delgado_03]

If you dont mind matt, do you have any online references / sample config files / how to's so i can cross reference it with my config and check if i miss configured / missed something?

Thanks Matt ...

[mflorell]

The O'Reilly book "High Performance MySQL" has actually been very useful. Other than that, there are a lot of websites that have replication information on them. I would suggest reading as much as possible.

[randy_delgado_03]

Thanks Matt, will look into this reference ...

Will let you know when this works for us, thanks again ...

[randy_delgado_03]

By the way Matt, may i ask what type of replication did you implement that works best? Statement-Based Replication or Row-Based Replication?

[mflorell]

We just do straight bin-log master/slave replication, and it always works wonderfully.

[randy_delgado_03]

Matt FYI: I have successfully established a Master-Slave replication on vicidial database using the o'reily high performance documentation you mentioned, i did some diggin in and found out about the MYISAM engine that has been mentioned many times on the forum that is much more compatible than INNODB, as always thank you, we do have real time backup and running smoothly.

Thanks Vicidial Team (^_^) ...

[mflorell]

Thanks for posting a followup!

I'm glad it is working for you now.

[randy_delgado_03]

By the way Matt, have you ever thought of using the Multi Master Replication and using Heartbeat for failover? Or you already established this?

[mflorell]

We have looked at multi-master, and it is very difficult to set up properly as well as it is very easy to get the masters out of sync, especially with a high-query-rate application like ViciDial.

We have helped clients set up Master/slave with heartbeat and failover to the slave(which then becomes a master). It was not easy to set up but it does work.

[randy_delgado_03]

Got it Matt, currently i'm only using 2 servers and installed HAProxy with Heartbeat to connect to my 4 web servers.

I found a tool called CSync2, maybe ill look into that and update the forum if i successfully installed 2 database loadbalancing / fail over each other. Thanks Matt ...

[randy_delgado_03]

Hi Matt / Vicidial Team,

Have you ever looked in to the possibility of using DRBD on MySQL Server Files of Vicidial?

Or anyone had already did this?

[mflorell]

We have a client that set up their MySQL with DRBD before, and it seemed to work just fine.

[randy_delgado_03]

Thanks for the confirmation Matt. I think this is really important in terms of high availability and scalability. I will dig into it and follow up a how to.

Thanks again ...

[cristian]

I look forward to your post back randy!

[randy_delgado_03]

Matt / Cristian

I have successfully integrated DRBD(Dual Master Mode) + OCFS2 + Heartbeat on the distro thats in my signature for MySQL.

It is replicating data flawlessly with no problems. The only challenge i ran and get stucked right now is: say i have MySQL1 and MySQL2, when i make some changes on MySQL1, before you can see the data on MySQL2 in real-time you need to restart the service of MySQL on MySQL2 and vice versa.

If we resolve this challenge, we can have a real-time data with full real-time redunduncy.

Anyone that have ideas that i can dig into would be highly appreciated and will post back a step by step ...

[williamconley]

when you say you have to "restart the service" do you mean /etc/init.d/mysql restart?

if so, check the routines that occur during startup (it may force sync). If you can find all the startup routines, you may be able to execute each of them manually to find out which one(s) cause the data to go "Live" and just execute it manually. However: this may need to occur ... a bit often.

[randy_delgado_03]

when you say you have to "restart the service" do you mean /etc/init.d/mysql restart?

Yes william its /etc/init.d/mysql restart. Thanks for the idea, let me dig into this ...

[floydans]

Anyone ever tried MySQL partitioning with vicidial ???
We are running a large cluster, more than 200 agents and we will activate more in the next future, so what we are experiencing is a concurrency problem on heavely hammered tables (like vicidial_live_agents), even if we have lot of cpu power and hw raid 10 with 4x SAS 146GB 15krpm + 1GB FBWC, 64GB RAM...

It looks MySQL Partitioning can split data into several logical partitions, so it will take less for a query to run and minimum table lock if mysql query optimizer will know in advance which partition to look into.

What's your opinion ???

[williamconley]

sounds like a good idea to me. as long as it'll work with myisam.

you could attempt it with a single table first.

you could also look at changing the live agent table(s) to memory tables for a pickup in speed. after all, it's not like that table needs to survive reboot or anything.

[floydans]

We already tried setting the vicidial_live_agents table to Engine=Memory, but it did not boost the performance, so we reverted to MyIsam as with it we noticed slight better results (maybe due to the presence of the 1GB of Flash Back Write Cache on Raid controller ???)

We make use of Vicidial Agent Api for external integration so the first problem we notice when things are getting bad is non-responding Api functions or just responding after several seconds (even up to 10 seconds).

Data about functions to be fired (hangup, park, grab, makecall,etc...) are written by the agent api in the vicidial_live_agent table and will be parsed by some other vicidial script (...don't remember which one...)

Since our problems seem to derive from the heavy concurrent use of this table, we will direct our efforts on partitioning this one first.

We have 8 Dialers, so I think it will be convenient to partition by the server_ip column, so queries from every single dialer will affect only one little piece of the cake.

Don't know whether this will solve our problem or will just make things worse, I have seen many discordant articles on mysql partitioning...

Let's give it a try, I'll post back results

[williamconley]

raid 10? 15K SAS drives?

[floydans]

trying to drive me somewhere...?

[williamconley]

inevitably with larger systems the bottleneck/hiccup is mysql. if mysql is not responsive enough, it can hammer all the other servers.

raid is nice, don't get me wrong, but speed of response is where it's at. any complication that can cause even a half-second delay can kill the sql server. it must have no sudden issues regardless of how temporary. a raid controller not perfectly configured or incapable of massive throughput is counterproductive. try removing the raid entirely (just use a stupid-fast 15K SAS drive and see if that helps). use a utility to see if there are I/O delays. eliminate the DRIVE as an issue on the sql server, as that is generally where the problem lies.

if it isn't the drive response on the sql server, it usually turns out to be networking ...

[mflorell]

If you have changed vicidial_live_agents to a MEMORY table(which would mean you must have altered the TEXT fields to CHAR fields) then the drive has nothing to do with your issues. You might actually need to LOWER your query cache size since we have found that having that too large can cause speed issues on larger systems. In some cases we have lowered it to 10% of it's original size and have seen tremendous speed improvements with MEMORY tables.

It could also be many other issues, sometimes it takes a while to whittle down what exactly is the best way to optimize your database. We have worked on systems much larger than yours and sometimes the solution can be very different depending on what we see going on with the servers.

[williamconley]

the first trick is identifying the bottleneck. what is slowing down/not responsive. then you can work on Why. Or pay someone who's done it a few times already. (ie: The Vicidial Group!)

[will_brook]

Hi,

We're in the process of installing a clustered setup 2 x telephony server (hopefully expand to 6), 2 x web & 2 x database. Ideally I'd like to install the database and web servers on a VSphere platform but I've seen the general concensus to be that this is a bad idea. However with VShere version 5 some performance issues may be addressed as we're now able to assign multi-core CPU's to VM's (previously only possible if you had enterprise plus licensing).

I've already setup 2 DB server as VM's with 2 x Quad-Core Intel Xeon, 2500 MHz, and 32GB of RAM on each. No contention from other VM's, as these are the only virtual server on our two hosts. These are setup with MYSQL replication between them. I've also started building a Physical box with the same hardware as a comparison. All the same servers use a Fibre connected SAN for disk access (EVA8100).

My plan is to test the performance between the different sets of servers to see how they perform. Has anyone done anything similar or could recommend a good performance test to use. I was going to use mysqlslap to replicate 200 connections.


Many Thanks

Will


Vicibox_redux.i686-3.1.10 from .iso Asterisk 1.4.39.2-vici | Single Server | Sangoma A104 hardware, 4 x E1 pstn lines | No Extra Software After Installation.

[bghayad]

Hi Will_Work;

We're in the process of installing a clustered setup 2 x telephony server (hopefully expand to 6), 2 x web & 2 x database.

* Hope for u all the success.
I am really caring about some points if u can advise and guide:

1) When u say 2 x telephony server: They are clustered? How they can be clustered? In other words, if I do configuration at first server, then I will see this configuration at other server without need for manual update?
By the way, do they take the telephone configurations from the database or from conf files?
2) About the 2 x telephony servers: the load (the phones) will be distributed automatically ot manually? In other words, there will be a virual IP that will direct for server 1 and server 2, or this will be done manual?
3) If asterisk telephony server 1 is down, then phones will automatically register to telephoney server 2 without need need to add this IP Phone configuration at asterisk server 2?

4) About the web servers (clustered): samething about load, how agents will login for web server 1 or web server 2 or a virual IP that will direct to proper web server? And if web server 1 down, then agents will login using web server 2?

Your kindly help is highly appreciated.
Regards
Bilal

[will_brook]

Hi bghayad,

Vicidial has a guide on how to build a clustered guid which will probably help you understand the different components involved - http://download.vicidial.com/iso/vicibo ... luster.pdf.

In regards to your points:

1) When u say 2 x telephony server: They are clustered? How they can be clustered? In other words, if I do configuration at first server, then I will see this configuration at other server without need for manual update?
By the way, do they take the telephone configurations from the database or from conf files?

We're in the process of building this setup but I'll let you know how it works once complete. I believe the settings are copied acros, i.e. Generate Cross-Server Phone Extensions & PLLB Grouping Limit


2) About the 2 x telephony servers: the load (the phones) will be distributed automatically ot manually? In other words, there will be a virual IP that will direct for server 1 and server 2, or this will be done manual?

I believe you can restrict the numbers per telephony server but not sure what you're really asking here?

3) If asterisk telephony server 1 is down, then phones will automatically register to telephoney server 2 without need need to add this IP Phone configuration at asterisk server 2?

You can also configure some IP phones to have backup sip settings for resilience or even use a central resilient sip gateway server like opensips to manage all your phone connections into Vicidial.

4) About the web servers (clustered): samething about load, how agents will login for web server 1 or web server 2 or a virual IP that will direct to proper web server? And if web server 1 down, then agents will login using web server 2?

We're using a load balancer to manage the connections between two nodes, then replicate scripts, etc between the servers.

[bghayad]

I readed the manual, but really it does not answer my questions, and thanks for guiding me.

1) When u say 2 x telephony server: They are clustered? How they can be clustered? In other words, if I do configuration at first server, then I will see this configuration at other server without need for manual update?
By the way, do they take the telephone configurations from the database or from conf files?

We're in the process of building this setup but I'll let you know how it works once complete. I believe the settings are copied acros, i.e. Generate Cross-Server Phone Extensions & PLLB Grouping Limit

* Good luck and hope all the success, the efforts are appreciated and fully thanks in advance.
But at least, currently in the clustering situation: The configuration will be readed from the database of configuration files? And do I need to configure the IP Phone on first telephony server and then do the same configuration (manual) on the second telephony server or configuration will be duplicated automatically on both servers?

2) About the 2 x telephony servers: the load (the phones) will be distributed automatically ot manually? In other words, there will be a virual IP that will direct for server 1 and server 2, or this will be done manual?

I believe you can restrict the numbers per telephony server but not sure what you're really asking here?

* OK, I will tell you what I mean:
For example in Cisco, the Call Managers clustering is compound from Publisher and Subscribers servers. So, the IP Phone will communicate with the Publisher and the Publisher will direct the Phone to which Subscriber server to connect (also it is possible to register at the publisher if the subscribers are down). This to distribute the load between the telephony servers instead of having too many IP Phones register at one telephony server and little quantity of IP Phones register at other telephony server.
So, how Asterisk telephony servers in this clustering solution will manage the load balancing? Is there an automatic method or it has to be manual (by determining the IP Phones that will register at telephony server 1 and the IP Phones that will register at telephony server 2)?


-- Actually, as I can understand until now, that the clustering is related to the database only, I do not see any clustering at the web server or at the telephony servers, I am correct? Otherwise, I hope to know which processes (services) are clustered at those servers?

Again, I am fully thanks for your help and kindly reply.

[will_brook]

The word clustered is probably quite misleading with the vicidial setup. Multi-server is probably the best description as it allows you to grow from a one server solution (limited to around 30-50 agents) to a system that can handle 150-200 agents.

Vicidial by itself doesn't give you the high availability that I think you've after. We're just starting the install ourselves of a multi server dialler so I'm far from an expert, however the kind of technology we're looking at to give us that high availability are:

Data base server - From what I've read this can't be clustered in the traditional sense, but we're looking at using DRBD with HA.

telephony nodes - At first I'll just setup a maximum of 40 phones per node, with a backup node configured on each phone . Eventually I'd like to look at using Opensips or OpenSER to manage all my phone SIP connections across all the vicidial telephony nodes, but I haven't started looking at this properly yet.

Web servers - you should look to find a web load balancer and then replicate the scripts between the two servers. This forum might help you - http://www.eflo.net/VICIDIALforum/viewt ... 2a0d37ea12