Large database LOAD

[ruben23]

VERSION: 2.14-627a
BUILD: 170828-2105
© 2017 ViciDial Group
Asterisk 1.8
Ubuntu Server 12.04.5 LTS 64bit
1Large Sole Database
1 Web Server
4 Vicidial/asterisk

Guys, Any idea how to resolved my issue during production time suddenly my database Core spike and started the vicidial to lagged and the page are not all loading or pretty slow, agents cant dial moving forward and i have this on monitor in the process of this issue in realtime





AND This is the I/O output

Code: Select all

root@soledatabase:~# iostat -c -d -h -z -t -x 1 5
Linux 3.13.0-117-generic (soledatabase)         09/28/2017      _x86_64_        (24 CPU)

09/28/2017 02:52:16 PM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.27    0.01    1.04    0.02    0.00   95.67

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda
                  0.12     6.56    5.05   65.36   111.00   530.25    18.22     0.87   12.29    0.46   13.21   0.13   0.91

09/28/2017 02:52:17 PM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.03    0.00    1.17    0.00    0.00   92.80

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util

09/28/2017 02:52:18 PM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.57    0.00    1.30    0.00    0.00   94.13

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda
                  0.00    45.54    0.00    1.98     0.00   190.10   192.00     0.00    2.00    0.00    2.00   2.00   0.40

09/28/2017 02:52:19 PM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.02    0.00    1.09    0.00    0.00   94.89

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util

09/28/2017 02:52:20 PM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8.01    0.00    1.64    0.00    0.00   90.35

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util


Any help guys or suggestion. Thanks

[proper]

Run full database repair and then optimize it.
It probably has errors.

Cleanup vicidial_carrier_log table, make a copy of it if you want, but you need to reduce it in size significantly. This table if unmanaged usually kills the server they way you describe.

Follow guidelines I posted in other thread you had.

[macaruchi]

Hi!
I have good news and bad news for you !!
First goods, this problem come when you use intensely the reports from vicidial and your DB is so big. I had this problem before and it is so stressing. I solve the situation doing a new system reports and moving all data tables necesary for this to Postgres dtabase, I tried with replica, duplicate tables but nothings worked.
I did a new system report and with CRON we move all data. Mysql is so bad with relations and to use a SELECT so heavy down all system.
I solved this issue forver in my center

[ruben23]

@macaruchi,

your very correct i had this problem, when on production time and someone generate Report, ti will totally disable the vicidial system, coz of select and query status that spieks all the CPU of the large databased system, any idea how did you did the migration process of your data to Postgres dtabase.?

[mflorell]

If reports are your issue, just install a slave DB server and run the reports off of that, it's not hard to set up and you can choose what reports you want to run on the slave server right in the VICIdial System Settings.

As for PostgreSQL, it's a great database system, but it's not as fast as MySQL, and you can't just easily switch to it for VICIdial use.

[ruben23]

@Matt,

Thanks you mean this Mysql Replication implementation for Master- Slave.? i will do the implementation and fill in the info on the vicidial system..

[mflorell]

Yes, we have dozens of clients using master -> slave database replication. It's great for a backup solution on 24/7 operating call centers, as well as warm spare and running reports.

[macaruchi]

I used a master-slave replication but I didnt get anything. I have a whole system about statitics, reports and administartion system using out tables and replicate tables from mysql. In my concern the speed between Mysql and PG is so big and different to postgres favor. I have a lot complex reports using vicidial data and own data and the speed using Mysql was so poor that was the reason to migrate whole system to Postgres . But it was my solution and was so good for my customers

[ruben23]

@Matt,

Do i need to have same Specification with the Master database when implementing Slave server or i can go to a lower spec Server somehow..?

[proper]

@Matt,

Do i need to have same Specification with the Master database when implementing Slave server or i can go to a lower spec Server somehow..?

He suggested that you create a master - slave configuration and use slave server for reporting, to reduce load on the master. Running realtime report creates significant load especially if more than one instance is open and caching for carrier stats is not enabled.

This means you create separate server with MariaDB and set it up to act as slave to your main database server, once connected, it will be exact copy at all times and you will then connect your admin webserver to use it for reporting.

Find a tutorial for how to setup replication on MariaDB and follow the process.

[mflorell]

You can go lower on slave server DB specs, but you don't want to go so low that it won't be able to keep up with the replication process during production hours. We usually recommend using the same specs so that the slave can also function as a warm spare if your main DB server goes out for any reason.

As for the real-time screen, we have a client with up to 750 concurrent agents running 100+ instances of it on a daily basis, and with caching stats and nightly caching of the stats the main DB server is able to handle it just fine. We usually recommend running the real-time report off of the Master DB server, and the other reports off of the slave. This is because the real-time report is low-impact, and is greatly affected by the slave running behind, unlike most of the other reports.

[ruben23]

@Matt,

We usually recommend running the real-time report off of the Master DB server, and the other reports off of the slave. This is because the real-time report is low-impact, and is greatly affected by the slave running behind, unlike most of the other reports.

You mean the realtime screen monitoring will be pointed to slave database and other reports should be on Master.? but the problem is when i report using the master it will cause spike on CPU cores and stop the process, cant we not run this all in slave.? would the report be consistent somehow since its running behind the Master Database.


Also Matt,

Do we have any script on how to clear logs files on the carrier_log tables.? somehow, or it should be execute as Trunkcate the table itself

[mflorell]

No, realtime report on master, all others on slave.

The archive script is how you roll your log files, we use this for most of our clients, regularly scheduled in the crontab:

# /usr/share/astguiclient/ADMIN_archive_log_tables.pl --help
allowed run time options:
[--daily] = only archives call_log, vicidial_log_extended, vicidial_dial_log and vicidial_drop_log tables, only last 24 hours kept
[--carrier-daily] = will also archive the vicidial_carrier_log table when --daily is run
[--vlog-daily] = will also archive the vicidial_log table when --daily is run
[--days=XX] = number of days to archive past, default is 732(2 years)
[--months=XX] = number of months to archive past, default is 24(2 years) If 'days' used then 'months' ignored
[--queue-log] = archive QM queue_log records
[--only-trim-archive-level-one] = will not perform normal archive process, instead this will only delete records
that are older than XX months from least important log archive tables:
call_log_archive, vicidial_log_extended_archive, vicidial_dial_log_archive, vicidial_drop_log
[--only-trim-archive-level-two] = same as --only-trim-archive-level-one, except includes tables:
vicidial_carrier_log_archive, vicidial_api_log_archive, vicidial_rt_monitor_log_archive
[--only-trim-archive-level-three] = same as --only-trim-archive-level-two, except includes tables:
vicidial_log_archive, vicidial_agent_log_archive, vicidial_closer_log_archive, vicidial_xfer_log_archive
[--recording-log-days=XX] = OPTIONAL, number of days to archive recording_log table only past
[--cpd-log-purge-days=XX] = OPTIONAL, number of days to purge vicidial_cpd_log table only past
[--quiet] = quiet
[--calc-test] = date calculation test only
[--test] = test

[hiteshg]

Matt,

Can you help me the steps for setting up Master - Slave instance

[mflorell]

There are dozens of tutorials on how to set up MySQL master-slave replication, just google it.