MYSQL_DB load Spike

[ruben23]

Multi Server- 1DB,Web,telephony
ASterisk 1.4.27-vici
Dahdi-current
2.4 svntrunk
Ubuntu server 10.04 LTS
Scracth Install
VERSION: 2.4-357a
BUILD: 120125-2107
© 2012 ViciDial Group

Database Spec:
Inte QuadCore Xeon 2.6Ghz
16GB ram
HDD SAS

Asterisk Spec:
Intel Quad Core Xeon 2.4Ghz
4GB ram
SAS HDD

Web Server Spec
Intel Xeon Dual Core
4Gb ram
SAS hdd

hi guys i have issue with my sole Databased Server i have spike load while monitoring with htop on particular on some designated time which i come to isolate its realated to the cronjobs being executed on there givin schedule time:
--->/usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl --debug --postal-code-gmt
---> /usr/share/astguiclient/AST_DB_optimize.pl
---> /usr/share/astguiclient/AST_reset_mysql_vars.pl

And i dont know why this affecting so much on teh database whihc the load goes to 100 percent straight for few minutes and by the tie this occur agent cant login at all on the agent interface all seems freezing cant click even any button. the tie duration goes like 5-10 minutes then after all will be workign well back again.im confused why this happening i trid running single server before but this cronjob are not affecting at all on production, but now it is.On teh CPU core somehow like i got four cores

1----->5 percent
2--------------------------------------------->100 percent
3--1 percent
4--------------------------------------------->100 percent

i used this to optimized my databased ----> http://download.vicidial.com/conf/my-vicidb.cnf

Issue also almost all the hours of operation are fill in i got break 10-15 minutes only(For the meantime i put the cronjobs schedule on that time frame)
Any idea guys.Thanks in advance.

[williamconley]

please post a link to the installation instructions you used (this links those using the same instructions together so help can be "grouped" when possible).

modify the crontab for those processes to move them outside your working hours.

[ruben23]

@williamconley

im using this installation process ---->http://www.vicidial.org/VICIDIALforum/posting.php?mode=reply&f=2&t=25073 <----split into different server with its application like DB, Telephony and Web Server.

yes im trying to move it but the vacant time frame is too short...is this not really possible to run this cronjob on prodcution times, this really affect database performance and eventually cause some issues.?

One more thing, can this cronjob runs only @ weekend like restday so it would be a lot more time. or not possible should be run on daily basis.

[williamconley]

It is entirely possible to limit this process to weekend when you have more time. But you are saying that you are using your server 24/7 and cannot find a moment to run it daily or nightly? You actually use your dialer all the time during the week?

[ruben23]

I have free time like 1 hour in the morning and 1 hour in the evening split into two 30 minutes break. thats all i have and we dont have operation on sunday.Ok so i can run this only in weekends and the dialer will still be ok and fine right..? no issue will arise when this 3 scripts runs only in weekend..?

[williamconley]

--->/usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl --debug --postal-code-gmt
Run this one weekly on Sunday when you're not working.

---> /usr/share/astguiclient/AST_reset_mysql_vars.pl
Run this one during your 30 minute break.

---> /usr/share/astguiclient/AST_DB_optimize.pl
Try running this one on your other 30 minute break, it should not take more than a few minutes to run.

[ruben23]

@williamconley

Im still having the spike load around 1 Pm my time and it spike 1000 percent on the database, even i already change the time of the cronjobs; here is my cronjobs:

Code: Select all

# m h  dom mon dow   command
### recording mixing/compressing/ftping scripts
#0,3,6,9,12,15,18,21,24,27,30,33,36,39,42,45,48,51,54,57 * * * * /usr/share/astguiclient/AST_CRON_audio_1_move_mix.pl
0,3,6,9,12,15,18,21,24,27,30,33,36,39,42,45,48,51,54,57 * * * * /usr/share/astguiclient/AST_CRON_audio_1_move_mix.pl --MIX
0,3,6,9,12,15,18,21,24,27,30,33,36,39,42,45,48,51,54,57 * * * * /usr/share/astguiclient/AST_CRON_audio_1_move_VDonly.pl
1,4,7,10,13,16,19,22,25,28,31,34,37,40,43,46,49,52,55,58 * * * * /usr/share/astguiclient/AST_CRON_audio_2_compress.pl --MP3
#2,5,8,11,14,17,20,23,26,29,32,35,38,41,44,47,50,53,56,59 * * * * /usr/share/astguiclient/AST_CRON_audio_3_ftp.pl --MP3

### remove old vicidial logs and asterisk logs more than 2 days old
28 0 * * * /usr/bin/find /var/log/astguiclient -maxdepth 1 -type f -mtime +2 -print | xargs rm -f
29 0 * * * /usr/bin/find /var/log/asterisk -maxdepth 3 -type f -mtime +2 -print | xargs rm -f
30 0 * * * /usr/bin/find / -maxdepth 1 -name "screenlog.0*" -mtime +4 -print | xargs rm -f
### fix the vicidial_agent_log once every hour and the full day run at night
33 * * * * /usr/share/astguiclient/AST_cleanup_agent_log.pl
50 0 * * * /usr/share/astguiclient/AST_cleanup_agent_log.pl --last-24hours
## uncomment below if using QueueMetrics
#*/5 * * * * /usr/share/astguiclient/AST_cleanup_agent_log.pl --only-qm-live-call-check

### keepalive script for astguiclient processes
* * * * * /usr/share/astguiclient/ADMIN_keepalive_ALL.pl

### updater for VICIDIAL hopper
* * * * * /usr/share/astguiclient/AST_VDhopper.pl -q

### adjust the GMT offset for the leads in the vicidial_list table
1 22,2 * * 0 /usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl --debug --postal-code-gmt

### optimize the database tables within the asterisk database
30 20 * * * /usr/share/astguiclient/AST_DB_optimize.pl

### VICIDIAL agent time log weekly summary report generation
2 0 * * 0 /usr/share/astguiclient/AST_agent_week.pl

### roll logs monthly on high-volume dialing systems
#30 1 1 * * /usr/share/astguiclient/ADMIN_archive_log_tables.pl

## uncomment below if using Vtiger
#1 1 * * * /usr/share/astguiclient/Vtiger_optimize_all_tables.pl --quiet

### kill Hangup script for Asterisk updaters
* * * * * /usr/share/astguiclient/AST_manager_kill_hung_congested.pl

### updater for voicemail
* * * * * /usr/share/astguiclient/AST_vm_update.pl

### updater for conference validator
* * * * * /usr/share/astguiclient/AST_conf_update.pl

### flush queue DB table every hour for entries older than 1 hour
11 * * * * /usr/share/astguiclient/AST_flush_DBqueue.pl -q

### reset several temporary-info tables in the database
30 8 * * * /usr/share/astguiclient/AST_reset_mysql_vars.pl

### remove old recordings more than 7 days old
#24 0 * * * /usr/bin/find /var/spool/asterisk/monitorDONE -maxdepth 2 -type f -mtime +7 -print | xargs rm -f

### Reboot nightly to manage asterisk issues and memory leaks - uncomment if issues arise
#30 6 * * * /sbin/reboot

### remove text to speech file more than 4 days old
#20 0 * * * /usr/bin/find /var/lib/asterisk/sounds/tts/ -maxdepth 2 -type f -mtime +4 -print | xargs rm -f

load spike again:

load average is 0.49 ony

1--
2--
3--
4 --------------------------------------->100 percent






any idea guys..?

[ruben23]

Guys, Any information about this or Idea...any help...Thanks

[williamconley]

since you do not have a stock opensuse and installed your vicidial manually ... it is quite possible you have a debian mysqlcheck process running.

you should use mytop or mtop and check your my.cnf and other debian/ubuntu documentation to see

you should also consider using a stock Vicibox machine for your mysql server to see if Kumba's basic mysql install resolves your issue with no further troubleshooting.

there is something to be said for avoiding reinventing the wheel. Kumba and many others have done the debugging on the Vicibox install and have a lot of man-hours into it.